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.
check out CTE
;with cte
as
(
< query >
)
select *
from cte
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.
