SQL Query Optimization. 1

[slobad23]

If I was to perform this query, which would you expect to be faster in SQL Server 2008 with a table containing 100_000 records:

First version (with #temp table):

SELECT one
  , two
  , ...
  , n
INTO #results
FROM table AS temp

SELECT convert(...one)
  , convert(...two)
  , convert(...)
  , convert(...n)
FROM #results

Second version (directly from the table):

SELECT convert(...one)
  , convert(...two)
  , convert(...)
  , convert(...n)
FROM table

I have conflicting opinions from people here and the times recorded (within SQL Server Management Studio and through C# code) fluctuate slightly showing them to be almost identical.

I would just like to know what the experts consider best practice here.

Thanks in advance,

Jake

"It doesn't do anything"

"Correction, it DOES nothing

 

[gmmastros]

Without a doubt, I would expect the second one to be faster.

When you use a temp table, the data is stored in TempDB and actually written to the disk. So, the first query must read the data from disk and copy it to disk in TempDB, and then return the results.

The second one reads from disk and returns the results.

There are 2 ways to measure performance. Time and File I/O. Obviously time is important because you want your queries to be as fast as possible. Many people underestimate the importance of File I/O. File I/O is more important in production environments where multiple queries may be running simultaneously.

I've run a similar query in my database. The table has approximate 330,000 rows. The following is the output of the statistics io

Begin Method 1

Table 'MapStreets'. Scan count 1, logical reads 5147, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Results____________________________________________________________________________________________________________0000000001F8'. Scan count 1, logical reads 1028, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Begin Method 2
Table 'MapStreets'. Scan count 1, logical reads 5147, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Method 1, Logical Reads 5147 + 2018 = 7165
Method 2, Logical Reads 5147

There are less logical reads in method 1 because I only used 4 columns of the 15 or so columns in the table.

Try it yourself:

SET NOCOUNT ON
SET STATISTICS IO ON

Print 'Begin Method 1'

SELECT one
  , two
  , ...
  , n
INTO #results
FROM table AS temp

SELECT convert(...one)
  , convert(...two)
  , convert(...)
  , convert(...n)
FROM #results 

Print 'Begin Method 2'

SELECT convert(...one)
  , convert(...two)
  , convert(...)
  , convert(...n)
FROM table

Run the query. When it is done, click on the messages tab.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[slobad23]

Perfect response. Thank you very much!

"It doesn't do anything"

"Correction, it DOES nothing

 

[SQLBill]

The main reason (in my opinion) for using a #temp table or a table variable is when the subset of the data has to be reuseable.

For example...you have a table with 100,000 records. Your query has to do a couple of things with the data, but you only are using 25,000 rows of the original data. Instead of your query going back to the table multiple times for the same data, you would put the data you need into a #temp table or table variable and then use that for your multiple queries.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

I agree with Bill.

Basically, only use temp table when you need to. As you move up to newer and newer versions, there are less and less reasons to use temp tables.

I have seen situations where temp tables will speed up execution for exactly the reason Bill describes.

I've also seen developers over-use temp tables. One significant drawback to temp tables is that the query engine no longer has access to indexes that may exist in the real table. Of course, you can index temp tables, but there is a considerable over-head to doing that.

My normal approach is this...

1. Write the query without using temp tables (or table variables) when I can. If the performance is acceptable, I'm done. To me, acceptable usually means less than 50 milliseconds, but it really depends on the complexity of the query and the amount of data involved.

2. If I cannot get acceptable performance, then I start thinking about using temp tables and table variables.

3. If I cannot get acceptable performance from temp tables or table variables, then I start looking at indexes.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[slobad23]

Hi guys,

Thanks for the additional details on when you "would" use the temp table.

I don't think that my use case falls into those categories and from the original response, it has shown the non temp table method to be faster (in terms of IO and elapsed time).

Again, thanks for the additional details, it is very much appreciated.

Jake

"It doesn't do anything"

"Correction, it DOES nothing