Solved

Why does an optimized query takes the same elapsed time as the original query?

Posted on 2007-11-27
4
190 Views
Last Modified: 2012-05-05
Hi,

I have a complex querey that I have optimized (mostly with indexed views)

Before: EstimatedCPU=34, EstimateRows=2547409, TotalSubtreeCost=42,47
After:  EstimatedCPU=19, EstimateRows=1307739, TotalSubtreeCost=19,48

Why do I get the same elapsed time for both queries (before and after version takes 7 seconds) while I am alone on a test machine?

One thing to note however is the statistics produced by the option "set statistics time on" are consistent with the elapsed time. That is it reports around 7000 ms for each query. Thta is the timing I get from SQL Management studio.
0
Comment
Question by:512Thz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20363493
7000ms = 7s, and the Estimated Rows is quite a high value...
now, can you show us a bit more details about the query? it eventually runs in the same time because the estimates are wrong...
0
 
LVL 4

Author Comment

by:512Thz
ID: 20365190
Despite the labels state "estimated", the *actual* plan (not the estimated) was used to produce the numbers.  The query runs on a dual 2Ghz core.

## code snippet deleted at Asker's request
## modus_operandi, EE Moderator
0
 
LVL 4

Author Comment

by:512Thz
ID: 20365202
The optimized query replaced some of the resource hug sp with indexed views.

The actual plan gave me a 50% improve but elapsed time stayed almost the same.
0
 
LVL 4

Author Closing Comment

by:512Thz
ID: 31411328
Even if the plan is the *actual* plan, the cpu times are just estimated. The actual cpu time was much higher due to a function.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with SQL joins 9 56
Delete from table 6 48
Help Required 2 46
Why i am getting a star, SSMS does not show me any error. Division Error 5 38
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question