Any contraindication of using Stored Procedures performing SELECT, UPDATE, INSTERT...

Question

I'm using one SP performing all CRUD operations
So, basically I'm executing same SP depending what action necesary:

eg

-- for select
exec USP_ORDER @MODE='S', @ORDER_DATE='2009/01/01'
-- for update
exec USP_ORDER @MODE='U', @other_params
-- for insert
exec USP_ORDER @MODE='I', @other_params
-- for delete
exec USP_ORDER @MODE='D', @ID=100

Thanks that I have only 1 SP per 1 Buisness Object which keeps my DB ordered. But recently I experienced performance issues. In light on that my question:
Is this approach correct? Can it has influence on performance / proper exec. plan?

Answer 1

It can have performance implications due to possible caching the 'wrong' query plan. Check out the topics 'parameter sniffing' and query plan caching.

EDIT: In response to John's comment, you could also have your top level SP decide which CRUD SP to call, then each would get its own cached query plan.

Answer 2

I think this is more a coding/design preference question.

Personally, I am big fan of keeping things simple and for this reason I would suggest you break out your operations into separate stored procedures.

This will be more transparent and also aide any performance tuning you may have to do in future, i.e. if your update procedure/logic is performing slowly, you can immediately isolate it as the cause whereas if the logic is part of a much larger procedure with varying CRUD operations, the root cause of the issue will not be quite so obvious.

Answer 3

I'm also fan of simplification (if possible).

But the reason I've decided on that way: currently I have ~80 SPs If I divide all by function they serve (eg. USP_Sample_Insert, USP_Sample_Select1, USP_Sample_Select2, USP_Sample_Delete) I will have ~400 SPs!

Managing, navigating, updating, sync parameters between such huge amount if SP instancess would be nightmare to me.

For me - only reasoneble case to do it is performance....

PS Does anybody know how to agregate SPs into folders in MSSMS?