Suppose that I run five queries against the same table with the same starting filter, like:
SELECT ColumnDate, ColumnKey, ColumnValue
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 FROM #temp WHERE ColumnValue = 'sf')
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?