Optmize Select * from Temp Table

Question

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

Answer 1

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.

Answer 2

Did you try to create a clustered index on the groupobjectcode column in the temp table?

Answer 3

Where are you receiving the data? If you do a plain SELECT * of 800000 rows, what will matter is the network speed, and the processing capability of the client. There is usually nothing you can do in SQL Server. The exception would be if the table is a heap and it has been populated by inserting, deleting, inserting, deleting etc and therefore there is a lot of empty space. But even if there is, the client and the network is likely to be limiting factor.

I get the impression that you real aim is to write the data into this temp table into other temp tables. In this case, the data will not leave SQL Server, so the execution times you will see will be entirely different.

Answer 4

Hi，@Ismail Cassiem
Welcome to Microsoft T-SQL Q&A Forum!

I see that your stored procedure seems to pass in parameters when executing, and if so, SQL Server uses a procedure called parameter sniffing when executing a stored procedure with parameters. When the procedure is compiled or recompiled, the values passed to the parameters are evaluated and used to create the execution plan.
This value is then stored in the plan cache along with the execution plan. In subsequent executions, the same value and the same plan are used. You can try adding WITH RECOMPILE on the original stored procedure.
You cannot create an index for groupjectcode, whether you can replace your temporary table with a memory-optimized table, of course, the premise is that the space load needs to be large enough when you run the program, otherwise it will be affected by the performance of the machine, which will undoubtedly be blocked.
For the use of performance tables, see this article.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Thank You All

@Guoxiong - no i have not, i did not want to create the index as without the where clause its very slow

@Erland Sommarskog - correct, i need to query the main temp table "#Results_usp_XXX_Date_Base" into additional temp tables based on clauses

@Bert Zhou-msft - due to the base application being 3rd party, i dont have access to edit the base/tables or sp. Only my new sp & temp tables. Is there a way to execute the " WITH RECOMPILE" outside of the SP and on the "EXEC [usp_XXX_DateRange_Base] @BuildXML,NULL,@apl , NULL, NULL, NULL, NULL, 1, NULL,1" or before the select?

Due to my limitations of not being able to create physical objects or editing external owned SP, can i create a memory optimized temp/variable table?
How could in scripts attach an execution plan or scrap the old one that done the insert?

Please help