Sample Header Ad - 728x90

Time to generate execution plan for select into from a view takes significantly more time than select

3 votes
0 answers
592 views
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?
Asked by Simon (131 rep)
Jan 20, 2021, 05:34 PM