Solved

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

Posted on 2007-11-27
4
188 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

829 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