0

Suppose that I run five queries against the same table with the same starting filter, like:

SELECT ColumnDate, ColumnKey, ColumnValue
FROM QueryTable
WHERE ColumnDate > DATEADD(DD,-1,GETDATE())
---- each other query would have additional criteria

All queries hit the same exact table five times each with further filters and grabbing one record. The starting filter is always the same though - and it generally grabs less than 1% of the total records on the table.

An alternative would be to read the table once, keeping it in a subset - like a CTE or temp table - then filtering on that subset in further queries or subqueries in the SELECT statement, which would be a fraction of the table with only one read of main table. Temp table example:

SELECT ColumnKey
(SELECT ColumnKey FROM #temp WHERE ColumnValue = 'sf')
FROM #temp
WHERE ColumnValue = 'nyc'

On a smaller data set of less than 10K records, I don't see an impact in testing this alternative; as this scales to higher amounts, where the date could be filtering a two day period out of twenty years, would this start to perform better?

Or, another better question to ask is what is a better "best practice" than hitting a table five times with a similar query, if any?

  • (1) why do you think an additional AND with (ORs) for your 5 conditionals would be less efficient than 5 separate queries? It might be, but do you know? (2) a CTE and #temp table are not the same. Common misconception that a CTE is materialized but it is not. If you're trying to prevent multiple reads due to multiple references, a #temp table is the way to go. CTEs can be re-evaluated/re-materialized for each reference. I put together some info on CTEs here. – Aaron Bertrand Feb 8 '17 at 17:17
1

As Aaron pointed out a CTE is a tool for developers to reuse code. It will not encourage the SQL Server query optimizer to spool the results to disk so they can be reused.

You're talking about trading space for time. It's very difficult for anyone to issue a statement that will be a best practice for all scenarios. The benefits of using a temp table here depend on the table size, which indexes are defined on the table, the workload, the workload on tempdb, etc.

For an example of when using a temp table is clearly better, consider a billion row table with no indexes. Out of those billion rows none of them match the initial filter condition. You don't use any space but avoid four full table scans of the large table.

For an example of when using a temp table is clearly worse, suppose that there is a heavy workload on the server and space in tempdb is limited. Your query could fail or cause other queries to fail if it loads too much data to a temp table.

My advice would be to start with the simplest code or the code that makes the most sense to you and to measure the performance of it. Depending on the results consider adding indexes, using a temp table, or other tricks to speed up the performance of the queries.

In the systems that I work on its very common for different users to run similar or sometimes even identical queries. I'm sure that if we took all of the queries after the fact it would have been possible to create temp tables that would have decreased overall server resource utilization. However, SQL Server is designed to deal with this kind of workload. Pages from tables that are needed for queries are loaded into the buffer cache. Subsequent queries on the same tables will often run faster because data is already in memory.

| improve this answer | |

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.