Optmize Select * from Temp Table

Ismail Cassiem 61 Reputation points
2022-04-11T19:53:48.743+00:00

Good day,

I have an sp that executes another sp and stores result in a temp table
its a 3rd party application and cant create a physical table

TRUNCATE TABLE #Results_usp_XXX_Date_Base
EXEC [usp_XXX_DateRange_Base] @BuildXML,NULL,@apl , NULL, NULL, NULL, NULL, 1, NULL,1

-- Executing the Select Only (800,000 records, 25 columns, runs 5min)
SELECT * FROM [#Results_usp_XXX_Date_Base]
--WHERE groupobjectcode IN ( SELECT DISTINCT groupobjectcode from function)

Excluding the where clause = 5 min
Including the where clause = 4 min

I cant create an index ,containing all the columns "i think" or insert an index on temp table but this also runs for 4-5min

This temp should populate an additional 3 temp tables based on the where clause and need it to be much faster.
Otherwise im looking at 15min only to populate the temp tables

Please assist with ideas to optimize this down to seconds please

Regards

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 106.8K Reputation points
    2022-04-12T21:14:22.407+00:00

    I understand less and less. Why are you looking up the table name in sys.tables?

    Previously you were asking about a plain SELECT * from your temp table. Now you are joining two temp temp tables and you are aggregating the result, which I assume results in far less rows.

    The query as such is somewhat dubious. Is GroupObjectID a unique key in the table #ObjectResolver_XML_usp_DateRange? If not, you are likely to get incorrect results. It may be better to rewrite it as:

     SELECT P.Date AS [Date],P.ClientCode AS PoolCode,SUM(MarketValueBase) AS MV  
     FROM #Results_usp_DateRange P 
     WHERE EXISTS (SELECT *
           FROM #ObjectResolver_XML_usp_DateRange F 
          WHERE F.GroupObjectId = P.GroupObjectId)
     GROUP BY P.Date,P.ClientCode
    

    Note also that I removed the NOLOCK hints. NOLOCK hints is nothing you should use as a matter of routine, and it's completely pointless to have them on temp tables.

    Whether this rewritten query will run better, I don't know. But it can certainly help to define GroupObjectId as the primary key of the #ObjectResolver_XML_usp_DateRange table.. Assuming that it is unique, that is.

    Did I understand correctly that the query in the last post runs for 15 minutes?

    Can you share the actual execution plan for the query above? Please upload it to http://www.pastetheplan.com.

    0 comments No comments

9 additional answers

Sort by: Most helpful
  1. Ismail Cassiem 61 Reputation points
    2022-04-13T19:13:36.833+00:00

    Thank You very much, i found the 2 indexes were efficient and brought total qry time to 4min

    0 comments No comments