Why is my stored proc that uses temp tables slower than the SQL in a batch outside of a stored procedure?

Question

I have some SQL that runs quickly on its own, but is 60 times slower when the same SQL is inside a stored proc. The SQL uses temp tables in many of its queries. Why is it slower in a sp?

Answer 1

tl;dr

After populating the (temp) table then consider a) running create index and/or b) running update statistics on the table; both of these provide the following benefits:

• forces a recompilation of any follow-on queries that reference the (temp) table
• provides the compiler with stats about the (temp) table [and index]

---

Hard to say with 100% certainty without a minimal, reproducible example (to include ASE version and query plans) but some ideas to consider:

1. I'm guessing the SQL that runs quickly on its own is based on a multi-batch process (eg, temp table created in one batch, temp table populated in one batch, query(s) run in yet another batch) in which case the query(s) is compiled with the benefit of having access to some stats for the non-empty temp table
2. with the proc the steps (create temp table, populate temp table, run query(s)) is all part of a single batch and therefore, as pointed out in eric's answer, the query(s) will (initially) be compiled based on some assumptions about the temp table size
3. another possibiilty: 1st time proc is executed it's compiled based on user1's (temp table) data set; user2 then runs the proc and ends up using the cached version of user1's execution plan, but if user2's (temp table) data set is considerably different (such that user2's data would generate a different query plan) then applying user1's query plan to user2's data could lead to a poor/slow execution

Regardless of why the proc's query(s) is run with a less-than-optimal query plan the general 'solution' is to make sure the query(s) is compiled with some up-to-date info about the temp table.

How/when to (re)compile the proc's query(s) depends on ASE version and any number of associated scenarios:

• newer ASE versions have config parameters deferred name resolution, procedure deferred compilation and optimize temp table resolution which can dictate when individual queries, within a proc, are compiled; but even this may not be enough if idea #2 (above) is in play
• the proc could be created with recompile (to force a recompilation on each run) but this may not be enough if (again) idea #2 (above) is in play
• creating an index (after the temp table has been populated) and/or running update statistics (again, preferably after temp table has been populated) should force a recompilation of any follow-on queries
• (re)compilation of large, complex queries may extend the overall run time for the query (as opposed to re-using a pre-existing query plan); net result is that excessive recompilations can lead to overall degradation in performance
• the use of abstract query plans (as well as other optimizer hints), while not capable of eliminating recompiations, can help reduce the time to (re)compile queries [this starts getting into advanced P&T work - likely a bit more than what is needed for this particular Q&A]

While the documentation's recommendation to create the temp table(s) prior to calling the proc may have some benefit, there are a few pitfalls here, too:

• having to manage/remember the relationship between parent process (batch or proc where temp table is created) and the child proc (where the problematic query is run)
• for a child proc called by multiple/different parent processes the same temp table DDL must be used by all parent processes otherwise the child proc could end up with excessive recompilations due to schema changes in the temp table; the issue is that if the proc notices a different temp table structure (eg, different datatypes, columns named in different order, different indexes) then it will force a recompilation

Answer 2

Does you stored procedure has parameters?

If yes, SQL Server uses a process called parameter sniffing when it executes stored procedures that have parameters. When the procedure is compiled or recompiled, the value passed into the parameter is evaluated and used to create an execution plan. That value is then stored with the execution plan in the plan cache. On subsequent executions, that same value – and same plan – is used.

What happens when the values in a table you’re querying aren’t evenly distributed? What if one value would return 10 rows and another value would return 10,000 rows, or 10 million rows? What will happen is that the first time the procedure is run and the plan is compiled, whatever value is passed in is stored with the plan. Every time it’s executed, until it’s recompiled, the same value and plan will be used – regardless of whether it is the fastest or best plan for that value.

You can force SQL Server to recompile the stored procedure each time it is run. The benefit here is that the best query plan will be created each time it is run. However, recompiling is a CPU-intensive operation. This may not be an ideal solution for stored procedures that are run frequently, or on a server that is constrained by CPU resources already.

ALTER PROCEDURE SP_Test
@ProductID INT
WITH RECOMPILE
AS
 
SELECT OrderID, OrderQty
FROM SalesOrderDetail
WHERE ProductID = @ProductID

Answer 3

Sybase says, "When you create a table in the same stored procedure or batch where it is used, the query optimizer cannot determine how large the table is because the table was not created when the query was optimized. This applies to both temporary tables and regular user tables."

Sybase recommends creating the temp tables outside of the stored proc. Also some solutions if you create indices in certain ways. See Sybase docs for more specifics