Use result of Select Statement multiple times

I use a select query which gives me a resultset. Now, i want to use the resultset in another select query many times. If i am storing the resultset in temporary table or table variable, as per SQL Server Execution Plan, it takes 75% of execution time in just storing the data into the table variable/temporary table. Can anyone help me making this more efficient.

more ▼

asked May 25, 2010 at 08:19 AM in Default

avatar image

Abhisek Rathi
1 2 2 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

check out CTE

;with cte            
    < query >            
select *            
from   cte            
more ▼

answered May 25, 2010 at 08:56 AM

avatar image

Squirrel 1
1.6k 1 3 5

(comments are locked)
10|1200 characters needed characters left

I wish I had more time to write this out more cogently but be advised that you are always going to have overhead regardless of your temp storage solution. Perhaps you should investigate the query that retrieves that information to see if it can be made more efficient before exploring how it's stored for the subsequent queries.

In our shop, my DBA pointed me to using CTE's as well. The funny thing about it was that I was typically storing large amounts of data and switching to a CTE actually diminished the performance of my queries, so be sure do some tests before you completely switch over a large procedure.

It's important to be aware of how much room your temp tables have available when you are seeing slow downs for a temp table. If you have plenty of room for the temp tables then they will typically be plenty efficient. They are usually the right solution when you have a large amount of data to store because depending on how much memory is available to SQL Server, you can quickly and easily fill up the SQL Server's available memory when using a cte which would then totally kill any performance gain you would experience by being in memory. Conversely, if you only had a list consisting of a few columns with a minimal number of tuples then the CTE would be a better way to go.

more ▼

answered May 25, 2010 at 10:40 AM

avatar image

Brett Phipps

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 25, 2010 at 08:19 AM

Seen: 1681 times

Last Updated: May 25, 2010 at 08:19 AM

Copyright 2016 Redgate Software. Privacy Policy