3

I am having following query:

DECLARE @VersionsIds TABLE(VersionId INT)

INSERT @VersionsIds(VersionId) VALUES (520)

SELECT
vEps.MasterPolicyId
-- INTO #Temp
FROM [Events].[vwEventMasterPolicy_Search] vEps
WHERE vEps.MasterPolicyId IN (SELECT * FROM @VersionsIds) 

When executing without INTO statement, execution plan is ok, and query is running fast. It finishes in 124ms. When running it with INTO statement, it is taking about 20sec to finish. The difference is in execution plan. In fast variant (without inserting data into temp table), it can be seen that value from table variable @VersionsIds are propagated into view vwEventMasterPolicy_Search, which is union of multiple UNIONS, as shown in execution plan:

Execution plan which is ok

Fast Execution Plan

But in case when I would like to insert data into temp table, it can be seen that the view is fully calculated (and its result set is huge) and only after that join with table variable is done, as shown in execution plan: Execution plan is not ok

Slow Execution Plan

So, my question is - why execution plan which is got from select, and which is propagating predicate values inside view, is changing so drastically when I just want to catch that same result set of the same select statement into temp table? Why predicate values are not propagated into view?

5
  • Can you please upload both execution plans to Paste The Plan? They hold more information in them than a screenshot shows.
    – J.D.
    Apr 4 at 19:27
  • Here is the link to correct execution plan brentozar.com/pastetheplan/?id=Byy52T_Xc and here is the link to bad execution plan brentozar.com/pastetheplan/?id=rytI66_79 Apr 4 at 19:54
  • Hi J.D. I have checked execution plans and these are actual execution plans. You can see that everyone of them - every operator is having estimated number of rows and actual number of rows, for example. Which is a sign that these are real actual execution plans. Apr 5 at 9:24
  • Yes you're right, my mistake. I must've clicked on the wrong operator yesterday when I was quickly checking from my phone. Thank you! I'll try to review when I get a chance today.
    – J.D.
    Apr 5 at 11:58
  • Table variables don't have statistics and so plans that involve them often have bad cardinality estimation. You may be able to solve the issue by just using a temp table for VersionIDs instead. (Under the hood they both use tempdb so the I/O should be the same.)
    – nateirvin
    Apr 7 at 20:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy