July 30, 2008 at 12:21 pm
Working with developers of all skill levels, in most places I have worked, I have found it to be safest to use stored procedures. That way at least when someone says there is a database performance problem, I can have a decent shot at tracking it down and fixing the problem.
With stored procs I can easily find all stored procs that reference a certain table and/or column, without having to search trough (hopefully current) source control systems.
With stored procs I can "easily" figure out where I might have bad code when upgrading from version to version of SQL.
I didn't decide on being a stored procedure (almost always) DBA from reading a best practices white paper on the internet. I decided on it from what worked and what didn't work the last 13+ years. After being put in charge of converting a system from 6.5 to 7.0 that had dynamic SQL in the code all over the place (about 50K lines of SQL code, in C++....) it just entrenched my position that (for DBAs) SQL code in the application is a bad think.
July 30, 2008 at 12:29 pm
MentalWhiteNoise's post best represents my thoughts.
Here's my experience: For 10 years now, I've been using a mix. I use stored procedures where it makes the most sense and direct access/SQL in my apps where it makes the most sense. Obviously over that time, my systems have changed/needed maintenance. And not once has there been a maintenance problem. Maintenance all works out very easy, initial development is quick, and performance of the applications is just what we need. When I decide whether or not a proc is in order, I consider these three needs (maintenance, development time, and performance) and work toward an optimal balance.
But using a mix is what makes sense in my environment. Your environment may require a different approach.
What I appreciate about the editorial is that it helps to give us permission to do what makes sense instead of worrying about the dogma of "always use procs for everything" that I have read and heard many times over the years.
July 30, 2008 at 1:02 pm
JJ B (7/30/2008)
MentalWhiteNoise's post best represents my thoughts.Here's my experience: For 10 years now, I've been using a mix. I use stored procedures where it makes the most sense and direct access/SQL in my apps where it makes the most sense. Obviously over that time, my systems have changed/needed maintenance. And not once has there been a maintenance problem. Maintenance all works out very easy, initial development is quick, and performance of the applications is just what we need. When I decide whether or not a proc is in order, I consider these three needs (maintenance, development time, and performance) and work toward an optimal balance.
Quick development, good performance, no maintenance problems? You're hired!!!
July 30, 2008 at 1:10 pm
Can we all agree that mindless ANYTHING is silly? Fun maybe, but still silly 😉
__________________________________________________
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 1:23 pm
I also end up having a mix. WRT ORM, I find that the issue is not doing it, it is that most of the tools generate lousy SQL and that because the SQL is seldom even SEEN, that testing isn't done properly if at all. THe developers hardly see the database or the SQL, only the objects they create without any respect to the database as it is. SQL and objects have never worked all that well together, but having the tools hide it even more has led to really poor design WRT the database server and even poorer SQL. When I join a firm and hear ORM, I KNOW that one of my first tasks is tuning. With over 90% of the performance problems I see coming from bad SQL, and most of the rest started by poor database design to start with, it is not surprising that (in general) performance of databases under ORM ain't the greatest.
July 30, 2008 at 2:04 pm
You're hired!!!
hee, hee. Alas, I like my current job.
July 30, 2008 at 5:54 pm
Having read through the comments, it can be all summed up in the usual two words that apply when someone asks if something can/is best done in a particular way: it depends.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
July 30, 2008 at 5:56 pm
I like the opinion but disagree with large chunks of it.
Not all developers are created equal. What may work well in one shop will fail in another because of the skill and knowledge of the developer. This applies to both .NET and SQL Developers. I've seen both types turn out some really clever code -- and I've seen the inverse that caused locking an blocking and can brought a 4-way dual core machine to its knees.
One topic missing from the article is Consistency. By using procedures as "methods" on the database you are able to achieve a level of consistency no matter what task is being performed.
I know that having business logic in the database (stored procedures) is supposed to be a big no-no because it doesn't scale. My counter argument is that the best place to perform data intensive operations is in the database. Also, there are hints about a Microsoft grid database.
Another issue that's difficult to handle is refactoring the database. Yeah, DataDude helps in this area but so does scripting out the db code to individual files and then including them as "related documents". Then I can interrogate my source code for sp calls and figure out what apps are impacted.
Development is development. If the DB needs to change then the app probably does as well. The same applies to the App. Most of the time some new data requirement is being requested and the app needs to drive that information into the DB.
The name of the game is cooperation. If a developer want's to use LINQ or a DAL and store the strings there then I as a SQL Developer will try to discover all the hooks but have no real way of discovering those dependencies unless they're in the code.
--Paul
--Paul Hunter
July 30, 2008 at 6:12 pm
Mark Harr (7/30/2008)
Colin Heming's example of encryption is a good example of the fallacy of using only stored procedures. What good is it to encrypt data when the only access to the data is sprocs that decrypt the data for you? Encrypting data HAS to include encrypting it from endpoint to endpoint to be effective at all. It must be encrypted once the data is entered, and not decrypted until it is needed to be used. And that is virtually always at the application level. If you decrypt in the stored procedure, then pass unencrypted data "over the wire" (though the connection", that is not securing your data.
Yes, data encryption needs to be end to end to be effective.
The point of SQL server level encryption is to secure data from physical theft of servers, hard drives or backup tapes. In that regard, stored procedures are an acceptable place to store this logic. The point here is that the encryption key itself is managed by the database, without the need for the application to know anything about using it, or indeed knowing the key at all. Separation of duties in this case leads to tighter security.
If data were sensitive enough to require encryption, of course you'd pass it over the wire through an SSH tunnel, VPN or other form of encrypted link. Using stprocs and over-the-wire security are not mutually exclusive.
July 30, 2008 at 6:20 pm
Scott White (7/30/2008)
If you have around 50 tables in a system and you have just 4 sprocs for each (create, update, delete & select) you would have at minimum 200 sprocs. If you have to change a primary key from INT to BIGINT after you make the corresponding changes in your tables you have to make changes in each sproc which may number in the dozens. With ORM you would simply change this in the mapper. To me at least this makes a very good case for ORM, rather than putting faith in each developer to write consistent, quality SQL code you put the onus on the years of refactoring and evolution of an ORM framework.
I'm curious Scott, why would you only have to change the database without changing the application? What magic were you able to fit an Int64 into what's defined as an Int32 in the rest of the application?
Also the number of times and places you will need to go from 2.1 billion records (int) to a big int is precious few. I can't wait until we have over 2 million customer with each having over 1,000 orders. Can anybody say lookout Bill Gates. Yes there is a need for the BigInt but...
IMHO: this is a red-herring argument.
--Paul Hunter
July 30, 2008 at 6:41 pm
Heh... someone asked me why I've tried to specialize in behind-the-scenes-batch-programing in SQL Server... I believe I'll refer them to this thread. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2008 at 6:50 pm
Jeff Moden
Heh... someone asked me why I've tried to specialize in behind-the-scenes-batch-programing in SQL Server... I believe I'll refer them to this thread.
LOL: Way to go Jeff... The monk hides in his monastery while us "luddites" try to explain the religion.
--Paul Hunter
July 30, 2008 at 7:11 pm
The issues finally seem to be coming out in the wash here.
The main reason people are giving for sproc wrapping is that of providing a wrapper for the database.
I'd have to agree with the 'it all depends' crew. For really small, simple databases a wrapper is probably overkill. For medium complexity databases, it's probably a really good thing and the best way to go. But for large systems, do you really want to make sprocs the bottom layer of the DAL ? How big are those sprocs going to get ? 1000 lines ? 5000 lines ? How much business logic is going to get sucked in there ? How scalable is it going to be ? How portable is it going to be ?
Check out subsonic: http://subsonicproject.com/
Its DAL autogeneration. Wraps sprocs, views, tables. To change DBMS, just change the config file (MSSQL, Oracle, MySQL, others soon).
All the embedded SQL is completely autogenerated in the DAL, using good techniques. To do the usual database complex aggregation and set logic, do that in a view (read) or sproc (write) and glue it all together with application code using the DAL wrappers.
Scaleability, portability.
The autogenerated DAL is purely the database/SQL wrapper (there should be NO embedded SQL outside of it !). Any further DAL aggregation or business logic can be written on top of that, and is preserved when the database schema or DBMS changes and the bottom layer DAL is regenerated (heard of .NET partial classes folks ? Check them out, they make this elegant ...).
I've got a foot firmly in both the .NET developer and the MSSQL DBA camps, and this is the best solution I've found yet. There are still a few rough edges, but things are getting a lot better.
July 30, 2008 at 8:05 pm
Jack Corbett (7/30/2008)
I'll admit I'm in the "USE stored procedures camp" and one of the main reasons is this quote from your editorial which you use as a reason to NOT use them (bolding done my me):The granular security argument is that you don't give users access into the table, you only give them access to execute a stored procedure. My response to this is so what, the user's still have access to insert. Really what is the risk (odds) of a user finding out the database security credentials, understanding the database, and issuing a proper sql statement.
I know most users can't build a proper SQL statement which is why I do it for them in stored procedures and don't allow them direct table access.
Then the argument someone put forth that most large CRM packages don't use stored procedures is a reason why they have performance issues. Sure you get portability, but you lose performance because of some ugly SQL created.
One of the fundamentals of security is - you don't worry about the 99.99% of the users that wouldn't know what to do with a SQL connection string if you gave it to them, but rather the .01% that does, and is interested. If you can discourage just a few of those, then you've made a huge difference.
Sorry -but having worked in healthcare, government and/or insurance, relying on user apathy is just not an acceptable form of security. I'm going to have to disagree with you there.
Remember - it just takes one musical genius to breach you in the right way, and the next thing you know - your company is being talked about on the dailyWTF site, and your name is being changed to protect the incompetent. Don't play chicken with security.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 30, 2008 at 8:18 pm
Grant Fritchey (7/30/2008)
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.
Grant - I have to disagree with you there. The use of the mindless when designing systems have been one of the reasons I've had jobs continuously for as long as i've been working. It's not silly at all - it's great for business...:)
And yes - that applies to the mindless use of _________________ in system design (see - I'll let you fill in the blank - put anything there you wish.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 76 through 90 (of 160 total)
You must be logged in to reply to this topic. Login to reply