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.
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
-
Richard A. Dalton wrote:
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.
But THIS is the problem, not really the 'concepts' of stored procedures or packages. The same can be said about regular code and writing shared libraries and using objects. Poorly written anything is junk. You can’t blame the tool or the concept really. Blame the user of the concept/tool = YES Blame the reviewer of the designer = YES Blame the reviewer of the implementation = YES You can use the best tools on the best platform using the best agreed upon methodologies and still write code that is ugly.
No, there's more to it than "poorly written anything is junk". The problem is structural in nature, not just a matter of bad coders. The problem introduced here is actually very similar to "DLL hell". An application's domain of code-based dependencies and interactions is well mapped and tracked by modern programming tools. Typically you define a "solution" or something along those lines, with all your code together tracked in source control, and have a build process that carefully lays out what is dependent on what. Stored procedures are an aberration from this entire model. They're dependencies that you don't build in, they sit out there somewhere outside the same domain of control and tracking, and they change. Or they don't, and they become one-use things that build up in a giant, unstructured list, violating both notions of code organization and code re-use. They're like web services, in that they are exposed for use, and then you never really know who or what is using them, and so they are really hard, compared to regular code, to be confident in changing or eliminating. All kinds of applications, services, and other database objects could have come to rely on an SP over time, but the dependencies are invisible, and it takes a lot of work to track them down, and sometimes isn't even possible. Don't get me wrong, SP's are actually important, for performance reasons, for code safety reasons, and so on. But they definitely break the model of good code practices that we all want to follow, not breaking it just because of coders being good or bad, but because these things exist in a different arena outside the bounds of the models and practices that good programmers put in place to manage their projects. They also make themselves frustrating in two other ways. #1, They lend themselves to obfuscated code in a way that regular programming languages do not. SQL is designed as a query language, not a procedural, object-oriented, or business object modeling language. Things that are perfectly clear and can be documented and written very clearly in, say, C#, are very messy in SQL. This would be okay-- use each language for its own best purpose-- except... #2, They nearly always wind up including more than just data queries. Admittedly this is partly-- but not completely-- an architectural or coder-dependent decision, but SP's tend to absorb business logic and destroy the multi-tiered design principles that you worked so hard to keep to. You wind up with a design that looks like this:
-
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.
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!
-
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?
-
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?
-
No I was not aware of this - so thanks for the tip. Does it also work for Oracle? MySQL, SQLite? Or is this specific to the MS product stack?
I'm not sure, I've only ever tried it with SQL Server. Though, if you want to research it, this might be a good place to start.
-
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.
-
No, there's more to it than "poorly written anything is junk". The problem is structural in nature, not just a matter of bad coders. The problem introduced here is actually very similar to "DLL hell". An application's domain of code-based dependencies and interactions is well mapped and tracked by modern programming tools. Typically you define a "solution" or something along those lines, with all your code together tracked in source control, and have a build process that carefully lays out what is dependent on what. Stored procedures are an aberration from this entire model. They're dependencies that you don't build in, they sit out there somewhere outside the same domain of control and tracking, and they change. Or they don't, and they become one-use things that build up in a giant, unstructured list, violating both notions of code organization and code re-use. They're like web services, in that they are exposed for use, and then you never really know who or what is using them, and so they are really hard, compared to regular code, to be confident in changing or eliminating. All kinds of applications, services, and other database objects could have come to rely on an SP over time, but the dependencies are invisible, and it takes a lot of work to track them down, and sometimes isn't even possible. Don't get me wrong, SP's are actually important, for performance reasons, for code safety reasons, and so on. But they definitely break the model of good code practices that we all want to follow, not breaking it just because of coders being good or bad, but because these things exist in a different arena outside the bounds of the models and practices that good programmers put in place to manage their projects. They also make themselves frustrating in two other ways. #1, They lend themselves to obfuscated code in a way that regular programming languages do not. SQL is designed as a query language, not a procedural, object-oriented, or business object modeling language. Things that are perfectly clear and can be documented and written very clearly in, say, C#, are very messy in SQL. This would be okay-- use each language for its own best purpose-- except... #2, They nearly always wind up including more than just data queries. Admittedly this is partly-- but not completely-- an architectural or coder-dependent decision, but SP's tend to absorb business logic and destroy the multi-tiered design principles that you worked so hard to keep to. You wind up with a design that looks like this:
Have a 5 from me for perfectly expressing the point I was trying but failing to make. -Rd
Hit any user to continue.
-
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?
aspdotnetdev wrote:
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?
Actually it gets better than that. You can write your stored procedures in actual C# or VB.Net if you want. I've played with it but not really done more than that. Anyone jumped on this bandwagon? Any thoughts? -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.
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.
-
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 shitty 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
-
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 shitty 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.
-
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?
-
No I was not aware of this - so thanks for the tip. Does it also work for Oracle? MySQL, SQLite? Or is this specific to the MS product stack?
mutantdna wrote:
Does it also work for Oracle? MySQL, SQLite?
My understanding is that it will only work for MSSQL. I believe that Microsoft has built in some debugging capability into the Management Studio engine that Visual Studio utilizes.
I wasn't, now I am, then I won't be anymore.
-
aspdotnetdev wrote:
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?
Actually it gets better than that. You can write your stored procedures in actual C# or VB.Net if you want. I've played with it but not really done more than that. Anyone jumped on this bandwagon? Any thoughts? -Richard
Hit any user to continue.
Indeed, I guess that's called SQL Server CLR Integration. Could be useful, but I'd only use it when necessary, as that does create maintenance burden for the next guy who has to administer the database.
-
Yeah, worked fine for me. There were some limitations though. Off the top of my head, I think I was unable to view data in table variables or temp tables (though you can create a cursor to loop through each value in the temporary table and set a breakpoint to inspect the resulting variables, one row at a time) and you apparently need to change your connection string so connection pooling isn't used.
-
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.
Funny stuff. Database related code should remain with the database. Just because in your experiences people don't seem to be able to write good clean sql code doesn't mean its to be avoided. Before a proc is committed to source control or production it should be reviewed just like you should with other code you write. Instead of complaining and avoiding the real world, offer your expertise to help others, offer your time to review and explain the hows and whys of your criticisms. Don't let the end product suffer just because some are not as capable as yourself. Make the best choice of technologies to use, decide on the best way to use them, educate yourselves and your peers.
-
Funny stuff. Database related code should remain with the database. Just because in your experiences people don't seem to be able to write good clean sql code doesn't mean its to be avoided. Before a proc is committed to source control or production it should be reviewed just like you should with other code you write. Instead of complaining and avoiding the real world, offer your expertise to help others, offer your time to review and explain the hows and whys of your criticisms. Don't let the end product suffer just because some are not as capable as yourself. Make the best choice of technologies to use, decide on the best way to use them, educate yourselves and your peers.
ohmyletmein wrote:
Instead of complaining and avoiding the real world, offer your expertise to help others, offer your time to review and explain the hows and whys of your criticisms. Don't let the end product suffer just because some are not as capable as yourself.
I have sort of a problem with your post. But you may have just picked me up wrong. You seem to think that I think I'm some sort of expert looking down my nose at bad programmers. Far from it. I said explicitly I don't think I am qualified to put complex logic into stored procs. You say that Data related code belongs in the Database. Well where to you draw that line? Ultimately it's all data related logic right? We build apps that manipulate Data. What it comes down to is that languages like PL/SQL are fundamentally unsuited to representing complex logic. Here's an example and this is code that comes from Oracle of all companies. I'm trying to change the date on a contract. I have to call a function in package. The log file for toggling that currency runs to over 40 PAGES if I paste it into word. Other similar small changes produce log files that are over 100 pages. And it isn't becuase the logging is very detailed, it's because there is a shed load of business rules veing validated. This logic is virtually impossible to debug. It might be possible to rewrite this logic in PL/SQL and improve it but I don't believe it could ever be represented as elegantly and as simply as it could be in proper high level programming language. PL/SQL is for relatively simple querying and manipulation of data and rudimentary validation. When things get more complicated than that It might not be suitable at all, or if it is "possible" the level of skill requried is frankly beyond the majority of developers (including me). -Rd
Hit any user to continue.
-
ohmyletmein wrote:
Instead of complaining and avoiding the real world, offer your expertise to help others, offer your time to review and explain the hows and whys of your criticisms. Don't let the end product suffer just because some are not as capable as yourself.
I have sort of a problem with your post. But you may have just picked me up wrong. You seem to think that I think I'm some sort of expert looking down my nose at bad programmers. Far from it. I said explicitly I don't think I am qualified to put complex logic into stored procs. You say that Data related code belongs in the Database. Well where to you draw that line? Ultimately it's all data related logic right? We build apps that manipulate Data. What it comes down to is that languages like PL/SQL are fundamentally unsuited to representing complex logic. Here's an example and this is code that comes from Oracle of all companies. I'm trying to change the date on a contract. I have to call a function in package. The log file for toggling that currency runs to over 40 PAGES if I paste it into word. Other similar small changes produce log files that are over 100 pages. And it isn't becuase the logging is very detailed, it's because there is a shed load of business rules veing validated. This logic is virtually impossible to debug. It might be possible to rewrite this logic in PL/SQL and improve it but I don't believe it could ever be represented as elegantly and as simply as it could be in proper high level programming language. PL/SQL is for relatively simple querying and manipulation of data and rudimentary validation. When things get more complicated than that It might not be suitable at all, or if it is "possible" the level of skill requried is frankly beyond the majority of developers (including me). -Rd
Hit any user to continue.
I agree that complex logic does not have a place in db code, but I also believe that the integrity of data written in he database needs to be ensured by extensive use of constraints, keys, and, most importantly, value validation code. The database should be viewed as a data store that knows just enough to maintain the integrity of the data housed in it, but actual data manipulation should be left to the consuming applications.
I wasn't, now I am, then I won't be anymore.