Stored Procs vs. ADO.NET representation

[Sazlo]

Hello,

This is a bit of a long read, but heres to hoping you read to the end.

Perhaps this is an age old discussion, perhaps not. I am a long
standing advocate of using stored procs when accessing SQL server. For
me, its stored procs for everything, basic CRUD operations, complex
data management or manipulation for DTS packages etc. I have a new
found friend that disagrees. He is not of the school of thought that
stored procs are evil, he just doesn't see the added benefit.

Its his opinion that the use of stored procs has the potential to
increase information retrieval time and error reporting time. He feels
that if you recreate the table structure as a series of tables in a
DataSet, with a full setup of DataRelations etc. You can stop trips to
the DB for data validation (referencial integrity and such) and thus
reduce the time it takes for the application to generate a response in
favor or against the request.

This leads to the issue of data representation. I am of the belief that
the business layer of an application should be exposed as plain old
objects (Employee, house, bottle) and be treated as separate entities
that handle their own functionality, he is of the type that would
prefer a collective approach of sorts. Encapsulate a Datatable into a
object and iterate through it as a collection, retrieving information
via indexes or Primary keys (into and from the business layer all the
way up to presentation), thus having all the information present in a
table for updating when ready. He would use one object with 50
employees; I would use 50 employee objects.

I hope you can see the argument here:

Does the use of stored procs in order to keep client applications (at
any abstraction) ignorant to a persistent data structure worth the
5-10millisecond lag time(or less) introduced into the CRUD process?

Is there a break even point to which using an in memory representation
of a DB out ways the taxing of a server with user requests for stored
procs?

If given an ability to quickly and easily rewrite the code to create
the "in memory" database structure after a change and it just
becomes a matter of recompilation and redistribution of applications,
does that justify the strong coupling of data and business layers? In
a web environment?

Thank you kindly for your time and consideration of this matter.
Saz

 

[Nerseus]

First, stored procedures can do all sorts of different things. They can (and often do) have some level of business rules in them. They can do simple INSERT, UPDATE, DELETE and SELECTs as well. More often, about 75% of the procs in a system are the "simple" kind. It's the 25% that we spend 80% of our development time. This includes searches, complex "gets", and reports.

Depending on how you implemented your system, this 25% of 80% could also include the "save" procs. Here are some solutions I've seen/used in regards to saving data:
1. Have one proc per INSERT, UPDATE or DELETE. Requires an outside transaction (I've used COM in VB6 with MSDTC and ADO.NET transactions, among others).
2. Have a number of procs that each do more complex saving. Similar to #1.
3. Have a single "save all" proc. This was done with an XML string passed to a proc which used OPENXML (in SQL Server) to read the XML as a table.

That was info to shed some light on my background as to how I'm going to answer the questions you asked.
Since you mentioned procs, I assume you do not want to consider dynamic SQL. If you're like most, you immediately dismiss dynamic SQL for a number of reasons. You may want to read this article and some of the ones that spawned from it.

Also, before I answer any questions about procs - or DB access at all - I'll focus a short paragraph on what I think the real question is: How do you best separate out the business layer from the DB layer?

Since most business apps worth talking about save data to a relational database, our first inclination as developers is to try and get that data in a similar structure in our code. .NET has made DataSets and they are PERFECT for representing DB data in client-side or webserver code. They also have a ton of advantages such as binding, remembering original values, selecting filtered views of data, etc.

That brings up a fundamental question of what do you store in a business object. You may also debate whether you need so many "layers" if the "business" object has a DataSet that's ripe for sending to the DB for updates.

I would throw this out first: In my experience, working with "pure" objects for client side applications is FAR nicer (more maintainable with easier to read code) than working with data-centric objects. Once I moved to more OO programming on the client code, I could finally "see the light" as to why objects are more intuitive. Unfortunately, I have yet to see any good book on how to apply OO ideas to most business applications. In other words, how do you step from "I have an object that represents a noun - such as a tire and a car" to a real world application that has bizarre things like customer data, procedure codes, fees and other things.

Regardless of whether you go with an OO solution or not, there's still the question of how you represent things on the screen and how you get them into the DB.

Does the use of stored procs in order to keep client applications (at
any abstraction) ignorant to a persistent data structure worth the
5-10millisecond lag time(or less) introduced into the CRUD process?

Easy/short answer: I would *always* go for easier to understand code on a first phase of design. Go for the easy solution and don't worry about time to call the DB for gets or updates.

Explanation:
I make this assumption on fact: I'm a smart guy and I can figure things out. From that I assume that if my code does what it's supposed to, but it's slow, I can work on tweaking things later. If I wrote 100 stored procs and 80 of them are too slow because of individual calls instead of combining them, then I say "oh well" and I combine them. You could argue that spending time up front to "figure out" how to make them faster is worth it. I would say this: if you're smart enough to know how to make them faster up front then you would just do that - you wouldn't really need to plan for it. That "smartness" is really experience. If you don't have that experience, then I can bet that most of the time the planning to make things faster before you know what's slow is going to bit you in the butt.

Is there a break even point to which using an in memory representation
of a DB out ways the taxing of a server with user requests for stored
procs?

I think I hear a bad assumption being made from your friend. The argument your friend sounds like he assumes that a DataSet may be able to handle referential integrity "faster" because it prevents DB calls may be true, but it would be true even without DataSets, I would hope!

If given an ability to quickly and easily rewrite the code to create
the "in memory" database structure after a change and it just
becomes a matter of recompilation and redistribution of applications,
does that justify the strong coupling of data and business layers? In
a web environment?

This answer may be one of the easiest to answer. One of the main reasons to have more layers (n-tier) is to break up the dependency/coupling of those layers - to separate out what each layer does. From that point of view, I would hope that a DB change (table changes, etc.) would mostly just affect the stored procedures.

Here's a thought:
Why not use DataSets to get data from the DB. Use stored procedures, if that's your method, to get data out of the DB and into the DataSet. Whether that's one big proc call or a bunch of small ones - don't worry, just take a route and run with it. Return the DataSet to the client but wrap it in an object for later. When it's time to save, call a method on the object. Use whatever you want to save the DataSet - DataAdapter, individual Command objects, whatever.

Now you've got a model to start with and you have your layers.

The next step, in my mind, is to make that object useful. Give it some meat. Don't let the UI use the DataSet directly unless necessary. For example, most of the apps I work on have 5 or 6 meaty tables. The rest are lookups to populate drop-downs or enforce some kind of rule - the RI your friend mentioned. Of the 5 or 6 meaty tables, 1 is usually represented in a "header", 1 or 2 are tables with one row and 2 or 3 usually end up needing some kind of grid to allow adding/removing/editing rows. I would start by looking at your UI (or requirements spec if you have one) and encapsulating the non-grid tables. There's no reason single row tables have to be exposed via the whole dataset. A purist may argue that the UI shouldn't even know that a DataSet is involved. To that I saw "who cares?" - it's usually the same developer writing the object and the UI! Change the UI to use the object as much as possible. When you run into the grids, you more or less have to bind to something. It's kinda pointless to KNOW that you have a DataSet and yet only expose collections of objects that aren't condusive to binding, which is what the grid wants. Why rewrite all the code that the DataSet provides? To be pure OO? Poo on that, use the DataSet and save yourself some time.

And that's my short answer on this subject.

-ner

 

[Sazlo]

Absolutely beautiful. Thank you very much *a 1000 times over* for providing such an in depth thought. It will go along ways and is much appreciated.

Saz