I pulled this out of the "multiple views..." thread since it's really its own topic.
xAvailx:
I am not looking for a neverending debate. As I previously posted, I was looking for specific verifiable and reliable resources so I can research further.
Again, pretty much all of my previous research and experience says otherwise (performance, security, maintenability, etc...) about using dynamic sql from a client application and I am not looking for a debate, just some reliable resources I can research further for which "specific" scenarios client dynamic sql is "better" than a stored proc.
If you have any specific links, I would appreciate if you would share.
Thx.
Well, you can start by looking in the SQL 2005 Books Online under Stored Procedures Basics. You'll note in the "benefits" section there isn't a mention of performance anywhere in sight. In contrast, in SQL 2000 Books Online there is a specific section implying (erroneously) that the compiling of stored procedures brings a performance benefit. So, the official Microsoft position is that performance is not a reason to choose sprocs - is anything more necessary?
The following is a rather infamous blog wherein the author later corrected himself and admitted that the pre-compile argument was complete myth.
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx
The following is a fairly well-known blog on the myths of stored procedure advantages. Frans is perhaps a little extreme himself in his disdain for sprocs, but it gives good arguments against the myths which have fueled the "always use stored procedures" position.
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
The alleged security advantages of sprocs have been hit on in the other thread... views offer many of the same advantages, at least when you're talking about reading the data. As I said before, I do think sprocs offer some benefit for writing and I do my writing with sprocs exclusively. Among other things, they provide severe limitations on the amount of damage a wild insert/update/delete command can do.
There is the potential for some benefit from the reduction of network traffic, and the difference in the traffic TO the server can be quite drastic in some cases.
As far as maintainability, that's a matter of opinion of course. Even more significant, it's a matter of the particular talent pool you have at your disposal. Everyone writing business objects understands the language being used there, but they might not necessarily understand a sproc with temp tables or coalesce commands.
I don't have time to repeat all my research, but as for a specific case where dynamic SQL is more performant, see what you can find googling including "coalesce". That was one specific example I remember - the problem being basically that in a complex sproc with lots of conditional testing going on you ended up with two problems:
1 - a lot more code to run compared to the business layer that likely already knows exactly what it needs to do
2 - the final query that ends up being performed is so variable that you really have no cached execution plans and therefore no performance benefit over the specific ad-hoc dynamic calls
david.wendelken:
Gosh, we sure are not communicating well! :(No, absolutely not. I am not talking about allowing unknown/untrusted applications to communicate with the database. I am talking about someone writing a web page for their own application that allows a user of that page to inject unwanted sql commands into it - all because they dynamically constructed the sql statement by truncating user-entered strings together. Are we mis-communicating because you don't see how this is a problem?
Why does dynamic sql become an exercise in tacking strings together
blindly? You have this problem calling a stored proc as well, if you
just build the Sql call by tacking strings together. The solution is
to parameterize your query (which you can do with any sql, not just
proc calls). I've never argued against parameterizing your query, quite the opposite.
Again, we are not communicating. If your business object accepts user-entered free-form text and constructs the sql statement to be issued by concatenating the strings together, a user of that web page has the capability to totally and completely subvert the security the business object tries to add.
Where did I ever say you just stick strings together? I said
dynamic sql is fine, and it is. The problems you describe are solved
by parametizing the query, but again you don't need a proc to
parametize a sql statement.
I agree that proper usage of database roles is good practice - and I will continue to use it. It's a good, basic level of security that's very cost effective. This technique is a quick and inexpensive way to safely use dynamically created sql on a query page. That's all.
I wouldn't consider role based security basic... I would say its fundamental to proper security in a Sql server database.
It's not far-fetched. Happens in big business all the time. Depending upon software licensing arrangements, using a different database server and database instance might cost huge pots of money in licensing fees. Plus extra hardware. And extra rent, heating, cooling, hardware maintenance and repair fees, etc. Plus the extra labor cost - even with code utilities to help out - of keeping multiple installations synchronized.
All those costs can easily be justified if the alternative is that your competitor even has a slight chance
of harming your business because they store their data in the same
database. Also, in a hosted environment (which is likely how you'd end
up in such a scenario), the hosting fees for a dedicated server are
still much less than simply adding up all those expenses. But again,
dynamic sql is not a problem as long as you parameteize your query just
as you would with a stored procedure call.
Hi Guys
This is an interesting discussion. The arguments for and against stored procedures are fairly well-known - they're all over the web as you've pointed out.
I'm wondering what most of the CSLA community use. I would suspect that sprocs are pretty much the rule, but there are those of us, such as myself that have built on top of ORMs.
I use LLBLGen which is interesting in itself as it purports to be a complete solution for n-tier development - basically you transport the objects generated from the database to your client and back again. I've been down that track and you get some awful compromises and exposure of the data model design to your UI.
Instead, I have a BO designer (that I built) that plugs into the LLBLGen Designer environment. It gives me access to all the LLBLGen-generated classes and I can construct my BOs from whatever entity objects I want - I can even get down to database views and sprocs if I want.
Anyway, I am wondering if the great divide around ORMs and dynamic SQL vs. SPROCs mainly comes down to who owns the DB, and whether you have the services of a DBA. I work for a small ISV and basically our applications "own" the database and so we're "in charge" of the DB and can basically use whatever technology is required to "do our stuff". The customer really doesn't care, as long as it does its job.
Perhaps in an enterprise environment, where DBAs "rule the roost" you're more constrained about what technology is allowed to be used.
Just some random thoughts on the subject. Hope everyone has a great Christmas (or whatever you happen to celebrate at this time of year!)
Craig
![Wink [;)]](/emoticons/emotion-5.gif)
Andy
I well remember the bad old days when I used VB3 and we connected to an Access database and also being surprised when our code bombed out with names with a single quote character in them.
So what are the advantages you see in your custom DAL over using Stored Procs or some other commercial or open source ORM?
Craig
[Answering Dave here]Gosh, we sure are not communicating well! :(
Ok, now we are beginning to communicate! Our unshared assumptions are now understood. :)
Why does dynamic sql become an exercise in tacking strings together blindly? You have this problem calling a stored proc as well, if you just build the Sql call by tacking strings together. The solution is to parameterize your query (which you can do with any sql, not just proc calls). I've never argued against parameterizing your query, quite the opposite.
I'm all for parameterizing. Here's an example.
select p.*
from project p
inner join table1 t1 on t1.id = p.t1_id
inner join table2 t2 on t2.id = p.t2_id
inner join table3 t3 on t3.id = p.t3_id
inner join table4 t4 on t4.id = p.t4_id
inner join table5 t5 on t5.id = p.t5_id
inner join table6 t6 on t6.id = p.t6_id
where t1.field1 like @field1
and t1.field2 like @field2
and t2.field3 like @field3
and t2.field4 like @field4
and t3.field5 like @field5
and t3.field6 like @field6
and t4.field7 like @field7
...
and t6.field99 like @field99
Now, I agree that I could build a parameterized query in a business object or via a stored procedure that had 99 parameters, fields 1 thru 99. Those parameters could all default to "do not care, show me everything". It would be perfectly safe.
If that's what you mean by dynamic sql, then I agree with your points as you presented them.
But it's not what I mean by dynamic sql.
Let's suppose that joining to table table2 is really, really, really slow. And that the users do not want to do so 99% of the time. So, if the users left the on-screen parameters that map to table2 as a wild-card, I would prefer to construct my query at run time like this:
select p.*
from project p
inner join table1 t1 on t1.id = p.t1_id
inner join table3 t3 on t3.id = p.t3_id
inner join table4 t4 on t4.id = p.t4_id
inner join table5 t5 on t5.id = p.t5_id
inner join table6 t6 on t6.id = p.t6_id
where t1.field1 like @field1
and t1.field2 like @field2
and t3.field5 like @field5
and t3.field6 like @field6
and t4.field7 like @field7
...
and t6.field99 like @field99
Note the complete absence of any reference to table2. The query should now run much faster.
That's what I mean by dynamic sql.
If you have a ready-made solution to parameterizing this so that entire references to tables, where clause lines, etc, can be included or not, I would like to know how!
And I'm not being a smart-aleck when I say that. I have lots to learn in this technology stack.
I apparently am still a poor communicator. ;)
I thought your previous objections to dynamic sql were that they were risky because they allowed end-users to pass strings into the SQL statement. The response we've made is that parameterizing the queries prevents that in dynamic sql just as it does in a stored procedure.
Now you seem to have switched completely to a discussion of the challenge of creating EFFICIENT dynamic sql. That's something entirely different, and even as difficult as it may turn out to be (I don't think it is), the fact that it's even possible is precisely the advantage dynamic sql brings to the table.
And for clarity of my position and previous statements, the logic of determining what the final most-efficient query that should run can be done dynamically in the business layer or indeed in the stored procedure itself, but in the latter case you don't really have a stored procedure at all in terms of performance because there won't be a consistent execution plan anyway. And I'd argue that the sum of the "code" isn't nearly as clean, unless your talent pool is heavily slanted towards dba types.
No, I haven't switched the subject. As I recall, the original poster explained a need to allow a user to choose between many, many data filtering options. Dynamic sql was proposed as the solution because then only the sql needed would be executed.
Creating a sql statement on the fly is what I (and many others I've worked with) have called dynamic sql. I've seen the same terminology used in the MS documentation.
Parameterized queries are just static queries with parameters added. :) I don't consider them dynamic sql. It appears that you do. Maybe they are, maybe they aren't. But at least our individual terminology is now understood :) Maybe we could settle on these three terms:
My objections to constructing a sql statement from user-supplied strings (concatenated sql) for security reasons remain. You apparently agree. :)
As for the reality of my example (of skipping entire tables in the query), here's an example of why someone would do that. This sample query screen returns a list of all projects that meet the user's selection criteria. It displays the project id, project name, customer, start and finish dates.
User Requests:
"Find me all projects where Joe Shmoe was assigned to it, and in which the XYZ Machine was used." (The user is concerned about quality control issues with Joe on the XYZ Machine.)
"Find me all projects that had work performed in Upper Slobovia between 2004 CE and 2006 CE." (The user just discovered that Upper Slobovia passed a retroactive law that might affect us, and we need to investigate those projects in detail.)
"Find me all projects that are more than 10% over budget and more than 10% overdue. (The user wants to investigate those projects in detail to get them back on track.)
Neither of the above criteria are fields actually shown in the list of projects. They are just used to determine which objects are returned. That's the difference between filter criteria and display information - they don't have to be the same thing.
This need for dynamically constructed, efficient sql is why I proposed the database solution I explained in the other thread. It is a simple way that allows you to construct a sql statement at runtime using user-supplied data and be completely safe from sql injection attacks.
As for whether database stored procedures are faster than queries issued from the object layer for static, parameterized queries, I don't have a clue. Haven't done the testing to prove it one way or the other. For the situation the original poster described, it's a moot point because the execution path should vary based upon user input. If it doesn't, it's going to be inefficient anyway, because it will be querying data the user doesn't care about. :)
No, I haven't switched the subject. As I recall, the original poster explained a need to allow a user to choose between many, many data filtering options. Dynamic sql was proposed as the solution because then only the sql needed would be executed.
Creating a sql statement on the fly is what I (and many others I've worked with) have called dynamic sql. I've seen the same terminology used in the MS documentation.Parameterized queries are just static queries with parameters added. :) I don't consider them dynamic sql. It appears that you do. Maybe they are, maybe they aren't. But at least our individual terminology is now understood :) Maybe we could settle on these three terms:
- Static Sql - definitely static!
- Parameterized Sql - sort of static, sort of dynamic!
- Concatenated Sql - definitely dynamic!
My objections to constructing a sql statement from user-supplied strings (concatenated sql) for security reasons remain. You apparently agree. :)
Parameterized sql is not necessarly static or dynamic. My data searcher dynamically builds the where clause and order by clause based on the list of SelectionCriteria and OrderCriteria it receives. Even though its built dyanmically, its still parameterized. The select and from clauses by their nature will typically not change within a single business object.. after all if it does, you'll have properties which cannot be populated.
As for the reality of my example (of skipping entire tables in the query), here's an example of why someone would do that. This sample query screen returns a list of all projects that meet the user's selection criteria. It displays the project id, project name, customer, start and finish dates.User Requests:
"Find me all projects where Joe Shmoe was assigned to it, and in which the XYZ Machine was used." (The user is concerned about quality control issues with Joe on the XYZ Machine.)
"Find me all projects that had work performed in Upper Slobovia between 2004 CE and 2006 CE." (The user just discovered that Upper Slobovia passed a retroactive law that might affect us, and we need to investigate those projects in detail.)
"Find me all projects that are more than 10% over budget and more than 10% overdue. (The user wants to investigate those projects in detail to get them back on track.)
Neither of the above criteria are fields actually shown in the list of projects. They are just used to determine which objects are returned. That's the difference between filter criteria and display information - they don't have to be the same thing.
Fair enough, but I would say its better to have the BO 'know' which view to select from to perform the search.
This need for dynamically constructed, efficient sql is why I proposed the database solution I explained in the other thread. It is a simple way that allows you to construct a sql statement at runtime using user-supplied data and be completely safe from sql injection attacks.
I would still argue a better (simpler, and more maintanable) solution would be to construct various views and have the BO choose which view to select from, dynamically building the where and possibly order clauses (while parameterizing the sql).
If you haven't read the links from DansDreams, I suggest you do so. The query (whether dynamic or from a stored procedure) is cached the same way, starting with Sql server 2000.As for whether database stored procedures are faster than queries issued from the object layer for static, parameterized queries, I don't have a clue. Haven't done the testing to prove it one way or the other. For the situation the original poster described, it's a moot point because the execution path should vary based upon user input. If it doesn't, it's going to be inefficient anyway, because it will be querying data the user doesn't care about. :)
Parameterized sql is not necessarly static or dynamic. My data searcher dynamically builds the where clause and order by clause based on the list of SelectionCriteria and OrderCriteria it receives. Even though its built dyanmically, its still parameterized.
So, the $64,000 question is this:
"How are you building a query that dynamically changes what commands are in the where clause without concatenating those where clause code fragments into the sql statement?"
If you've got a spiffy technique for doing that, I would really like to learn it.
The select and from clauses by their nature will typically not change within a single business object.. after all if it does, you'll have properties which cannot be populated.
Agree with you on the select clause, not on the from clause. The select clause is to define the data elements returned, the from clause defines what data is searched. A from clause can reference lots of data that is never, ever, in the select clause. For a (hopefully) humorous example...
"Show me a list of names and addresses for every sales prospect that has neon colored paisley-print facial tattoos and over 50 body piercings on their head, plus dreadlocks - any hair color will do."
I don't want to look at them (so no image data in the select clause!), I just want their addresses so I can send them a sales brochure. :)
"How are you building a query that dynamically changes what commands are in the where clause without concatenating those where clause code fragments into the sql statement?"
Agree with you on the select clause, not on the from clause. The select clause is to define the data elements returned, the from clause defines what data is searched. A from clause can reference lots of data that is never, ever, in the select clause. For a (hopefully) humorous example..."Show me a list of names and addresses for every sales prospect that has neon colored paisley-print facial tattoos and over 50 body piercings on their head, plus dreadlocks - any hair color will do."
Again, that's better handled by changing which view to select against.
David, I'm not even sure what we're disagreeing about at this point.
We seem to all agree that letting any user hand us some sql to send to the database is an idiotic idea. Stored procedures or not is a moot point in that regard.
I think everyone who's contributed is using sprocs for updates, which means that no user has update permissions on any table. There's no need to discuss malicious updates, from sql injection or otherwise. Many, if not most, of your arguments seem to be centered around that.
You have also broght up row-level security. I went back and read the proposed solution in the other thread. In the first example ("simplest version") you conclude with explaining how it protects from sql injection, which wasn't the problem you were trying to solve. It doesn't even address the row-level security problem, so it's just a waste.
Your second solution is to have separate views that are row-level specific to each user. This has nothing to do with using stored procedures or not, of course.
Your third example of one sproc calling another also does not seem to address the row-level security issue at all, but is also apparently trying to address the sql injection risk.
So, for the sake of my thick skull, could you try once again to explain why you think stored procedures are superior to ad-hoc (non-sproc) queries for reasons other than injection protection, and explain why?
I do not use dynamic SQL or sp's. Well I do but there is no trace of this anywhere in the BO's.
I use what I call Data Requests. Basically they are xml files that define how to retrieve data. They extend the database interfaces in .NET.
For db access Data Requests are generally a block of sql. Parameters can be added and removed as needed. Each Data Request has a select, update, insert and delete request. Each is optional so a single Data Request may only allow Select. Or allow Select and Update but not delete or insert. depend upon the needs. (There is an option to allow insert, update and delete to be created from the Select part. Don't use it much but it is there.)
So my criteria classes take two things. A Data Request name and a ParameterList class. The Parameter class can handle Like, Between, IN and looking for NULLs. (The IN and Between parameters is the whole reason I created this thing in the first place. SP's parameters can't handle anything like an array and I need that functionality allot. But I will not tolerate sql in my middle tier classes.)
The thing is the Data Request can be changed from using straight SQL statements to a stored proc or UDF by simply changing the stuff in the xml. (The straight SQL is converted to parameterized SQL queries. It supports all the .NET data providers that come with the framework. Adding new ones doesn't take that much work.)
Now the BO doesn't know any thing about these requests except that they are named something and that they take parameters. The DAL controls this and knows if the data is on a local network (direct access to the db and it returns a datareader for performance) or if it is remoted (the DAL uses a remoted component and passes data tables and DataRequests back and forth.) The BO's are able to handle either the datatables or datareaders. It just looks at what type is handed to it and the populates the private fields.
One of the nice things is that the Data Requests can also handle getting info from text, xml or a couple of more types of files. I can also 'query' a web service. For xml it usually uses the WilsonXML provider. It can use simple XPATH queries but it does choke on complex ones. (I am no xml expert.)
I can also 'build' these Data Requests dynamically because they really are just CSLA (1.x) derived classes with all the cool things that brings. Internally I check and fail for the 'bad' sql words meaning no DDL statements are allowed and comments are stripped out. It also validates that a date really is a date etc.
Because the DAL handles the data retrieval I can use several data sources because each Data Request also defines the connectionStringName. The actual connection information is handled by another class and server.
The whole reason I bring this up is because the SP vs "Built" SQL debate is limiting in itself. You have to look beyond the debate and see what else can be done to make the debate moot. I rarely use SP's mainly because I have hundreds of DBs to maintain. If I could have all my DB's use a central SP server I might do it. But we only have two developers and a manager that also do all the maintenance. It is much easier to change a xml file on the server and have all the applications for all our customers use the latest and greatest. This includes the ClickOnce, Webapps and traditional windows forms.
I may be a little late to the thread, but here's one more thing to throw in that has not been mentioned in this or the previous thread: abstraction.
Craig hit the nail right on the head asking if the issue had to do with the availability of DBA's, etc. effecting the viable options. As in Andy's case, many of us are one man shows while others are working in more rigid corporate environments and this does affect the approaches we use. When in a one-man-show, we don't have to worry about satisfying the needs or working within the limitations of other developers or deal with corporate standards and/or restrictions, etc. However, and this is just me, because of my varied background I find myself developing as if in a team environment even when not.
Anyway, the point I wanted to raise has to do with the fact that stored procedures also provide an additional point of abstraction for your application. Sprocs abstract the underlying table structure from our BO's and is one of the biggest reasons I've continued to use them as all of the former benefits have slowly fallen by the wayside - whether myths or simply a result of improvements in other areas.
For example, if I have an application that displays a read-only list of contacts with columns for the contact's name, address, phone, fax and e-mail address, I would simply call the GetContactList sproc. Initially this sproc could be retrieving records from the flat Contacts table with columns of the same names. Later, either myself or a DBA or whatever sees the DB bloating and decides to normalize address data by creating a lookup table so that multiple contacts sharing the same address point to the same record. This results in a change to the Contacts table whereby the Address nvarchar(x) column is replaced with a foreign key to the new Addresses table. Subsequently, my GetContactList query much change to implement the join required to flatten the data.
Granted, this same thing can be accomplished using a view (and between you & I, I typically do use views as the basis for any stock queries that require joins, etc. to create a...view... of the underlying data). So, in my case, GetContactList would actually be something like:
SELECT * FROM ContactList ORDER BY Name
And yes, this means that our BO could simply execute the exact same statement and make use of the view bypassing the sproc and that is certainly legitimate. But, as I strive for consistency, and there are times when calling a sproc is required because of additional logic or processing required, I have elected to standardize my BO data access to call sprocs. For dynamic searches, I use an approach similar to what Andy has described with his DataSearcher approach.
(And yes, I am ignoring the fact that such a change to the Contacts table would undoubtedly affect our read/write object for the same data - it's just an example to illustrate my point.)
Anyway, in a discussion that has seemingly revolved around performance and security, I thought it appropriate to point out how sprocs add another layer of abstraction for our apps. Those of us working in team environments with or without DBA's can appreciate the fact that the changes I described above can be implemented without a single change to the application code. For those of us working in one-man-shows, my parting thought is that everything changes and just because you are a one-man show today doesn't mean you'll be one tomorrow.
I was designing an application recently that I anticipated being the sole developer on; but, as the development phase drew nearer, some of the company's off-shore resources became available and I was "directed" to find a way to use them. Having a clearly defined "API" of sorts for the database as provided by the set of sprocs I had designed allowed me to easily turn over the chore of creating the myriad of Reporting Services reports that were required for the app. All I had to do was indicate which sprocs were required for each. As the application took shape and the design invariable changed as I went along, none of the reports that had already been completed had to change because they all relied on the sproc - not the underlying schema or elements. This saved us a tremendous amount of time in a number of areas.
Just my two cents...
Those are good thoughts, and in the "food for thought" context I'll comment. Really, just adding to your comments...
The value or cost of that abstraction must be determined for each environment. For reading, views have the significant difference of being more easily discoverable in ways more commonly understood. You can even link to them directly from Access 2000. That is a good or bad thing depending on your particular point of view and corporate atmosphere.
So, the choice of views vs. sprocs for reading to some degree depends simply on the "philosophical" decision of where you build the bridge between the normalized relational database and the user-friendly access. And in a large development team those "users" could be defined as the business layer developers (users of the DBA's work) who I believe still benefit from more discoverable nature of views.
Keep in mind that your "more discoverable ways", I am assuming, consist of a set of system procedures that may be used to obtain a list of schema elements. This requires knowledge of those procedures which I cannot assume that the developer knows. However, he certainly knows how to expand the nodes in Server Explorer or SQL Server Management Studio at which point expanding the Stored Procedures node becomes just as easy and accessible as the Views node.
IMO, and the point I was vague about, is that sproc allow you to define an API much in the same way we define an API/interface to our BOs for our UI designers (e.g. ContactList.GetContactList()). So, just like we abstract how our BO's are implemented and do their job from the UI designer, the GetContactList sproc provides an API/interface that abstracts how the underlying structure and schema of the database is implemented from our business layer developer. And, yes, it will require the same steps/efforts to convey the nature of the API to the applicable developers just as with our BO's - documentation!!
Copyright (c) Marimer LLC