Why does performance increase when moving from a derived table to a temp table solution?

I read "Distributing SQL Server Execution Plans" from Grant Fritchey, and it helps me find out a lot why some queries are slow.

However, I am at a standstill with this case where a simple rewriting process is much faster.

This is my first attempt, and it takes 21 seconds. It uses a view:

-- 21 secs
SELECT *
  FROM Table1 AS o JOIN( 
    SELECT col1
    FROM    Table1
    GROUP BY    col1
    HAVING  COUNT( * ) > 1
) AS i ON ON i.col1= o.col1

My second attempt is 3 times faster and just moves the view to the temporary table. Now it is 3 times faster:

-- 7 secs
SELECT col1
INTO    #doubles
FROM    Table1
GROUP BY    col1
HAVING  COUNT( * ) > 1

SELECT *
FROM Table1 AS o JOIN #doubles AS i ON i.col1= o.col1

My main interest is why moving from a view to a temporary table improves performance so much, and not how to make it even faster.

, - , , () .

Xml : https://www.sugarsync.com/pf/D6486369_1701716_16980

D6486369_1701716_16979? DirectDownload = true

1

2 , , " ", ( , !). , . sqlplan 2 , .

, , temp table. , ( ), .

A ( ) 2- , stat , . 2 stat , . @Grant , , ?

2

, , .

, . , , .

, :

, XML, , . "" "EstimateRows"

  1. : SQL Sentry Plan Explorer http://www.sqlsentry.net/

, . .

enter image description here

  1. : SSMS http://www.ssmstoolspack.com/

,

enter image description here

,

+5
1

, . 800 1,2 . , , .

+3

All Articles