Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    1

    Unanswered: View behaviour with index.

    Hi

    If I have a view:

    SELECT A,B
    FROM tblTable
    WHERE A = 1

    And then I have a s-proc using that view:

    SELECT *
    FROM MyView
    WHERE B > 6

    My question: If I add an index to tblTable for the column B (not used in the view's WHERE clause, but used in the s-proc), will it have a performance improvement, because of the WHERE B > 6 on the view, assuming that this condition would benefit from the index if it were in the view itself.

    I guess I could also put it this way: can an index on a column in a table improve the performance of a condition on a view using that table.

    Thanks

    Peter

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, it should.

    Views are not pre-compiled, so the optimizer looks at the underlying view statement to arrive at the best query plan for your procedure.

    You can always check the query plan to be sure.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Posts
    269
    Quote Originally Posted by brirtch
    Hi

    If I have a view:

    SELECT A,B
    FROM tblTable
    WHERE A = 1

    And then I have a s-proc using that view:

    SELECT *
    FROM MyView
    WHERE B > 6

    My question: If I add an index to tblTable for the column B (not used in the view's WHERE clause, but used in the s-proc), will it have a performance improvement, because of the WHERE B > 6 on the view, assuming that this condition would benefit from the index if it were in the view itself.

    I guess I could also put it this way: can an index on a column in a table improve the performance of a condition on a view using that table.

    Thanks

    Peter
    perfomance of query depends on index column which is in where clause.(no matter whether its outside or inside view)

    From above scenerio,
    'select * from MyView' query will do table scan.because column A dont have index.
    but ,

    'select * from MyView where B>6' query will do Index seek or Index scan.Because column B have index.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •