1

I have 2 different queries for identifying dups (below). The only difference between the 2 queries is that one uses a CTE and the other uses a #Temp table.

Does anyone know why the CTE is so much faster than the #Temp table (0:20 seconds VS. 1:22)?

I would rather use the CTE but I need to run 2 STATEMENTS using the CTE (DELETE from CTE and then INSERT INTO table FROM CTE) but SQL Server only allows you to write one STATEMENT on the CTE.

Query 1:

WITH DUPS AS(
   Id,
   Column1,
   Column2,
   Column3,
   RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id)
   FROM mytable
)
  Select top 1 * FROM DUPS WHERE RN > 1 

Query 2:

 SELECT    
           Id,
           Column1,
           Column2,
           Column3,
           RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id)
       INTO #DUPS
       FROM mytable

       Select top 1 * FROM #DUPS WHERE RN > 1 
  • 2
    Why do you need to delete anything from the CTE? Why not just ;WITH CTE as () INSERT dbo.table SELECT * FROM CTE WHERE rn > 1;? – Aaron Bertrand Oct 20 '15 at 19:17
  • @AaronBertrand Good point. You are right, I don't actually need the DELETE but I do need to INSERT from the CTE into 2 different tables. The dups will go into a DUPS table and the rest of the records will go into a separate table. So I think I will still need to include a Temp Table in there somewhere as I will only be able to do 1 insert from the CTE. – Juan Velez Oct 20 '15 at 19:31
  • 1
    That is right, you can't insert into two tables from any single statement. – Aaron Bertrand Oct 20 '15 at 19:50
5

I believe if you look at the execution plans you'll see that the CTE gives you a better plan because it can take better advantage of the TOP. The Temp table will have to create the table in TempDB, persist all of the data to disk, and then select the top record. If you remove the TOP from both queries I bet you'll get much closer performance. That being said I would generally expect the CTE to out perform the temp table in a single use scenario like you have here since the CTE is doing less work than creating/inserting into a temp table.

This is probably just sample code but it is worth mentioning your results are non-determinate because you're using TOP without an ORDER BY clause.

For further reading you should check out this question. This answer to that question seems to apply to your situation in particular.


For the implied second half of your question:

Since you say you need to use the data from the CTE at least twice then you have four options:

  1. Copy and paste your CTE to both places
  2. Use the CTE to insert data into a Table Variable, and use the data in the table variable to perform the next two operations.
  3. Use the CTE to insert data into a Temp Table, and use the data in the temp table to perform the next two operations.
  4. Write a better tailored CTE. I assume you're doing different things so the queries must be slightly different.

I tend to prefer the option 2 (table variable) or option 4 (tailored CTE's) approach. I don't like the duplication and extra maintenance of copy/pasted CTE's. I also like the explicitly reduced scope of the table variable over a temp table. For an authoritative treatment on the differences between table variables and temp tables check out this answer. As far as choosing between options 2 and 4; that will depend on whatever your profiling dictates and/or what constraints you have to work within.

  • 1
    I removed the TOP from both queries and the CTE still ran at 22 seconds but the #Temp table actually ran about 30 seconds faster. So the TOP definitely had an effect on processing time of the #TEMP table as you pointed out. – Juan Velez Oct 20 '15 at 19:51
1

I would rather use the CTE but I need to run 2 STATEMENTS using the CTE (DELETE from CTE and then INSERT INTO table FROM CTE) but SQL Server only allows you to write one STATEMENT on the CTE.

You can do both the delete and and insert in a single statement, using the OUTPUT clause:

with cte as (
 select ...
 )
delete from cte
output deleted.id into #deleted_ids;
  • 1
    They actually want to insert one portion of the rows from the CTE into one table, and the remainder into a second table. While the syntax allows it, deleting from the CTE here actually serves no practical purpose. – Aaron Bertrand Oct 20 '15 at 21:11
  • @AaronBertrand Yup, I am trying to insert the records from the CTE into 2 different tables without having to create an additional temp table or CTE. So actually, if I delete the duplicate records, I think I can then reference deleted.id to join to my original table used to create the CTE and insert the non duplicate rows into 1 table and then join to the original table again and insert the duplicated rows (using delete.id) into another table. I am looking for what is most efficient. As long as I can reference deleted OUTPUT more than once I think it should work. – Juan Velez Oct 21 '15 at 20:48
  • 1
    @PokerPlayer23 Nope, that's not going to work, sorry. – Aaron Bertrand Oct 21 '15 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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