Solved

temp table - optimal records for performance.. (tsql)

Posted on 2016-08-09
6
29 Views
Last Modified: 2016-08-09
for 2 temp table with
joins like below

SELECT * FROM
#A1 AS A1
INNER JOIN
#B1 AS B1 ON
A1.Wrth_Nt_CODE = B1.Nt_CODE AND
A1.Wrth_Br_CODE = B1.Br_CODE

what is the optimal # of records that is recommended/suggested that will not lag the performance of the query? (SQL 2008)
(assuming temptables can hold more records than table variables and still do better..)

thanks.
0
Comment
Question by:25112
  • 3
  • 2
6 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 41748801
the problem is rather about how you want to implement the process (logically vs technically)
you are presenting a technical question, which may provide you some tech answsers, which may help.

I would rather like to see what you are trying to implement, why it must be "fast", or what you are seeing  as bottleneck etc...

note: temp tables go into tempdb
table variables are in memory until a certain size, and then go into tempdb also
you may only need to review the tempdb, or eventually better have those tables not as temp tables at all but as full/regular tables, with the proper indexing, partitioning etc in place
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41748802
there is no magical number of rows that is ideal. But do you know that you can create indexes on temp tables? That can help you a lot. Also other techniques like sub-queries and CTE (common table expression) can help in other scenarios. But without having your exact scenario, it is really hard to give the BEST answer!
0
 
LVL 5

Author Comment

by:25112
ID: 41749127
thank you..

i've comprised the logic in simple format below for your input.. the whole thing is put in stored procedure.. many users will hit this report..
right now, this report takes a minute to run per each call..

generating first temp table...– 8k records

The Join is
FROM TABLE1
      INNER JOIN TABLE2 ON CONDITIONS
      LEFT JOIN  (TABLE 3, TABLE4 INNER JOIN ON CONDITIONS)  
      LEFT JOIN  (TABLE 5, TABLE6 INNER JOIN ON CONDITIONS)  



SECOND temp table 900K Records.

FROM TABLE7
      INNER JOIN TABLE4 ON CONDITIONS
      INNER JOIN #TEMPTABLE1 ON CONDITIONS
      OUTER APPLY (
                                    FROM TABLE7
                                    INNER JOIN TABLE8 F ON CONDITIONS
                                    INNER JOIN TABLE9 AI ON CONDITIONS
                                    WHERE CONDITIONS
                  )


 
Third temp table 160K Records.

FROM #TEMPTABLE2 INNER JOIN #TEMPTABLE1  ON CONDITIONS
      OUTER APPLY (
                              SELECT  
                              FROM TABLE10
                                    INNER JOIN TABLE4 ON CONDITIONS
                                    INNER JOIN TABLE11 ON CONDITIONS
                              WHERE CONDITIONS
            )


The final select statement is comes out of two temp tables..
#TEMPTABLE3 TEMPTABLE3
      INNER JOIN #TEMPTABLE1 TEMPTABLE1 ON TEMPTABLE3.TEMPTABLE3_COLMN1 = TEMPTABLE1.TEMPTABLE1_COLMN1 AND TEMPTABLE3.TEMPTABLE3_COLMN2 = TEMPTABLE1.TEMPTABLE1_COLMN2
WHERE <conditions>

drop 3 temptables..
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41749181
this doesn't help me at all! Do you have proper indexes? Have you tried to run your queries with "include actual execution plan" to see where the time is lost?
0
 
LVL 5

Author Comment

by:25112
ID: 41749225
using "include actual execution plan"

these are the top 3 costly :
 Cost 46% to do clustered index scan on Table3 PK. (Part of creating first temp table)
 Cost 36% to do table insert for TempTable1
 Cost 4% to do clustered index scan on Table1 PK. (Part of creating first temp table)

it is suggesting I need to be focusing much on temptable1 creation? actually I run that separately and runs fast and it is relatively less records (8k, compared to the 900K,160k for TempTable2 and 3)
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 250 total points
ID: 41749228
>>Cost 46% to do clustered index scan on Table3 PK

You are surely missing an index here. A scan occurs when all the rows needs to be processed or no index matches the WHERE/JOIN clauses
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need return values from a stored procedure 8 40
RAISERROR WITH NOWAIT 2 24
SQL QUERY 3 33
compare date to getdate() 8 15
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

740 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