www.msteched.com/.../view.aspx On Wednesday, I participated on a panel that discussed data access options.  We started out with Richard Campbell throwing in Entity Framework.  The panel was

Oh this will be GREAT! ;)

Jeff,

For most of the talk, you seemed a little more pro stored procedure then I thought you would be. At the end when you asked everyone on the panel to talk about how they handle data, you said you used nHibernate for 90+% and then sprocs when needed, which is what I had been expecting from you.

I would have liked to hear more on the security aspect of sprocs vs. non-sprocs. My counter argument to the pro-sproc camp in the context of security, is if the web box gets compromised, forcing the hacker to call sprocs instead of being able to write select * from Table is only going to slow them down a little bit.

Also, something that seemed to be glossed over was looking at your QoS and non-functional requirements. At what point do you really need the extra performance afforded by using stored procedures vs. using an ORM. We're not all writing software that needs millisecond response times with user loads exceeding thousands of users at the same time. Also, the argument that RAM (hardware) is cheap could be a pro ORM argument as well.

I think the panel discussion was a great format, and would have liked to see it go longer. Perhaps part II can be held during PDC09.

Adam

Yeah, pretty much what Jeff said above. Was expecting there to be more from a non-SP pro-ORM standpoint but it was more of the same, and very old, dba-as-the-data-craftsmen diatribe. I don't understand why anyone would look to SPs for security (if your box gets compromised, you've got bigger issues than your choice of data access techniques, and surely backups are common regardless?), and performance. Storage is a commodity, hardware is cheap, and programmers aren't.

Yeah, pretty much what Jeff said above. Was expecting there to be more from a non-SP pro-ORM standpoint but it was more of the same, and very old, dba-as-the-data-craftsmen diatribe. I don't understand why anyone would look to SPs for security (if your box gets compromised, you've got bigger issues than your choice of data access techniques, and surely backups are common regardless?), and performance. Storage is a commodity, hardware is cheap, and programmers aren't.

i thought many of the cons of sprocs was not properly brought up. For example the huge amount of more code (SQL/C#) that sprocs usually mean compared relying on adhoc sql that Nhibernate generate.

For 95% av all queries writing a store procedure seems like a huge waste of time.

Another problem is that sprocs usually mean a lot of code duplication among sprocs and more importantly between logic in sprocs and logic needed in the app.

Good comments. I really love NHibernate, and I use it for 90+% of queries. However, the make-up of the panel were people who are SQL Server professionals. They have built their careers on the relational databvase. I attempted not to attack a premise of their career, because I don't think that's productive. I attempted to discuss architecture, which is of much more importance than the nuts and bolts of using a stored procedure. The title of the panel was too narrow also, IMO

"I attempted not to attack a premise of their career, because I don't think that's productive."

You don't have to attack them to disagree with their approach. What's the point of the pros/cons if everyone agrees. This was more of a discussion about about the misconceptions of stored procs.

There was also an undercurrent of ORM's are an option, but when would you really use it?

There was too much "it depends". This is suppose to be a panel of experts. Experts need to have an opinion and express those opinions to the masses. Not that you need to take a hard stance and scoff at those who disagree, but you need to say why your method works for you. Most developers don't know what scenario they are in, much less how to analyze it.

Most apps are not doing major data crunching for the bulk of their work. it's small atomic workflows. The preformance argument doesn't really stand out when you are talking about a handful of queries per unit of work. SP vs ORM on preformance is a wash.

I was looking forward to this discussion, but left disappointed and wanting a true debate about SP vs. ORM.

I look forward to other methodology and tool discussions in the future. I do hope that you will talk about what you practice rather than joining the majority of your peers.

@Jason,

The panel was not to discus SP vs. ORM. It was to discuss Stored Procedures. If you are interested in discussing more data access methods more broadly, make sure to check out the next panel in this series (will probably be posted tomorrow). It deals with the ORM factors as well.

The above panel _was_ specific to stored procedure use.

The last guy's (I don't know his name) comment in video really upsets me. He didn't really listen to what other people were saying and he went on to say stored procedure should be a default ... what a joke ... And when the third last person spoke, he tried to inject his pro-stored procedure idea.

My experience is that there are two kinds of people who REALLY LOVE stored procedure - SQL experts that doesn't really understand why some developers don't like stored procedure ... and developers who doesn't know that they have an option of not using stored procedures ...

I can understand Jeffrey's position. Those SQL 'experts' are not easy to give up their love, and the only hope is to show them there are alternatives ...

I too was a little disappointed in this talk. The maintenance issues along are enough to steer me away from stored procs except in cases like Jeffrey said, where doing it in C#/ORM isn't straight forward or performant enough. SQL while it has evolved, is still lags way behind C# and other modern languages. There are no objects or arrays in t-sql. There's no inheritance or lambda expressions. Just passing a list of items from application to stored proc is painful.

Just writing 5 stored procs, say 2 SELECTs and the 3 CUDs (create, update, delete) will at the very LEAST require you to duplicate the column names of the table 5 times. And thats just the beginning.

I would have like to hear more about these kind of issues, even if you're not using an ORM, you can do a lot of your own data access/mapping and reuse a lot more code in C#.

Stored Procedures are the assembly language of our time.

"Stored Procedures are the assembly language of our time."

It really cannot be put any more succinctly. Bravo.

@Jeffery

"The panel was not to discus SP vs. ORM. It was to discuss Stored Procedures." No wonder I was disappointed :) I look forward to the next video.

Dissapointmenting from a lack of arguements from the non-pro sproc side. I realize this was comprised of mostly MS sql professionals but had this been a group of Java guys it would have been the other way around. I would add I think it takes more work to test sprocs than say NHibernate hql or object queries.

Nearly everyone on the panel with the exception of Eric and Jeff seemed to be focused on one particular area of expertise - SQL Server. So their bias is expected. Jeff and Eric did as well as they could to inject their opinion without starting too many useless arguments. None of the people in the panel could be convinced that stored procedures are not maintainable. They all seem to accept the maintenance headache as a necessary evil to maintain the 5% performance benefit they provide.