Why is declaring DateTime speeding this up? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Why is declaring DateTime speeding this up?

Author

Topic

rkruis
Starting Member

28 Posts

Posted - 2010-04-08 : 19:55:09

I have two select statements that are the same. One is extremely faster and the only difference is the faster statement I declare the two datetimes as variables. The other statement, they are strings.

From what I know, comparing DateTime is much faster than comparing strings. And in this case, the field that it is comparing against is a DateTime. So I am assuming it is casting the string as a DateTime then comparing which is why it is slower. Is this true?

How could I make the slower statement faster? I have already changed the DateTime compares to use BETWEEN, which helps some.

Slower One (Approx. 5 min 24 seconds)

select top 100 pos.item as item, 
sum(pos.quantity) as quantity, 
count(*) as cnt, 
sum(value_num) as amount 
from posdata pos 
where receipt_no in ( select distinct p.receipt_no from posdata p, va_pos v 
where v.register_num = p.register_num 
and p.server_time > '2010-03-01 00:00:00.000' 
and p.server_time < '2010-03-31 00:00:00.000'
and v.start_time > '2010-03-01 00:00:00.000'
and v.start_time < '2010-03-31 00:00:00.000'
and p.server_time >= v.start_time 
and p.server_time <= v.end_time 
and rule_name like '%gas%' 
and p.value_char like '4002' ) 
and pos.value_char = '4002' 
and pos.server_time > '2010-03-01 00:00:00.000'
and pos.Server_time < '2010-03-31 00:00:00.000'
group by pos.item order by quantity desc

Faster one (Approx. 32 seconds)

DECLARE @StartTime as datetime
DECLARE @EndTime as datetime

set @StartTime = '2010-03-01 00:00:00.000'
set @EndTime = '2010-03-31 00:00:00.000'


select top 100 pos.item as item, 
sum(pos.quantity) as quantity, 
count(*) as cnt, 
sum(value_num) as amount 
from posdata pos 
where receipt_no in ( select distinct p.receipt_no from posdata p, va_pos v 
where v.register_num = p.register_num 
and p.server_time > @StartTime
and p.server_time < @EndTime
and v.start_time > @StartTime
and v.start_time < @EndTime
and p.server_time >= v.start_time 
and p.server_time <= v.end_time 
and rule_name like '%gas%' 
and p.value_char like '4002' ) 
and pos.value_char = '4002' 
and pos.server_time > @StartTime
and pos.Server_time < @EndTime
group by pos.item order by quantity desc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-08 : 20:03:47

It's not the conversion that is slowing it down. That part would be very quick. My guess is that the second one has a bad execution plan in cache or you are encountering parameter sniffing. Try clearing the procedure cache before each query so that you compare to the same baseline.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rkruis
Starting Member

28 Posts

Posted - 2010-04-08 : 20:23:27

I do not think caching is what is making it faster because I have tried it over several test. But I can be wrong. How can I clear the procedure cache to prove this?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 20:27:12

The first one may also do a lot of IO that the second one doesn't have to do, because the pages are still in memory after calling the first one.

I've actually seen the opposite happen, on more complex queries that use joins to subqueries. With hard coded dates the quest would return instantly, but once the date was put into a variable, it took 20 seconds to run. I believe the reason was that a variable inside the subquery forced SQL server to evaluate the subquery for every join candidate. With hard coded date, there was no reason the subquery would return different values on subsequent joins, so it only needed to be run once.

There are 10 types of people in the world, those that understand binary, and those that don't.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-08 : 20:31:12

To clear the procedure cache, you run DBCC FREEPROCCACHE.

I didn't say that caching was making it faster. I'm not referring to the cached data. I was referring to the cached execution plan. The queries could have different execution plans, which can easily be verified by adding that option in your query window. If they do have different execution plans, then you should clear the procedure cache. And if that still doesn't work, then you would consider adding index hints to help the query optimizer.

For all performance tests, you should run these two commands before each test:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Query1...

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Query2...

With that you'll have the same baseline and therefore accurate results.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-08 : 20:33:04

By the way, the DBCC commands affect all queries on your system. So if this is being done on a production system, you should consider doing it during off-hours. Hopefully you are doing your performance tests in a proper test environment and can run these at will.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-04-10 : 03:34:38

Have a look at the Query Plan and see the difference (if any) between the two?


SET SHOWPLAN_TEXT ON
GO

 ... put query here ...

SET SET SHOWPLAN_TEXT OFF
GO

Note that string dates should be in the form "yyyymmdd" (no hyphens) to be unambiguous - so you might like to try changing "2010-03-01 00:00:00.000" to "20100301" - but I doubt that would make a difference to performance.


where receipt_no in
(
	select distinct p.receipt_no 
	from	posdata p,
		va_pos v 
	where v.register_num = p.register_num 
		and p.server_time > '2010-03-01 00:00:00.000' 
		and p.server_time < '2010-03-31 00:00:00.000'
		and v.start_time > '2010-03-01 00:00:00.000'
		and v.start_time < '2010-03-31 00:00:00.000'
		and p.server_time >= v.start_time 
		and p.server_time <= v.end_time 
		and rule_name like '%gas%' 
		and p.value_char like '4002'
)

would be better done as an EXISTS - you are making a list with "select distinct p.receipt_no" which requires finding all values and then de-duping them, whereas EXISTS would terminate with "success" on the first one that it found.

You are also repeating the inner WHERE clause in the outer query - but there is no need surely? That may be causing SQL to make a different query plan.


	and pos.value_char = '4002' 
	and pos.server_time > '2010-03-01 00:00:00.000'
	and pos.Server_time < '2010-03-31 00:00:00.000'

is also performed by the inner query, so receipt_no can only be within that range anyway.

Thus I think it can be optimised to:


SELECT TOP 100 pos.item as item, 
	sum(pos.quantity) as quantity, 
	count(*) as cnt, 
	sum(value_num) as amount 
FROM	posdata AS pos 
WHERE	EXISTS
(
	SELECT *
	FROM	va_pos AS v 
	WHERE 	v.register_num = pos.register_num 
		and v.start_time > '20100301'
		and v.start_time < '20100331'
		and pos.server_time >= v.start_time 
		and pos.server_time <= v.end_time 
		and rule_name like '%gas%' 
) 
	and pos.value_char = '4002' 
	and pos.server_time > '20100301'
	and pos.Server_time < '20100331'
GROUP BY pos.item
ORDER BY quantity desc

and even then the


	and pos.server_time > '20100301'
	and pos.Server_time < '20100331'

may be superfluous.

Just looking at the query, and the actual date-limits set, did you really mean to use GREATER THAN rather than GREATER THAN OR EQUAL (ditto for LESS THAN instead of LESS THAN OR EQUAL)?


		and v.start_time > '20100301'
		and v.start_time < '20100331'
...
	and pos.server_time > '20100301'
	and pos.Server_time < '20100331'

but that has next-to-nothing to do with using Parameters instead of String values for the WHERE clause ranges. Parameters will be better, anyway, because the query will be cached (which is a separate issue to whether Parameter Sniffing will help / hinder)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources