Solved

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

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

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now