November 17, 2009 at 12:01 pm
This:
WHILE 1 = 1
BEGIN
IF GETDATE() = GETDATE() BEGIN
SET @DUMMY = 'TRUE'
END
ELSE BEGIN
SET @DUMMY = 'FALSE'
BREAK
END
END
(something funky here at work, I had to remove the DECLARE and PRINT statements to get this post to post)
is not the same as this:
select getdate(), getdate();
The former will actually make two separate calls to getdate(), the later only one.
November 17, 2009 at 12:07 pm
Well then, parallelism must be working on my system here because I have run this 10,000 times and don't have a discrepancy between them. System here has dual quad core processors.
November 17, 2009 at 12:11 pm
Lynn Pettis (11/17/2009)
This:(something funky here at work, I had to remove the DECLARE and PRINT statements to get this post to post)
I agree. I clicked 'Quote' a couple of times and it pulled the wrong post into the editor.
November 17, 2009 at 12:31 pm
Okay, I have just run some more tests.
If I generate the test values using GO 10000 to load a table I can get it to fail about 9 times out of 10000. HOWEVER, if I am doing it using a Tally table to load 10,000 values as one batch, I don't.
Here is my code for those of you who would like to test it also.
November 17, 2009 at 12:33 pm
Tom Garth (11/17/2009)
Lynn Pettis (11/17/2009)
This:(something funky here at work, I had to remove the DECLARE and PRINT statements to get this post to post)
I agree. I clicked 'Quote' a couple of times and it pulled the wrong post into the editor.
No that usually happens when you click the quote button after someone else has posted a comment that you haven't seen yet. I try to refresh the page just before using the QUOTE button.
The problem I was having only occurs to me when I am at work. For instance, my last post I couldn't get to post until I removed my code and uploaded it as an attachment to the post instead.
November 17, 2009 at 12:37 pm
Lynn Pettis (11/17/2009)
Well then, parallelism must be working on my system here because I have run this 10,000 times and don't have a discrepancy between them. System here has dual quad core processors.
I have 8 cores too and I didn't get any difference in the milliseconds either. However, it was the hours that were different (but that's the time zone issue we are discussing). Suppose if you have a slow server you could potentially see more differences even in the minutes if you have an extremely busy and slow server.
November 17, 2009 at 12:38 pm
I clicked 'Quote' a couple of times and it pulled the wrong post into the editor.
Same here. That is annoying. Sometimes it works right, sometimes you have to click the quote on the message below the one you're replying to.
November 17, 2009 at 12:38 pm
Back to the code posted earlier. Anyone have an explanation as to why one may generate a small number of values that are differnet and the other doesn't?
I guess I should check the execution plans.
November 17, 2009 at 12:41 pm
I also got a False Response even though you are wrong. I ran the statements and clearly have different times for UTC.
November 17, 2009 at 12:41 pm
You TallyHo you.
It doesn't really apply to the QoD. I only followed that path because I was curious, and now I know. Will it ever come in handy to know it? Probably not.
I would be willing to bet that if you ran it enough, they would eventually be not equal.
November 17, 2009 at 12:44 pm
Okay, I think I have my answer. In the first query, using the Tally table to generate a batch of 10,000 entries, the scalar function IS called once. In the other code that is a single SELECT statement executed 10,000 times the function is called twice for each insert.
Anyone else agree or disagree with that statement?
I'm not going to bother trying to load the plans here, they are easily generated in SSMS if you wish to look at them.
November 17, 2009 at 12:47 pm
Yes. I think that typical system clock resolution is 0.060 seconds. In spite of CPU speed, the clock doesn't register every millisecond.
cengland0 (11/17/2009)
I clicked 'Quote' a couple of times and it pulled the wrong post into the editor.
Same here. That is annoying. Sometimes it works right, sometimes you have to click the quote on the message below the one you're replying to.
November 17, 2009 at 12:53 pm
Okay, nope, it is called twice. Once for each column. Changed the Tally version to generate 100,000 records. Guess what, Date1 was the same for every row and Date2 was the same for every row but the were different from each other by 0.003 milliseconds.
Okay, this gets us to what Jeff always says, test against 1,000,000 rows (even though 100,000 worked here).
So lets recap:
1) Scalar functions are called however many times they are used in a single query.
2) GETDATE() <> GETUTCDATE() (not equal)
3) GETDATE() == GETUTCDATE() (equivalent)
Can we agreement here???
November 17, 2009 at 12:53 pm
What about daylight savings time? Getdate() accounts for that because the server time changes. Does the UTC time take into account? I doubt it. So when we are in Eastern time, the difference between 4 or 5 hours.
Doesn't the UK also observe daylight savings time and does that affect UTC?
November 17, 2009 at 12:54 pm
Just wondering... did you write the QOD Lynn?
Viewing 15 posts - 106 through 120 (of 203 total)
You must be logged in to reply to this topic. Login to reply