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

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!

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

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?

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)