posts - 916, comments - 758, trackbacks - 11

My Links



Post Categories

Misc. Coding

Stored Procedures versus Dynamic SQL - the old debate...Frans Bouma's take...

Frans Bouma really doesn't see the point of using Stored Procedures over using Dynamic SQL - so Frans, what's your take on such things as Indexed Views and User Defined Functions - which are essentially SPs but there's no equivalent for Dynamic SQL, so you're suggesting I should not use these any more?
To be honest I'm always a bit reticent to pay much credence to an argument on an approach to Data Access from someone who has a vested interest (I know LLBLGen Pro supports SPs but it does have a strong bias towards Dynamically Generated T-SQL), kinda like MS on the Linux debate...slight vested interest (or former Halliburton CEO on Iraq - but that's a different matter :-))
I do use SPs almost exclusively for Data access, I have no vested interest in one approach or the other, I used to use Dynamic SQL in the bad old days of ASP/VBScript but switched to SPs when I needed more oomph in my data access code. I have a strong preference for manipulating data using Set based logic, yes, for more complex operations I'll bring the data into code but in general, I use the DB for manipulating and then return the smallest amount of data back to code. The reason I do this isn't stubbornness, simply I find it easier to filter and composite my data closest to it's source.
I also find some of Frans's arguments a bit lacking, there is an argument for using Dynamic SQL where you have to react to user input for filtering data; reports are a classic example where you might want to find matching data items based on a variable number of inputs - by all means use Dynamic SQL for that, but in 90% of cases I just want to pull data from a DB as quickly and painlessly as possible this is where I'd use SPs. I often use Table variables for compositing and filtering data, treating SPs as set providers and modifying the eventual output resultset to contain only the data I actually need - this would usually require multiple trips to and from the DB (we're talking in the dozens here for each instance) and would be pretty slow, as well as causing heavy network traffic which I can usually ill afford in highly scalable sites.
In short what I'm saying is that for my purposes, the 90% Stored Procedures, 10% Dynamic SQL works best - making absolute statements about such things is like commenting on religion, politics or Open Source or Java - I don't believe there is a completely binary argument to be made in any of these areas, there's too many entrenched views - horses for courses is my opinion :-)

Print | posted on Tuesday, November 18, 2003 5:12 PM | Filed Under [ Links Long & Rambling SQL ]


# re: Stored Procedures versus Dynamic SQL - the old debate...Frans Bouma's take...

You can discard my arguments because I seem to have a 'vested interest', but that's your choice of course :) If you search for LLBLGen on the forums you'll find an old thread between Thomiszec and me. I defend SP's, he defends dynamic SQL. I lost 2 months of development because I tried to create a DAL with SP's and a flexible usage of that DAL in C#/VB.NET code. I failed miserably, because the SPs API is too inflexible. I was convinced, Dynamic SQL is the better choice. You don't have to believe me, that's your choice.

If you read my blog well enough you'll see that I mention 'views' as well. Objects inside the database which can be used inside the database in a flexible way, views are one of them functions are another, are usable. A stored procedure for doing DML is something else than a function which prepares a given set of data for you (like calculate a next date on a given date) which are used for default values etc. A stored procedure with DML is very inflexible. If you don't believe me, that's fine, that's your choice. However answer me this: how many stored procedures does it take YOU to create a set of filters on a given table with 10 fields and 3 FK's. At least 6, and more if you're unlucky. If you think that's flexible, fine, that's your choice. However I've run into various occasions where clients had over 500+ stored procedures, created during a 2 year lifespan and it was a complete nightmare to CHANGE some functionality. Remember: BL logic isn't in the stored procs: they just have to get you the data or modify data for you.

If you still think that's flexible enough for you, fine, that's your choice.

Oh, and using table variables is very slow for filtering. (See SqlServer magazine last month if I'm not mistaken). Table variables are not that of an requirement either, because most filters can be constructed using the FROM clauses in for example UPDATE and DELETE (delete has double FROM clauses), or joins with filters.

"but in 90% of cases I just want to pull data from a DB as quickly and painlessly as possible this is where I'd use SPs."
I want that too, but I learned the hard way that what you state isn't as simple as it gets. LLBLGen 1.x generated stored procs. However the generated procs weren't enough for a lot of situations because they didn't contain filters on non-FK fields for example. For update/delete logic idem. A simple select using the PK is great, however how many times do you have to filter on 2 or 3 fields, sometimes filter on a field in a related table? That always requires for YOU a new stored procedure, because the one filtering on just the PK isn't sufficient. Now, you probably will say that that argument is in most situations nonsence, that's your choice. I develop database oriented software for a long time now and I can assure you, stored procedures are severily limiting your development.

You don't have to buy my product, I didn't wrote that blog because I want to sell my tool, I want to convince other people what I learned THE HARD WAY, by losing 2 months of development time. Stored procedures CAN be great, but when using them you should be aware of the disadvantages of them AND should be aware of the fact that there are alternatives. Microsoft moves towards Object spaces in .NET and their next gen business software framework which is their next gen cash cow product. Objectspaces generate dynamic queries on the fly. Now, tell me, why would MS do that when SP are so much better?

11/18/2003 6:12 PM | Frans Bouma

# re: Stored Procedures versus Dynamic SQL - the old debate...Frans Bouma's take...

Frans, thanks for the comments, let me respond to some of your comments. I came to using SPs the hard way, via spaghetti PL-SQL and T-SQL embedded in code and through using pure OODBMS systems with J2EE. The object approach as espoused by O-R generators such as your own is a good approach for some users I agree.
You argument:
"how many stored procedures does it take YOU to create a set of filters on a given table with 10 fields and 3 FK's", I stated, I don't do this, if I have to vary my filter, I'll probably use Dynamic SQL, it's all about using the correct tool for the correct job.
The stuff about changing 500+ stored procedures, you do have to architect and design how your SPs interact with your data just like you do for objects in your application, this IS a limitation of traditional RBMS systems however, if I had 500+ instances of Dynamically generated T-SQL distributed throughout my DAL, I'd be even more reticent to change them - they don't compile against the DB so I can't be sure they will function correctly when I come to use them.
An O/R mapper such as yours does partially solve these problems (as ObjectSpaces probably will too) what no O/R mappers I've come across (I haven't used yours so I cannot comment on yours specifically) address is the need to limit network calls in scalable apps, they typically fire off multiple calls to composite the object required. Essentially my argument boils down to the fact that you attempt to dismiss ALL uses of Stored Procedures in your post, that is simply not a realistic approach to take.
You're obviously pretty knowledgable about the subject but taking an approach which only advocates one position is flawed in my opinion.

11/18/2003 6:39 PM | Scott Galloway

# Dynamisk SQL eller SP?

I det seneste d

11/19/2003 2:21 AM | guidmaster

Comments have been closed on this topic.

Powered by: