Post reply

Does querying a view extract values for all rows before WHERE?

  • August 14, 2011 at 3:36 pm

    #246203

    I have a view myView that among things extracts values from an XML column. I suspect it may be a slow operation. I have a condition that I want to put in the WHERE clause when querying the view:

    SELECT myValues FROM myView WHERE myCondition

    Let's say SELECT * FROM myView, with no WHERE clause, returns 10,000 rows, whereas SELECT * FROM myView WHERE myCondition returns 5 rows.

    Now is the question:

    When I run SELECT * FROM myView WHERE myCondition, will the SQL Server first extract values from the XML column for all 10,000 rows and then apply myCondition to get the resulting 5 rows? If it will, I would rather replace the view with a stored procedure that will apply myCondition to the original select resulting in 5 rows in the first place.

  • August 14, 2011 at 3:58 pm

    #1368420

    Depends entirely on what the optimzer thinks is the fastest thing to do.

    I'd suspect that if not filters are based on the xml then yes, those will definitely be procesed before the parsing.

    For any other case I'd check the execution plan to confirm what's going on.

    You can upload them here for help interpreting them.

  • August 14, 2011 at 4:54 pm

    #1368429

    During the parsing phase SQL replaces the view names with the view definitions. That's done before the optimiser starts. So what the optimiser sees is not

    SELECT * FROM MyView WHERE <some condition>

    What the optimiser sees is this

    SELECT * FROM (

    SELECT <rest of view definition> ) AS MyView

    WHERE <some condition>

    The optimiser will then evaluate plans for that, treating the subquery just like any other subquery. Whether the where predicate can be evaluated as a search argument or as a secondary filter depends on what the filter is on, not on the usage of the view.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 15, 2011 at 12:52 am

    #1368458

    Thanks, guys, makes sense.

  • August 15, 2011 at 5:00 pm

    #1368900

    Eliyahu (8/14/2011)

    Now is the question:

    When I run SELECT * FROM myView WHERE myCondition, will the SQL Server first extract values from the XML column for all 10,000 rows and then apply myCondition to get the resulting 5 rows? If it will, I would rather replace the view with a stored procedure that will apply myCondition to the original select resulting in 5 rows in the first place.

    To summarize what the others have said, if the WHERE clause contains a condition which includes a value that must be extracted from the XML, then yes... all 10,000 rows would need to be shredded before the value in the WHERE clause could be applied.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply