I need your help to one strategic question in my programing for database.
I work on Delphi 2010 and mssql 2012. Components Sdac for access to database. I quite often use one stored procedure for very many often absolutely different tasks. Fortunately mssql allows different result sets in one stored procedure. I have a parameter @Wmode int (and several others too) and each value of wmode is separate task. Some of this are heavy other not, some have quite a lot of code some very few.
I use this approach because I try to put as much logic of application in code of stored procedures because at least for me its much easier to make any modifications, to test, debug, to set logs or to execute separately of my application. But it isn't very good to make huge quantity of stored procedures and it isn't really efficient to get needed one from this huge quantity too.
Tell me please how much this approach can affect a performance this requests to database and how i can improve it. Is it better for me to use such approach only to seldomly executing parts of application. Can you give some links to read on this subject.
one idea is
.... all stored procs seperate ..small small bits
..... in main stored proc call the individual stored procs
how you ORGANIZE them is the key ?
1 Like