selecting dataset with row numbers

Question

selecting dataset with row numbers

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

VERY interesting. Thanks, Orlando, especially for the datatype match reminder.

What's really interesting (interesting as in "very odd") is that the changes to the code actually made the table variable code perform a bit worse in 2k5 on my older/single CPU box.

Here're the (typical) results from my original code in 2k5 from one of my previous posts...

(400000 row(s) affected)

========== Temp Table Solution ==========

SQL Server Execution Times:

CPU time = 2625 ms, elapsed time = 4868 ms.

(399769 row(s) affected)

========== Table Variable Solution ==========

SQL Server Execution Times:

CPU time = 5968 ms, elapsed time = 6595 ms.

(399769 row(s) affected)

Here are the (typical) results for the modified code in 2k5 on the same box...

========== Temp Table Solution ==========

SQL Server Execution Times:

CPU time = 2735 ms, elapsed time = 4383 ms.

(399769 row(s) affected)

========== Table Variable Solution ==========

SQL Server Execution Times:

CPU time = 7406 ms, elapsed time = 8081 ms.

(399769 row(s) affected)

Just to make the post more complete, here are the (typical) 2K results from the same box...

(400000 row(s) affected)

========== Temp Table Solution ==========

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(399769 row(s) affected)

SQL Server Execution Times:

CPU time = 3578 ms, elapsed time = 4247 ms.

========== Table Variable Solution ==========

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(399769 row(s) affected)

SQL Server Execution Times:

CPU time = 5328 ms, elapsed time = 8834 ms.

One might gather from this that they did, indeed, increase the performance for Temp Tables in 2k5 but it would also appear that they made the performance slightly worse for Table Variables... at least for some of the older hardware. I certainly wouldn't cast that as a worthwhile observation because of the limited testing we've done collectively but it does make testing the different methods during development a bit more important if every millisecond is important for a particular application. The Delta-T between the two methods is almost insignificant on newer hardware like yours and it almost boils down to personal choice.

Just to mention it, both my 2K and 2K5 installions are the Developer's Edition. 2K is at sp4 and 2K5 is at sp3 (yeah... I'm a bit lazy when it comes to sp's).

Thanks again for doing the testing on your box, Orlando.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Anytime. The drop in performance when removing a data type mismatch is odd. I cannot even venture a wag as to why that might have occur.

It's not of much consequence to me since I don't use 2000 much these days but I suppose we could add another use-case to the list where one should default to using a temp table over a table variable.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato