Performance question View vs SQL query within view

  • Hi:

    I have a view as

    create view vwOpsasselect * from tableAwhere type = 'S'

    When I run the view as select * from vwOps where ID = 24 it takes 15 secs.

    Whereas when I run the query it returns the records in 2 secs.

    select * from tableAwhere type = 'S'and ID = 24

    I ran with statistics time IO and found that when I run the view the returned rows are more whereas with query it is less. Any idea what could be the reason?

  • First try refreshing the view.  The "*" in the view may be causing some issues if the table has changed after the view was created.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • I had refreshed the view. Does this make a difference?

    select * from vwOps where ID = 24

    vs

    select * from tableA where type = 'S'and ID = 24

     

    • This reply was modified 3 years, 2 months ago by  sqldba20.
  • It definitely could, esp. if there's an index containing the type column.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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