Discuss the following statement performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Discuss the following statement performance

Author

Topic

homertechnologies
Starting Member

12 Posts

Posted - 2014-06-24 : 13:46:44

Discuss the following sql query with respect to performance in an applicaiton involving more number of concurrent users creating and deleting records. The objective is to create continuous primary key integer values.

Table name: SitePage

Column DataType
--------- -----------

PageID BigINT

PageName nchar(10)

Query to insert new record


DECLARE @intFlag INT
SET @intFlag = 0
WHILE (@intFlag =0)
BEGIN
BEGIN TRY
Insert into SitePage (PageID, PageName) values ((Select max(PageID)+1  from SitePage), CONCAT('Page ID : ', (Select max(PageID)+1  from SitePage)));
set @intFlag = @@rowcount
END TRY
BEGIN CATCH
SET @intFlag=0
END CATCH

END
GO

we don't want to use auto increment integer value for primary key because of the following reason

http://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

homertechnologies
Starting Member

12 Posts

Posted - 2014-06-24 : 13:48:03

the second

(Select max(PageID)+1 from SitePage)

is only for example, i would not use it in real world scenario, if this query is good

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-24 : 14:22:18

quote:
Originally posted by homertechnologies

The objective is to create continuous primary key integer values.

Complete waste of time, effort and processing power.

homertechnologies
Starting Member

12 Posts

Posted - 2014-06-24 : 14:32:51

quote:
Originally posted by Lamprey

quote:
Originally posted by homertechnologies

The objective is to create continuous primary key integer values.
Complete waste of time, effort and processing power.

What do you recommend

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-24 : 14:38:07

Don't worry about an ID being non contagious; Just use an IDENTITY column.

homertechnologies
Starting Member

12 Posts

Posted - 2014-06-24 : 14:52:47

Unfortunately, that is the requirement. to create contagious records.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-24 : 14:56:57

Not directed at you, but the requirement, that is a BS requirement. That's going to have a pretty heavy performance penalty. If you can't get rid of that requirement, I wish you luck on the project.

homertechnologies
Starting Member

12 Posts

Posted - 2014-06-24 : 15:09:26

The reason for asking is, current sql server 2012 creates huge gaps in the primary key value when it restarts. eg. if the primary key value is 10000 after a restart it reseeds from 20000

Even if i am going to create only 10000 records with primary key value from 1 - 10,000, because of the jumping of values i would be having 10000 records with primary key values from 1 - 100,000 . This happens more in shared hosting environment.

If i were to create a forum application, you can imagine how the id will look. id=1001, id=10001, id = 20001 even if am having only 1000 posts.

My issue is the jumping of primary key values

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-24 : 15:19:28

If you, or something else, perceives non-contiguous values a problem, then you have to pay a price to try and keep them contagious. Plain and simple.

A solution might be to set up a sproc or something that fires when the server is reset to try and check the table values against the seed value and reseed the table is the sequence has jumped up..?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-24 : 15:55:23

quote:
Originally posted by homertechnologies

http://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

Then use TF 272.

Regarding discussion of performance for your query, there is no discussion here. It is not scalable. Use an identity column with TF 272.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-24 : 17:51:48

You can also use a SEQUENCE object, with NOCACHE hint.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

homertechnologies
Starting Member

12 Posts

Posted - 2014-06-24 : 19:20:55

quote:
Originally posted by SwePeso

You can also use a SEQUENCE object, with NOCACHE hint.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thanks, sequence also posses the same problem generating discontinuous primary key numbers after a restart

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-24 : 19:22:44

quote:
Originally posted by homertechnologies

quote:
Originally posted by SwePeso

You can also use a SEQUENCE object, with NOCACHE hint.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thanks, sequence also posses the same problem generating discontinuous primary key numbers after a restart

You can have one or the other:
1. No gaps
2. Performance

Pick ONE.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

homertechnologies
Starting Member

12 Posts

Posted - 2014-06-24 : 19:27:51

Tara,

gaps are ok, but not huge gaps like jumping from 200 to 1000, 2000 to 10000.

BTW, how this forum primary key values are generated

TOPIC_ID=194863

mostly they are continous without huge gaps

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-24 : 19:29:30

Then use trace flag 272.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

homertechnologies
Starting Member

12 Posts

Posted - 2014-06-24 : 19:33:14

It did not work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-24 : 19:40:49

File a bug with MS.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-24 : 20:04:04

quote:
Originally posted by homertechnologies

Thanks, sequence also posses the same problem generating discontinuous primary key numbers after a restart

Not with the NOCACHE hint.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

homertechnologies
Starting Member

12 Posts

Posted - 2014-06-25 : 09:34:33

Ok i tried Sequence with no cache, it worked even with after restarting sql server and again killing the sql server process

Also, i tried to insert 1 million rows in a test table using sequence and using the code snippet which i posted first. Surprisingly, the query with with select max created 1 million rows in 11:11 (11 mins 11 secs)

And the query with sequence create 1 million rows in 19:34 (19 mins 11 secs). now i am not sure which is good

Table : SitePage


CREATE TABLE [dbo].[SitePage](
	[PageID] [bigint] NOT NULL,
	[PageName] [nchar](50) NOT NULL,
 CONSTRAINT [PK_SitePage] PRIMARY KEY CLUSTERED 
(
	[PageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

here are the queries

1. Query to create Sequence


create Sequence PageTableSequence
    START WITH 1
    INCREMENT BY 1
    NO CYCLE
    NO CACHE
	;

2. Query to create 1 million records using Select max(id)


DECLARE @intFlag INT
SET @intFlag = 0
Declare @maxrecords bigint
set @maxrecords = 0
while(@maxrecords<1000000)

BEGIN
WHILE (@intFlag =0)
BEGIN
  BEGIN TRY
  Insert into SitePage (PageID, PageName) values ((Select max(PageID)+1  from SitePage),'Some Page Name');
  set @intFlag = @@rowcount
  
  END TRY
  BEGIN CATCH
  SET @intFlag=0
  END CATCH

END
set @maxrecords = @maxrecords+1
set @intFlag=0
END

GO

3. Query using Sequence to insert 1 million records


Declare @maxrecords bigint
set @maxrecords = 0


while(@maxrecords<1000000)


BEGIN
  
 
  Insert into SitePage (PageID, PageName) values (next value for PageTableSequence, 'some page name');
 


  set @maxrecords = @maxrecords+1

END

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-27 : 09:14:58

Do you think that is a fair comparison and somewhat relevant to a real time service?

The sequence method is multiuser safe, whereas the MAX(ID) is prone to duplicate values because you are using READ COMMITTED transaction level.
It is possible that two simultaneous users are getting the exact same value.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

homertechnologies
Starting Member

12 Posts

Posted - 2014-06-27 : 11:52:40

quote:
Originally posted by SwePeso

Do you think that is a fair comparison and somewhat relevant to a real time service?

The sequence method is multiuser safe, whereas the MAX(ID) is prone to duplicate values because you are using READ COMMITTED transaction level.
It is possible that two simultaneous users are getting the exact same value.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

That is purpose of using try catch, when there is a failure it will try till a successful insertion.

I am not sure whether it is a fair comparison or not. I am trying to explore and discuss outside thebox

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources