Search code examples
sqlsql-serverquery-optimization

Should I Use a Temp Table To Optimize?

I have a somewhat complex query that I need to access for the IDs in order to delete from multiple tables, something along the lines of:

DELETE FROM Table1 WHERE ID IN ( -- Query here -- )
DELETE FROM Table2 WHERE ID IN ( -- Query here -- )

Would selecting the query into a temp table be more efficient than writing out the entire query twice, or is it just visually cleaner?

SELECT ( -- Query here -- ) INTO #Temp 
DELETE FROM Table1 WHERE ID IN ( SELECT * FROM #Temp )
DELETE FROM Table2 WHERE ID IN ( SELECT * FROM #Temp )

Also, am open to other suggestions that I may have overlooked.

Thanks in advance

Solution

  • Completely agree with Jeroen Mostert. Try it and see. You could although try to use CTE for deletion or table variables and select distinct values of IDs there in case you have no distinct values of IDs in your query. And if you have a lot of data to delete, try to create additional indexes in temp table.