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

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