Solved

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

Posted on 2016-08-09
6
22 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 69

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 69

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 69

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now