April 9, 2018 at 10:32 pm
Why does anyone think that stored procedures are a bottleneck in the development cycle? The Developer writes a proc and it gets promoted at the same time other code does. What's even better is that if the proc needs a change, you don't have to wait for an app release.
If stored procs are causing a bottleneck in your Development process, you might want to take a second look at the process because there's no reason at all for the Development of stored procedures to be a bottleneck.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 10, 2018 at 12:26 am
xsevensinzx - Monday, April 9, 2018 10:23 PMActually, 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.
Sounds like a process problem to me.
Where I work the final responsibility on stored procedures is mine. But I am okay with developers writing a first version and then handing it to me for review. I then later replace the version they have in test with a better performing, better documented, and more error-proof version. They typically don't even notice. My version is the one that gets pushed to production, eventually.
There are always multiple people involved in software development. When one developer needs to call a method developed by a co-worker, it creates the same type of dependency as when a developer needs to call a stored procedure written by the database person. That's why you have scrum meetings. And a project manager. You plan and coordinate activities and you ensure that everyone who works on the project is involved right from the start. So they know what needs to be done and can do it before their co-workers need it.
If someone decides to NOT include the database people in a project, and THEN complains that their work becomes a bottleneck ... yeah, thanks for pointing out the obvious. Replance "database person" with any relevant role in the previous sentence and it remains true.
April 10, 2018 at 6:13 am
Where I work the final responsibility on stored procedures is mine. But I am okay with developers writing a first version and then handing it to me for review. I then later replace the version they have in test with a better performing, better documented, and more error-proof version. They typically don't even notice. My version is the one that gets pushed to production, eventually.
OK folks I think we figured things out, let me know if any more questions!!!@@@
April 10, 2018 at 6:25 am
Jeff Moden - Monday, April 9, 2018 10:32 PMWhy does anyone think that stored procedures are a bottleneck in the development cycle? The Developer writes a proc and it gets promoted at the same time other code does. What's even better is that if the proc needs a change, you don't have to wait for an app release.If stored procs are causing a bottleneck in your Development process, you might want to take a second look at the process because there's no reason at all for the Development of stored procedures to be a bottleneck.
I guess it depends on your organization. In my experience, it has surely been a problem because you often don't have a 1:1 match of SQL Developers to programmers. You end up having a SQL dev supporting more than one programmer as well doing other SQL related things that have to do with the overall system, not just the app if you're not lucky to have dedicated resources.
For example, it's not uncommon to have to submit a ticket, wait for the review, then approval before work can even begin on a stored proc. That time alone is much longer than just letting the ORM do the work as soon as the code compiles/executes. Seconds versus minutes or hours or even days compared to what a SQL developer can do. The issue of course is that approach with ORM is not most optimal path, there is less control from the operational side of the SQL team, which can lead to poor performance that may outweigh the time savings of not using that store proc path.
Right now, I can write a Python application that will essentially take all of my classes and model/create all of my physical tables in the database. Then it will take all of my methods that utilize the data and generate the SQL for me from a single command line without the need of a SQL dev. That's not accounting for the fact it will also set all your PK/FK constraints on top of all of your indexing. This is extremely powerful and extremely addictive for the programmers because they do not have to rely on another body to do the work regardless of the process in your organization.
If your only retort to that is you can do it better than the ORM, then it's only a matter of time before that ORM becomes better at making better choices. This goes back to that other debate you were having about automating the DBA role you were having in regards to the automation making dumb decisions.
April 10, 2018 at 6:27 am
xsevensinzx - Monday, April 9, 2018 10:23 PMActually, 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.
Yep, going to have to pile on to this too. Hugo and Jeff make excellent cases, and this post just smacks of limiting one's thought process solely to the concerns of the developers, and excluding the concerns of pretty much everyone else. There is always enough time in any project to do things two or three times until it's either "done right" or "good enough". And there always seems to be time to go back and fix it later. Yet the concern for those costs is for some reason "accepted" by the company because the developers and their managers say so. I call BS on that entire load. The real problem is usually that you just don't hire quality developers, or that you think an H1B visa candidate, at a dime a dozen, is somehow less expensive than hiring quality and getting better results right out of the gate. Developers are NOT a dime a dozen, and MOST H1Bs are not worth the time or the trouble. Database developers are what is needed ALL the time. So maybe if the managers who continue to cost the company far more in delays and re-writes and "fixes" would start losing their jobs over it, something would change.... maybe... Human nature is a vicious enemy...
End of rant...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 10, 2018 at 7:05 am
xsevensinzx - Tuesday, April 10, 2018 6:25 AMJeff Moden - Monday, April 9, 2018 10:32 PMWhy does anyone think that stored procedures are a bottleneck in the development cycle? The Developer writes a proc and it gets promoted at the same time other code does. What's even better is that if the proc needs a change, you don't have to wait for an app release.If stored procs are causing a bottleneck in your Development process, you might want to take a second look at the process because there's no reason at all for the Development of stored procedures to be a bottleneck.
I guess it depends on your organization. In my experience, it has surely been a problem because you often don't have a 1:1 match of SQL Developers to programmers. You end up having a SQL dev supporting more than one programmer as well doing other SQL related things that have to do with the overall system, not just the app if you're not lucky to have dedicated resources.
For example, it's not uncommon to have to submit a ticket, wait for the review, then approval before work can even begin on a stored proc. That time alone is much longer than just letting the ORM do the work as soon as the code compiles/executes. Seconds versus minutes or hours or even days compared to what a SQL developer can do. The issue of course is that approach with ORM is not most optimal path, there is less control from the operational side of the SQL team, which can lead to poor performance that may outweigh the time savings of not using that store proc path.
Right now, I can write a Python application that will essentially take all of my classes and model/create all of my physical tables in the database. Then it will take all of my methods that utilize the data and generate the SQL for me from a single command line without the need of a SQL dev. That's not accounting for the fact it will also set all your PK/FK constraints on top of all of your indexing. This is extremely powerful and extremely addictive for the programmers because they do not have to rely on another body to do the work regardless of the process in your organization.
If your only retort to that is you can do it better than the ORM, then it's only a matter of time before that ORM becomes better at making better choices. This goes back to that other debate you were having about automating the DBA role you were having in regards to the automation making dumb decisions.
The way we beat "the system" is that I sit with the WebDevelopers on our team and I've taught them how to write T-SQL through peer reviews that are actually mentoring sessions. Most of them write better (in all ways) SQL than the folks that write database code in a dedicated fashion (they are not SQL developers).
I think that's the key to success. Instead of fighting with people, teach them and give the a strong sense of self-worth and pride. Most of the Frontend Developers can actually spot performance problems in older code written by the previous "team" (I use that term loosely because they were a bunch of terribly misguided and uncontrolled ego maniacs that couldn't even write decent front end code never mind database code) and they brag about how they converted some of the front end code to stored procedures and the incredible performance gains they were able to achieve. And it's a long term solution... we've been doing this for 6 years and the word DevOps wasn't a popular term back then.
The really cool part is that we've been able to eliminate the proverbial revolving door of Developers by doing all of this.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 10, 2018 at 7:21 am
That works too. Not to distort the issue as being tied specifically to stored procs. That's actually semantics. But, it's often communicated as stored procs being the issue because everyone knows that means someone else has to write it before they can push forward. If you can get your actual developers writing it instead, then that's exactly the same methodology that I am saying with ORM. Instead of someone else writing that stored proc, you teach your team to write it for themselves.
I'm not going to touch that silly H1B post. I will just simply say, I disagree on that mindset, especially factoring in someone's work status or even nationality as a major factor to solving a problem.
April 10, 2018 at 7:53 am
edited to remove too much distractions from the thread.
I get the trepidation of cloud computing but thats looking pretty inevitable with today's tech momentum. It would make for an interesting thread in itself!
April 10, 2018 at 9:16 am
I'd agree with Jeff above that the key is working with others and teaching them to code better. I have no issue with an ORM, or even using that instead of stored procedures to release faster, but that does place the burden on developers to write better code.
April 10, 2018 at 9:54 am
patrickmcginnis59 10839 - Tuesday, April 10, 2018 7:53 AMWe 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.
Yeah but honestly sp_getapplock works exactly as advertised and its a stored procedure, and if you could eliminate 400TB of logical reads with any changes in code then stored procedures were pretty much not your problem to begin with right? You are not moving 400TB of sql text across your network every 8 hours. What data you DO move across the network wire shouldn't be dependent on the original question of stored procedures in my opinion (except I'm thinking there could be more actual SQL text going from client to server but I don't think that's going to be in the terabyte range.)
Honestly, with sp_getapplock, you can cause the same issues stored procedures or not.
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. 😉
Is this the old team that you thought were less than ideal, the new team you work closely with and mentor with proper SQL or is this a third team we haven't heard of yet?
I get the trepidation of cloud computing but thats looking pretty inevitable with today's tech momentum. It would make for an interesting thread in itself!
It's the "good" team of Developers that I've been working with for 6 years. They made a serious mistake in judgment because they were put under pressure that I couldn't protect them from (they don't work for me). They're going through the same thing with this move away from stored procedures thanks to outside influences. The group is actually a great group of folks but they're also afraid to say "No" and I couldn't protect them this time because of the level of the people giving this stupid and uninformed direction. And, yes, I continue to try to help them and keep their butts out of a sling. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
April 10, 2018 at 10:22 am
edit: These are good conversations but I tend to discuss things to the bitter end to probably nobody's benefit so I'd rather remove my posts at this point!
April 10, 2018 at 9:47 pm
patrickmcginnis59 10839 - Tuesday, April 10, 2018 10:22 AMJeff Moden - Tuesday, April 10, 2018 9:54 AMpatrickmcginnis59 10839 - Tuesday, April 10, 2018 7:53 AMWe 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.
Yeah but honestly sp_getapplock works exactly as advertised and its a stored procedure, and if you could eliminate 400TB of logical reads with any changes in code then stored procedures were pretty much not your problem to begin with right? You are not moving 400TB of sql text across your network every 8 hours. What data you DO move across the network wire shouldn't be dependent on the original question of stored procedures in my opinion (except I'm thinking there could be more actual SQL text going from client to server but I don't think that's going to be in the terabyte range.)
Honestly, with sp_getapplock, you can cause the same issues stored procedures or not.
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. 😉
Is this the old team that you thought were less than ideal, the new team you work closely with and mentor with proper SQL or is this a third team we haven't heard of yet?
I get the trepidation of cloud computing but thats looking pretty inevitable with today's tech momentum. It would make for an interesting thread in itself!
It's the "good" team of Developers that I've been working with for 6 years. They made a serious mistake in judgment because they were put under pressure that I couldn't protect them from (they don't work for me). They're going through the same thing with this move away from stored procedures thanks to outside influences. The group is actually a great group of folks but they're also afraid to say "No" and I couldn't protect them this time because of the level of the people giving this stupid and uninformed direction. And, yes, I continue to try to help them and keep their butts out of a sling. 😀
Did the team of developers use sp_getapplock and tailor their design to move that 400TB extra around the net? And if they did, can you provide the causal link that removing stored procedures forced this? Its germane to the discussion after all.
Also if they did design with sp_getapplock and decide to move the extra 400TB around, was this a decision that came down from above or did the team decide themselves to do this?
Also your 400TB figure amounts to 13,888,888,889 bytes a second. Can you describe the hardware transporting that workload and why your developers went that route?
No. The 400TB of logical reads per day were eliminated by a combination of converting the GUI code and ORM code to stored procedures and using some common sense. One of the biggest problems was in the form of columnar implicit casts because the ORM was generating some really stupid stuff like passing a parameter as an NVARCHAR for the letter "Y" against tables that contained no NVARCHAR columns. There was also some really weird stuff the ORM did like passing the letter "A" as an integer value of 65 and then wrapping that in the NCHAR() function, again, against a VARCHAR column. All of that caused scans against clustered and non-clustered indexes instead of a single row seek, like it was supposed to. One that was even weirder was it would pass the letter "A" as an integer value of 65 and then convert the column it was comparing to to an integer value 5 using the ASCII function.
There was a ton of ORM generated code that contained nested selects for many columns across each of 2 or 3 tables with some insane criteria that could be boiled down to just a couple or several columns using a simple inner join or 2. Of course, these also had the implicit cast problems and a lot of the parameters were passed as NVARCHAR(4000).
There was also some GUI code that the previous regime had written that would check privs... for every bloody field on the screen and whether or not the field was actually used or someone was just doing a tab through to the next field. The code was also calling a really poorly written stored procedure where DISTINCT was used to remove duplicates caused by many-to-many joins in the code and was also riddled with implicit casts to NVARCHAR on VARCHAR columns. Obviously, that one wasn't just a front end problem but the front end did need some serious help there especially since that also caused a lot of totally unnecessary network traffic.
And, yep, you're spot on about the logical reads being almost 14GB per second... until a batch run would kick in (equally poorly written) and the floor would suffer an "outage" of 10 minutes 4 or 5 times a day. Very fortunately, most of the those logical reads were (except for that privs thing) within the system and not floating around on the network as you suggested.
There were also SQL Injection opportunities all over the place. A lot of the SQL was assembled in front end code using concatenation instead of parameterized queries. A lot of that came in the form of "catch all" queries and wasn't just subject to SQL Injection but was riddled with performance problems because, despite the dynamic code they wrote, they still elected to use a lot of ORs and seriously non-SARGable code. All of that was rewritten as stored procedures and Gail Shaw's method for writing secure "catch all" queries was used extensively.
And, no, the previous regime of developers didn't "decide to go that route". They just didn't know any better. And, no. Most of the 400TB wasn't being transported over the network. It was logical reads (all memory, which also affected CPU a whole lot due to the volume) within the box due to some really crappy code both on the part of the ORM and on the part of that particular group of developers.
Could a group of good front end Developers have done it all without resorting to conversions to stored procedures? I think that's more than just a possibility, especially since there are ways to tell the ORM to use the correct datatypes in a self-sensing fashion (and the current Developers have done such things in certain areas). The problem with that is we have a lot of changes to the "rules" that we have to obey thanks to changes in the mortgage and insurance industries. Stored procedures are usually a whole lot easier to change when we're in a hurry.
Totally agreed on the sp_getapplock thing. It works precisely as designed. I just don't believe they needed to use it to get the job done and it's contrary to them standing up parallel instances that all have to run through that particular knothole.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 10, 2018 at 10:19 pm
Regarding ORM's.
ORM's could be discussed forever. and there are some great conversations out there.
My go to google for interesting reading goes something like "sql orm vietnam", but for me it boils down to the fact (for me) that you can describe more constructs with objects than you can (efficiently describe) with relations. My favorite example for instance, that while you have a hierarchy or graph represented in tables with traditional SQL, they can be represented NATIVELY with objects, ie., references are truly references rather than keys that must be traversed through a balanced tree (ie., SQL index, hope that makes sense).
Obviously Microsoft again notices things like this, don't they have a new graph gadget in the new SQL version(s)?
If that is where Luis is going than its going to benefit from a much bigger conversation than whether they use stored procedures or not. (And that of course assumes the DBA gets to participate in the conversation at all).
I am certainly seeing some mitigations for the great ORM problem when I read stuff I find using the search term I mentioned but the discussion has to happen from both perspectives, objects and databases.
April 10, 2018 at 10:28 pm
xsevensinzx - Tuesday, April 10, 2018 7:21 AMThat works too. Not to distort the issue as being tied specifically to stored procs. That's actually semantics. But, it's often communicated as stored procs being the issue because everyone knows that means someone else has to write it before they can push forward. If you can get your actual developers writing it instead, then that's exactly the same methodology that I am saying with ORM. Instead of someone else writing that stored proc, you teach your team to write it for themselves.I'm not going to touch that silly H1B post. I will just simply say, I disagree on that mindset, especially factoring in someone's work status or even nationality as a major factor to solving a problem.
+1000 Totally agreed on that. We have quite the mix of nationalities and they're an awesome as a group and individually.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 10, 2018 at 10:41 pm
p.s. Just to be clear, the current team didn't cause the 400TB logical read problem. They were a major factor in fixing that problem (problems, actually).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 191 total)
You must be logged in to reply to this topic. Login to reply