• Sue_H - Wednesday, February 7, 2018 6:45 AM

    I'd be interesting in know how - maybe it's not that obvious.
    It was my understanding that the optimizer doesn't even know it's a view - all that gets to the optimizer is the query that is the definition of the view. So it wouldn't be the optimizer slowing things down. What exactly makes it slower when either the query or the view and it's executing the same thing?

    Sue

    No, the optimizer knows it's a view. However, it treats a view like what it is, a query. So while the optimizer understands that it's working with an object, the definition of that object is such that it gets treated as if it wasn't anything special (because, except for materialized views, it isn't).

    However, don't take this as criticism. You're dead on accurate. It's not the view that's making something slower or faster. It's something about the query against the view vs. the other query and what the optimizer can do with it that is defining performance here.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning