Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why? I must do queries with large data (millions records) and I want to be sure I am using the best choice.

CREATE TABLE A(
    id BIGINT IDENTITY(1,1) NOT NULL,
    field1 INT NOT NULL,
    field2 VARCHAR(50) NULL,
);

CREATE TABLE B(
    id INT IDENTITY(1,1) NOT NULL,
    field1 VARCHAR(10) NULL,
    field2 INT NULL
);

INSERT INTO A 
    (field1,field2)
VALUES 
    (1,'a'),(2,'b'),(3,'c'),(2,'d'),(5,'e'),
    (6,'f'),(7,'g'),(8,'h'),(9,'i'),(2,'j');

INSERT INTO B 
    (field1,field2)
VALUES 
    ('a',1),('b',2),('c',3),('d',4),('e',5),
    ('f',6),('g',7),('h',8),('i',9),('j',2),('k',3);

DECLARE @begin INT=0,@end INT=200;

Derived tables

/*derived tables*/
SELECT 
    C.id,C.field1,C.field2,C.field3 
FROM
(
    SELECT
        A.id,A.field1,A.field2,B.field2 AS field3, 
        ROW_NUMBER() OVER (ORDER BY A.id) AS iRow
    FROM 
        A INNER JOIN B ON A.field1=B.id
) C
WHERE iRow BETWEEN @begin AND @end;

Temporary tables

/*temporary tables*/
CREATE TABLE #C (
    iRow INT IDENTITY(1,1),
    id bigint,
    field1 INT,
    field2 VARCHAR(50),
    field3 INT );

INSERT INTO #C 
    (id,field1,field2,field3)
SELECT TOP 1000 
    A.id,A.field1,A.field2,B.field2 
FROM  
    A INNER JOIN B ON A.field1=B.id
ORDER BY 
    A.id;

SELECT id,field1,field2,field3 
FROM #C 
WHERE iRow BETWEEN @begin AND @end;

DROP TABLE #C;
share|improve this question
2  
Answers to this question seem to address what you're looking for: stackoverflow.com/questions/2326395/… –  user16484 Feb 11 at 15:12
5  
Read this awesome answer by @MartinSmith –  Kin Feb 11 at 15:47
1  
@a_horse_with_no_name I am aware of that, but OP (in the comments) asked for temp table advantages and that article covers the length and breath of #temptables :-) –  Kin Feb 11 at 17:21
1  
I tend to use temporary tables when im dealing with large datasets because you can index the temp table. Derived tables are kept and manipulated in memory (unless it spools it to tempdb for processing) so for smaller datasets its usually enough –  Nabil Becker Feb 11 at 19:35
1  
You have a SELECT TOP 1000 without any ORDER BY, that's not good. I think you need to add ORDER BY A.id; for the two ways to be equivalent. –  ypercube Feb 11 at 20:32
show 10 more comments

2 Answers

In general, it depends on your particular queries and the size of the temporary results.

For the specific scenario given, which is paging, temp tables are totally unnecessary. Why would you want to save 1000 rows into a temp table only to then return the 1st 200? Using a 'derived' table or a CTE in this scenario is much more efficient, because the full result set does not have to be stored anywhere, or in most cases even produced. For example when requesting the 1st page of 200 rows only the first 200 rows will have to be retrieved from the base tables (assuming existing indexes can support the sort order requested in the query).

share|improve this answer
    
+1, though I would add that using derived tables also lets the Query Optimizer deal with both queries at the same time. This can be good or sometimes bad, again "depending on the particular query". Which is why it is always good to test both (on real data, not sample data) rather than guess :-). –  srutzky Mar 17 at 13:18
add comment

@user16484 already directed you to Which one have better performance : Derived Tables or Temporary Tables in the comment.

Also see Temp Table 'vs' Table Variable 'vs' CTE. which also covers derived tables.

A quick summary: #temp tables can be indexed, can have UNIQUE indexes/constraints, can be references more than one time in the same query, can be referenced (FROM or JOIN) by more than one query. Derived tables can be referenced (FROM or JOIN) once in one query.

Performance-wise, pull out Profiler for SQL:BatchCompleted and RPC:Completed, watch the Read, Write, CPU, and Duration columns, and see what a few runs of derived tables vs. #temp tables vs. indexed #temp tables does for each particular query.

In general - if you're going to use it more than once, #temp table wins. If you're joining a lot of tables, #temp table probably wins. If you're joining only a few tables, derived table has a reasonable chance of winning. Benchmark it!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.