Views hurt Performance?

  • if you join a view with other tables, does the SQL SERVER 2005 optimizer need first to materialize the view in memory before joining or not?

    For example, assume you have a view that return like...a million rows...and you join this view with other tables, even if the where close make the whole query very selective....does the view first need to be materialized in memory returning one million rows before joining and evaluate the where close of the whole query?

    I think yes, but I was told the the optimizer is smart enough and does not materialize the whole view in memory and then join the virtual table with other tables....is this true? any idea?

  • SQL Server's optimizer is smart enough not to materialize the view.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Thanks for you answer, but can i ask you why you are sure of that? So what it the purpose of view then? just security and make the code simpler?

    many thanks.

  • The optimizer MAY materialize a view if that will generate what it thinks is the best performance. Of course, the optimizer may do this with any part of a query it wants to - it does not have to include a view.

    Using a view in a query is not much different than using a sub query. The optimizer will optimize the query based on the indexes of the base tables when you join to a view so there is no performance loss between a view and simply joining to a sub query with the same definition.

    As far as benefit, views are able to give you a layer of security, they can simplify complex schema, and since they can include hints they are able to help you optimize queries consistently. They are also very useful for backward compatibility support and I have seen them used to simplify complex operations using instead of triggers. Also, with a schema bound view, you can create indexed views which can have a great performance benefit.

  • vittorio caminiti (4/22/2008)


    Thanks for you answer, but can i ask you why you are sure of that? So what it the purpose of view then? just security and make the code simpler?

    many thanks.

    Yes and yes.

    The View is the principal and generally most preferred object in SQL for abstraction and/or encapsulation.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thanks everybody.....;)

  • There is a danger in assuming that the database engine will simply do what we simplistic users fear we would have to do if we were processing the query. Remember that SQL is not procedural, and the SQL syntax is almost "upside down". For example, column aliases are defined "up front", so one would expect to be able to use then in the where clause "down at the bottom". And many SQL users fear cross joins (cartesian products) because the combinations are not limited until the where clause "down at the bottom". Same thing with linking views; typically only the rows that meet the join and where criteria are actually accessed.

    But the lesson to learn is: without looking at the execution plan and timing the actual execution you will never know, so go ahead and try it.

    (This was particularly striking in MS Access, which calls views queries, and made it even more "obvious" that having to run all those intermediate queries to get a final result would be sure to create performance problems. T'aint so, and the Jet Engine does a wonderful job of only accessing the data that is actually needed for the final result.)

  • A simple query consists, generally, of six items:

    1. SELECT columns

    2. FROM

    3. JOIN types

    4. ON

    5. WHERE

    6. ORDER BY

    However, it does not process the query in this order. The query is actually processed in the following order:

    1. FROM

    2. ON

    3. JOIN types

    4. WHERE

    5. SELECT columns

    6. ORDER BY

    I could be off in a couple of the first four steps. My point here is that data does not even get returned until step 5 and by this point, SQL Server knows exactly what needs to be returned.

    If I have the order wrong here, someone please correct me...

    Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

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