?
Solved

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

Posted on 2016-08-09
6
Medium Priority
?
40 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
[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
  • 3
  • 2
6 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 1000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

752 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