Stored Procs, Packages, Views...Pah!
-
Warning: The Following is a Rant. From time to time I get into the religious 'To Stored Proc or not To Stored Proc' argument. Let me confess right now. As much as I appreciate all the pro's of Stored Procs, I still prefer to not use them when it can be avoided. It may be a technically inferior solution but I still prefer to create functions and subs in my VB or C# code that perform the equivalent task. I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc. It's a bloody nightmare. And to top it all, I regularly have to deal with the fact that the features needed to debug aren't installed, or they're running some cut down version of a DBMS. So I'm debugging this rats maze of code using techniques that I haven't used since I wrote Basic on my Sinclair Spectrum. Plug in Values. Run. Check Log File. Nothing Happened. Plug in Values. Run. Check Log File. Something unexpected happened. repeat to fade. Right now I'm trawling through the PL/SQL code of a very large globally known company. Honestly I've decided that what I'm looking at is a cast off from that team of monkeys that are working on the complete works of Shakespeare. I also regularly have to deal with Databases that contain hundreds of functions, procs and views where nobody knows if they are needed anymore but everybody is terrified to modify or remove any of them. I see companies who spawn a new copy every time a proc needs to be changed (just in case) and use that. I see companies who have no Gold version of their DB. When they need a DB either for test or for a new Production site, they just copy an existing production site. As for version control. It seems DB Objects live in some abstract zone, like International waters that aren't covered by the treaties that cover version control. This is no way to live. Thanks for letting me get that off my chest. Now back to the rats maze. -Richard
Hit any user to continue.
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!
-
ScottM1 wrote:
Different DBMS's have different escape characters
True. They also have different stored procedure syntax. Are you suggesting that's a good reason not to use stored procedures?
That's not what I'm suggesting at all. If you were using stored procedures the procedure syntax would not change the way you call the stored procedures from your application. If you were building SQL queries in your application and escaping it yourself different escape characters would force you to go through every single one and change it. What happens if you forget to escape one of the arguments before you pass it? Read this[^]
-
That's not what I'm suggesting at all. If you were using stored procedures the procedure syntax would not change the way you call the stored procedures from your application. If you were building SQL queries in your application and escaping it yourself different escape characters would force you to go through every single one and change it. What happens if you forget to escape one of the arguments before you pass it? Read this[^]
So if I change the database, I have to change one function (note: not "every single query") that escapes out strings. If you change database, you (possibly) have to rewrite every single stored procedure.
ScottM1 wrote:
What happens if you forget to escape one of the arguments before you pass it?
Then you have a security bug. Newsflash: If you don't write code correctly, it has bugs.
-
So if I change the database, I have to change one function (note: not "every single query") that escapes out strings. If you change database, you (possibly) have to rewrite every single stored procedure.
ScottM1 wrote:
What happens if you forget to escape one of the arguments before you pass it?
Then you have a security bug. Newsflash: If you don't write code correctly, it has bugs.
Electron Shepherd wrote:
Then you have a security bug.
No, then YOU have a security bug, I don't have to worry about this. The fact of the matter is you shouldn't be escaping characters yourself. Parameterized queries were created for this purpose so why would you want to even do it yourself? There are also performance benefits to using parameterized queries.
-
Electron Shepherd wrote:
Then you have a security bug.
No, then YOU have a security bug, I don't have to worry about this. The fact of the matter is you shouldn't be escaping characters yourself. Parameterized queries were created for this purpose so why would you want to even do it yourself? There are also performance benefits to using parameterized queries.
ScottM1 wrote:
The fact of the matter is
Opinion, not fact.
ScottM1 wrote:
so why would you want to even do it yourself?
And if you need to uyse and IN clause? What do you do then?
ScottM1 wrote:
There are also performance benefits to using parameterized queries.
Really? As they say "state your source". Have you measured them?
-
Electron Shepherd wrote:
Then you have a security bug.
No, then YOU have a security bug, I don't have to worry about this. The fact of the matter is you shouldn't be escaping characters yourself. Parameterized queries were created for this purpose so why would you want to even do it yourself? There are also performance benefits to using parameterized queries.
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.
-
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.
-
ScottM1 wrote:
The fact of the matter is
Opinion, not fact.
ScottM1 wrote:
so why would you want to even do it yourself?
And if you need to uyse and IN clause? What do you do then?
ScottM1 wrote:
There are also performance benefits to using parameterized queries.
Really? As they say "state your source". Have you measured them?
Electron Shepherd wrote:
Opinion, not fact.
Fact
Electron Shepherd wrote:
And if you need to uyse and IN clause? What do you do then?
I already told you in a previous post.
Electron Shepherd wrote:
Really? As they say "state your source". Have you measured them?
A parameterized query will use the same execution plan each time even if there are different arguments passed, adhoc statements will recompile each time different arguments are passed.
-
Electron Shepherd wrote:
Opinion, not fact.
Fact
Electron Shepherd wrote:
And if you need to uyse and IN clause? What do you do then?
I already told you in a previous post.
Electron Shepherd wrote:
Really? As they say "state your source". Have you measured them?
A parameterized query will use the same execution plan each time even if there are different arguments passed, adhoc statements will recompile each time different arguments are passed.
"The fact of the matter is you shouldn't be escaping characters yourself." That is an opinion, held by you. You are perfectly entitled to hold that opinion. However, the one thing it is not is a fact.
ScottM1 wrote:
A parameterized query will use the same execution plan each time even if there are different arguments passed, adhoc statements will recompile each time different arguments are passed.
Is that true? I don't see how it can be. I thought they were compiled each time, since a parameterised query is an ad-hoc query. All you are changing is how the variable parts of the query reach the DBMS.
-
"The fact of the matter is you shouldn't be escaping characters yourself." That is an opinion, held by you. You are perfectly entitled to hold that opinion. However, the one thing it is not is a fact.
ScottM1 wrote:
A parameterized query will use the same execution plan each time even if there are different arguments passed, adhoc statements will recompile each time different arguments are passed.
Is that true? I don't see how it can be. I thought they were compiled each time, since a parameterised query is an ad-hoc query. All you are changing is how the variable parts of the query reach the DBMS.
It is true. Parameterized queries result in prepared statements whose execution plan is re-used with different parameters, this is because it basically calls the system stored procedure sp_execute. An adhoc query will only use the same execution plan if the query is exactly the same including all parameters and whitespace characters.
-
It is true. Parameterized queries result in prepared statements whose execution plan is re-used with different parameters, this is because it basically calls the system stored procedure sp_execute. An adhoc query will only use the same execution plan if the query is exactly the same including all parameters and whitespace characters.
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.
-
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.
-
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.
-
I agree 100%, there is too many drawbacks on using adhock queries in the client application that most of the times prevents scaling an application. what if you have a table holding customer records, and you want to move the address columns to a new table to introduce multiple addresses for a customer. how are you going to due this when you have a web app, thick client, analysis cubes, reporting that will all require change and how do you manage deploying this change out? by preventing adhock or table direct queries all one would have to due is modify a few views and procs. secondly how can you ensure that all the adhock queries in all applications that are consuming this database are all using indexes, common business logic and formulas. and how would you trace down a poorly written adhock query that is table scanning, how do you go about fixing it and deploying it without impacting other user applications. my developers may only select from a view or call a proc from any application period.... look on the bright side, you have an opportunity to catalog all objects and send out impact statements to understand who is using what and also have an opportunity to find synergies between your development teams to come to a common process
Well, in a well designed application, the data access logic would be in a separate library called from all you clients. So you'd change it there (similar to just changing the SP). The change is still in one place - but it is in code, that the developer can find, debug, version control, etc. And a quick scan of the code can find the references - no orphaned SP hanging out there.
-
You are echoing exactly the point I made about VB. It's the developers not the tools that are the problem. Yes. You can write good, clear PL/SQL (ish). Yes. All of the problems I have mentioned are the fault of humans, not the software, not the language. BUT....... The level of expertise generally for PL/SQL or T/SQL or indeed databases in general seems so poor that I consider them toxic pieces of technology. Right now I'm working on Code that comes from...ORACLE. Surely to goodness if any company should care about turning out a decent stab at good PL/SQL it should be ORACLE. It's a fricken mess. I can't stress enough just how bad this code is. I've been in this business for over 14 years and this is genuinely the worst code of any kind that I have ever worked with. I will go so far as to say that it is impossible to work with. Now. As bad as the code can be in VB or ASP.Net or C++ or Whatever, at least I have decent relatively mature and solid debugging tools. At least by and large source code tends to be under source control. At lest by and large there tend not to be multiple different versions of the same functions (ish). Also, the nature of PL/SQL and T/SQL etc are that they are In my opinion fundamentally ugly languages. Verbose and difficult to work with when doing anything but the most basic tasks. The Development Tools seem geared towards light admin rather than heavy development. Perhaps, with the right set of tools, life could be easier, but far from the right set of tools, most companies I visit don't even have a basic set of tools. They set up the cheapest simplest configuration that gets them moving. Need Debugging? Screw you Jack...create some log files. So we have a number of problems coming together in a perfect storm. 1. Developers DB skills beyond the absolute basics are poor. Attitude seems to be...DB? Pff How hard can that be? It's just SQL right? 2. Most who can write PL/SQL or T/SQL have no concept of what quality code is. Indeed I don't think they see their PL/SQL as code, and they don't feel obliged to observe ANY rules of craftsmanship. 3. Development and Debugging tools are poor at best. 4. Most clients seem confused about how to integrate DB development into their overall development process. And seem reluctant to heed advice on how to do so. 5. There can and often is a turf war between developers and DBA's that make a difficult situation worse. Overall...my feeling is...it's not wort
"Treat the DB as a data store only" Sorry but that’s not any better a philosophy to follow either unless your DB and use load is so small that performance doesn't matter. The problem is one of 2 competing camps in which few are able to work with the other, less are knowledgeable about both and even fewer can properly work in and properly use each as it is intended to be. Your right in that the DB should be your data store and your App should be your interface to the user but you don't place your business logic in your app just because you don’t like or aren't familiar with using T-SQ/ or PL-SQL. When procedural or OOP types trying to do their own IUD's (Inserts, Updates and/or Deletes) they almost always resort to that most foul of all things in SQL programming the CURSOR. They do this because they get the CURSOR concept and so they can use it with far less effort. The problem is that CURSOR is Latin for "Slow this Mo-Fo Down" and so performance and good design get thrown out like last year’s lowest rated reality TV show. The answer is if you know how to use both, the OOP/Procedural app side and the RBDMS/SQL side then use each as they should be. If you are one who likes CURSORS then you got no business writing DML statements. On the flip side if you can't properly design and instantiate classes and objects or worse you ask what the difference is then you got no business in Visual Studio. Experience however shows that more often you have developer types doing too much DML coding in their app instead of leaving it to the more knowledgeable DBA types then the reverse. Most DBA's are happy not dealing with the OOP world and so you don’t really have to worry about them crossing over where they shouldn’t. Reading SAMS “SQL in 24 Hours” no more makes a developer a good DBA then “.Net in 24 Hours or less” makes a DBA a good developer.
-
Warning: The Following is a Rant. From time to time I get into the religious 'To Stored Proc or not To Stored Proc' argument. Let me confess right now. As much as I appreciate all the pro's of Stored Procs, I still prefer to not use them when it can be avoided. It may be a technically inferior solution but I still prefer to create functions and subs in my VB or C# code that perform the equivalent task. I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc. It's a bloody nightmare. And to top it all, I regularly have to deal with the fact that the features needed to debug aren't installed, or they're running some cut down version of a DBMS. So I'm debugging this rats maze of code using techniques that I haven't used since I wrote Basic on my Sinclair Spectrum. Plug in Values. Run. Check Log File. Nothing Happened. Plug in Values. Run. Check Log File. Something unexpected happened. repeat to fade. Right now I'm trawling through the PL/SQL code of a very large globally known company. Honestly I've decided that what I'm looking at is a cast off from that team of monkeys that are working on the complete works of Shakespeare. I also regularly have to deal with Databases that contain hundreds of functions, procs and views where nobody knows if they are needed anymore but everybody is terrified to modify or remove any of them. I see companies who spawn a new copy every time a proc needs to be changed (just in case) and use that. I see companies who have no Gold version of their DB. When they need a DB either for test or for a new Production site, they just copy an existing production site. As for version control. It seems DB Objects live in some abstract zone, like International waters that aren't covered by the treaties that cover version control. This is no way to live. Thanks for letting me get that off my chest. Now back to the rats maze. -Richard
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.
-
Could you supply example code of how to use parameters with an IN clause, where the number of items in the in clause is not known until runtime? I've never seen a solution to that one...
What kind of query do you have in mind here?
Wout
-
"Treat the DB as a data store only" Sorry but that’s not any better a philosophy to follow either unless your DB and use load is so small that performance doesn't matter. The problem is one of 2 competing camps in which few are able to work with the other, less are knowledgeable about both and even fewer can properly work in and properly use each as it is intended to be. Your right in that the DB should be your data store and your App should be your interface to the user but you don't place your business logic in your app just because you don’t like or aren't familiar with using T-SQ/ or PL-SQL. When procedural or OOP types trying to do their own IUD's (Inserts, Updates and/or Deletes) they almost always resort to that most foul of all things in SQL programming the CURSOR. They do this because they get the CURSOR concept and so they can use it with far less effort. The problem is that CURSOR is Latin for "Slow this Mo-Fo Down" and so performance and good design get thrown out like last year’s lowest rated reality TV show. The answer is if you know how to use both, the OOP/Procedural app side and the RBDMS/SQL side then use each as they should be. If you are one who likes CURSORS then you got no business writing DML statements. On the flip side if you can't properly design and instantiate classes and objects or worse you ask what the difference is then you got no business in Visual Studio. Experience however shows that more often you have developer types doing too much DML coding in their app instead of leaving it to the more knowledgeable DBA types then the reverse. Most DBA's are happy not dealing with the OOP world and so you don’t really have to worry about them crossing over where they shouldn’t. Reading SAMS “SQL in 24 Hours” no more makes a developer a good DBA then “.Net in 24 Hours or less” makes a DBA a good developer.
You make some good points, but you're replying to only half of a quote... He said:
Treat the DB as a data store only *unless* the client has the infrastructure and the expertise to support using the DB for more.
In other words, don't mess with stored procedures unless you (or your client) has the skill-set available to support and maintain them. So you guys seem to be pretty much in agreement :)
Proud to have finally moved to the A-Ark. Which one are you in?
Author of the Guardians Saga (Sci-Fi/Fantasy novels) -
Except when it comes to security - isolating code that changes the database into stored procedures that accept parameters helps guard against some of the most common security violations - SQL injection attacks, and far too few coders seem to appreciate the effort required to adequately ensure that SQL statements build and submitted to the database are adequately cleansed to prevent this. This scenario comes to mind: Little Bobby Tables (XKCD)
Seems Code Project is not susceptible to this form of attack. :)
-
Warning: The Following is a Rant. From time to time I get into the religious 'To Stored Proc or not To Stored Proc' argument. Let me confess right now. As much as I appreciate all the pro's of Stored Procs, I still prefer to not use them when it can be avoided. It may be a technically inferior solution but I still prefer to create functions and subs in my VB or C# code that perform the equivalent task. I have finally decided that the reason for my bias is that the greatest Code Horrors I deal with now are badly written Packages and Stored Procs etc. It's a bloody nightmare. And to top it all, I regularly have to deal with the fact that the features needed to debug aren't installed, or they're running some cut down version of a DBMS. So I'm debugging this rats maze of code using techniques that I haven't used since I wrote Basic on my Sinclair Spectrum. Plug in Values. Run. Check Log File. Nothing Happened. Plug in Values. Run. Check Log File. Something unexpected happened. repeat to fade. Right now I'm trawling through the PL/SQL code of a very large globally known company. Honestly I've decided that what I'm looking at is a cast off from that team of monkeys that are working on the complete works of Shakespeare. I also regularly have to deal with Databases that contain hundreds of functions, procs and views where nobody knows if they are needed anymore but everybody is terrified to modify or remove any of them. I see companies who spawn a new copy every time a proc needs to be changed (just in case) and use that. I see companies who have no Gold version of their DB. When they need a DB either for test or for a new Production site, they just copy an existing production site. As for version control. It seems DB Objects live in some abstract zone, like International waters that aren't covered by the treaties that cover version control. This is no way to live. Thanks for letting me get that off my chest. Now back to the rats maze. -Richard
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