x

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

Abhisek Rathi gravatar 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
as
(
< query >
)
select *
from cte
more ▼

answered May 25, 2010 at 08:56 AM

Squirrel 1 gravatar image

Squirrel 1
1.6k 1 3

thnx for letting me know bout CTE, but i have another issue. The resultset which is coming is coming from executing a stored procedure currently something like
INSERT INTO @filterTable EXEC usp_GetFiltertable @profileId
Please let me know how can i accomodate this in CTE?
Waiting eagerly for your reply.
May 26, 2010 at 03:36 AM Abhisek Rathi
(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

Brett Phipps gravatar image

Brett Phipps
1

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1834
x108

asked: May 25, 2010 at 08:19 AM

Seen: 1426 times

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