Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-11-27
4
Medium Priority
?
216 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
  • 3
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

972 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