April 2, 2014 at 8:19 am
Hi,
I have two sql queries both looking at the same table but the difference between the two is the where clause on how it executes its dates functions:
--First query
Select *
From TableA
Where endDate >= convert(date,getdate()-1) and endDate < convert(date,getdate())
--Second query
Select *
From TableA
Where convert(datetime,convert(varchar, endDate , 103), 103) = convert(datetime,convert(varchar,getdate()-1, 103), 103)
What do I need to check on the Execution Plan that would tell me which one out of the two queries is better for performance.
Thanks
April 2, 2014 at 9:03 am
"Estimated Subtree cost" at the leftmost icon
April 2, 2014 at 9:11 am
There's a lot to it. I wrote a book on the topic (look in the links below). But, to get you started, I'd recommend this blog post.[/url]
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2014 at 9:13 am
SQL Guy 1 (4/2/2014)
"Estimated Subtree cost" at the leftmost icon
That is not a measure of which plan is better. Those are estimated values only and do not reflect any sort of measure in the real world. In fact, I would suggest you only use those values for comparing costs within a plan, never between two plans. Further, within the plan, be sure that you're not being fooled by things that never have cost or have very low costs, such as table variables or user defined table valued functions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2014 at 9:21 am
You shouldn't look at the subtree cost, that's an estimated value not a real one.
If you have an index on enddate, you might see a change from an index seek or index scan to a table scan.
April 2, 2014 at 9:54 am
Grant Fritchey (4/2/2014)
There's a lot to it. I wrote a book on the topic (look in the links below). But, to get you started, I'd recommend this blog post.[/url]
excerpt from the linked article:
"3. The most costly operations. Yes, I know you can’t trust these values because they are just estimates. Yes, the estimated operator cost is the same in both estimated and actual plans. No measurements of actual cost are taken by an execution plan. But these are the numbers available, so I use them.
April 2, 2014 at 9:59 am
SQL Guy 1 (4/2/2014)
Grant Fritchey (4/2/2014)
There's a lot to it. I wrote a book on the topic (look in the links below). But, to get you started, I'd recommend this blog post.[/url]excerpt from the linked article:
"3. The most costly operations. Yes, I know you can’t trust these values because they are just estimates. Yes, the estimated operator cost is the same in both estimated and actual plans. No measurements of actual cost are taken by an execution plan. But these are the numbers available, so I use them.
Yes, you look at it. But you'll notice in the linked article that it's not the only thing that you look at.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2014 at 10:05 am
To allow the best possible use of any existing indexes, approach 1 is vastly superior to 2. Neither is best, however. Instead, assuming "endDate" is a date/datetime/etc., use the method below.
Select *
From TableA
Where
endDate >= convert(char(8), getdate()-1, 112) and
endDate < convert(char(8), getdate(), 112)
Edit: Technically, since date has the lowest data precedence among all the date types, it can be safely used. However, since you want to be apply the same general technique to all situations, I'd stick with a varchar format that must be implicitly converted by SQL Server as long as the number of values that need converted is limited.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 2, 2014 at 10:42 am
SQL Guy 1 (4/2/2014)
Grant Fritchey (4/2/2014)
There's a lot to it. I wrote a book on the topic (look in the links below). But, to get you started, I'd recommend this blog post.[/url]excerpt from the linked article:
"3. The most costly operations. Yes, I know you can’t trust these values because they are just estimates. Yes, the estimated operator cost is the same in both estimated and actual plans. No measurements of actual cost are taken by an execution plan. But these are the numbers available, so I use them.
Having wrote the article, I know exactly what I meant, and it goes with what I said above, internally, these are the numbers you get and you will use them. But externally, comparing plan to plan, is not what I meant, and I don't say that in the article.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2014 at 11:29 am
Select *
From TableA
Where endDate >= convert(date,getdate()-1) and endDate < convert(date,getdate())
Any time you have consistent date math it's much faster to go
select * from
(select convert(date,getdate()-1) d1,convert(date,getdate()) d2) a
outer apply
(Select *
From TableA
Where endDate >= d1 and endDate < d2
) b
And if you're really using * as your result set
select * from
(select convert(date,getdate()-1) d1,convert(date,getdate()) d2) a
outer apply
(Select id
From TableA
Where endDate >= d1 and endDate < d2
) b
inner join TableA c on c.id = b.id
which does keylookups instead of table scan.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply