Writing Faster T-SQL

Question

Writing Faster T-SQL

Viewing 3 posts - 31 through 32 (of 32 total)

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

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 1

That's surprising! Is there a way to verify this? I always thought that the entire Select Statement is executed atomically - meaning all at once. This seems to suggest that the engine would execute the first getdate and then the other getdate as if there were two selects getting executed.

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

Lynn Pettis (11/21/2009)
Jeff Moden (11/21/2009)
Understanding is the key... thanks, Saurabh. :blush:
Actually, Jeff, in a query such as this:
select getdate(), getdate();
the getdate() function is actually called twice. Found this out during the duscussion regarding bitbuckets QotD a few days ago.

Heh.... although I can see someone taking what I said that way because I left out the words "each instance of", I agree. It's just the same way that more than one instance of the RAND would work except that more than one instance of GETDATE() in the same query has a very, very high probability of having the same value.

SELECT TOP 100

RAND() AS UnseededRAND1,

RAND() AS UnseededRAND2,

GETDATE() AS Date1,

GETDATE() AS Date2

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

And it also depends on how the query is written as well. A straight SELECT query from a Tally table for instance all the values were the same for 100,000 records. However, I was also using a query with a cross join, and there when I hit 100,000 records the values differed by 3 milliseconds. I just can't find the query I used during that discussion. Chances are if I saved it, it was at work, not here at home.