July 30, 2008 at 7:03 am
Ian Brown (7/30/2008)
Oh, and I included a few more indexes.
Much more likely that this was the cause of the improvement.
Still there is a very good reason to not do updates and deletes directly to tables but do them through procs. THE DREADED MISSING WHERE CLAUSE.
ATBCharles Kincaid
July 30, 2008 at 7:08 am
Giving direct access to users is a bad thing. Granted a savy user may be able to figure out a sp. However, even casual users have access to tools such as MS Access, which makes it way too easy to link to a table and make direct changes to the data in it if they have the rights.
I suppose you could use application id's and force access through apps or middle layers. Now you will have to deal with auditing issues when you need to determine who has access to what. These id's have access to these tables, these id's are used in these apps, these apps are accessed by these people, these id's have non-expiring passwords that are hopefully encrypted, etc., etc.
Sp's keep it simple. They are easy to write, easy to troubleshoot, easy to audit, easy to tune, perform well, can sometimes be reused and minimize network traffic.
While sp's are generally a good appreach, there are allways multiple ways to get the job done. You need to pick the best approach depending on the needs.
July 30, 2008 at 7:10 am
Eliminating stored procedures to do simple CRUD, ok. Eliminating stored procedures for *everything*? Disaster.
The single most important aspect of stored procedures is (when called from triggers or constraints) they are *unavoidable*. This one aspect makes them irreplaceable. In other words: business rules. 🙂
From my point of view a stored procedure is an adjunct to a trigger--a place to put extra code so it can be used by multiple triggers. Sure SPs are used for all sorts of other things. But let apps worry about doing their thing, let the database worry about integrity--and business rules are part of integrity.
Besides, if you use SP's for CRUD, aren't you in effect creating a view? At least conceptually, if not physically? So why not use a view instead?
July 30, 2008 at 7:10 am
In OO world, encapsulation is a fundamental rule. Developers need to create private members and wrap them by SET / GET as properties. They donot expose the members, insdead, they expose methods and properties.
In DB world, the same people forget encapsulation, forget module boundary. They want all the doors open to them. They want some dirty and quick way to access data. What a shame.
July 30, 2008 at 7:20 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 7:23 am
Oh yeah, it really is a good editorial because it has sparked debate. Just because I don't agree with the opinion in the editorial doesn't mean I didn't enjoy it. Anything that gets 4 pages of responses by 9:30 Eastern Daily Savings Time is a good one IMO, as long as the debate doesn't descend into a flame war.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 7:27 am
In OO world, encapsulation is a fundamental rule
The only fundamental rule is to write robust, performant, maintainable systems. The OO paradigm is one means to this end - it is not an end in itself !
July 30, 2008 at 7:30 am
I would tend to disagree with this editorial in almost every way. The only real argument I saw as viable was the point about creating 4 sprocs per table. The problem here isn't SQL but the current set of technology tools. Imagine if the UI devs said they weren't going to use the designers because they didn't support some commonly used feature. We should be demanding that SQL add tools to auto-generate such boiler plate code.
I see a problem with not using sprocs for even the simplest updates: changes. Changes are inevitable. I'm completely against having hard-code SQL code inside compiled code because it makes maintenance a nightmare. If the DB schema changes then we would have to find all the references in code, make the changes and then recompile and deploy. That's a nightmare. By using sprocs we can force references into a a single codepath. Modifying the codepath allows us to make changes over time to the schema without impacting the clients (hopefully).
One could argue that triggers can also give us this ability but a trigger is nothing more than a sproc that is automatically called at certain times. We really haven't "eliminated sprocs" if we are relying on triggers to handle schema changes.
Just my opinion but I'll stick wih sprocs until someone can provide better arguments against their use.
July 30, 2008 at 7:32 am
Charles Kincaid (7/30/2008)
Ian Brown (7/30/2008)
Oh, and I included a few more indexes.
Much more likely that this was the cause of the improvement.
Nah...indexes took it down to 8seconds, the re-org then took it down to 60ms. Execution plan became more complex - but crucially all the indexes were used as before the db engine couldn't work out which mix of indexes to use because of all the 'or' statements in the where clause. Now, it treats them as separate selects, each selecting based on a single field....on which there is an index.
Query optimization - a black art but FUN:)
July 30, 2008 at 7:34 am
Stored procedures give the DBA control over database access. Embedded Sql does not. They do aid re-use, because if they are written in conjunction with a well architected middle object tier you can re-use basic corporate reference data all over your business in many applications. They also make debugging easier, I would flatly refuse to work anywhere where I had to sift through a bunch of .net code to find a poorly performing embedded sql statement rather than just running profiler and getting the Sproc name. To me that's one step up from using MS Access as a front end to sql. (That's another environment where I would turn the job offer down!) I can't belive this article has been posted on a Sql server site. Is it a wind up? I hope so.
July 30, 2008 at 7:35 am
If you have ever had to migrate between SQL and Oracle (or vice versa) you will appreciate the use of an abstraction layer like sprocs. For example, just look at an insert on a table with a database generated unique identifier. SQL handles this with the IDENTITY property and one of the @@IDENTITY functions, Oracle handles this with Sequence objects. By encapsulating this functionality within the sproc the migration becomes much simpler.
July 30, 2008 at 7:45 am
Michael Taylor (7/30/2008)
I would tend to disagree with this editorial in almost every way. The only real argument I saw as viable was the point about creating 4 sprocs per table. The problem here isn't SQL but the current set of technology tools. Imagine if the UI devs said they weren't going to use the designers because they didn't support some commonly used feature. We should be demanding that SQL add tools to auto-generate such boiler plate code.
This is not hard to write yourself even using T-SQL. I have not done it for generic use in T-SQL, but have done it in VB.NET. But in reality MS should provide this in SSMS. It would be a simple to change to the Script Table As to add Insert SP, Delete SP, Update SP.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 7:47 am
HOORAH! Thanks for supporting common sense development, and not mind-numbing, one-solution-fits-all rules ("all sprocs, all the time"). It is always amazing to meet up with a "wizard" (aka DBA) at a client site that has this archaic attitude. No matter what common sense discussion I try to have to enlighten the dba luddite, he always falls on the excuse "it's best practices". Maybe this will help enlighten a few more luddites.
Regarding performance: I just don't see why or how these luddite DBA do not see that "dynamic SQL" and "stored procedures" are compiled exactly the same. ALL queries are precompiled - period. Then read the quote from BOL in the article: "When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles ...". The luddite DBA believes that the compile step occurs during the Create Proc execution. NO. Stored procedures are compiled when they are first executed. The compiled plan is then saved. For dynamic queries, it is exactly the same. They are compiled when first executed, then when the query is resubmitted (which it will be if the query is from an application), the saved plan is used. There is no difference.
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.
Mark
July 30, 2008 at 7:47 am
Jack Corbett (7/30/2008)
Michael Taylor (7/30/2008)
I would tend to disagree with this editorial in almost every way. The only real argument I saw as viable was the point about creating 4 sprocs per table. The problem here isn't SQL but the current set of technology tools. Imagine if the UI devs said they weren't going to use the designers because they didn't support some commonly used feature. We should be demanding that SQL add tools to auto-generate such boiler plate code.This is not hard to write yourself even using T-SQL. I have not done it for generic use in T-SQL, but have done it in VB.NET. But in reality MS should provide this in SSMS. It would be a simple to change to the Script Table As to add Insert SP, Delete SP, Update SP.
See first page of thread guys.....:D
Second Page I mean 😉
Hiding under a desk from SSIS Implemenation Work :crazy:
July 30, 2008 at 7:47 am
The editorial has some good points and some bad - mostly I see it as a referendum on the heavy handed tactics by DB groups to force "all database access through procs". This has served well in the past, it does force developers into not being able to leverage some of the better technologies of today efficiently. However, complex queries being designed by an ORM will kill a system - but that same query in a proc will get you a sub second return, as a result of good execution plan and some skilled tuning.
I am mildly curious how ORMs will insure that systems are accessing data in the correct order, taking appropriate locks and not accessing huge datasets by columns that will force table scans. In a system where complex updates are required - how does the ORM insure its not doing something like this:
SELECT CUSTOMER_DETAIL
JOIN CUSTOMER_MASTER ...
and then another ORM sql call
is doing
Update Customer_Master
Update Customer_Detail
I see a deadlock coming - something that can be managed via procs.
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
Viewing 15 posts - 31 through 45 (of 160 total)
You must be logged in to reply to this topic. Login to reply