November 6, 2017 at 2:27 pm
There are two aspects of the OP: 1) Pro and Con of using SPs; 2) Cost of conversion
For me the main advantage of SPs over code embedded in Java or wherever is security. You only need to grant execute on the SP, and grant no access to the underlying tables and views. This can help make GDPR and other compliance easier by reducing the footprint of what has direct access to the underlying data.
However, the cost of conversion should be the biggest issue here. Changing all SP use to embedded code add business risk and cost for just about zero benefit. There must be a massive cost of opportunity loss in doing such a conversion - it is a case of running fast merely to stand still. Is the most important thing to the business to do a code conversion or is it to gain market share?
Any code conversion has a business risk. If a lift and shift is done it is inevitable there will be bugs in the new system that do not exist in the old. If a redesign is done to incorporate new functionality then there are the normal problems of migrating data and operational processes to the new system. It still comes back to what the business priorities are, and I just do not see that converting code for the sake of conversion should be given any form of priority.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 6, 2017 at 3:30 pm
Jeff Moden - Wednesday, November 1, 2017 7:45 AMdmbaker - Wednesday, November 1, 2017 6:41 AMJeff Moden - Tuesday, October 31, 2017 8:09 AMOooohhh.... you said a mouthful on that "emotional" basis thing. we spent $350K USD on something that isn't much more powerful than SSIS because of that silliness and that doesn't include yearly maintenance fees nor the FTE hours to support the damned thing. It also takes a lot longer to do things like import a spreadsheet instead of just loading up the ACE drivers and "doing it simple" from T-SQL. It takes someone a day to do it in the product I speak of and it should only take a half hour or so to import a flat, single tab spreadsheet that has all the qualities of a table.
It's mostly because of an emotional decision by someone that got burned when MS refused to make 64 bit Jet Drivers and hadn't come out with the ACE drivers yet. They simply didn't know the work arounds.
Does the name of this something happen to start with an "I" and end with an "a"? 🙂
No... it's "Web Methods". What product are you talking about? It might make me feel better that we're not the only ones paying from deep pockets because of an emotional decision.
Oh I see y'all bought the CHEAP version of WM lol. The price tag gets much, much bigger quickly.
I'm afraid though - if you bought it as an ETL tool, you should probably smack whoever was perusing the catalog because they weren't even in the correct section. That's like buying a week whacker to trim your nose hairs (technically possible, but a REALLY bad idea).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 6, 2017 at 5:27 pm
Matt Miller (4) - Monday, November 6, 2017 3:30 PMJeff Moden - Wednesday, November 1, 2017 7:45 AMdmbaker - Wednesday, November 1, 2017 6:41 AMJeff Moden - Tuesday, October 31, 2017 8:09 AMOooohhh.... you said a mouthful on that "emotional" basis thing. we spent $350K USD on something that isn't much more powerful than SSIS because of that silliness and that doesn't include yearly maintenance fees nor the FTE hours to support the damned thing. It also takes a lot longer to do things like import a spreadsheet instead of just loading up the ACE drivers and "doing it simple" from T-SQL. It takes someone a day to do it in the product I speak of and it should only take a half hour or so to import a flat, single tab spreadsheet that has all the qualities of a table.
It's mostly because of an emotional decision by someone that got burned when MS refused to make 64 bit Jet Drivers and hadn't come out with the ACE drivers yet. They simply didn't know the work arounds.
Does the name of this something happen to start with an "I" and end with an "a"? 🙂
No... it's "Web Methods". What product are you talking about? It might make me feel better that we're not the only ones paying from deep pockets because of an emotional decision.
Oh I see y'all bought the CHEAP version of WM lol. The price tag gets much, much bigger quickly.
I'm afraid though - if you bought it as an ETL tool, you should probably smack whoever was perusing the catalog because they weren't even in the correct section. That's like buying a week whacker to trim your nose hairs (technically possible, but a REALLY bad idea).
Heh... I didn't buy it for ETL... someone else did. There's no way I'd have bought a separate tool to handle the simple but large files we deal with everyday. If you've ever seen the bloody "Blue Dots" moving through the "pipes" in the sales pitch them you know what they actually fell in love with. Funny thing is, they don't even look at that display and it's a good thing because it would take and hour or two for one silly Blue dot to move. 😉
It's amazing to me the things that people spend money on when they don't know what the tools they already have can do... well... except the idiots that say stupid things like "Well, just because you can do something is SQL, doesn't mean you should". It all gets back to that "old knowledge" that I spoke of.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2017 at 6:52 pm
Jeff Moden - Monday, November 6, 2017 5:27 PMMatt Miller (4) - Monday, November 6, 2017 3:30 PMJeff Moden - Wednesday, November 1, 2017 7:45 AMdmbaker - Wednesday, November 1, 2017 6:41 AMJeff Moden - Tuesday, October 31, 2017 8:09 AMOooohhh.... you said a mouthful on that "emotional" basis thing. we spent $350K USD on something that isn't much more powerful than SSIS because of that silliness and that doesn't include yearly maintenance fees nor the FTE hours to support the damned thing. It also takes a lot longer to do things like import a spreadsheet instead of just loading up the ACE drivers and "doing it simple" from T-SQL. It takes someone a day to do it in the product I speak of and it should only take a half hour or so to import a flat, single tab spreadsheet that has all the qualities of a table.
It's mostly because of an emotional decision by someone that got burned when MS refused to make 64 bit Jet Drivers and hadn't come out with the ACE drivers yet. They simply didn't know the work arounds.
Does the name of this something happen to start with an "I" and end with an "a"? 🙂
No... it's "Web Methods". What product are you talking about? It might make me feel better that we're not the only ones paying from deep pockets because of an emotional decision.
Oh I see y'all bought the CHEAP version of WM lol. The price tag gets much, much bigger quickly.
I'm afraid though - if you bought it as an ETL tool, you should probably smack whoever was perusing the catalog because they weren't even in the correct section. That's like buying a week whacker to trim your nose hairs (technically possible, but a REALLY bad idea).
Heh... I didn't buy it for ETL... someone else did. There's no way I'd have bought a separate tool to handle the simple but large files we deal with everyday. If you've ever seen the bloody "Blue Dots" moving through the "pipes" in the sales pitch them you know what they actually fell in love with. Funny thing is, they don't even look at that display and it's a good thing because it would take and hour or two for one silly Blue dot to move. 😉
It's amazing to me the things that people spend money on when they don't know what the tools they already have can do... well... except the idiots that say stupid things like "Well, just because you can do something is SQL, doesn't mean you should". It all gets back to that "old knowledge" that I spoke of.
Yeah I know. We have the whole alphabet soup here as well. I'd understand buying something like these big tools if you ever were going to bother to train someone (everyone) on how to use it, etc... We actually have the right use cases for said blue dots and red dots, but we've never built that out so it works, mostly because the people who need it know better than to turn on the performance monitoring aspect....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 7, 2017 at 4:46 am
patrickmcginnis59 10839 - Monday, October 30, 2017 10:32 AMYep. Understood. I thought I made it clear that it was speculation on my part but maybe not.
Just to clear things up a bit and although we certainly aren't operating anywhere near the scale the EBAY does, my "speculation" isn't based on hear-say nor even supposed "Best Practice" (which are frequently not) recommendations or arguments. They're based on the personal experience of my own small world.
Well my counter speculations are based on simple reading and experience with MS SQL, and I'm just not going to believe that stored procedures will cut EBay's server cost by 90 percent, even if you're the one writing them all 🙂
Even Stack Exchange, a very famous microsoft stack user seems intent on removing their stored procedures and they too are big on caching, and the folks even admit their workload in some cases can approach over 90 percent read only. And I'm going to hazard a guess that Stack Exchange's workload is going to be a fraction of EBay's.
https://nickcraver.com/blog/2016/02/17/stack-overflow-the-architecture-2016-edition/
Your counter speculation can only be based on a bad experience with incorrectly designed MS SQL databases with wrong or missing indexing strategy, lack of data flow architecture, a lot of useless partitioning and so called "fact tables" containing "cached data for further querying", with a significant chunk of it stored in XML, or even worse, in JSON format..
It's all the traces left by so called "dot Net developers with SQL experience", "full stack developers", etc., which are so popular amongst business ownrers.
I wonder - why they don't hire "full stack car designers"? They must be too stupid there in BMW.
I've just seen such a system.
About 90 percent of data on their servers was a useless junk, I mean - "fact tables".
Those tables are being constatly refreshed from the those 10% of data which make actual transactional and dimensional data.
New portions of transactional data were loaded overnight, and refreshing of fact tables could not finish till COB.
So, the reports are usually based on yesterday's facts.
I have been there for a short time only, and I could complete refactoring of a singlle module of the system, but that refactoring eliminated 2 replications (3 hour per day), made redundant several data transformation processes (6-8 hours per day), made unnecerrary about 2/3 of data amount stored on the server, and made final reports became run twice as fast comparing to those usiing "prepared, optimised" fact tables, which took 6 hours to populate.
Considering the reports took less than an hour to execute (all together) per day, the total execution time was reduced by (1 - 1 hour/(3+6+2)hours)*100% = about 90%.
So, Jeff's estimation is quite conservative.
I consider it's conservative because in my case there were SQL queries, mostly in procedures, executed on the server.
So, network traffic was not added to the equasion (well, apart from replications).
If they'd had data processing done in an external application (requiring full sets of data to be loaded upstairs for each query) the optimisation ratio could easily reach 95%.
But yes, you're right, moving processing from external application to SP's of that kind which I've found there would not make too much difference.
_____________
Code for TallyGenerator
November 7, 2017 at 6:27 am
Sergiy - Tuesday, November 7, 2017 4:46 AMpatrickmcginnis59 10839 - Monday, October 30, 2017 10:32 AMYep. Understood. I thought I made it clear that it was speculation on my part but maybe not.
Just to clear things up a bit and although we certainly aren't operating anywhere near the scale the EBAY does, my "speculation" isn't based on hear-say nor even supposed "Best Practice" (which are frequently not) recommendations or arguments. They're based on the personal experience of my own small world.
Well my counter speculations are based on simple reading and experience with MS SQL, and I'm just not going to believe that stored procedures will cut EBay's server cost by 90 percent, even if you're the one writing them all 🙂
Even Stack Exchange, a very famous microsoft stack user seems intent on removing their stored procedures and they too are big on caching, and the folks even admit their workload in some cases can approach over 90 percent read only. And I'm going to hazard a guess that Stack Exchange's workload is going to be a fraction of EBay's.
https://nickcraver.com/blog/2016/02/17/stack-overflow-the-architecture-2016-edition/
Your counter speculation can only be based on a bad experience with incorrectly designed MS SQL databases with wrong or missing indexing strategy, lack of data flow architecture, a lot of useless partitioning and so called "fact tables" containing "cached data for further querying", with a significant chunk of it stored in XML, or even worse, in JSON format..
It's all the traces left by so called "dot Net developers with SQL experience", "full stack developers", etc., which are so popular amongst business ownrers.
I wonder - why they don't hire "full stack car designers"? They must be too stupid there in BMW.
I've just seen such a system.
About 90 percent of data on their servers was a useless junk, I mean - "fact tables".
Those tables are being constatly refreshed from the those 10% of data which make actual transactional and dimensional data.
New portions of transactional data were loaded overnight, and refreshing of fact tables could not finish till COB.
So, the reports are usually based on yesterday's facts.
I have been there for a short time only, and I could complete refactoring of a singlle module of the system, but that refactoring eliminated 2 replications (3 hour per day), made redundant several data transformation processes (6-8 hours per day), made unnecerrary about 2/3 of data amount stored on the server, and made final reports became run twice as fast comparing to those usiing "prepared, optimised" fact tables, which took 6 hours to populate.
Considering the reports took less than an hour to execute (all together) per day, the total execution time was reduced by (1 - 1 hour/(3+6+2)hours)*100% = about 90%.
So, Jeff's estimation is quite conservative.
I consider it's conservative because in my case there were SQL queries, mostly in procedures, executed on the server.
So, network traffic was not added to the equasion (well, apart from replications).
If they'd had data processing done in an external application (requiring full sets of data to be loaded upstairs for each query) the optimisation ratio could easily reach 95%.
But yes, you're right, moving processing from external application to SP's of that kind which I've found there would not make too much difference.
Honestly, I think we're junking up the thread simply because I didn't take the 90 percent savings of stored procedures to be obvious and I still don't, ESPECIALLY in EBay's case. However, I do apologize to the OP for the off topic post.
November 7, 2017 at 12:09 pm
patrickmcginnis59 10839 - Tuesday, November 7, 2017 6:27 AMSergiy - Tuesday, November 7, 2017 4:46 AMpatrickmcginnis59 10839 - Monday, October 30, 2017 10:32 AMYep. Understood. I thought I made it clear that it was speculation on my part but maybe not.
Just to clear things up a bit and although we certainly aren't operating anywhere near the scale the EBAY does, my "speculation" isn't based on hear-say nor even supposed "Best Practice" (which are frequently not) recommendations or arguments. They're based on the personal experience of my own small world.
Well my counter speculations are based on simple reading and experience with MS SQL, and I'm just not going to believe that stored procedures will cut EBay's server cost by 90 percent, even if you're the one writing them all 🙂
Even Stack Exchange, a very famous microsoft stack user seems intent on removing their stored procedures and they too are big on caching, and the folks even admit their workload in some cases can approach over 90 percent read only. And I'm going to hazard a guess that Stack Exchange's workload is going to be a fraction of EBay's.
https://nickcraver.com/blog/2016/02/17/stack-overflow-the-architecture-2016-edition/
Your counter speculation can only be based on a bad experience with incorrectly designed MS SQL databases with wrong or missing indexing strategy, lack of data flow architecture, a lot of useless partitioning and so called "fact tables" containing "cached data for further querying", with a significant chunk of it stored in XML, or even worse, in JSON format..
It's all the traces left by so called "dot Net developers with SQL experience", "full stack developers", etc., which are so popular amongst business ownrers.
I wonder - why they don't hire "full stack car designers"? They must be too stupid there in BMW.I've just seen such a system.
About 90 percent of data on their servers was a useless junk, I mean - "fact tables".
Those tables are being constatly refreshed from the those 10% of data which make actual transactional and dimensional data.
New portions of transactional data were loaded overnight, and refreshing of fact tables could not finish till COB.
So, the reports are usually based on yesterday's facts.I have been there for a short time only, and I could complete refactoring of a singlle module of the system, but that refactoring eliminated 2 replications (3 hour per day), made redundant several data transformation processes (6-8 hours per day), made unnecerrary about 2/3 of data amount stored on the server, and made final reports became run twice as fast comparing to those usiing "prepared, optimised" fact tables, which took 6 hours to populate.
Considering the reports took less than an hour to execute (all together) per day, the total execution time was reduced by (1 - 1 hour/(3+6+2)hours)*100% = about 90%.
So, Jeff's estimation is quite conservative.
I consider it's conservative because in my case there were SQL queries, mostly in procedures, executed on the server.
So, network traffic was not added to the equasion (well, apart from replications).
If they'd had data processing done in an external application (requiring full sets of data to be loaded upstairs for each query) the optimisation ratio could easily reach 95%.But yes, you're right, moving processing from external application to SP's of that kind which I've found there would not make too much difference.
Honestly, I think we're junking up the thread simply because I didn't take the 90 percent savings of stored procedures to be obvious and I still don't, ESPECIALLY in EBay's case. However, I do apologize to the OP for the off topic post.
Our opinions obviously differ there. No need to apologize to anyone because I don't believe any of the posts have been off topic. I rather thought that, even including our diametrically differing opinions, it was a damned good conversation.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply