Solved

Long data query optimization with temp table

Posted on 2013-01-30
4
415 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

615 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