RE: same query different results

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 01/13/05 

Date: Thu, 13 Jan 2005 07:19:06 -0800

> Question 1:
> I understand why the first one is slower, but why does it affect a
> different number of rows?

The diff between 1 and 2 is because the expressions:

DATEDIFF(d, seq.time_of_creation, GETDATE()) > 500

and

seq.time_of_creation < DATEADD(d, -500, GETDATE())

are not the same. Let us see an example. Suppose that a row has the value
'2003-09-01 09:55:39.557' in column [time_of_creation], then:

declare @TIME datetime
SET @TIME = GETDATE()
select @time
select DATEDIFF(d, '2003-09-01 09:55:39.557', GETDATE())
select DATEADD(d, -500, @TIME)

Result:

2005-01-13 10:03:02.037
500
2003-09-01 10:03:02.037

select DATEDIFF(d, '2003-09-01 09:55:39.557', GETDATE()) = 500, so it is not
greather than 500 and statement 1 will not bring it.

select DATEADD(d, -500, @TIME) is equal 2003-09-01 10:03:02.037 and
2003-09-01 09:55:39.557 is less than the result, so second statement will
bring the row.

If there is another row with the value 2003-09-01 10:03:02.037 in column
[time_of_creation], then second statement will not bring it, but because
third statement was executed calling function getdate() again (SET @TIME =
GETDATE()), the elapsed time between 2 and 3 could be a millisecond so,

DATEADD(d, -500, @TIME) will be equal to 2003-09-01 10:03:02.038 and
2003-09-01 10:03:02.037 is less than this value. third statement will bring
it.

You should grab the value of GETDATE() in the first line and use @TIME for
subsequent calculation. You should be consistent, do not use @TIME in one and
GETDATE() in another.

One more comment, to compare times, you should clean the cache before
executing next select statement. Use DBCC FREEPROCCACHE to do this.

AMB

 

"Jo Segers" wrote:

> Hi when I execute the statements below I get the following results:
>
> DECLARE @TIME DATETIME
>
> -- 1 Slow version with DATEDIFF
> SET @TIME = GETDATE()
> SELECT *
> FROM dbo.sosobject AS seq
> WHERE DATEDIFF(d, seq.time_of_creation, GETDATE()) > 500
> PRINT 'Slow version: ' + CAST(DATEDIFF(ms, @TIME, GETDATE()) AS VARCHAR)
>
> -- 2 Fast version with DATEADD
> SET @TIME = GETDATE()
> SELECT *
> FROM dbo.sosobject AS seq
> WHERE seq.time_of_creation < DATEADD(d, -500, GETDATE())
> PRINT 'Fast version: ' + CAST(DATEDIFF(ms, @TIME, GETDATE()) AS VARCHAR)
>
> -- 3 Fastest version with DATEADD and a VARIABLE
> SET @TIME = GETDATE()
> SELECT *
> FROM dbo.sosobject AS seq
> WHERE seq.time_of_creation < DATEADD(d, -500, @TIME)
> PRINT 'Fastest version: ' + CAST(DATEDIFF(ms, @TIME, GETDATE()) AS VARCHAR)
>
> Results (in miliseconds):
>
> (4212 row(s) affected)
> Slow version: 406
>
> (4280 row(s) affected)
> Fast version: 360
>
> (4280 row(s) affected)
> Fastest version: 356
>
> Question 1:
> I understand why the first one is slower, but why does it affect a
> different number of rows?
>
> Question 2:
> Version 3 is the fastest but what is best done using a variable or the
> function in the query like in version 2?
>
> Thanks in advance,
>
> Jo Segers.
>