June 10, 2009 at 4:16 am
Hi,
I'm a bit confused if my I've improved the performance of my query or not.
Originally there are 7 UNIONs in my query and I've removed 5 of them by using OR on one query instead. This improved the Estimated Operator Cost of the whole query by 50%, and adding a condition "Column_primaryKey>0" also improved the operator Cost a little more. However, when checking the STATISTICS IO, the original query has less logical reads, one of the tables has a logical read of 144 which jumped to 1391 on the new query. Moreover, the STATISTICS TIME for the new query is
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 12 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
while it was
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
for the original one.
Can anyone tell me if I'm on the right track? Is the new query performing better? In the execution plan, it all looks fine, it seems to be using indeces correctly.
June 10, 2009 at 4:25 am
Hi,
The first thing to remember is that the estimate execution plan is "estimated" and not "actual", so you can't trust it to be what will actually happen.
Second the %'s from the execution plans are not always correct.
look at the times I would say the first query is faster.
Please could you supply both the "actual" execution plans by saving them as .sqlplan and zipping it up and uploading to the thread.
Secondly could you give us both the queries you used, along with table/index definitions and some sample data 🙂
Thank you
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 10, 2009 at 7:21 am
Percentage costs are based on the estimated costs and as Christopher pointed out, estimated costs are just that, estimated. They're based on the statistics available to the optimizer and are affected by the parameters supplied, parameter sniffing, age of statistics, oh, all kinds of things. While they're somewhat useful when tuning queries, the most useful numbers are execution times, scans and reads. From what you've got here, I agree with Christopher. The original query was faster.
"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
June 11, 2009 at 2:23 am
So if I have 3 tables with the following number of rows 7949, 837, 1848 and their respective logical reads in the query are 52, 58, and 144. So far it's good right? However, when table 2 and 3 each have around 20k records and table 1 with 1.8million records, the query runs for half a minute. Looking at it's execution plan, most of the cost comes from clustered index seek. Do you think something must be done with the unions? Will rewriting the query help much?
Thanks so much!
June 11, 2009 at 2:36 am
As mentioned, please supply more information in the form of table structures queries and execution plans.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 11, 2009 at 6:15 am
t_king (6/11/2009)
So if I have 3 tables with the following number of rows 7949, 837, 1848 and their respective logical reads in the query are 52, 58, and 144. So far it's good right? However, when table 2 and 3 each have around 20k records and table 1 with 1.8million records, the query runs for half a minute. Looking at it's execution plan, most of the cost comes from clustered index seek. Do you think something must be done with the unions? Will rewriting the query help much?Thanks so much!
It's just not possible to provide an answer without knowing more. For example, it's doing an index seek, right? Is it an ordered seek, a range seek? What do the joins look like? Is it using hash, merge, loop? How many rows are feeding into each side of the join? If it's a loop, you should see a small number of rows on top & a larger number of rows on bottom. If not, it's inefficient.
There's just too much to know & look at & understand, most of which is answered with the execution plan, the query & some DDL (sample data is nice too), that it's flat out not possible to give you any meaningful answers here.
"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
June 14, 2009 at 4:48 pm
When doing the testing make sure you drop the buffers on TEST Server
DBCC cleanDROPBUFFERS
So run your statement get your times etc.
Then tweak program and do dbcc dropbuffers and run again.
DBCC cleanDROPBUFFERS
Otherwise the 2nd time it seem quicker but thats because it sitting in the buffer cache the 2nd time.
Or quest for toad is awesome you put in your query and say hey optimizer it ...it will then give you 20 different ways to rewrite it with the savings...best tool i have seen.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply