Long data query optimization with temp table

having query that might produce 100,000 records, but it takes quite alot of seconds to load them, having heard of using temp table might ease the load time, trying to find out how the query should be written with the temp table and how to use it / call the stored procedure in .net
Who is Participating?
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
The other question you have to answer before you use a temp table to solve performance problems, is how often the query is run.

If this particular query runs 1000's of times, then it may be better to find a different way.  if it runs 2 times a day, I would start with a temp table and see if it helps.

The other main thing to know about using a temp table in this scenario, is that you should try not to do a SELECT ... INTO #temptable FROM dbo.table WHERE ...

You should do a
CREATE TABLE #temptable (
.. column definitions

INSERT INTO #temptable
FROM ...

so that you do not lock the allocation or the system tables in Tempdb until your query finishes.
Temp table may not help in every situation.
You can check the SQL Execution Plan to find out which part of your query takes most of the time and try to figure out how it can be optimised. First thing to try is to create indexes on the columns that take much time.
Then you can try to tune the query. For example if you have a few tables that you join together, it may be more efficient to have a table with many records to be filtered first of the records that are not required (e.g. Status="deleted"), and then join the resulting set with other tables.
It would help if you could replicate the structure in the SQLFiddle and show us the query you are running
doramail05Author Commented:
dbaduck :

it would be just a normal sp call from .net code behind and get the selected columns to display as requested? or any particular kind of call?
DBAduck - Ben MillerPrincipal ConsultantCommented:
I only say that the nature of the call is important because if you have 100,000 rows happening into TempDB 1 time per second, you are churning data into TempDB very often and it could cause some thrashing on TempDB since it is a shared DB for all the databases in the instance.

But there is only one way to really know, and that is to test it.  If TempDB is sized appropriately and you create the temp table correctly as above, you will see if you will have problems in testing.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.