October 6, 2009 at 2:56 pm
Hi. I have been playing with two versions of a query. Both return cost relative to batch 100%.
The table has a clustered index on ManufacturingOrder + non clustered index on ManufacturingOrderId and
SystemType
[Code]
--Option 1
SELECT mo.ManufacturingOrder,mo.ManufacturingOrderId,GETDATE() AS
CurrentDate,mo.DueDate,DATEDIFF(dd,mo.DueDate,GETDATE()) AS Overdue,
CASE mo.SystemType
WHEN 'F' THEN 'Firm'
WHEN 'R' THEN 'Released'
WHEN 'I' THEN 'Issued'
END AS MOStatus
FROM ManufacturingOrders AS mo
WHERE mo.DueDate < GETDATE() AND mo.SystemType <> 'C' AND mo.SystemType <> 'P' AND mo.SystemType <> 'D'
[/Code]
[Code]
--Option 2
SELECT mo1.ManufacturingOrder,mo1.ManufacturingOrderId,GETDATE() AS
CurrentDate,mo1.DueDate,DATEDIFF(dd,mo1.DueDate,GETDATE()) AS Overdue,
CASE SystemType
WHEN 'F' THEN 'Firm'
WHEN 'R' THEN 'Released'
WHEN 'I' THEN 'Issued'
END AS MOStatus
FROM ManufacturingOrders AS mo1
WHERE EXISTS
(SELECT mo2.ManufacturingOrder
FROM ManufacturingOrders AS mo2 WHERE mo2.ManufacturingOrder = mo1.ManufacturingOrder
AND mo2.SystemType <> 'C' AND mo2.SystemType <> 'P' AND mo2.SystemType <> 'D')
AND DueDate < GETDATE()
[/Code]
I have attached the estimated execution plans for both options. To be honest the query does not run that
long, it is simply a learning exercise.
Question: Can the query be written better?
Thanks for any advice,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 6, 2009 at 3:18 pm
Option 1 seems to be better. You should look at the IO cost: execute SET STATISTICS IO ON and then the queries.
Also, run both queries in one batch - both of them cannot be 100%. If they are ran in one batch, you will see relative difference between them.
You might try to add DueDate to the key of the [IX_ManufacturingOrders_SystemType] index or you can try to add it as included column to this index. Then this index will cover the query and the Option 1 may use it. Compare the IO cost, it should be even lower than Option 1.
Regards
Piotr
...and your only reply is slàinte mhath
October 6, 2009 at 3:31 pm
Hi Piotr. You were correct when run as a batch option 1 was 46%, option 2 54%, run individually each were 100% (which I suppose makes sense!). Not really looked at estimated execution plan before now. Option 1 clustered index scan cost 97%. I am unable to modify the indexes as this is a third party app.
Thanks,
Phil.
Update: SET STATS Cost 0% for both optons
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 6, 2009 at 3:40 pm
So the difference is not that big. I suppose it is a matter of the amount of data matching the search criteria.
You can add another index if you cant modify existing one just to test, you can always drop it. Preferably on a test environment 😉
Regards
Piotr
...and your only reply is slàinte mhath
October 7, 2009 at 7:24 am
2Tall (10/6/2009)
Hi Piotr. You were correct when run as a batch option 1 was 46%, option 2 54%, run individually each were 100% (which I suppose makes sense!). Not really looked at estimated execution plan before now. Option 1 clustered index scan cost 97%. I am unable to modify the indexes as this is a third party app.Thanks,
Phil.
Update: SET STATS Cost 0% for both optons
Just remember that cost relative to batch is based on estimated execution costs. This is a very poor choice for comparison between procedures because cost estimates can be at very wide variance from actual costs on the queries.
"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
October 7, 2009 at 9:14 am
Thanks guys.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply