Geek Answers Handbook

Stored Procedures - General or Specific

What is the preferred way to use stored procedures between the two following methods and why:

One common SP, such as "GetOrders", that returns all the columns for the Order table. Several different parts of the application will use the same SP.

OR

Some more specific SPs, such as "GetOrdersForUse1" and "GetOrdersForUse2", which return a subset of all columns. Each SP is used by only one part of the application.

In general, an application will use only a subset of the columns returned by SP. I thought about using a specific method for performance reasons, but would it really be worth the extra work? I am developing a website using ASP.NET and SQL 2005.

+3
source share
3 answers

Like all great things, it depends. How different is the logic in your variations. If, for example, the only difference is the return columns, then all your savings have some bandwidth over the network, and some memory, both of which are much cheaper, and then the time that it is going to take to create the options, checks them and supports them.

Now, if there is a very significant selection logic (joining different tables, etc.), then you might be better off having specialized SPs.

The latter is not prematurely optimized. Build it simple and working first, and then when you find that you need an extra millisecond, you can look at the setting.

+1
source

I would choose the second option simply because you should NOT retrieve data from the database you need (rows or columns) - it puts an unnecessary load on the DBMS and transfers useless data over the cable, losing network bandwidth.

+1
source

Think of them as functions if you write a separate function, and then maybe use separate stored procedures. If in doubt, use separate stored procedures because:

  • it will save bandwidth
  • he will save memory

I find individual stored procedures easier to maintain than one giant one.

+1
source

More articles:


All Articles