3

I made a view that does some query logic for a table of products. When I select from that view like

Select * from dbo.productView where productID = UUID

the execution plan is horribly inefficient. But if I take the query that generates the view and just add the where clause to it, the execution plan is exceedingly efficient. Why would these produce such radially different execution plans. I thought in the case of normal views (not indexed) the query analyzer would expand the view, so in effect it would be like adding the where clause to the SELECT that makes up the view.

I can't just index the productView since it uses a CTE and windowing functions, but I'd like to be able to the query analyzer to generate a more performant execution plan, like from the plain select.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Eric Pigeon
  • 1,091
  • 2
  • 10
  • 21
  • try adding `option (recompile)` to the end of each sql statement and then comparing plans again. – Laurence Aug 25 '14 at 21:06
  • 1
    Needs more details but maybe a dupe of http://stackoverflow.com/q/13635531/73226 – Martin Smith Aug 25 '14 at 21:06
  • 1
    Never think of it in terms of "just index the productView" - an index isn't a magic thing you add to any view to make it faster. Egads I need to blog about this. – Aaron Bertrand Aug 25 '14 at 21:15

1 Answers1

1

I thought in the case of normal views (not indexed) the query analyzer would expand the view

This is true. Still, the two query variants have different execution plan cache slots. Probably, you got unlucky reusing an old plan that has sniffed bad parameter values.

Obtain fresh plans. Clear the cache on a test system or add the RECOMPILE hint. The plans will now be identical.

To fix the problem apply the usual techniques for resolving bad plans and parameter sniffing.

usr
  • 168,620
  • 35
  • 240
  • 369
  • 3
    From the mention of windowing functions they may be hitting the predicate pushing issue described [here](http://stackoverflow.com/q/13635531/73226) – Martin Smith Aug 25 '14 at 21:26