August 12, 2013 at 8:16 am
Hi there,
I want to modify an SP to use #temp tables, as its very slow at the moment, but I have an issue. The queries I use to generate the contents of these temptables are dynamic, so I have to use sp_executeSQL to run them, and this means the temp tables are generated out of scope.
Most people recommend creating your #temptable in the parent script and populating it inside the sp_executeSQL, but the issue I have is I don't know how many #temptables I'm going to need, so I need to be able to create them dynamically, and the only way I know how to do this is with sp_executeSQL
I can't use ##temptables, as this SP might be run by more than one user at once.
Is there any way I can make sp_executeSQL share scope with its parent script?
Any help is very welcome
August 12, 2013 at 9:01 am
I want to modify an SP to use #temp tables, as its very slow at the moment, but I have an issue.
I think you may be chasing your performance problems down the wrong path. Just changing to a temp table is not going to make something faster. It actually has the potential to make it slower. You still have to query the same base tables but now you also have to make a copy of that data. Now that you add filling those with dynamic sql it sounds like there may be other things going on here too. Perhaps if you can post all the details we can help with the performance problems.
We would need to see the ddl of the tables (including indexes), the proc you are working with, what you want this proc to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2013 at 9:37 am
Thanks for the response. Normally, I'd agree, but we are seeing significant improvements working with temp tables (something I usually avoid like the plague). I think I have a solution now though. I have set up a separate database, and I am creating the temp tables in there, using GUIDS to name them, and manually clearing them down.
The SP in question allows clients to combine selections they have made in the past into a new, composite query. The new query is built from the old ones using a series of subqueries, and using Unions and Excepts to join them. As the results of the subqueries get bigger, there seems to be an exponential slowdown on the composite query.
Using temptables seems to avoid this slowdown. For example, one query that took 4 hours this morning ran in 4 minutes this afternoon. The base table I am querying has 2bn rows in it, so when the combined query works on the temptables, it seems to have a lot less work to do when it comes to joins.
You are correct though, as there does seem to be an overhead on simpler queries, but this is only a few seconds, so I think its acceptable.
Thanks for your help
August 13, 2013 at 8:55 am
Bakhesh (8/12/2013)
Thanks for the response. Normally, I'd agree, but we are seeing significant improvements working with temp tables (something I usually avoid like the plague). I think I have a solution now though. I have set up a separate database, and I am creating the temp tables in there, using GUIDS to name them, and manually clearing them down.The SP in question allows clients to combine selections they have made in the past into a new, composite query. The new query is built from the old ones using a series of subqueries, and using Unions and Excepts to join them. As the results of the subqueries get bigger, there seems to be an exponential slowdown on the composite query.
Using temptables seems to avoid this slowdown. For example, one query that took 4 hours this morning ran in 4 minutes this afternoon. The base table I am querying has 2bn rows in it, so when the combined query works on the temptables, it seems to have a lot less work to do when it comes to joins.
You are correct though, as there does seem to be an overhead on simpler queries, but this is only a few seconds, so I think its acceptable.
Thanks for your help
Without expressing any opinion about the rest of your process (because I don't understand it well enough from the brief description), I was going to suggest the path you've already taken to solve the context issue with temp tables and dynamic SQL - creating persisted tables with names that will be unique rather than temp tables. "Manually clearing" those tables sounds like a drag, though. When I had to write a stored procedure that created persisted tables that only needed to exist for the duration of the execution, I coded it to clean up after itself.
My method requires a table with three columns (a GUID, a varchar(128), and a datetime) to support the process - let's call it dbo.procCreatedTables. In the stored procedure, I created and set a uniqueidentifer variable to identify each particular execution of the stored procedure - let's call it @procExecutionID. Each time the stored proc creates a persisted table, it writes a row with @procExecutionID, the name of the table, and the current value of getdate() to dbo.procCreatedTables.
At the end of the stored procedure, I wrote a bit of code that selects all of the table names created by this particular execution from dbo.procCreatedTables into a cursor and used dynamic SQL to work through the cursor and drop each of those tables. After that, I deleted all rows for this particular execution from dbo.procCreatedTables.
The datetime column in dbo.procCreatedTables supports a clean-up stored procedure that runs periodically to catch any orphaned tables that need to be dropped. It populates a cursor with table names from dbo.procCreatedTables where the datetime value is earlier than a cutoff datetime that ensures they're old enough that they should be dropped, runs the same dynamic SQL as the other stored proc to drop those tables, then deletes the applicable rows from dbo.procCreatedTables.
Jason Wolfkill
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply