Coffeehouse Post

Single Post Permalink

View Thread: How big are your sprocs?
  • User profile image
    Sven Groot

    irascian wrote:
    putting all the logic into a stored proc that gets called once rather than artificially using multiple stored proc invocations can make for much better performance.

    I do try to avoid multiple sproc calls for what is logically a single operation.

    What I willt typically do is chain my stored procedures. Let's say I have a Customer table and an Order table. If I want to be able to get a single customer and all its orders (where I don't want to use a join since I don't need all the customer's information on every row), I'd have a GetOrdersForCustomer sproc, and a GetCustomer sproc that would first select the customer and then run "exec GetOrdersForCustomer @id".

    Then the DAL may figure out how to put those two results sets together. But both results are returned from a single call to the DB.