SQLServerCentral / Discuss Content Posted by Dinesh Priyankara / Article Discussions / Article Discussions by Author / Reusing Identities / Latest Posts

RE: Reusing Identities

G.R.Prithiviraj Kulasingham — Tue, 01 Nov 2005 22:25:00 GMT

I am replying after a looooooooooooong time

On the first method the author is using a not so optimized query. He is using different methods in each queries (sub query vs outer join and Min vs top 1). More than that, Should we declare a variable, get the result into the variable and select the variable?

I tried with Left outer join and the results are really better. (I tried with both statistics time and statistics IO ON)

Select top 1 t1.IDENTITYCOL + IDENT_INCR('OrderHeader') As NextIdentityValuefrom OrderHeader t1 Left Outer join OrderHeader t2ON T1.IDENTITYCOL+IDENT_INCR('OrderHeader')=t2.IDENTITYCOLWhere t2.IDENTITYCOL is null

This is the result:

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 2 ms.2210

Table 'OrderHeader'. Scan count 2210, logical reads 4450, physical reads 0, read-ahead reads 0.

SQL Server Execution Times: CPU time = 26 ms, elapsed time = 26 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

Later I introduced a variable to get the Indentity increment first the the results are even better

Declare @Increment intSelect @Increment= IDENT_INCR('OrderHeader')Select top 1 t1.IDENTITYCOL + @Increment As NextIdentityValuefrom OrderHeader t1 Left Outer join OrderHeader t2ON T1.IDENTITYCOL + @Increment = t2.IDENTITYCOLWhere t2.IDENTITYCOL is null

The results are slightly better:

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 2 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.2210

Table 'OrderHeader'. Scan count 2136, logical reads 4439, physical reads 0, read-ahead reads 0.

SQL Server Execution Times: CPU time = 7 ms, elapsed time = 7 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

Finally, I changed the min into top 1. the results are almost to my previous results:

SELECT top 1 IDENTITYCOL + IDENT_INCR('OrderHeader') As NextIdentityValueFROM OrderHeader t1WHERE IDENTITYCOL BETWEEN IDENT_SEED('OrderHeader') AND 32766 AND NOT EXISTS (SELECT * FROM OrderHeader t2 WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + IDENT_INCR('OrderHeader'))

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time: CPU time = 3 ms, elapsed time = 3 ms.2210

Table 'OrderHeader'. Scan count 2210, logical reads 4440, physical reads 0, read-ahead reads 0.

SQL Server Execution Times: CPU time = 15 ms, elapsed time = 26 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

RE: Reusing Identities

Razvan Socol — Wed, 18 Feb 2004 03:48:00 GMT

Dinesh wrote:

As per the output, there are 20086 logical reads and it has taken 200 ms for the first method. But in second method there are only 19 logical reads and the execution time is less considerable.
That’s why I selected to continue in my way. But there may be a side that I have not seen but you can see. So, try on this and see whether how this T-SQL solution suit for you.

Actually, I think that you are not considering two things:

1. The overhead of creating a table of 30000 rows is much greater than the difference between the two queries. If you prefer to store this table permanently, you have to consider that it's size will be added to each backup and that you need to adjust the number of rows as more data will be added in the main table.

2. The example from Books Online doesn't assume that the seed and the interval are 1, instead it determines them and this is the main cause of the many logical reads. Try:

SELECT MIN(IDENTITYCOL) + 1 as NextIdentityValueFROM OrderHeader t1WHERE IDENTITYCOL BETWEEN 1 AND 32766 AND NOT EXISTS (SELECT * FROM OrderHeader t2 WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + 1)

You will get only about 120 logical reads (instead of about 20000) and the execution time is much better.

Razvan Socol

RE: Reusing Identities

Andy Warren — Fri, 07 Mar 2003 06:34:00 GMT

You can of course, and that is the right answer. Downside will be (or may be anyway!) blocking.Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Reusing Identities

Dinesh Priyankara — Thu, 06 Mar 2003 21:03:00 GMT

Hi Andy,Why can’t we run this insertion process within a transaction?Dinesh PriyankaraEdited by - Dinesh on 03/06/2003 9:17:45 PM

RE: Reusing Identities

Andy Warren — Thu, 06 Mar 2003 05:03:00 GMT

In a high volume environment, wouldnt you run into issues with two users trying to use (re-use) the same key?Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Reusing Identities

Dinesh Priyankara — Wed, 05 Mar 2003 20:58:00 GMT

Hi Mike,Actually what we want is, reuse removed identities. So, whatever the method, whether TOP 1 or MIN, you get desired result. But if you consider about performance, TOP 1 is better.[font=Verdana]Dinesh Priyankara[font=Verdana]mcp mcse mcsd mcdba

RE: Reusing Identities

Michael R Schmidt — Wed, 05 Mar 2003 12:24:00 GMT

First: With a Clustered PK the data is *always* returned in the natual table order, so order by is not necessary. Second: why do we need to get the min occurence, all we really care about is a missing ident right? so just give me the first ident and I will be happy.

RE: Reusing Identities

Dinesh Priyankara — Thu, 13 Feb 2003 08:21:00 GMT

Hi Jeremy,I agree with you if the OrderHeader table has no index created with IDENTITY column. But as a general rule, we always create clustered or nonclustered unique index.If no indexes available, Of course you have to use one of your statements. Then there will be slight performance problems but still the method is the winner.Dinesh Priyankaramcp mcse mcsd mcdba

RE: Reusing Identities

Jeremy — Thu, 13 Feb 2003 07:28:00 GMT

I don't think your second method is correct. You are looking for the lowest number in the first gap, correct? In that case you'll either have to add an ORDER BY clause to your TOP query, or use "SELECT Min(intNumber)" instead of the TOP clause. The point is that the only way to guarantee that results are ordered - and thus to guarantee that the TOP 1 clause delivers the lowest gap - is to add an order by clause.Of course, in practice, due to the clustered primary key on the Numbers table you might well witness the correct result every time you try it without the ORDER BY, but there's no certainty. This behaviour might change with differences in the clustered index on the other table, differences in paralellism, or changes in the underlying db engine in future SQL Server versions.I would either rewrite your method as:SELECT TOP 1 intNumber FROM OrderHeader RIGHT OUTER JOIN tb_Numbers ON tb_Numbers.intNumber = OrderHeader.intIDWHERE intID IS NULL AND intNumber < = (SELECT MAX(intID) FROM OrderHeader) ORDER BY tb_Numbers.intNumber- or -SELECT Min(intNumber)FROM OrderHeader RIGHT OUTER JOIN tb_Numbers ON tb_Numbers.intNumber = OrderHeader.intIDWHERE intID IS NULL AND intNumber < = (SELECT MAX(intID) FROM OrderHeader) It would be interesting to see if the differences in performance with these (IMHO) correct methods are still as large, and which of these two alternative methods would be the fastest.Jeremy

Reusing Identities

Dinesh Priyankara — Sat, 08 Feb 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dPriyankara/reuseidentities.asp