I’m working on optimizing a stored procedure for a client and converting a lot of it to dynamic SQL to better handle the branching logic in the WHERE clause. As part of this stored procedure, there’s heavy use of temporary tables, so I played around with inserting the data from the queries into them. And some of the inserts are quite large, with hundreds of thousands or even millions of rows each.
As part of my testing, I had the insert statement both inside the dynamic SQL…
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = "insert into #table
select ..."
exec sp_executesql @SQL, ...As well as puting the insert statement outside of the dynamic SQL…
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = “select …”
insert into #table
exec sp_executesql @SQL, …
Other than where the INSERT statement was, the syntax was exactly the same, so this made for as good a test as you would get.
With the INSERT statement outside the dynamic SQL, the procedure took 3:30 to execute; with it inside the dynamic SQL, it took only 2:05.
Let’s look at the why
When you think about how dynamic SQL executes, and the fact that it executes in a separate context from the calling thread, this starts to make more sense. With the INSERT statement outside of the dynamic SQL, the record set needs to be returned from the dynamic SQL’s context to the parent context to be executed. Then those rows can be inserted into the destination table.
However, when the INSERT statement is inside the dynamic SQL, the data doesn’t need to be moved between contexts; the data is simply put into the destination table within the context. There’s no need to return the data to the calling code; the data can just stay within the engine and get shoved into the destination (in this case, a temporary table).
This small change makes the entire stored procedure run that much faster. In this case, because of the length of the dataset and the width of the dataset (it’s pretty wide), the amount of time spent on just this one data movement can be measured in over a minute.
Test More; Assume Less
This goes to show that things that you wouldn’t expect to have a lot of impact on your performance can, and should be tested to ensure that they are working as expected.
Denny