SQL Server Performance

Views vs Stored Procedures: Correct Use

Discussion in 'Getting Started' started by OffDaBench, Apr 3, 2010.

  1. OffDaBench New Member

    Hi
    I have seen several threads related to what I am asking but none that quite answer this question:
    I have recently started a new job. The other developers I am now working with rely heavily on views where I would rather use stored procedures. They script SELECT statements of varying complexity and create a view from each statement. As the statements are generally for very specific ad hoc purposes they don't tend to be used again in their same format.
    When faced with a new ad hoc query, the developers will look for a similar view they have used in the past and then alter that view to suit the new purpose. If there isn't a similar one, they create a new view. This results in an excessive number of views which gets quite difficult for a newbie like me to scan through to find a similar view. I often give up and end up creating a new view from scratch!
    I would have preferred to see fewer views and then pull these together as 'building blocks' for the stored procedures.
    Before raising this with my new colleagues and, potentially, ruffling feathers I thought I would put this out 'there' to see what the general consensus was.
    Am I off the mark? Are my new colleagues off the mark? Or is there some common ground somewhere in the middle where we should be meeting?
    Thanks in advance!
    Vaughan
  2. sfortner New Member

    Yeah, you're right on target. Ask them if they can spell reuse! : ) You may find that most of them are thinking the same thing but probably got caught up in the culture at the company and are just doing whatever everyone else is doing.
    --Steve
  3. michaellutz New Member

    Few thoughts to share. First is, I generally agree w/you. It sounds like someone found a technique that worked and then it proliferated across the organization without much thought to the ramifications.
    There are downsides to views, right? (performance-wise) Depending on the complexity of the views and their usage patterns there may be inefficient query plans generated. Also, stored procedures tend to faciliate query plan reuse whereas views likely do not unless the application programmers are properly parameterizing their queries. I'm guessing that since views have proliferated to this extent the developers aren't parameterizing their queries. If this is an OLTP system with a high number of queries being executed this could create CPU pressure.
    But most importantly for your sake, as a new employee take your time trying to introduce change. Maybe approach with a question first to a few developers, "say I was wondering, these views are increasing in number. How did this pattern of development start? Have we ever tried using stored procedure instead?" I'm guessing you'll get various answers some you'll agree with and some you won't. Maybe you'll be surprised and there are good reasons why the views are used. But then, maybe you'll find that's it's just a pattern that's worked and people haven't thought too carefully about it. ;-)
    Good luck. Remember relationships are more important than the code. Took me a number of years to learn this. :)
  4. OffDaBench New Member

    Thanks for both your replies. It's good just to confirm what I'm thinking.
    I will bide my time and wait for the right moment to broach the subject. Tact will definitely be the way to go!
    Thanks
    Vaughan

Share This Page