July 30, 2008 at 8:50 am
It seems that everyone is taking extreme positions on this topic.
There is no real difference (performance, security, or maintenance) between using SQL in your DAL and using sprocs via a DAL. (You are using a DAL, right?) Problems arise when you have SQL scattered throughout the application.
The key is that you should keep your data access code in one place. It doesn't really matter where that place is. However, if you go with sprocs then you can enforce the "NO SQL outside of the DAL" rule at the server.
--
JimFive
July 30, 2008 at 8:59 am
With respect to security....
SQL server 2005 supports context permission (I believe that I learned that from SQL ServerCentral). Using SP's and context you can use a read only ID that execute an SP via the application only.
July 30, 2008 at 9:04 am
One more problem is network traffic. For SP, client just needs to pass the SP name and the parameter values; For DAL, the whole query TSQL command blocks, which are much longer than the SP name in most cases, need to pass to the DB. For a busy system with thousands of users, the performance or the throughput will be affected.
Though execution plan is cached for DAL, SQL Server needs to compare the whole query command to find a matching plan and usually takes longer time than SP.
July 30, 2008 at 9:08 am
Jack Corbett (7/30/2008)
To broadly generalize and call someone a luddite (one who is opposed to especially technological change - Mirriam-Webster Dictionary) because they take a stand on something or have not learned something is a a bit harsh. The caching of ad-hoc SQL statements was not done in earlier versions of SQL Server so while there may be ignorant DBA's out there I wouldn't call them luddites.
It may be harsh, and probably not productive to the discussion, but I do think the term applies. Precompiling and caching for dynamic queries has been the standard in SQL Server since version 7 (released in 1999).
Mark
July 30, 2008 at 9:19 am
peterhe (7/30/2008)
One more problem is network traffic. For SP, client just needs to pass the SP name and the parameter values; For DAL, the whole query TSQL command blocks, which are much longer than the SP name in most cases, need to pass to the DB. For a busy system with thousands of users, the performance or the throughput will be affected.Though execution plan is cached for DAL, SQL Server needs to compare the whole query command to find a matching plan and usually takes longer time than SP.
This is a valid critique, ORM solutions or any dynamic SQL solution will be a little more chatty on the network. I would argue with gigabit networks and modern switches this is minuscule and given that the payload of the resultset is where the network can be saturated.
Regarding security I would propose to firewall the database so that your database only allows database connections from your application server and your reporting server. This way even if your application's database credentials are compromised they cannot reach the database.
July 30, 2008 at 9:41 am
Simple, single-table CRUD done by Linq or nHibernate or whatever, is fine by me. I've seen the SQL code generated by these kinds of tools for anything more complex than a single-row, single-table action, and until that quality comes up to the point where I wouldn't fire any DBA who wrote it, I recommend against it.
I've seen threads on this site where the question was, "how can I make this run faster, without changing the code, because the code was auto-created by X application". The code supplied, in one case at least, crashed after running for 8 hours. 8 hours! It took 18 input parameters and joined three or four tables, for a select, and had so many functions built into the Where and Join clauses that it was impossible for it to use any indexes efficiently, plus it ended up using inefficient joins methods because of the code. There was no way to fix it, in that particular case, without changing the code.
Dozens of comparable examples later, I shudder every time I see "this code was written by X application".
So, that brings up the idea of using ORM for single-row, single-table CRUD, and procs for everything else. Might be workable, but makes debugging that much harder, reduces scalability, and means you have to look in two different places for your SQL. That might be a viable idea. Pros and cons would have to be weighed on a database-by-database or shop-by-shop basis. Personally, I think it would be a viable solution in the place I work.
That idea would speed up the devs here, on many projects. That's a worthwhile trade-off: Definite, large current gain vs possible, minor future cost. (I say minor because moving any particular code from direct to proc isn't that big a deal.)
Aside: I have to laugh that at least half the posts in this thread are lamenting how all the other posts are so polarized. I've seen two really polarized posts so far, including the one by the guy who misused the term "luddite". Even that wasn't so much polarized as it was simply rude.
Aside: SQL can't possibly be a religion. We don't have a book with enough "begats" in it! 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 30, 2008 at 9:43 am
scott.w.white (7/30/2008)
This is a valid critique, ORM solutions or any dynamic SQL solution will be a little more chatty on the network. I would argue with gigabit networks and modern switches this is minuscule and given that the payload of the resultset is where the network can be saturated.
Regarding security I would propose to firewall the database so that your database only allows database connections from your application server and your reporting server. This way even if your application's database credentials are compromised they cannot reach the database.
Not everyone has the luxury of gigabit connections. Most of my users are connected via 2mb or smaller MPLS pipes from various locations around the world. Some locations are connected with 512k satellite routes and 600ms latency. We do a lot of our work over terminal server, but anything that reduces network traffic is considered a good thing in our environment.
July 30, 2008 at 9:48 am
On the security issues and I am certain that someone has already addressed this but i thought I would 'inject' this.
"You can use sprocs and still have SQL Injection vulnerability if you are doing something like this:"
So do not use the execute in the sproc and you are much safer.
One the processing or efficiency side of the equation, i would be surprised if the MS person you spoke with was not rebuked strongly by his MS peers. Using Sprocs with CLR or .Net code on the server side and defining functions that are clearly reusable you are able to gain in performance as well as stretch the reuse of code into new areas on the server side.
Just some random thoughts on this.
Miles...
Not all gray hairs are Dinosaurs!
July 30, 2008 at 9:51 am
Very interesting post! I think one of the largest proponents for why to use stored procedures is the benefit of recognizing what applications perform CRUD operations on each table. In the shops I have been in, the DBAs need to have the best understanding of where all the data is. However, if the code is all in select statements locked away in an application, then if there is ever a maintenance request, there is a huge scramble for all of the developers to search ALL of their code. For smaller shops, that's not a big deal, but when you have 60 custom apps... - the task is a bit daunting! My argument is that if everyone is using SPs, then when a change request to modify the length of a column comes in, I can quickly search through syscomments to find all of the SPs affected. If you have a bunch of individual selects, each developer has to dig hard to hope to find everything affected.
Another idea to consider is performance tuning. If there is no insight into affixing a "label" onto code sets that are running slow, the challenge for the DBA will not only be to discover what query is causing performance problems, but now figure out which application, and then work with the developer to even discover where in the code that query is created! Again - to me, that's a painful process.
I'm curious to see what other folks have to say on this topic.
Todd
July 30, 2008 at 10:33 am
This has been a fun read 😀 Here's my two cent's worth.
SQL isn't a religion, but it is a speciality. DB access is not inherently procedural nor object oriented. The most efficient queries are sometimes counter-intuitive to many people. I work with a lot of very bright application developers. Yet, some of them have come to be known as "SQL Slayers" because of incidents where they generated queries that made the server groan until their transactions timed out. I have been able to make improvements in the efficiency of their queries, not because of any differences in our IQs or progressive v. old school orientation, but because they don't spend day in day out working through and understanding the nuances of execution plans. When I see references about "luddite DBAs" not knowing that compilation takes place at first execution, it strikes me as a gross overgeneralization, and a false one. Maybe a particular DBA didn't know that, but that's an individual problem. There are many, many application developers who are ignorant of that fact. It isn't a reflection on their intelligence, it's just that their focus isn't on how the DB engine operates, they've never had occasion to read about it.
I am in the stored procedure camp for all of the reasons specified for security, performance, auditability, and ease of problem determination, not because someone chants "best practice". I've heard nothing to change my mind, the pros still vastly outweigh the cons. While I understand and pretty much agree that it seems silly to have a proc that executes a single insert, update, or delete statement, consider this:
If nothing else, such stored procs can be used to prevent direct access to the underlying tables. This is a good thing. Just for a moment, let's assume that you have one line procedures. Suddenly, you decide that one of your tables should NEVER have rows physically deleted, but simply flagged as inactive. Or perhaps a physical deletion should only be allowed under certain circumstances. Or perhaps deletions should always be archived first. If all "deletions" must be done through a stored proc, then you can implement these policies without having to educate a group of UI developers and trust them all to follow the standard.
Disclaimers: Yes, I know code reviews should catch these kinds of things, but use of the stored proc makes it one less thing to worry about. Yes, I know you could create objects to drive these rules and insulate developers from directly accessing the db. Yes I know that some application developers are more knowledgeable about SQL than some DBAs. Yes, I know that foreign keys, constraints, and triggers are other tools available for enforcing rules and maintaining the integrity of the db. Sorry for all these disclaimers, but I've learned in these forums that using something for an example is dangerous 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 30, 2008 at 10:39 am
Grant Fritchey (7/30/2008)
Not taking advantage of, for example, a Common Table Expression because it's not supported in DB2 (is it?) seems stupid when you consider that the piece of code might have to live 10 years. Why would I put it into the lowest common denominator at the sacrifice of speed, flexiblity, maintainability and scalability?
Especially when you can put sprocs in Oracle too... You can customize your Sprocs by environment so that each returns the same results, but takes advantages of what is available.
July 30, 2008 at 11:14 am
SQL Server is a robust RDBMS. There are SEVERAL programming components that can be arbitrarily placed in either the stored procedure or the compiled code. The question is not should the "Always" stored procedure rule be used, so much as should the "Always" stored procedure rule be used IN A SPECIFIC ENVIRONMENT.
Diagramming out an application, you have many layers. There needs to be a line between an RDBMS and an application, but where do you put it? IMO, that depends specifically on your development environment, your application goals, and your developer skill sets. If you are trying to design a robust application that is portable to any database system, obviously the more you rely on sprocs and database specific functionality the more coding you have to do to allow compatiblilty with different database systems. If you are an MS shop designing an internal app you would most likely want to draw the line where the best performance can be gained, and leverage the technologies already in place. Similarly, if you have several code-monkeys with only one sql-code monkey, you may want to push more responsibility onto the non-sql server side.
Just my 2 cents.
July 30, 2008 at 11:22 am
Scott,
Thanks for the editorial and it's provoked an interesting debate.
I wasn't sure what people would say, and I don't completely disagree with Scott's arguments, but I don't think they're great. To some extent it appears to me that Scott's point of view is somewhat limited in terms of the applications using SQL Server and the issues involved.
Personally I'm a sproc only person, but I've never worked in a company where we could enforce that beyond one or two applications. When we did, it worked very well and a few hours working with developers to explain how things work and everything was smooth.
I'll add a couple arguments for stored procedures from my experience:
- Version control is much easier and it's easy to see where the code lies. I know a good DAL can do this, but I've never seen anyone contain all the SQL code to a DAL without it growing large. Often parts of queries filter into the application.
- Simple CRUD - I see no reason not to use sprocs. It enforced good habits for more complicated procedures. You get used to calling a sproc, using parameters, etc. You HAVE to pass parameters as parameters, not as part of a string and you can auto-generate these. Build a table, auto gen 4 sprocs. Does it help? Sure, I can make schema changes, implement defaults, any number of abstractions without changing the application. If you don't auto-generate these, you'd not developing very smartly.
- Security, it's more control over security by limiting surface area. It doesn't prevent Access/Excel/etc. from being used, but it does prevent them from doing things outside of our business rules. Only way to ensure this is triggers, which can be problematic.
- Mark Harr had an interesting point in encryption, and he's correct for end-to-end security. However I think the vast majority of breaches are media related (physical drives or backups). Encryption helps here, and so the sprocs can encapsulate this.
- Application changes - If you're in a web/tiered environment, it's a wash, but I still don't see good arguments NOT to use them. If you're in a thick client spot, I can't see any reason you wouldn't use them.
- Reuse - It's valid and it works, however if you're not using it here, do you allow each developer to add their own methods because they don't understand yours? It's not a good argument. You have to share information and document to have a well run development shop, which means that stored procedures get reused.
- Performance - This is often a wash. However you can recompile a stored procedure, mark it for recompile, and better control the need to re-use or not re-use plans. It can be done with ad-hoc SQL, especially in 2005/2008, but it's more cumbersome and requires more skill.
- Network traffic - depends on the client situation. If we're centralized, with few clients or web based, probably doesn't have any measureable impact. That's not always true, especially for some apps that generate those 1000 line queries. However if you have lots of thick(er) clients, even Ajax style clients, it can make a difference. Not so much to the server, but to the clients. I was always taught ligher weight was better, so why not take advantage of sprocs and keep things light weight.
The bottom line is that I don't see learning to write (and implement) sprocs as being any harder than adding in new methods to an app. If that's the case, why not use them? Surely you can write tools to auto-gen calls to sprocs just like I can auto-gen trivial sprocs? All the meta data is there, and the overhead should be low.
If you make a slight effort, I think you can implement sprocs with very low overhead in any shop.
July 30, 2008 at 11:23 am
Scott, you took the words right out of my mouth. I have always disagreed with the mindless use of sprocs in the design of systems, and I am glad to see there are others who agree with me.
July 30, 2008 at 12:00 pm
gcopeland (7/30/2008)
Scott, you took the words right out of my mouth. I have always disagreed with the mindless use of sprocs in the design of systems, and I am glad to see there are others who agree with me.
Hey! I totally agree. The mindless use of in the design of systems is pretty silly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 61 through 75 (of 160 total)
You must be logged in to reply to this topic. Login to reply