April 3, 2018 at 12:59 pm
I got an interesting email today about cutting back on the reliance of the applications on databases and plans to remove all stored procedures from the code.
In my opinion, this is nonsense but I don't feel that it's a good idea to say it like that to my manager. Could you help me establish some valid reasons set my point? I'm thinking on better handle of security, the possibility of less call between app servers and db servers, possible performance improvement from cached plans.
Or maybe I'm wrong and you could give me some reasons to change my mind.
April 3, 2018 at 1:49 pm
I can't imagine any DBA allowing an app to stop relying on stored procedures. If the stored procedures aren't performing well, that can be addressed, as it's not the nature of stored procedures themselves, but human beings that write poor T-SQL coding, that is the problem. Typically, by forcing the use of stored procedures, it's easier to review code, and easier to force the development to go in the right direction, because everything database happens in a stored procedure. Can't really imagine walking away from all of that benefit. Of course, if a given company is just not making use of that benefit, then I can at least see how they come to such conclusions... not a good thing, mind you...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 3, 2018 at 1:50 pm
I actually 100% disagree with that idea.
If you use a stored procedure, you can join two tables, filter on a predicate, do some aggregation and then only send the results to the client. Network traffic: One procedure call, one result set. (Plus the benefit that the optimizer compiles a pretty smart plan, which is then stored in the procedure cache and reused; but you know all that already).
Without a stored procedure, how are they planning to do that? Open some rowsets from the client? In that case there's going to be hundreds of server roundtrips, for getting row after row after row. Including rows that are not needed once the join is done and the filter can be applied. But all that logic is now in the client so the data has to be pushed through the network. This is painful in a normal network; enter the cloud and it gets worse. Cloud has latency, you know.
Or are they planning to build a query in the client application and send that as a single query? In that case they still need to have the logic of the query stored somewhere. Whether it is embedded in the client program or in a stored procedure does not change anything related to managing and maintaining the code. (Having it in a stored procedure does make it easier to handle permissions, prevent plan cache bloat, and ensure that there are no SQL Injection vulnerabilities).
For me, moving to the cloud would be MORE reason to push for stored procedures.
April 3, 2018 at 3:11 pm
Thank you, guys. I just hope my manager won't hate me (more) for going against the "cloud architects". At least, I'm sure that I'll get support from the other DBAs in here.
April 4, 2018 at 6:21 am
Luis Cazares - Tuesday, April 3, 2018 3:11 PMThank you, guys. I just hope my manager won't hate me (more) for going against the "cloud architects". At least, I'm sure that I'll get support from the other DBAs in here.
Maybe the question to ask these "alleged" architects, is what, exactly, is so beneficial to not using stored procedures? I'd want actual proven numbers that can make their case. Just 'cause they "say so", isn't good enough. You can at least point to an entire forum full of considerable experts who can back up their claims with facts and experience (not that you can't do so, but having a forum full of like-minded folks sure don't hurt!)..
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 4, 2018 at 8:42 am
Hugo Kornelis - Tuesday, April 3, 2018 1:50 PMI actually 100% disagree with that idea.If you use a stored procedure, you can join two tables, filter on a predicate, do some aggregation and then only send the results to the client. Network traffic: One procedure call, one result set. (Plus the benefit that the optimizer compiles a pretty smart plan, which is then stored in the procedure cache and reused; but you know all that already).
Without a stored procedure, how are they planning to do that? Open some rowsets from the client? In that case there's going to be hundreds of server roundtrips, for getting row after row after row. Including rows that are not needed once the join is done and the filter can be applied. But all that logic is now in the client so the data has to be pushed through the network. This is painful in a normal network; enter the cloud and it gets worse. Cloud has latency, you know.
Or are they planning to build a query in the client application and send that as a single query? In that case they still need to have the logic of the query stored somewhere. Whether it is embedded in the client program or in a stored procedure does not change anything related to managing and maintaining the code. (Having it in a stored procedure does make it easier to handle permissions, prevent plan cache bloat, and ensure that there are no SQL Injection vulnerabilities).
For me, moving to the cloud would be MORE reason to push for stored procedures.
Depending on the application and programming, you might still be able to prevent injection and plan cache bloat.
Just throwing that out there for a couple of reasons, firstly, if you complain to the app folks about plan cache bloat and parameterization without understanding that they might already know about it, you've wasted a meeting, and secondly, if they actually proceed with their plan you can work with them to prevent these problems if there are opportunities to do so.
Its worth spending time understanding where the "cloud guys" are coming from. We really haven't heard why they're making the move and I'm pretty surprised that they're not keeping you on the loop in regards to the advantages that they might be seeing. Remember, its often not about hard numbers but more subjective issues like maintainability and programmer velocity, these things are getting more attention nowadays.
April 4, 2018 at 9:27 am
Here's what I'd say.
A stored procedure is an encapsulation, with all of the reasons that Hugo has given. Developers like encapsulation, and really, it's been a fundamental part of procedural, OOP, and I believe, Functional, programming. We want an interface to make a call, which is what a proc is.
Whether you want a cloud database, a CosmosDB type call, or a serverless function that gets your data from a lake, you want encapsulation. You could replace the procedure call with a function call of the same name to a different endpoint, processing the call in a completely different way, but keep the code changes in your application to a minimum. The GetProductsForCustomer stored procedure in SQL Server could still be a GetProductsForCustomer serverless Python function that reads from a JSON store in MongoDB. Sure, some code changes for the mechanics of the connection, and perhaps even some of the result processing, but the knowledge of this call remains the same.
Avoiding procedures, on top of all the performance and security advantages, just seems silly from the point of view of knowledge and encapsulation.
April 4, 2018 at 12:35 pm
Here's one that'll appeal to any manager... MONEY!
Most cloud based providers charge based on a combination the following: 1. number of CPU ticks 2. the amount of memory used and I/O used... Well written stored procedures that can take advantage of the query plan cache and data cache and return only the data that's required will be demonstrably less expensive than an ORM (for example) that grabs everything, including the kitchen sink, hauls it across the copper and abandons 99% of it before returning results to the end user.
If you're able, set up two instances of Azure. One with stored procs and one without. Hit them both with comparable work loads and compare the DTUs being used. With any luck you'll be able to demonstrate that not using stored procs adds another 0 to the end of the monthly bill.
April 5, 2018 at 11:23 am
Forgive me if this has been mentioned before and I missed it but along with all the other great points made here, fixing / modifying data related functionality is much faster with stored procedures. Otherwise, they need to push a new release of the application in order to fix a data issue that can be handled very quickly with a simple fix to a stored procedure.
April 6, 2018 at 10:49 am
Luis Cazares - Tuesday, April 3, 2018 12:59 PMI got an interesting email today about cutting back on the reliance of the applications on databases and plans to remove all stored procedures from the code.
I'd love to know more about the architectural objectives they're trying to achieve. The phrase about reliance on databases suggests that they could be looking to implement a microservices architecture and they see tight coupling to a monolithic database as an anti-pattern. If so, maybe they're looking to break a shared database into multiple bounded stores? In that case I would argue that SPs aren't a cause of tight coupling or low cohesion; they are, as Steve suggested, a valuable means of encapsulation and can actually aid in any data migration since they can shield the application from breaking changes in the data store - they basically serve as an interface to the data store; and whatever data store you use, you're going to want a well defined interface to it.
April 6, 2018 at 11:11 am
Steve Thompson-454462 - Friday, April 6, 2018 10:49 AMLuis Cazares - Tuesday, April 3, 2018 12:59 PMI got an interesting email today about cutting back on the reliance of the applications on databases and plans to remove all stored procedures from the code.I'd love to know more about the architectural objectives they're trying to achieve. The phrase about reliance on databases suggests that they could be looking to implement a microservices architecture and they see tight coupling to a monolithic database as an anti-pattern. If so, maybe they're looking to break a shared database into multiple bounded stores? In that case I would argue that SPs aren't a cause of tight coupling or low cohesion; they are, as Steve suggested, a valuable means of encapsulation and can actually aid in any data migration since they can shield the application from breaking changes in the data store - they basically serve as an interface to the data store; and whatever data store you use, you're going to want a well defined interface to it.
I'd love to know more as well. It's been 2 days since I wrote an email asking for an explanation but I haven't got an answer. I'll insist next week.
April 7, 2018 at 12:39 pm
Luis Cazares - Friday, April 6, 2018 11:11 AMSteve Thompson-454462 - Friday, April 6, 2018 10:49 AMLuis Cazares - Tuesday, April 3, 2018 12:59 PMI got an interesting email today about cutting back on the reliance of the applications on databases and plans to remove all stored procedures from the code.I'd love to know more about the architectural objectives they're trying to achieve. The phrase about reliance on databases suggests that they could be looking to implement a microservices architecture and they see tight coupling to a monolithic database as an anti-pattern. If so, maybe they're looking to break a shared database into multiple bounded stores? In that case I would argue that SPs aren't a cause of tight coupling or low cohesion; they are, as Steve suggested, a valuable means of encapsulation and can actually aid in any data migration since they can shield the application from breaking changes in the data store - they basically serve as an interface to the data store; and whatever data store you use, you're going to want a well defined interface to it.
I'd love to know more as well. It's been 2 days since I wrote an email asking for an explanation but I haven't got an answer. I'll insist next week.
I've gotta agree with Jason here. MONEY/ROI is the key. However, you're a voice of one standing in front of a firing squad of people in the "Ready, Fire, Aim" mode. The ONLY way that you'll be able to convince them of anything is through the code comparison that he mentioned. Unfortunately, the firing squad (including the managers) are probably all convinced that what they're heard (or have seen in a poorly rendered experiment) negates the need for aiming before firing. Convince them that they need to do the insitu comparison and that YOU need to make sure the stored procedures are actually written correctly for the test.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2018 at 1:21 pm
We really don't know why they are removing stored procedures tho. I'm guessing that the project(s) could be becoming a burden on the programmers and they could be reaching for better mechanics of abstraction. SQL tends to have a very flat namespace and attempts at modularity impose arbitrary burdens, witness scalar functions and how they kill the query optimizer for instance.
Heck I know of one giant ERP system that has a count of stored procedures in its OLTP system that you can count on two hands and a foot. There might be a darn good reason going on here for this choice, maybe performance concerns are dwarfed by programming challenges and there's no doubt T-SQL doesn't have much going on in the abstraction toolbox right? Lets just say complex things get complex. Programming productivity is a pretty hard thing to measure and no database performance test with objective numbers is going to touch that concern.
Just throwing my 2 cents out there!
April 9, 2018 at 9:48 pm
patrickmcginnis59 10839 - Monday, April 9, 2018 1:21 PMJeff Moden - Saturday, April 7, 2018 12:39 PMI've gotta agree with Jason here. MONEY/ROI is the key. However, you're a voice of one standing in front of a firing squad of people in the "Ready, Fire, Aim" mode. The ONLY way that you'll be able to convince them of anything is through the code comparison that he mentioned. Unfortunately, the firing squad (including the managers) are probably all convinced that what they're heard (or have seen in a poorly rendered experiment) negates the need for aiming before firing. Convince them that they need to do the insitu comparison and that YOU need to make sure the stored procedures are actually written correctly for the test.
We really don't know why they are removing stored procedures tho. I'm guessing that the project(s) could be becoming a burden on the programmers and they could be reaching for better mechanics of abstraction. SQL tends to have a very flat namespace and attempts at modularity impose arbitrary burdens, witness scalar functions and how they kill the query optimizer for instance.
Heck I know of one giant ERP system that has a count of stored procedures in its OLTP system that you can count on two hands and a foot. There might be a darn good reason going on here for this choice, maybe performance concerns are dwarfed by programming challenges and there's no doubt T-SQL doesn't have much going on in the abstraction toolbox right? Lets just say complex things get complex. Programming productivity is a pretty hard thing to measure and no database performance test with objective numbers is going to touch that concern.
Just throwing my 2 cents out there!
I've seen these types of things before. I've not seen such a move based on good reason, yet. This could be one but I doubt it. I think it's based on Developers simply not wanting to work in T-SQL or they have Google technology on the brain.
We just did something smaller and the effects are devastating. The Developers and a whole herd of bobble heads all agreed that something couldn't and can't be done in SQL server and so they used sp_getapplock to serialize a run and did everything else in C# and stood up 6 instances of all that because it wasn't processing fast enough for them (which I told them would happen). Now, the 6 instances of the app are blocking each other anytime there's a popcorn fart in the system and the idiots (heh... that came out loud, didn't it?) still don't get it even after tonight's "upgrade deployment" of their code, which immediately started the 6 instances blocking each other even worse. They seem to have forgotten that every time we had such problems in the past, we converted the process to T-SQL and we've never had problems in those areas ever again. Over the last several years, we've eliminated more that 400TB of logical reads every 8 hours and we've dropped CPU from 60% to 8% and we have about 10 times the previous load on the server, the database has grown twice as large, and we have more than three times the number of connections.
These are the same Developers that somehow convinced folks that the next project should be on the cloud. Oddly enough, they've not invited me to any of the meetings even though I'd have been able to help them do it right. I don't object to something being on the cloud. They just don't want a DBA involved because he might have something to say that they don't want to do. No problem. I won't set them up for failure but I will give them the opportunity to fail (and I truly hope they don't fail so read that as the opportunity to succeed.). Besides, I'm too busy to get involved in reindeer games. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2018 at 10:23 pm
Jeff Moden - Monday, April 9, 2018 9:48 PMI've seen these types of things before. I've not seen such a move based on good reason, yet. This could be one but I doubt it. I think it's based on Developers simply not wanting to work in T-SQL or they have Google technology on the brain.We just did something smaller and the effects are devastating. The Developers and a whole herd of bobble heads all agreed that something couldn't and can't be done in SQL server and so they used sp_getapplock to serialize a run and did everything else in C# and stood up 6 instances of all that because it wasn't processing fast enough for them (which I told them would happen). Now, the 6 instances of the app are blocking each other anytime there's a popcorn fart in the system and the idiots (heh... that came out loud, didn't it?) still don't get it even after tonight's "upgrade deployment" of their code, which immediately started the 6 instances blocking each other even worse. They seem to have forgotten that every time we had such problems in the past, we converted the process to T-SQL and we've never had problems in those areas ever again. Over the last several years, we've eliminated more that 400TB of logical reads every 8 hours and we've dropped CPU from 60% to 8% and we have about 10 times the previous load on the server, the database has grown twice as large, and we have more than three times the number of connections.
These are the same Developers that somehow convinced folks that the next project should be on the cloud. Oddly enough, they've not invited me to any of the meetings even though I'd have been able to help them do it right. I don't object to something being on the cloud. They just don't want a DBA involved because he might have something to say that they don't want to do. No problem. I won't set them up for failure but I will give them the opportunity to fail (and I truly hope they don't fail so read that as the opportunity to succeed.). Besides, I'm too busy to get involved in reindeer games. 😉
Actually, to argue the other side here since it's clear there is only data guys commenting here for the most part, stored procedures are a bottleneck to the development cycle. It has little to do with the "cloud" or to do with not wanting to mess with T-SQL. It has everything to do with the fact as a programmer, you have to rely on someone else to get things rolled out. Someone else like a SQL Developer or someone wearing that hat who is making the stored procedure. That's not to account for the other aspects of the process such as data modeling, space, and so forth that might go into finding a home for the data in the system. You may end up needing more than 2 people to do a simple task, which all become bottlenecks to the programmer.
From a MONEY standpoint, you're talking physical bodies as well time and money with waiting. You end up eating a lot of costs just to have a simple stored procedure developed so the programmer can push forward. You add in the high costs of enterprise licensing and all of that jive, it adds up quickly. Much like it could from not using stored procedures, having optimal performance with caching and engine optimization/plans to boot.
As both someone who does a lot of development and data architecture. I do side with the concern many programmers have with constantly having to push everything to stored procedures. It's been a major thorn in the side of development on larger projects for the sheer fact that programmers cannot be lean with their development. Many believe the process to be archaic in the sense it's outdated compared to what many other organizations are able to do without the constant dependency on another team member to do their job before you can do yours.
From a business standpoint. If you can combine a total of 2 to 5 bodies into one, the business is saving a lot. But often times, this is at a cost too. Like poor data modeling, poor queries, poor performance and so forth. BUT, that's not always the case and certainly is not the only justification for exploring ORM or not constantly forcing stored procedures for every call and so forth.
In a perfect world, you would want every action on the system to take the most optimal path with the least resistance. That may mean all stored procedures that are optimized for performance on top of being able to manage every single call and transaction that happens from any source, user or application. Then of course be able to do all of this while being lean with any process, development or not. The issue of course, is it's not always that perfect. The more control and limitations you set on one system often causes negative reproductions on the other side, like as I mentioned above, not being as lean as you would like with development.
Viewing 15 posts - 1 through 15 (of 191 total)
You must be logged in to reply to this topic. Login to reply