Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Long data query optimization with temp table

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

670 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