SQL Subquery Help

• caseyo

  SSC Rookie

  Points: 47

  More actions

  July 16, 2010 at 11:29 am

  #1194254

  LutzM (7/16/2010)

  ---

  If you would post the execution plans for both queries it would help us a lot to see what changes need to be made to tune the code. 3 seconds for processing 30K rows still sound fairly high. How did you measure it? Did you use SET STATISTICS TIME ON/OFF?

  Here is the Info. Not sure how to copy and past the execution plans so here it is typed out.

  Version 1

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 1 ms.

  SQL Server Execution Times:

  CPU time = 31 ms, elapsed time = 256 ms.

  (26370 row(s) affected)

  (19 row(s) affected)

  SQL Server Execution Times:

  CPU time = 8158 ms, elapsed time = 2056 ms.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 1 ms.

  Execution Plan:

  Query 1: 41%

  Insert cost 0%

  Table Insert Cost 88%

  Top Cost 0%

  Index Seek (NonClustered) Costs: 12%

  Query 2 59%

  Select Costs 0%

  Sort Costs 0%

  Compute Scalar Cost 0%

  Stream Aggregate Costs 0%

  Sort Costs 0%

  Parallelism Costs 1%

  Hash Match (Partial Aggregate) Cost 44%

  Filter Cost 3%

  Nested Loops (Left Outer Join) Cost 30%

  Table Scan #tblResults Cost 7%

  Constant Scan Cost 14%

  Version 2

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:

  CPU time = 1449 ms, elapsed time = 6209 ms.

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 39 ms.

  SQL Server Execution Times:

  CPU time = 593 ms, elapsed time = 628 ms.

  SQL Server Execution Times:

  CPU time = 609 ms, elapsed time = 3231 ms.

  SQL Server Execution Times:

  CPU time = 9001 ms, elapsed time = 9033 ms.

  Execution Plan:

  Query1 45%

  Select into Cost 0%

  Table Insert Costs 69%

  Top Cost 0%

  Parallelism Cost 6%

  Sort Cost 23%

  Filter Cost 0%

  Nested Loops (Left Outer Joins) Cost 1%

  Compute Scalar Cost 0%

  Index Seek (nonClustered) Cost 0%

  Constant Scan Cost 1%

  Query 2 53%

  Insert Cost 0%

  Index Insert Clustered Cost 58%

  Sort Cost 39%

  Table Scan Cost 3%

  Query 3 2%

  Select Cost 0%

  Sort Cost 0%

  Compute Scalar Cost 0%

  Compute Scalar Cost 0%

  Stream Aggregate Cost 8%

  Clustered Index Scan Cost 91%