DO NOT USE PROCS FOR DATA ACCESS?
-
This question needs to be answered honestly, NOT SIMPLY THINKING OF SAVING YOUR JOB AS A DBA. Obviously DBAs have more to do than write procs for data access from an application. Two very pertinent blogs below, for and against this idea.
Rob Howard says: "At just about every talk I give I always try to make several consistent statements. One of which is: ‘Whenever possible use stored procedures to access your data’. "
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx
Frans Bouma says:"Let me start with a blunt statement: stored procedures are bad, they are a bad way to formulate data-access logic. I can't state that enough."
-
The more I think about this the more it boils down to results. What we need to hear are real-life experiences with both approaches, particularly with large numbers of records and large numbers of hits against a fairly normalized database.
We can argue the theoretical results forever, "the proof is in the pudding."
-
Let me answer them in reverse order and please remember this is only my opinion on the subject.
Procedures should not be used to control access to the data. That should be done through GRANT/DENY/REVOKE commands.
Procedures should be used to control security to scripts.
As a DBA/Developer/Programmer, I create procedures for my users to run. I don't intend for the procedures to control their access...I use them so I know that no matter who runs the script (procedure) it will be run the same way. If you only run the procedure, you don't see the underlying script and can not make changes to it that might adversly affect the query.
Real-life example: I had a query that my users ran (before procedure). Some of them were complaining about inaccurate data. I found they were changing the time format. And I got tired of having to explain it over and over. Solution, I turned the script into a procedure that took parameters. The script validated the parameters and I have never heard a complaint of invalid data yet.
-SQLBill
-
Sorry I didn't make myself clear. I'm talking about data access via asp.net pages. The controversy is whether to have the sql generated in the application code ( e.g. C# ) or by making heavy use of stored procedures. SQL 2000 will cache execution plans for "adhoc / dynamic " queries.
I wasn't referring to access in the sense of permissions. Again, the solution to this argument is hearing about real-world systems that use the two different approaches where the database is large and normalized. Otherwise we'll just be venting our opinions.
Randy
-
Then it is "depends". My users have Crystal Reports. When they run a query, CR sends the query to SQL Server, which sends ALL of the data back. CR then groups and organizes the data and does the final manipulation.
However, when the user runs a procedure from CR, SQL Server does all the work.
So, IN MY EXPERIENCE, using SQL Server stored procedures allows the work to be done totally on the SQL Server machine which is usually more powerful than the client's machine.
-SQLBill
-
It's not a simple question. Not simple at all. I'll give you my opinion, speaking both as a DBA and a development manager.
First point is that the job of a DBA doesn't depend on stored procs at all. I believe developers should write the code, let the DBA look it over for obvious or fatal flaws, performance issues, etc. Many developers prefer embedded SQL because they don't want to wait/ask for the DBA to apply a change. Not a great reason, but I can understand the source of the idea. DBA's have a LOT of stuff to do besides write code.
I prefer to control access via stored procs. Yes, I use roles to grant exec permissions on those procs. I almost never grant table (or view) level access to a user or role, and when I do it is almost always a read only role.
I like to put as much business logic in the proc as makes sense to avoid round trips. Some things are best done in client code and so should be done there, but round trips are evil. If I can do a lot of work in one proc call, I do so.
Almost no one codes dynamic sql to protect against sql injection - still, after all the press. Using stored procs and restricting use of exec()/sp_execute_sql limits the exposure.
If I were designing an app to work on multiple db platforms I might find procs brittle, but on a single platform they are empowering. They give my apps an additional layer where I can shim in as needed without a recompile.
I've been using the stored proc method of data access for years and find it to be easy enough to do and reliable. I'll grant that it takes a little more work up front, but not enough for me to worry about.
Data access is the most important thing most of our apps do. Whether you use stored procs or use dynamic sql, getting your DBA involved is the best you can do to guarantee a solid/fast app.
I don't have a "winning argument" that will convince you one way or the other. Take all the opinions in with an open mind, then try a project using one method, then try using the other method on the next one.
-
I agree with lots of what Andy wrote, but I'll chime in as well.
SQLInjection will become one of our biggest worries going forward. With so many bugs and patches coming out, the base SQL Server product, as well as DB2 and Oracle, are pretty secure. At least secure beyond the skills of most people. However SQL Injection attacks require a great deal less skill. Know the format of a batch and you can get in.
Now sprocs don't necessarily avoid this, but they do help.
As far as an ASP.NET app, consider this. If you find an issue with a query, or want to change some schema and restructure queries somewhere, what's the effort required? If you've reused code and I don't know who doesn't, you may end up changing the same code over and over in multiple ASP.NET (opr VB.net, c#, whatver) pages. Stored procedures exist for the sames reason you have methods or functions and don't repeat the same code over and over. It makes sense to resuse and minimize maintenance.
There is more effort, but it's more of a "making a habit" effort than code effort. Learning to code a stored procedure as well as your page is no different than building a VB.Net code behind in addition to your ASP.NET page. You are still separating things out and putting htem where they below. I'd argue that an OOP .NET app should be calling methods, not building SQL logic. Use the stored proc as you would any other method. Move the stuff to the database.
In SQL 2000 and below, there are quite a few things that make more sense to do on the client. String maipulation, etc. are not well developed in T-SQL. In SQL 2005, some of that isn't true, but the db is still a shared resource, so don't overload it unnecessarily. For things that make sense, more them to the client.
Another idea to think about is performance. Not the cached plans since it is true that many plans can be cached. Lots goes into this formula, but let's assume all your pages connect from the same server and use the same login (not necessarily connection). Reusing plans should help, but what about performance tuning? Remember the argument about making changes? Let the DBA make the changes to improve the app without bothering the developers.
I'll also put on my gross generalization hat. Most developers have little effing clue about how to write anything but basic SQL. Not a big deal, it's not their bag, but recongnize this. It's getting slightly better as more and more db apps are written, but still it's bad. You might be good and perhaps your cube neighbor, but what if he quits and you get a new developer. He might suck. Having the DBA at least approve and implement stored procedures goes a long way towards doing the right thing up front.
Even if you run multiplatform, don't write generic SQL. I've worked for two ERP vendors and some smaller multi-platform software companies and they all implement the write once suck everywhere model. Do yourself a favor, employ an Oracle DBA, a SQL DBA, a DB2 DBA and get things ported properly. These days it's not a big effort and your customers will all love you for it.
Dynamic SQL is quicker to develop with, but not much. Altering a stored procedure is trivial and with Windows you can run two editing apps at once. There really isn't a good argument for not doing it this way. If you can't get the security right, fix that problem. If developers don't know how to call a sproc, fix that problem. Give me a decent argument against stored procedures.
-
I have to say that after reading some of the comments posted at those weblogs I feel dummer for having read them
Those who argue against stored procedures seem to have one thing in common. They hold the view that a database is nothing more than a place to persist data. In fact one idiot there stated in effect that stored procedures are only useful in a properly designed database and that since there never has been and never will be a properly designed database they are a waste of time. WRONG! While the current SQL products, and the SQL Standard itself, are pretty severely flawed implementations of the relational model, I can state categorically that it is not only possible, but critical to have a properly designed database if you want good data. The limitations of today's SQL based products do mean that it is more difficult to do this than it should be, but it is nevertheless possible. Shortcommings aside, these products are vastly superior to the network and hierarchical DBMS's of years ago, and light-years ahead of application managed file-based data storage systems.
The bad news it that the major vendors, rather than more fully implementing the relational model are actually regressing to the older and failed hierarchical model by jumping on the XML bandwagon and encouraging others to do so. It is sometimes pretty depressing to see how few people working with databases actually understand the fundamentals of data management. Those arguing that all business rules ought to be in application code are essentially arguing for a return to file-based data management whether they realize it or not.
Lastly, just because Microsoft and other vendors have made improvements to how their products optimize ad hoc SQL doesn't mean that there still aren't advantages to using stored procedures most of which have been discussed ad nauseum on this and other forums.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply