January 17, 2020 at 1:39 pm
I was doing some testing to see if it's more efficient to use an identity column or to use ROW_NUMBER() to simulate a unique ID for the result set. My thinking was that ROW_NUMBER() would need to do some type of evaluation of the result set in order to assign the value where IDENTITY would use the seed and simply increment. Using a result set of 4 million records the ROW_NUMBER() approach was a full second faster. This is the opposite of what I thought would happen. Just an academic observation but I thought it was interesting.
January 17, 2020 at 2:22 pm
Not that shocking. To use an IDENTITY column, you need to write data somewhere, whereas the ROW_NUMBER() is just a calculation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2020 at 2:36 pm
Actually I wrote data in both instances. An example is below
-- Approach 1
CREATE TABLE FOO (iid int identity, ky int)
INSERT FOO(ky) SELECT ky FROM myTable ORDER BY ky
-- Approach 2
CREATE TABLE FOO (iid int, ky int)
INSERT FOO(iid, ky) SELECT ROW_NUMBER() OVER(ORDER BY ky) as iid, ky FROM myTable ORDER BY ky
January 17, 2020 at 2:47 pm
Try the test again, but use a SEQUENCE. You should see the same results. It's because of the processing needed for maintaining an identity value versus the calculation of the ROW_NUMBER.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2020 at 3:17 pm
Thanks!
ST
January 17, 2020 at 4:04 pm
I'm thinking that SEQUENCE is going to be about 4 times slower. That's just a guess.
Sounds crazy but this is fascinating to me because I work with some really large numbers of rows in the things I do and milliseconds do matter here. I'm putting together some of my own tests that I'll share but I've got an appointment I have to go to.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy