Are stored procs "hiding functionality"?

Post #415,774 by drook 12/1/16 9:58:23 AM 12/1/16 9:58:23 AM Reply	Are stored procs "hiding functionality"? I just heard from one of our architects that he doesn't like using stored procs for updates because they hide functionality and you don't know what they're doing. As a developer he wants to see everything in one place in the source. I have an opinion, but I haven't been slinging curly braces for a while. What's the feels? -- Drew
Post #415,777 by boxley 12/1/16 10:39:13 AM 12/1/16 10:39:13 AM Reply	wag, the same as a shared library, you dont know where it came from always look out for number one and don't step in number two
Post #415,778 by Another Scott 12/1/16 10:43:42 AM 12/1/16 10:43:42 AM Reply	Interesting question. Here's another view: https://msdn.microsoft.com/en-us/library/ms973918.aspx Maintainability and Abstraction The second potential benefit to consider is maintainability. In a perfect world, your database schema would never change and your business rules would never get modified, but in the real world these things happen. That being the case, it may be easier for you if you can modify a stored procedure to include data from the new X, Y, and Z tables that have been added to support that new sales initiative, instead of changing that information somewhere in your application code. Changing it in the stored procedure makes the update transparent to the application — you still return the same sales information, even though the internal implementation of the stored procedure has changed. Updating the stored procedure will usually take less time and effort than changing, testing, and re-deploying your assembly. Also, by abstracting the implementation and keeping this code in a stored procedure, any application that needs access to the data can get it in a uniform manner. You don't have to maintain the same code in multiple places, and your users get consistent information. Another maintainability benefit of storing your T-SQL in stored procedures is better version control. You can version control the scripts that create and modify your stored procedures, just as you can any other source code module. By using Microsoft Visual SourceSafe® or some other source control tool, you can easily revert to or reference old versions of the stored procedures. One caveat with using stored procedures to enhance maintainability is they cannot insulate you from all possible changes to your schemas and rules. If the changes are large enough to require a change in the parameters fed into the stored procedure, or in the data returned by it, then you are still going to have to go in and update the code in your assembly to add parameters, update GetValue() calls, and so forth. Another issue to consider is that using stored procedures to encapsulate business logic limits your application portability, in that it ties you to SQL Server. If application portability is critical in your environment, encapsulating business logic in a RDBMS-neutral middle tier may be a better choice. It sounds like there are trade-offs (as always). Cheers, Scott.
Post #415,781 by malraux 12/1/16 1:29:20 PM 12/1/16 1:29:20 PM Reply	Better to manage that sort of problem through services Stored procedures can be considered services, sure, but you run a maintainability cost just by virtue of how database servers work. Regards, -scott Welcome to Rivendell, Mr. Anderson.
Post #415,779 by malraux 12/1/16 1:28:01 PM 12/1/16 1:28:01 PM Reply	Depends on how they're managed. If the stored procedures are managed in the same source code repository and built with the same build system, then no, it's not hiding functionality (unless your architect just doesn't know SQL and therefore it's inscrutable to him, in which case he's not really an architect now, is he...). I used to run a system that had millions of lines of stored procedures, but it was part of the code base right next to the Java and C++. I wouldn't consider it hidden at all. If you have DBAs with a separate repository or, gasp, just running scripts into the DB ad hoc, then yeah, I'd think that stuff was hidden from the development team. Regards, -scott Welcome to Rivendell, Mr. Anderson.
Post #415,782 by mmoffitt 12/1/16 1:37:54 PM 12/1/16 1:37:54 PM Reply	And don't forget about sp's providing better performance on the database. bcnu, Mikem I think religion should be treated with ridicule, hatred and contempt. And I claim that right. Christopher Hitchens.
Post #415,793 by malraux 12/1/16 5:29:11 PM 12/1/16 5:29:11 PM Reply	That depends On architecture, caching, data usage patterns, scaling, and a myriad other concerns too. Regards, -scott Welcome to Rivendell, Mr. Anderson.
Post #415,817 by mmoffitt 12/2/16 11:07:32 AM 12/2/16 11:07:32 AM Reply	For OLTP applications, I disagree, with MS Sql Server anyway. Sure, you can optimize the server for ad hoc, force parameterization, etc., and vastly improve ad hoc performance, but overall (including network traffic) stored procedures on MS Sql Server for an OLTP application will outperform ad hoc. Unless you can convince me that this guy ( http://www.sqlbits.com/sessions/event14/Stored_Procedures_vs_Ad-Hoc_SQL_Performance_Showdown ) is completely wrong, I'm sticking with OLTP apps benefit performance-wise over ad hoc. If I'm wrong about that, what have I missed? (and yes, I am genuinely asking). bcnu, Mikem I think religion should be treated with ridicule, hatred and contempt. And I claim that right. Christopher Hitchens.
Post #415,824 by drook 12/2/16 2:39:34 PM 12/2/16 2:39:34 PM Reply	Performance is the least of my concerns at this point IMO queries should be written first for correctness, second for clarity, third for performance. (Just like all code.) Correctness includes returning the right results and maintaining data integrity. You have to do both as an absolute baseline before anything else matters. After that, clarity/readability is far more valuable than performance 90% of the time. -- Drew
Post #415,852 by malraux 12/4/16 10:59:35 AM 12/4/16 10:59:35 AM Reply	Until it isn't any more. :-) But generally I agree. Don't prematurely optimize. Stored procedures are dangerously seductive for that. Regards, -scott Welcome to Rivendell, Mr. Anderson.
Post #415,850 by malraux 12/4/16 10:58:19 AM 12/4/16 10:58:19 AM Reply	Performance != throughput Which is why I said it depends. A straight through speed test, even a comprehensive one, tells you little about how a system performs under full load. The database is the hardest piece of architecture to scale simply. When you reach the point where too much is going on for a single server, you start to have problems. If large portions of your logic are in the database, you really have problems. This isn't theoretical, either. I've seen Oracle databases running on the largest box Sun made at the time moved to RAC and having horrific problems due to intense stored procedure usage. Regards, -scott Welcome to Rivendell, Mr. Anderson.
Post #415,854 by drook 12/4/16 11:07:14 AM 12/4/16 11:07:14 AM Reply	Interesting Since all the logic in a proc has to happen somewhere, I would assume doing it outside the DB would take longer on head-to-head, no-load comparisons. As long as the logic happening in the proc has to be atomic, wouldn't that continue to get worse as things scale? In fairness, I'm not a DBA so I'm assuming there's something in DB world that's like the multiple small compute instances you can do with app servers. I've never built out DB infrastructure at this scale though, so I'm coming from the standpoint that I can't be the only one trying to do this so someone must have solved this. -- Drew
Post #415,874 by static 12/5/16 4:05:45 AM 12/5/16 4:05:45 AM Reply	I've looked at DB scale out, though not recently. In a previous job, we ran into questions about how to tackle that kind of problem. Read-scaling can be done with simple replication, if you don't need transactions. We did that, and that was reliable. It helped I was also one of the web programmers and could build the database abstraction layer to load-balance SQL Selects. Another method is sharding, which seems to have matured in the last few decades. We would've had to do it ourselves when we looked at the concept and I wasn't sure I was up to doing that! The Wikipedia article has more information. Wade.
Post #415,884 by malraux 12/5/16 9:15:44 AM 12/5/16 9:15:44 AM Reply	Eventually the amount of work exceeds the capacity of a single machine to do it At which point you have a distributed system whether you want it or not, and a distributed database is one of the hardest things to get right. Regards, -scott Welcome to Rivendell, Mr. Anderson.
Post #415,860 by boxley 12/4/16 5:15:50 PM 12/4/16 5:15:50 PM Reply	sure, lets take the speed out of the cpu and replace it in multiple small connected units, :-) always look out for number one and don't step in number two
Post #415,785 by pwhysall 12/1/16 2:01:23 PM 12/1/16 2:01:23 PM Reply	Implementation detail, someone else's problem. Also, can you take 20% off that effort estimate? Awesome! /bidmanager
Post #415,799 by crazy 12/2/16 3:11:10 AM 12/2/16 3:11:10 AM Reply	Who is responsible for what? And how many coders need access? Sps can "hide" the complexity of the under lying table/function which in turn can be a win, allowing the "lesser" coders to have functionality that they wouldn't otherwise have. Same trade-off as centralized library functions. Blanket statements such as this points to a troubling dogma. Bottom line: most coders should not have to think about all the ins and outs of data integrity, they simply need the job done, and a centralized access path makes it safer.
Post #415,808 by drook 12/2/16 9:53:40 AM 12/2/16 9:53:40 AM Reply	I'm coming down on the side of your data integrity comment This is coming up because we contracted out development of an integration with another app. They wrote a process to sync data between our schema and the new app's schema. When they checked it in, they were using raw inserts instead of some existing stored procs that did additional housekeeping. If we had proper constraints in the DB we could have had cascading updates and the procs would have been unnecessary, or at least the inserts would have failed immediately instead of creating inconsistent data. So it's not necessarily procs that I want, it's that somehow the data integrity should be managed in the DB, so there's no way for a developer to write code that breaks our data. -- Drew
Post #415,833 by static 12/3/16 5:05:28 AM 12/3/16 5:05:28 AM Reply	Sounds like a management problem. Surely the documentation told them they should use the stored procedures instead of direct inserts. It sounds like your architect is correctly upset, but possibly for the wrong reason. Data integrity can be done down in the database, if the app knows how to cope when the database says "No". It can be up in the app if it can cope with integrity broken from below (e.g. from an explicit insert). Wade.
Post #415,834 by drook 12/3/16 10:02:22 AM 12/3/16 10:02:22 AM Reply	You think? What if I told you there is no documentation of the requirements? How surprised would you be? -- Drew
Post #415,843 by static 12/3/16 10:20:51 PM 12/3/16 10:20:51 PM Reply	Not surprised at all. Which means that those complaining about using Inserts instead of the not-at-all-documented stored procedures kind of don't have all that much to complain about! :-D Though I'd be surprised if they were to take that to heart. Wade.
Post #415,853 by malraux 12/4/16 11:03:23 AM 12/4/16 11:03:23 AM Reply	Someone's going to be writing something that deals with integrity. So there's always a way to write code somewhere that breaks your data. :-) Note that I view DDL as code as well. If you insist on using SPs to manage integrity then you have to restrict access to the underlying tables. Whatever the problems with specification, they shouldn't have been able to do what they did. Two routes into that data were provided and they just happened to pick the one that blew things up. Regards, -scott Welcome to Rivendell, Mr. Anderson.
Post #415,841 by mvitale 12/3/16 6:08:26 PM 12/3/16 6:08:26 PM Reply	Re: Are stored procs "hiding functionality"? Tossing my two cents in... Personally, I prefer to not use stored procedures, because I haven't figured out how to write unit tests for stored procedures. (Note: I'm not asserting that it's impossible to unit test stored procs, but just saying I don't know how to do it.) I'm a Java guy by training, and a Groovy fan these last few years, but truth is, I don't sling that much code anymore. But what I teach the teams I work with in my role as ScrumMaster and/or DevOps Dude is that all code should be unit tested. I also echo what Scott said -- All code should be managed in the same source repository, and all deployments should be handled the same. For deploying updates to the database, I currently use and recommend Flyway. When used along with Maven/Gradle as a build tool, it's just another CLI arg: ./gradlew clean test flywayMigrate. -Mike @MikeVitale42 @AgileFortune "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." - Benjamin Franklin, 1759 Historical Review of Pennsylvania
Post #415,849 by malraux 12/4/16 10:52:24 AM 12/4/16 10:52:24 AM Reply	I used utPLSQL in a former life. https://utplsql.github.io/ Regards, -scott Welcome to Rivendell, Mr. Anderson.

Welcome to IWETHEY!