I am writing a procedure and find myself using the same select statement with the same where clause a lot. Currently the table holds over 55000 rows and grows about 100 or so rows each day. The two where clauses cut the data down to a few hundred rows in total.

I run the exact same select statement over a dozen times and the data results will always be the same, within the same run. Would it be better, performance-wise, to keep running the same select statement or should I do an insert into select to fill a temp table with only the data I need and pull from there instead?

Select statement ran (with some some IDs redacted):

SELECT WorkDay1, WorkDay1Brea, StartBreak, StrtTm
FROM dbo.DriverTimes
WHERE DriverTime.DrvrID = @DriverID
AND CONVERT(VARCHAR(12), dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND PryllID IN (

This select is ran a total of 8 times for each Driver and there are 40 some odd drivers.

  • Is the amount of data you are selecting Huge ? Normally when you use a permanent table, you get the data at that instant, no need to do any other work vs creating a temp table and then inserting data and then processing it. Also, you will need to create Indexes on temp table for the optimizer to efficiently get the rows from it, else it will be just heap. Please elaborate your question more. – Kin Shah Oct 31 '13 at 19:52
  • @Kin What do you consider Huge? The table it's self contains over 5500 rows, the first where clause cuts that down to just over 50000, the second where clause will cut that down to just a few hundred columns. The second where clause is doing a compare against a part of a multi-column PK. – Matthew Verstraete Oct 31 '13 at 20:01
  • your calculation does not match up The table it's self contains over 5500 rows, the first where clause cuts that down to just over 50000 – Kin Shah Oct 31 '13 at 21:24
  • 55000 is the correct number, sorry for the type-o – Matthew Verstraete Nov 1 '13 at 14:14
  • Can you show us this select query with the two where clauses and explain how you use it further? – ypercubeᵀᴹ Nov 4 '13 at 22:42

If the volume of data you're retrieving is large - roughly, millions of narrow rows, or hundreds of thousands of wide rows, or tens of thousands of rows with large objects - you should probably cache the data in a temp table and reuse that. If you're performing significant processing to get the data - such as a table scan - you should probably cache it. Aside from speed, I can think of a couple other reasons to dump the data to a temp table:

  • It minimizes the length of time the source table is locked (this can be very important).
  • It gives you the opportunity to rename fields or use more restrictive data types (such as NOT NULL constraints) that only apply to your data set.
  • It gives you a chance to add calculated fields or indexes which are not present in the original.

...and against:

  • It entails a little extra development work.
  • It requires additional space, in memory, on disk, or both.

That said, this may be premature optimization; how long does your sproc take to run?

| improve this answer | |
  • The original proc takes between 1.5 and 5 minutes, causing a timeout in the calling application. I have been tasked to rewrite the proc to improve speed so it does not cause the app to time out. At this point in the code it is only taking 1 second to run but I am just being to write it and would not have to go back and rewrite it again to change to a temp table if that is the way to go, performance wise. – Matthew Verstraete Nov 1 '13 at 14:16

This can depend on many things, so unfortunately the only general advice we can give without more individuation is to try each option and benchmark it (remembering to test for different sizes of data if it is likely to vary).

If the data is expected to vary with each lookup you will have to re-select each time or try merge the statement into the others. For a small number of rows use a table-variable unless your use enacts one of that option's limitations.

| improve this answer | |

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