3

I have a view, vw_example, that is quite complicated and has multiple joins across multiple databases. This view has been causing significant delays when used as part of other queries, and we've narrowed down the problem to execution plan generation.

One thing we've noticed that has vexed us is that this query takes a few seconds to run (and display all the data, ~44k rows):

SELECT * FROM vw_example

whereas this query takes minutes to run (tbl_example is created by the query):

SELECT * INTO tbl_example FROM vw_example

We've additionally compared the SQL execution plans between the two in Microsoft SQL Server Management Studio (2014). The former plan took 3 seconds to generate. The latter plan took 37 minutes.

Comparing the two plans, the only difference is that the latter plan has a "Table Insert" node at the start. Everything else is identical.

Does anyone have any idea why it would take significantly longer to generate a plan for the SELECT INTO statement vs a SELECT statement?

Improve this question
10
  • 2
    You could have run into this problem: Generating the SQL query plan takes 5 minutes, the query itself runs in milliseconds. What's up?.
    – Olivier Jacot-Descombes
    Jan 20, 2021 at 17:40
  • 4
    long compile times most frequently occur during the search 2 phase of query optimization, where things like join reordering kick into high gear. you can often get a plan much faster by using OPTION(FORCE ORDER); when you select from your view, but whether the execution plan is good or not is unpredictable at times.
    – Erik Darling
    Jan 20, 2021 at 17:46
  • I've tried SELECT * FROM vw_example OPTION(RECOMPILE) and that makes no difference to the compile time - although I'm not 100% clear that will affect a select statement rather than a stored procedure.
    – Simon
    Jan 20, 2021 at 17:52
  • OPTION(FORCE ORDER) got the SELECT INTO down to 15s! I'll look into what that changes behind the scenes. Thanks.
    – Simon
    Jan 20, 2021 at 17:53
  • 1
    >>>Comparing the two plans, the only difference is...<<< Could you please upload both the plans? It can be difference that you just do not see: someone don't se that one plan is serial and other parallel, others don't see that in one plan the clustered index scan is ORDERED and in other is UNORDERED, but these are differences
    – sepupic
    Jan 21, 2021 at 12:38

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.