CODEWITHSUNDEEP
sql-server
mattalxndr
mattalxndr

Reputation: 9418

Should subselects in FROM clauses be optimized into derived tables in SQL Server?

Query 1:

SELECT au_lname, au_fname, title 
FROM (SELECT au_lname, au_fname, id FROM pubs.dbo.authors WHERE state = 'CA') as a 
JOIN pubs.dbo.titleauthor ta on a.au_id=ta.au_id 
JOIN pubs.dbo.titles t on ta.title_id = t.title_id

Query 2:

DECLARE @DATASET TABLE ( au_lname VARCHAR(10), au_fname VARCHAR(10), id INT );

INSERT @DATASET SELECT au_lname, au_fname, id FROM pubs.dbo.authors WHERE state = 'CA';

SELECT au_lname, au_fname, title 
FROM @DATASET DATASET as a 
JOIN pubs.dbo.titleauthor ta on a.au_id=ta.au_id 
JOIN pubs.dbo.titles t on ta.title_id = t.title_id

My assumption is that these two queries are not very different from each other, from the standpoint of performance. Is Query 2 ever an improvement over Query 1?

As a side note, I know that the subquery in this example doesn't even need to be there. This is a simplified version -- the actual query I'm dealing with is much more complicated.

Upvotes: 3

Views: 122

Answers (3)

LoztInSpace
LoztInSpace

Reputation: 5697

No. Let the optimiser do its thing. By introducing your own temporary tables/variables you are taking away options that would otherwise be available. By telling it exactly what you want (i.e. a normal query) it can do its best. By trying to second guess it, you're making it break down into usually unnecessary steps.

Upvotes: 1

JNK
JNK

Reputation: 65157

If you have more than a couple rows of data, query 2 is in all likelihood worse than query 1.

  • You are SELECTing the data twice - once into the table variable and once again to return it
  • Table variables generally do not perform great - they have no stats or indexes and contrary to popular belief still write to tempdb and the log

If it were me I would rewrite the first query without a derived table, which is really unnecessary:

SELECT 
    au_lname
    ,au_fname
    ,title 
FROM 
    pubs.dbo.authors a 
INNER JOIN 
    pubs.dbo.titleauthor ta 
        on a.au_id=ta.au_id 
INNER JOIN 
    pubs.dbo.titles t 
        on  ta.title_id = t.title_id
WHERE 
    a.state = 'CA'

Note For more info than you ever wanted on table variables and temp tables, see this epic answer from Martin Smith.

Upvotes: 5

JC Ford
JC Ford

Reputation: 7066

The only thing Query 2 buys you is the ability to store additional data in the table variable (for instance, if your procedure generates extra meta data to support its purpose). What it costs you is the ability to use any indexes on the physical table.

Upvotes: 1

Related Questions