Query Optimizer

  • You have an SQL query that is referencing multiple tables and views. Using a query optimizer you discover your current views are causing the performance drain. You don’t want to replicate the data your view is using in a Table. Of the following, which solution should you choose?

  • Which choices?

  • Hi Steve - Here are the choices

    Create a new View

    Use a cursor and store the data in a temporary table

    Create an Indexed View

    Create a User Defined Function to return the needed data

    Create a new Table

  • dreamslogic (6/13/2009)


    Hi Steve - Here are the choices

    Create a new View

    Use a cursor and store the data in a temporary table

    Create an Indexed View

    Create a User Defined Function to return the needed data

    Create a new Table

    That would have an awful lot to do with what the supposed problem with the old views was. Views do not usually cause performance problems, but using them inefficiently can.

    [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]

  • dreamslogic (6/13/2009)


    Hi Steve - Here are the choices

    Create a new View

    Use a cursor and store the data in a temporary table

    Create an Indexed View

    Create a User Defined Function to return the needed data

    Create a new Table

    None of the above. Rewrite the query and/or view so that they are written optimally and add any necessary indexes.

    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
  • Or provide more information. A view isn't anything more than a query that is written over a table(s). The query optimizer looks at that query and includes it in the query you are writing on a view, looking to optimize things.

    There is no general advice outside of don't use a cursor.

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

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