How many strored procedures can....

  • I'm looking for a general kind of rule as to the number of stored procedures that can be called from a single web page.

    I've got 5 different stored procedures that I want to call on my page. They are fairly small in terms of the info they will search and return to the page.

    I just want to understand if Sql server has a certain amount of overhead it must use for each procedure regardless of complexity.

  • Not really. Most of the time will be building and tearing down the connection. If you can, use the same connection over and over.

    We call a few dozen on some Cold Fusion pages and they work fine.

    Steve Jones

  • I would expect the overhead of calling them to be minimal compared to the work they would be doing. Still, every round trip hurts (bandwidth) and it starts to add up. You might take a look at using one proc to call the others, or have a proc return multiple recordsets - either are valid methods to reduce bandwidth usage.


  • I tend to agree with Andy. Another thing to be aware of is that you should only return what you need. I often see select * when only a few fields are needed. Stored procedures should do the same thing and only return what is needed. Multiple recordsets are a good option, but most developers have trouble dealing with them.

    Steve Jones

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

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