Solved

Long data query optimization with temp table

Posted on 2013-01-30
4
408 Views
Last Modified: 2013-01-31
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
0
Comment
Question by:doramail05
  • 2
4 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 38837789
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
0
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 38837906
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
SELECT ...
FROM ...
WHERE ...

so that you do not lock the allocation or the system tables in Tempdb until your query finishes.
0
 
LVL 1

Author Comment

by:doramail05
ID: 38838461
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?
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38839644
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.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now