|
ScottM1 wrote: An adhoc query will only use the same execution plan if the query is exactly the same including all parameters and whitespace characters.
I can't speak for other DBMSs, but SQL Server is cleverer than that, and used auto-parameterization to allow different queries to reuse query plans. See http://www.benjaminnevarez.com/2010/06/auto-parameterization-in-sql-server/[^] for an example.
|
|
|
|
|
OK, I didn't know that it did that.
It will still only do it in a select few instances though, and looking at this[^] it will probably nearly never do it.
How often do you write a query that has no function calls, no GROUP BY statements, no sub-queries and also no joins?
|
|
|
|
|
ScottM1 wrote: How often do you write a query that has no function calls, no GROUP BY statements, no sub-queries and also no joins?
Apart from the joins bit, quite often. Single table queries only is a bit of a pain, though.
Interestingly, reading this[^], it looks like forced parameterisation removes the "single table" restriction.
|
|
|
|
|
That is interesting, the only downside appears to be that errors may be reported incorrectly.
I still think that if you want to query using parameters you should do it yourself, having the DBMS automatically changing your queries could end up in all sorts of funnys.
|
|
|
|
|
I go with Parameters all the way, even if I'm generating the SQL in Code.
The only downside of Parameters is that it's hard to grab the exact SQL (including values) that is being sent to the DB.
As for escape characters. On occasions when I have gone that route My escaping was always done inside a single function. There's no way I'd go around escaping SQL strings willy nilly.
In fact it goes further than that. When I'm building SQL in my App all contact with the Database is filtered through one specific DB class which wrapps ADO or whatever.
So the argument that you might "forget" to escape a particular query doesn't really stand up if you are doing this stuff correctly.
-Rd
Hit any user to continue.
|
|
|
|
|
What kind of query do you have in mind here?
Wout
|
|
|
|
|
|
Sql debug is always a problem no matter you use stored proc or straight sql statement in code. I see several advantages of using stored proc. It has faster execution. It is safe (against sql injection). Data/business logic can be modified in a stored proc alone without re-compliling the main application as long as the returned data columns are the same.
TOMZ_KV
|
|
|
|
|
Tomz_KV wrote: It has faster execution.
Not so much. Certainly not as much of a performance improvement as some would have you believe. There are situations where perhaps doing work on the DB server saves trips over the wire. In those cases yes. But for side execution of the same query, I wouldn't let performance influence me.
Tomz_KV wrote: It is safe (against sql injection).
The old SQL Injection argument is an iteresting one.
As I said above I believe at least 80% (probably more) of apps can get by quite nicely without the benefits that Stored Procs etc bring.
If you are building an app where SQL injection might be an issue you are absolutely in the 20% (or less) category.
My point on this thread is about what should be the Default Model.
I absolutely have no problem with people who need the features of a DBMS and know how to use them doing so.
Tomz_KV wrote: Data/business logic can be modified in a stored proc alone without re-compliling the main application as long as the returned data columns are the same
Well that's just an abstraction layer. You can build abstraction layers any way you want. A DLL, a web service, whatever.
Generally speaking even if you use stored procs you shouldn't have business logic in there anyway. So really what we're talking about is abstracting away the Database structure, which you should be doing in any case.
-Richard
Hit any user to continue.
|
|
|
|
|
Tomz_KV wrote: It has faster execution.
Not necessarily. The execution plan for the stored procedure is not determined dynamically, so may not be appropriate for the query as executed against the current data.
Tomz_KV wrote: Data/business logic can be modified in a stored proc alone without re-compliling the main application as long as the returned data columns are the same.
Why is that an advantage? Why is changing a stored procedure to implement a logic change "better" than changing compiled-to-exe code to implement a logic change?
|
|
|
|
|
Electron Shepherd wrote: Why is changing a stored procedure to implement a logic change "better" than changing compiled-to-exe code to implement a logic change?
For a in-house program, if the developer is not avaialble for making changes and re-compiling, a database guy could easily achieve the same goal by modifying the storedproc. This may not apply to a commercial program but happens frequenly for a home-grown program.
TOMZ_KV
|
|
|
|
|
Tomz_KV wrote: For a in-house program, if the developer is not avaialble for making changes and re-compiling, a database guy could easily achieve the same goal by modifying the storedproc
But, if the logic is in the stored procedure, the reverse is also true:
For a in-house program, if the DBA is not avaialble for making changes, a developer could easily achieve the same goal by making changes and re-compiling
Why is one better than the other?
The main disadvantage I can see with your approach is that an executable that has not changed starts behaving differently. That can lead to some unnecessary bug reports, when there is no "obvious reason" for the change in behaviour
|
|
|
|
|
Tomz_KV wrote: For a in-house program, if the developer is not avaialble for making changes and re-compiling, a database guy could easily achieve the same goal by modifying the storedproc.
This is NOT a valid reason for choosing one technology over another.
Changing logic at the database level is a BIG deal.
This isn't something you should be roping someone into simply because you can't find a handy developer. And hey! it's in the DB so a DBA should be able to handle it.
Does the DBA actually understand the intent of the original code? Or the full implications of changing it?
Or is he JUST FOLLOWING ORDERS?
This is exactly what I'm talking about. We treat a DB implemented API like it's some slow cousin from the country that can be pushed around and manipulated by anyone who can boot up TOAD.
It's Code dammit. It deserves the same amount of planning, control, respect and fear that VB or C# or C++ code commands.
The technical aspects of making a change to some VB isn't really all that different to changing a Stored Proc. Sure, deployment "might" require a little more effort, depending on your set up.
Technically making the change and deploying it isn't the bit that causes problems. It's the unforseen side effects of the change that will have you at your desk cursing at 8pm on a Friday night when your family is at home waiting for you.
And if it's your app that's breaking, the DBA that was just following orders isn't going to be by your side on Friday Night.
-Rd
Hit any user to continue.
|
|
|
|
|
After 15 years writing/debugging SQL oriented financial apps, I must disagree. The benefit to stored procedures boils down to how easy it is to code it in ADO/.NET/etc vs. a Stored proc. except when we are talking centralized commits and things having to do with exclusive access to a row/table. Those problems are best expressed in a Stored proc (which is centralized where the exclusive access is required), since tools like ADO are extremely wordy and obfuscated in comparison to the statements required to express them in PL/SQL or T/sql. Parallel queries and caching in modern databases have made the location of most SQL to be compiled totally unimportant. 20 years ago? You'd have a point. Now? Nope. Curious if what I'm saying is accurate? Try running a performance analyzer on such things when used more than once (like in a running system).
|
|
|
|
|
First off: Thank you for your rant. It's a one based on experience. If if folks don't agree or are quick to have a comment: I appreciate your frustration.
Now my turn for a comment or thought: I'll keep it general as possible.
I went into a database seminar related to a now defunc product that was one of the best analytic tools I've ever used in my entire career.. and this was in 2001! I've yet to see it's match today. It was called "BroadBase" and it was awesome. Anyway; before they were bought and the software purposely taken off the market even though it didn't compete with the new owner's products... I went to a seminar they hosted.
In that seminar we covered and discussed things like data-marts, data ware-housing, reporting, applications, various database vendors and design in general.
Some of the major points I remember quite clearly was where do you put your business logic: In the database or in the application? Now there are folks who say one, the other or both. Here were the things that interested me: they aren't answers.. but they are good things to consider when deciding:
1: Placing Business Rules and logic into the database (foreign keys, PL SQL etC) to help deliver information and protect data integrity put more reliance on the particular Database vendor. (Puts responsibility for data integrity on the database's shoulders)
2: Putting business rules and logic into your application makes your application less dependent on the specific DBMS your using
3: If multiple applications use the same database: Option 1 seems like a good option but putting business rules into a shared code base all systems can leverage gives benefits of option 2 but might be impossible or too difficult to implement enterprise wide.
So for me: I don't have an argument for one way or another. I'm also in agreement with other comments in this thread to the tune that it's not necessarily tools but skill that makes the difference in quality for finished systems.
I also am slow to bash folks for ugly systems I'm asked to fix because without knowing what environment the folks were working under: budget; time; boss; requirements; chief architect passed away mid project... god forbid...
My Opinion is: There isn't a one size fits all solution: consider ALL details; short term and long term goals of the project; consider your team, your budget, and make it happen as best you and your team can!
Know way too many languages... master of none!
|
|
|
|
|
JasonPSage wrote: My Opinion is: There isn't a one size fits all solution: consider ALL details; short term and long term goals of the project; consider your team, your budget, and make it happen as best you and your team can!
You come on here being all reasonable and well thought out.
We're trying to have an endless pointless quasi-religious argument here.
Apart from being a spoil sport, you are of course, mostly right. Except for that bit about forgiving people.
-Rd
Hit any user to continue.
|
|
|
|
|
|
Richard,
At least you appreciate the fact that knowing hwo to write SQL code does not mean one is a DBA. More often then not it is the DBA who has to deal with porrly designed DML code done by a OOP/Procedural developer then what you've run into. Before you decided on that pay cut just remember that if your good and well sought after you are more flexable and in a better possition when the job market is bad as it is now.
|
|
|
|
|
YSLGuru wrote: Before you decided on that pay cut just remember that if your good and well sought after you are more flexable and in a better possition when the job market is bad as it is now.
I don't have any problem with the job market. I've never had any problem finding work or getting well paid.
What I meant when I mentioned a pay cut is that I'm reaching the point where the money is no longer sufficient compensation for working in environments with bad (or no) development processes and a tangled mess of code.
-Rd
Hit any user to continue.
|
|
|
|
|
Ok, although I'm primarily a .NET developer, I'm gonna try to swing the religious discussion the other direction. It does sound you ran into some bad SQL code from a bad developer. Like others said, there are bad .NET developers as well, and there are plenty of programmers that don't store their .NET source code under version control. But you can very easily just export your db's DDL to a text file and put those under version control. It's so easy there's no excuse to not do it (people who don't should be fired). Furthermore, it's a _lot_ easier writing SQL code in e.g. SSMS where you have code completion, and where you can debug your SQL code. How are you going to debug your SQL that you wrote in strings in C#? There are good arguments for having all DB access go through sprocs (e.g. security is easier to manage). SQL injection is mostly not an argument (either way), as you can do parameterized queries in ADO just fine.
Having a insulation layer of sprocs can handle the core of business rules to keep the data mostly consistent. As far as I know you can't namespace your SQL code unfortunately, so it isn't really suitable to build huge frameworks in your SQL layer. It should just be the first line of defense keeping the integrity of the data. There will also be business logic in the C# layers on top of that, and possibly on the web layers (javascript an d such) as well. There's no way of putting business logic 100% in one neat layer (although you can apparently generate javascript to do some validation apparantly).
So things are not black and white, but they are shades of gray, and it takes years to judge the shades right and to judge how much weight should be put into which layer. The way I look at it, in a database centric application, there should be considerable weight on the database design and SQL code (sprocs) surrounding it, and it should not be regarded as just a dumb store of data for your super duper OO designed architecture. To put a number on it, I'd say around 30-40% of effort should be database related. If you're thinking too OO, then sooner or later you'll run into the impedance mismatch with the relational world. You'll build a much better system if you know _and_ how to design a database and write decent SQL code _and_ how to build the multi-tier business app on top of that. People that really don't want to know about the database, but are still gonna mess around with it, are most likely going to build a crappy system.
Wout
|
|
|
|
|
wout de zeeuw wrote: It does sound you ran into some bad SQL code from a bad developer.
No. I've run into 14 years of bad code from a host of developers, many of whom were quite good programmers, but when it came to DB stuff they (and I've done this myself) produced bad code.
I see the same thing with Javascript all the time incidently.
Read this other post for a perfect explaination of why I think there is something about Stored Procs that guide otherwise decent programmers into trouble.
http://www.codeproject.com/Feature/CodingHorrors.aspx?msg=3640078#xx3640078xx[^]
-Richard
Hit any user to continue.
|
|
|
|
|
Mwhoa, this whole thread is getting quite one sided because on CodeProject you are only getting the point of view of programmers and not of DBA's. So all the programmers are going to be naturally be inclined to think SQL and sprocs are inferior. You should post the same message on www.sqlservercentral.com[^] and see what responses you're getting there.
If a "good" programmer, writes bad SQL, then he's a bad SQL programmer, and he shouldn't be writing any. Either you let someone write SQL that has the skills, or you don't and you get sh*tty SQL.
About the dependencies that Trajan McGill is talking about: even in SSMS for SQL Server Express you can just right click a sproc and "View Dependencies". You can also debug SQL, step into sprocs etc. So his argument that you can't touch anything because you can't see the dependencies is incorrect. Furthermore also in .NET you often have dependencies outside the scope of your VS solution, which are also invisible, so in that department I see little difference between SQL/.NET.
Wout
|
|
|
|
|
wout de zeeuw wrote: If a "good" programmer, writes bad SQL, then he's a bad SQL programmer, and he shouldn't be writing any. Either you let someone write SQL that has the skills, or you don't and you get sh***y SQL.
The problem is that DBA's are focused on managing the Database, not developing Apps.
I've yet to work on any project where DBA's where charged with delivering application functionality. Or wanted to.
My problem and the problem I'm trying to get accross in this thread is that the debate about Stored Procedures and Packages tends to focus on the same old stuff...
* Performance (not a valid argument either way for most situations)
* SQL injection (not a valid argument for most situations)
* Abstraction of the Database Structure (not a valid argument either way)
The debate rarely focuses on the things that should be significant.
Do you have the skills, infrastructure and processes in place to actually to DB based development properly?
When one asks this question it's dismissed....
"Oh sure if you have developers that don't know how to write PL/SQL properly then you'll get crappy PL/SQL."
Well guess what? It would appear that the majority of developers don't know how to write good code in their specialty language, allowing them to write PL/SQL which they aren't specialists in is suicide.
And yet, the notion that it would be better to have these developers work exclusively in VB.Net or C# as much as possible is heresy. Nooo Nooo Nooo.....You must use Stored Procs....It's just...Better.
Well it's not better. Not if it's done wrong, which all to often it is.
I also maintain, and will always maintain the languages like PL/SQL are fundamentally unsuited to implementing complex business logic.
-Richard
Hit any user to continue.
|
|
|
|
|
Use an ORM and only revert to stored procedures where (and if) performance bottlenecks exist. The application will be cleaner, easier to debug, and you will develop it much faster. If you need more speed, then might want to consider alternatives to .NET. Now bring on the flames!
|
|
|
|
|
mutantdna wrote: easier to debug
Are you aware the Visual Studio allows you to put breakpoints in a SQL Server stored procedure and debug it just as you would C# or VB.Net code?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
