question

Herman avatar image
Herman asked

Performance degradation when using exec sp_executesql insert into #temp

Hi, I have a performance problem which I don't understand. I am using dynamic SQL to insert data in a temporary table . My script uses indexed views as source and insert 122268 ID's in a temporary table. The query performs normal, but when I use exec sp\_executesql ... insert into #temp my performance drops and SQL reports 247074 logical reads on Worktable. Below I compared the exec sp\_executesql query with a select into #temp and executing the dynamic sql without inserting it into temp. In all other cases no additional logical reads occur. Who can explain me what is going on? Is there a way to avoid the logical reads on worktable?

 
CREATE TABLE #Temp (ID_Subject int)

declare @SqlTemp nvarchar(max)
set @SqlTemp='
 select auth.ID_Subject
   from UserSubjectsWriterView auth WITH (NOEXPAND)
  inner join OrganizationBasicSubjectsSCView sc  WITH (NOEXPAND)
     on auth.ID_Subject = sc.ID_Subject
  where auth.ID_User = 81 
    and sc.ID_Organization = -1
  group by auth.ID_Subject'

 insert into #temp(ID_Subject)
execute sp\_executesql @SqlTemp

select auth.ID_Subject
  into #temp2
  from UserSubjectsWriterView auth WITH (NOEXPAND)
 inner join OrganizationBasicSubjectsSCView sc  WITH (NOEXPAND)
    on auth.ID_Subject = sc.ID_Subject
 where auth.ID_User = 81
   and sc.ID_Organization = -1
 group by auth.ID_Subject

execute sp_executesql @SqlTemp

drop table #temp
drop table #temp2
When I run this code I get the following results:

Table 'OrganizationBasicSubjectsSCView'. Scan count 1, logical reads 169, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'UserSubjectsWriterView'. Scan count 1, logical reads 442, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Temp_______________________________________________________________________________________________________________000000000222'. Scan count 0, logical reads 122464, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 247074, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(122268 row(s) affected)
Table 'OrganizationBasicSubjectsSCView'. Scan count 1, logical reads 169, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'UserSubjectsWriterView'. Scan count 1, logical reads 442, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(122268 row(s) affected)

(122268 row(s) affected)
Table 'OrganizationBasicSubjectsSCView'. Scan count 1, logical reads 169, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'UserSubjectsWriterView'. Scan count 1, logical reads 442, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
If you need more infor, please let me know Herman
performanceinsertdynamic
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Herman avatar image Herman commented ·
I forgot to mention that I am using SQL Server 2005!
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Is there any difference between the execution plans? And what happens if you modify the middle query to use a pre-created table (i.e. INSERT INTO .. SELECT instead of SELECT .. INTO)?
0 Likes 0 ·
Herman avatar image Herman commented ·
The funny thing is that I don't get an execution plan for the first query in mangement studio. I tried the INSERT INTO predefined table statement, it gives exectly the same IO results as the SELECT .. INTO
0 Likes 0 ·
Herman avatar image Herman commented ·
I got the execution plan from Profiler byusing the ShowPlan Text event. The first EXEC sp\_excutesql INTO query generates 2 ShowPlan events: 1) A merge join of the two tables 2) A table insert containing a top (ROWCOUNT est 0) containing a parameter table scan The others only one: - A table insert containing a top (ROWCOUNT est 0) containing the same merge join Sorry the comment does not have space to add the complete plan.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
I would do two things, instead of trying to capture a text plan, which, I find, difficult to read, use SET STATISTICS XML ON to capture a full XML plan (which will also be a graphical plan). Then, I'd capture the plan for the SELECT query independently of the INSERT query so you can understand exactly what's happening with it. Use SP_EXECUTESQL to execute it too, so that you eliminate as many variables as possible. Then you can compare the plan of the INSERT statement with the SELECT statement to see if there are changes in the plan. The plan is creating a work table. This means it's creating a temporary table to gather the data as part of the processing for the plan. This could mean you don't have proper indexes, or the indexes aren't selective enough or the statistics are out of date. It's also possible that the indexed views are not optimal for the query. Since you're using the NOEXPAND hint I assume you're not in Developer or Enterprise edition? If you are in Developer or Enterprise, try running without the NOEXPAND hint (again, just the SELECT statement and the INSERT statement) to see what happens when the optimizer is allowed to make choices (since a hint forces the optimizer down a path, it's not always the best path). That's about all I can tell you without seeing the execution plans.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Herman avatar image
Herman answered
Hi Grant, Instead of the Showplan Text I used the Showplan XML event in profiler to get the execution plan of the insert into #temp() execcute ... statement. Again, I am not able to get an execution plan in MS for the query, also not when I use SET STATISTICS XML ON. The execution plans are a bit too large too attach, but what is essential (I thnk) is that I get 2 plans for the insert into.. statement, while I get one plan for the others The indexed views are optimized views for this query giving only the data I need (the underlying tables have millions of rows) and removing the NOEXPAND hint does not change the join, which is by the way the same in all cases. The only difference I noticed is the fact that the execution has two execution plans. The first one performing the merge and the second one Performing a table scan, a top operation and a Table insert operation. Apparently the data is first saved in a temporary file and then saved into #temp. Why? This must be the cause of the additional logical reads. It seems this way of generating a #temp file is not optimal, and I am considering another option. But I would anyway be very happy if you or someone else could explain me what exectly is going on. Herman
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
It's hard to know precisely what's going on without seeing the execution plan, sorry. To a degree you may be asking a question that isn't well suited to this format. If you go to the regular SSC forums, you can attach the execution plan (save it as a .sqlplan file) and it'll make it much easier for us to understand what's going on.
0 Likes 0 ·
Herman avatar image
Herman answered
Hi Grant, Thanks for your effords. I decided to use a different solution and avoid this problem. I will try to reproduce it on one of the demo databases as soon as I have time. Thanks Herman
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.