Solved

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

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql calculate reminders 11 76
How to show the last employee that updated a record 5 46
SQL HELP 2 90
MS SQL Server - Looking to filter rows based on column value 3 38
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…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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