Why would using a temp table be faster than a nested query?

by

“should be” is a hazardous thing to say of database performance. I have often found that temp tables speed things up, sometimes dramatically. The simple explanation is that it makes it easier for the optimiser to avoid repeating work.

Of course, I’ve also seen temp tables make things slower, sometimes much slower.

There is no substitute for profiling and studying query plans (read their estimates with a grain of salt, though).

More Related Contents:

  1. What columns generally make good indexes?
  2. Avoid Naming User Stored Procedures SP% or SP_%
  3. OR Operator Short-circuit in SQL Server
  4. Does query plan optimizer works well with joined/filtered table-valued functions?
  5. SQL Server insert if not exists best practice [closed]
  6. An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server
  7. Are GUID collisions possible?
  8. Why do table names in SQL Server start with “dbo”?
  9. Add alternating row color to SQL Server Reporting services report
  10. LIKE vs CONTAINS on SQL Server
  11. How Do I Split a Delimited String in SQL Server Without Creating a Function?
  12. What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
  13. Is there any difference between DECIMAL and NUMERIC in SQL Server?
  14. How to enable bulk permission in SQL Server
  15. Should every User Table have a Clustered Index?
  16. How to load an XML file into a database using an SSIS package?
  17. TSQL: Create a view that accesses multiple databases
  18. How to connect to MS SQL Server using Inno Setup?
  19. Check if a temporary table exists and delete if it exists before creating a temporary table
  20. SQL count rows in a table
  21. What is the sql connection string I need to use to access localhost\SQLEXPRESS with Windows Authentication or SQL Authentication?
  22. SQL Error with Order By in Subquery
  23. Executing set of SQL queries using batch file?
  24. How to change default language for SQL Server?
  25. T-SQL incrementing counter based on change in a column value
  26. The request failed or the service did not respond in a timely fashion?
  27. Joining tables from different servers
  28. How to get a list of all the MS SQL Server instances on the local machine?
  29. mysqldump equivalent for SQL Server
  30. Forgot SQL Server Password

Leave a Comment