June 26, 2014 at 12:12 pm
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
We also don't want to use SEQUENCE as we have to create 50 sequence for 50 tables
We can't do trace flag 272
June 26, 2014 at 12:44 pm
I'm not sure I see a question, and it seems like homework to me. You say you want 'continuous' id values, yet you also indicate users can delete records. If you are always looking for the max id value, then adding 1 for new inserts, what happens if you have records 1 through 100, then someone deletes record 50, then inserts a new record? What id value should the new record get? 50 or 101?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
June 26, 2014 at 12:49 pm
Occassional delete is ok,
so it will be 101
The idea is how to create the values incrementally.
June 26, 2014 at 12:49 pm
And what about concurrency? It is possible you will get duplicate values when more than 1 person is executing this code at the same time. If your goal is to have a continuous number across tables why do you need 50 sequences? Why do you actually care what the value of your primary key is? You are using a "magic number" so what difference does it make if they are continuous?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 1:00 pm
that is reason using while statement and even if there is failure it is going to try again.
June 26, 2014 at 1:13 pm
carrieathomer (6/26/2014)
that is reason using while statement and even if there is failure it is going to try again.
What is the reason? Are you saying you are using a while statement to prevent concurrency issues? I am a bit confused. Again, why does it matter if the primary key is continuous?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 1:29 pm
i want continuous primary key integers without jump in values.
the while works until a successful insertion of a new record with unique primary key value
June 26, 2014 at 1:41 pm
carrieathomer (6/26/2014)
i want continuous primary key integers without jump in values.
Yes you have said that. My question is why? What purpose does continuous values for a primary key provide?
the while works until a successful insertion of a new record with unique primary key value
This type of "roll your own" identity comes up all the time. It is fraught with all sorts of challenges and never really works as well as it seems like it should. Why not just use a sequence? You said you don't want to manage 50 sequences but this looks like you are going to manage 50 procs instead.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 2:02 pm
do you see the url of this page, the id used inthe URL.
jumping 10000 everytime for server restart (happens in shared hosting environment) creates 7 digit or 8 digit id value in the url, it is kind of weird for a total of 5000 records my last entered primary key value is some 2345990.
ofcource for my 50 tables i need atleast 50x3 = 150 sproc's for adding deleting and updating.
Along with that i have to create another 50 sequences that is reason i don't want to use sequences.
purpose is to keep the id length minimal in the URL, just for the 200th record if i have to use 345344 as the primary key value looks very craze.
this problem was not there in sql server 2008, i used to use straightforward auto increment (1,1)
June 26, 2014 at 2:16 pm
carrieathomer (6/26/2014)
do you see the url of this page, the id used inthe URL.jumping 10000 everytime for server restart (happens in shared hosting environment) creates 7 digit or 8 digit id value in the url, it is kind of weird for a total of 5000 records my last entered primary key value is some 2345990.
ofcource for my 50 tables i need atleast 50x3 = 150 sproc's for adding deleting and updating.
Along with that i have to create another 50 sequences that is reason i don't want to use sequences.
purpose is to keep the id length minimal in the URL, just for the 200th record if i have to use 345344 as the primary key value looks very craze.
this problem was not there in sql server 2008, i used to use straightforward auto increment (1,1)
Yes the identity jump is certainly weird. I guess we will have to agree to disagree on the benefits. I don't see how that provides any benefit but you do. No biggie.
The point I was making is that you have to create 50 procs to get the next identity value, why not just create 50 sequences instead? Honestly you could have written them all by now and they are far less prone to collisions than a roll your own identity process. You would just access these sequences inside your insert proc. They way you are doing this you will have to recreate the same loop in all of them. And when you find out you need to change your logic (and you will) because of some logic error raised as a result of unseen issues with concurrency, you have to update all 50 insert procs. If you just use a sequence you don't have to worry about all this. If it were my system I would go with a sequence to avoid the hassles of trying to handle this on your own.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 2:33 pm
Sean Lange (6/26/2014)
carrieathomer (6/26/2014)
do you see the url of this page, the id used inthe URL.jumping 10000 everytime for server restart (happens in shared hosting environment) creates 7 digit or 8 digit id value in the url, it is kind of weird for a total of 5000 records my last entered primary key value is some 2345990.
ofcource for my 50 tables i need atleast 50x3 = 150 sproc's for adding deleting and updating.
Along with that i have to create another 50 sequences that is reason i don't want to use sequences.
purpose is to keep the id length minimal in the URL, just for the 200th record if i have to use 345344 as the primary key value looks very craze.
this problem was not there in sql server 2008, i used to use straightforward auto increment (1,1)
Yes the identity jump is certainly weird. I guess we will have to agree to disagree on the benefits. I don't see how that provides any benefit but you do. No biggie.
The point I was making is that you have to create 50 procs to get the next identity value, why not just create 50 sequences instead? Honestly you could have written them all by now and they are far less prone to collisions than a roll your own identity process. You would just access these sequences inside your insert proc. They way you are doing this you will have to recreate the same loop in all of them. And when you find out you need to change your logic (and you will) because of some logic error raised as a result of unseen issues with concurrency, you have to update all 50 insert procs. If you just use a sequence you don't have to worry about all this. If it were my system I would go with a sequence to avoid the hassles of trying to handle this on your own.
in the end somebody trying to help and understand my concern the jump issues. yes you are right i could have written 50 sequence in one hour or two.
I just thought to discuss other ways to create primary key values similar to autoincrement(1,1).
tommorrow i have to start with sequence with no cache 1,1.
June 27, 2014 at 6:16 am
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
why so much of difference between sequence and Select max ( select max which is unconventional)
June 27, 2014 at 7:24 am
Why have NO CACHE on your sequence? It does a good job of keeping the values sequential but all it really does is prevent gaps like you might find in an identity (which apparently was acceptable in 2008). Put the cache back in and see how the performance changes.
Also, not sure that using a while loop is a great test here because the looping itself requires overhead. I would use a tally table for the inserts. But with the nested loop approach for the MAX I guess you really can't do that. What is the reason for the nested loop? It seems overkill to me but maybe I am missing something.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2014 at 7:42 am
no cache is to create continuous values even when there is a server restart.
while loop is to make successfull insertion when there is a failure
June 27, 2014 at 1:46 pm
A tally table - whether a physical one or one that you would build on the fly using ROW_NUMBER() - can churn through 1M records in under 2 seconds. You will not get any gaps that way.
Not sure what failure you're trying to account for with the loop (you're not actually checking that the insert happened within the loop, so failures can happen and you'd know nothing about it), but in my experience, a process running 20 minutes is a lot more likely to fail that a single,just about instantaneous, query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply