Should SQLServer/.Net applications be built entirely with Stored Procedures?

Question

My partner at work is OLD SCHOOL, seriously. He seems to think that it is a good idea to build our ASP.Net(VB) applications using HUGE stored procedures. I am talking practically NO application logic in the OOP enviornment that .Net provides. Incidentally, the application we are working on is BLAZINGLY slow, and we can't seem to isolate the pronblem. I am thinking that it is either the stored procedures, or the fact that he uses 7 databases for a single application. Like most old timers, I can't seem to convince him of my position. I haven't found any concrete evidence that I am correct, but I am about to begin the research in earnest. What do you think, am I on the right track, or am I barking up the wrong tree?

Answer 1

For an application to run slow there can be so many reasons.

As far as your question is concerned, the purpose of a stored procedure is to execute a batch of SQL Statements at one call.

Do remember that Only go for the stored procedure if you have some modification statements(Insert,Update,Delete).Or else consider using ADO.Net in combination with SQL User-defined functions.There are no rules that you should have stored procedures to make your application run fast.Yes it helps.At the end it is the SQL Queries you write either in Stored procedure or the .NET application that matter most.

If you put all your logic in sored procedure make sure where are you using your stored procedure.Because any change in the stored procedure will effect all the calls to the SP througout the application.

Answer 2

Hi,



I think I can give you some suggestions. As a point of reference, I am MCSD and MCDBA, and spent several years as an enterprise architect with a major software company. I also taught development with a Microsoft Training Provider. ;)



There are some benefits to using stored procs for DB access. Especially parameterized ones since they can be pre-compiled for faster execution. However, this technique must be used correctly. Misusing stored procs can slow down the processor, but it should not make it "Blazingly slow"...



The item in your post which concerns me most is that the app logic is in the database. Most likely written in T-SQL. This is VERY, VERY poor architecture design because it means that DB servers will be your bottleneck and you will have substantial scalability issues.



Other than the fact that T-SQL execution is somewhat less efficient than VB/C#, but no so much as to cause horrible slow speed. My first guess for the "Blazingly Slow" performance is table locking.



The second potential problem is security and logins. I highly recommend connection pooling if at all possible which should give some performance gain if you are not currently using it.



Lastly, I wanted to make certain you are aware of the SQL server PerfMon (short for Performance Monitor). You can trace the execution of SQL statements which will let you know the cause of the slow performance. Just open Query Analyzer and execute the Stored Procs. This should be all the proof you need to show your partner there are some problems.



If the stored procs execute quickly, then either the code accessing the DB is slow (probably from not using connection pooling), there is a networking constraints to/from the DB.



Best of Luck,



Leo



ps. There is also a chance that you do not have correct indexes OR you are hitting the same table too many times.

Answer 3

First of all - as I'm a 25 year veteran - let me slap you cross the mouth, for bad-mouthing OLD TIMERS :-)



I work in a slightly different field. After many years of mainframe design & architecture work, I have now spent a number of years designing ditributed architecture - mostly recently getting into SOA design & building the ESB bus as a bit of light relief.



But, if you have large/complex Stored Procedures, and they aren't infiltrated with business logic - I'd guess there's a data modeling problems somewhere. Possible the business processes are imperfectly designed & that has resulted in poor interfaces.



Perhaps you can break down the data access, to sue more fine-grained SP's to perform the real database access & wrap them in a secondary transformation/enrichment/consolidation data layer ?



Are you working for a large enterprise client, that won't consider redesigning their databases, to reflect their business ? That's a common issue I have and sometimes.....you just gotta live with it - they pay you maybe $10 million to deliver something & they don't expect you to recommend changes to them - they just want their new apps to fit seamlessly into their existing environment.



But hey - that's why they pay us the big bucks (well, they pay me big bucks. If you're on poverty wages - that's your affair) !



GOOD LUCK !