Solved

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

Posted on 2016-08-09
6
27 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 142

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Backup Your Microsoft Windows Server®

Backup 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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
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 …

786 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