Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Long data query optimization with temp table

Posted on 2013-01-30
4
Medium Priority
?
422 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 25

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 25

Accepted Solution

by:
DBAduck - Ben Miller earned 1500 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 25

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline

916 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