How to pass temporary tables between stored procedures
-
Hi,
I've recently inherited a horribly-performing stored procedure that I've been trying to improve. It involves creating a temporary table, populating it by inserting data selected from another table depending on certain criteria, deleting some data from it depending on other criteria, joining to a second temporary table depending on yet more criteria... i.e. multiple possible execution paths, you get the idea! Anyway, since there were a lot of 'optional' queries that may or may not be called depending on the criteria passed in, I've split the procedure up using an umbrella procedure that doesn't do any actual data access, but just farms its work out out to other, more specific stored procedures that just do one thing. This was done to maximise the efficiency of the execution plans used.
However, the procedure USED to use a temporary table declared like this:
DECLARE
@ids TABLE (Id int)
Unfortunately I haven't been able to find a way of passing table variables between stored procedures, so I've had to revert to using tempdb tables, i.e.
CREATE
TABLE #ids (Id int)
Since using tempdb is generally less efficient than using table variables, does anyone know a way I can pass table variables into and out of a stored procedure?
-
Ian
Temporary tables aren't less efficient than table variables. Both will be created in memory if there is enough; both will end up in tempdb if they end up too large for memory. And table variables have certain restrictions, such as not being able to be indexed, and, it seems, not being able to be used as an output parameter to a stored procedure!
John
-
i agree, temp tables are not the enemy (cursors are!)
referencing the temp table should be the simplest option here. i have dealt with some pretty hairy SP's and always been able to make them work better, faster, smarter.
anyway, good luck, and on the first question, i have played with passing a table var and have not had any luck, nor was i ever able to find anything stateing i could.
-
Thanks guys. I've got the procedure working about 95% more efficiently than it was initially, I think I'll leave it at that
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply