The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

dlaub (4) [Avatar] Offline
#1
I would love to have complete database independence and do away with all my stored procedures (I already have no business logic in the SPs) But my DBAs say performance will suffer (we run sql server 2005) Is this true, is this a myth?
tobinharris (38) [Avatar] Offline
#2
Re: Are Stored Procedures a good ldea
Hehe, this is the stuff of heated debates smilie

All I can do is give a subjective opinion... Before I do that, my first piece of advice would be to run the SQL profiler against an app using NH + SQL, and compare to the sprocs.

It's not a myth. Stored procedures tend to run faster than SQL. However, in most apps I've worked on, the speed-differnce is insignificant. Bear in mind that network latency is usually where most time gets eaten up (web browser <-> server for example)

So, I find the speed trade-off of using NHibernate generated SQL worthwhile since I don't have to spend time maintaining stored procedures. I get less places to debug and all my data access code is in once place, and version controlled. I find it cheaper to develop that way.

If you're app works with 1,000,000+ records, you may find you need to use stored procedures to get optimal performance. There are other situations where sprocs really shine too (large data-intensive batch operations, for example). Before writing sprocs to overcome performance problems, my first task would be index analysis and optimisation. Then, if better performance is needed, write your sprocs. NHibernate can be configured to work with sprocs too.

Hope this helps.

T
dlaub (4) [Avatar] Offline
#3
Re: Are Stored Procedures a good ldea
Thanks - your response was very helpful! - I will do the profiler tests. We do have 1,000,000+ row tables, so SPs might be a necessary "evil"