Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reusing Identities Expand / Collapse
Author
Message
Posted Saturday, February 8, 2003 12:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dPriyankara/reuseidentities.asp
Post #9858
Posted Thursday, February 13, 2003 7:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 28, 2007 8:14 AM
Points: 14, Visits: 1
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.intID
WHERE 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.intID
WHERE 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



Post #53789
Posted Thursday, February 13, 2003 8:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107

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 Priyankara


mcp mcse mcsd mcdba
Post #53790
Posted Wednesday, March 5, 2003 12:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 30, 2006 3:20 PM
Points: 34, Visits: 1
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.




Michael R. Schmidt
Developer
Post #53791
Posted Wednesday, March 5, 2003 8:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107
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
Post #53792
Posted Thursday, March 6, 2003 5:03 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 9:27 AM
Points: 6,800, Visits: 1,917
In a high volume environment, wouldnt you run into issues with two users trying to use (re-use) the same key?

Andy
http://www.sqlservercentral.com/columnists/awarren/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #53793
Posted Thursday, March 6, 2003 9:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107
Hi Andy,

Why can’t we run this insertion process within a transaction?



Dinesh Priyankara


Edited by - Dinesh on 03/06/2003 9:17:45 PM
Post #53794
Posted Friday, March 7, 2003 6:34 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 9:27 AM
Points: 6,800, Visits: 1,917
You can of course, and that is the right answer. Downside will be (or may be anyway!) blocking.

Andy
http://www.sqlservercentral.com/columnists/awarren/




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #53795
Posted Wednesday, February 18, 2004 3:48 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 6, 2013 2:16 AM
Points: 693, Visits: 124

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 NextIdentityValue
FROM OrderHeader t1
WHERE 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>

>



Post #100899
Posted Tuesday, November 1, 2005 10:25 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:00 PM
Points: 421, Visits: 364

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 NextIdentityValue
from OrderHeader t1 Left Outer join OrderHeader t2
ON T1.IDENTITYCOL+IDENT_INCR('OrderHeader')=t2.IDENTITYCOL
Where 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 int
Select @Increment= IDENT_INCR('OrderHeader')
Select top 1 t1.IDENTITYCOL + @Increment As NextIdentityValue
from OrderHeader t1 Left Outer join OrderHeader t2
ON T1.IDENTITYCOL + @Increment = t2.IDENTITYCOL
Where 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 NextIdentityValue
FROM OrderHeader t1
WHERE 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.

 

 



Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/
Post #234365
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse