Removing stored procedures to move to cloud

  • Most have been said, but SQL Server is a tool like many others. Not using it the way it is intended to, what are they expecting then?
    Almost any tools will behave clunky (if it works at all) if they are not used for what they are intended for and the way they are intended to.
    It's bit a RTFM thing but on a larger scale.

    If they feel SQL it's the issue (so like Oracle, MySQL, and a lot of other data managing tool can be named here), why didn't they used flat files at first? Lightning fast coding! Lightning fast TTM (time to market) but lack of every other feature provided by the tool (and we like it or not, SQL engine is a solid tool compared to whole load of others software)
    They will have to write everything from scratch to match what the tool offer them out of the box (backup while querying data, concurrent access, data checksum, high availability, data concurrency, you know sql / rdbms features) and this will take (sink) a whole lot of time and debugging efforts. In short partly rewriting the tool which is not the core business of the company.

    All those efforts compared to having a few people managing the tool (here SQL)?
    There's a lot more to this issue but to my eyes, if you're going to used something (what ever the technology) and you don't take the time to understand how to used it, only the one who decided to avoid / refuse learning is to blame.

    And this apply to everything not only software.

  • Sean Lange - Friday, April 13, 2018 12:54 PM

    My point was to avoid generalizations and that stating that all full stack developers lack this ability as a fact is just wrong. I certainly hope you aren't suggesting I was butt-hurt. My skin is as thick as most people's heads. 🙂 I was simply calling BS on declared fact that was not correct. Calling fake news and fake news.

    Every generalization sucks. Including this one.

    Your call to avoid generalizations is as valid as my call not letting full stack developers develop databases. 
    I never said "all" when stating that full stack developers can't do T-SQL.
    So, funny enough, it's you who ended up making up fake news.

    My call is as correct as saying that men are taller and physically stronger than women.
    Of course, there are exemptions. I personally know several families with opposite correlation of physical characteristics of male and female partners.
    But it does not mean you have a right to call the general statement above a BS.

    P.S. It's especially strange to see such a sharp reaction on a general statement from somebody who's familiar with rules of English grammar. 🙂

    _____________
    Code for TallyGenerator

  • Sergiy - Monday, April 16, 2018 5:29 AM

    Sean Lange - Friday, April 13, 2018 12:54 PM

    My point was to avoid generalizations and that stating that all full stack developers lack this ability as a fact is just wrong. I certainly hope you aren't suggesting I was butt-hurt. My skin is as thick as most people's heads. 🙂 I was simply calling BS on declared fact that was not correct. Calling fake news and fake news.

    Every generalization sucks. Including this one.

    Your call to avoid generalizations is as valid as my call not letting full stack developers develop databases. 
    I never said "all" when stating that full stack developers can't do T-SQL.
    So, funny enough, it's you who ended up making up fake news.

    My call is as correct as saying that men are taller and physically stronger than women.
    Of course, there are exemptions. I personally know several families with opposite correlation of physical characteristics of male and female partners.
    But it does not mean you have a right to call the general statement above a BS.

    P.S. It's especially strange to see such a sharp reaction on a general statement from somebody who's familiar with rules of English grammar. 🙂

    Yeah, but...   your words implied that meaning...   And while I'll be among the first to go along with the idea that when you ASSUME something, you're making an ass out of you AND me, there are perhaps some finer points of English grammar and common usage that make it reasonably clear to most English speakers that your statement made no attempt to specify most vs specify all.   Thus the "reasonable" assumption that there was no compelling alternative meaning.   Sorry, dude...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Sergiy - Monday, April 16, 2018 5:29 AM

    Sean Lange - Friday, April 13, 2018 12:54 PM

    My point was to avoid generalizations and that stating that all full stack developers lack this ability as a fact is just wrong. I certainly hope you aren't suggesting I was butt-hurt. My skin is as thick as most people's heads. 🙂 I was simply calling BS on declared fact that was not correct. Calling fake news and fake news.

    Every generalization sucks. Including this one.

    Your call to avoid generalizations is as valid as my call not letting full stack developers develop databases. 
    I never said "all" when stating that full stack developers can't do T-SQL.
    So, funny enough, it's you who ended up making up fake news.

    My call is as correct as saying that men are taller and physically stronger than women.
    Of course, there are exemptions. I personally know several families with opposite correlation of physical characteristics of male and female partners.
    But it does not mean you have a right to call the general statement above a BS.

    P.S. It's especially strange to see such a sharp reaction on a general statement from somebody who's familiar with rules of English grammar. 🙂

    Actually here is what you said.

    There is a fact which is denied by absolute majority of IT community:So called "full stack developers" cannot do T-SQL code.

    Then followed with a bunch of supporting statements. Yes perhaps I had a sharp reaction, more so than usual certainly, but the statement of fact is incorrect. I can't tell you how many times I have had my chops busted around here for not being very precise in my word choice.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hugo Kornelis - Tuesday, April 3, 2018 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.

    Ever heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.

  • funbi - Tuesday, April 17, 2018 6:00 AM

    Hugo Kornelis - Tuesday, April 3, 2018 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.

    Ever heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.

    I beg to disagree....   The problems created by a lack of stored procedures CAN NOT be solved in the application layer.   Interaction with a relational database is ALWAYS going to have problems without the benefit of stored procedures.   Where the business logic is handled is largely irrelevant in that regard.   Problems like bloated plan cache and vulnerability to SQL injection don't walk away just because you have an application layer in existence.   Often, the lack of stored procedures is the direct cause of such problems.   Heck, I'd even say that's a prime cause.   Add developers who are :allegedly "full stack" capable, and you have a wonderful recipe for a continuous sequence of bad decision-making, followed by the kinds of problems Hugo described, which are then promptly blamed on the database instead of the bad decision-making, ...  add some IT-clueless management to the mix and that blame ends up in the database permanently, along with an utter inability to solve said problems, and eventually, IT collapses and takes the company with it...    Mind you, that might appear to be an extreme example, but how far can a company be from such a problem if they're seriously considering abandoning stored procedures?   Probably not as far away as they think...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • funbi - Tuesday, April 17, 2018 6:00 AM

    Hugo Kornelis - Tuesday, April 3, 2018 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.

    Ever heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.

    Well, this was the underlying point I was making in my prior post. That stored procedure development can be a bottleneck and the usage of ORM can speed up that process. I guess I didn't explicitly say ORM, but that's ideally what I was referring to overall. There are other approaches than having to rely on a stored procedure to be developed, that will likely be developed by someone else other than the developer working on the application end.

    I'm on the same page as you for a lot of the mundane areas of database interaction. ORM's can handle a good bulk of that. But when it comes to more of the complex tasks that may require more complex SQL, then it's likely going to require a different approach from someone who knows how to get the best out of the underlying code and system through a stored procedure among other things like proper modeling, indexing, and so forth.

  • funbi - Tuesday, April 17, 2018 6:00 AM

    Hugo Kornelis - Tuesday, April 3, 2018 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.

    Ever heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.

    "Ever heard of ORMs/Linq?"   Of course... and I'm actually a strong advocate of using them... but they still need to be used correctly.  The problem is exemplified by what the lead developer told me at one company when I asked him why everything that came across the line was NUMERIC(18) and NVARCHAR(256)... he looked at me and quoted Knuth saying "Preoptimization is the root of all evil".   Grant's wonderful T-Shirt that says "I may look calm but, in my mind, I've already killed you three times" came to mind.  The moroff had no clue about datatype matching nor any understanding of the differences between preoptimization and writing good code and this was the bloody lead developer being so stupid.  The code that came out of that team sucked so bad it had it's own field of gravity and it turned out to be a black hole.  It destroyed everything it touched and sucked in a whole lot of people into doing likewise.

    It doesn't matter what it is... T-SQL... stored procedures, ORMs, whatever tool... you have to know the correct way to use it and I've found that there are a whole lot of people that "cobble together objects in code" and they do it the wrong way.  There's only one way to fix that and that's for the people using the tools to actually take the time to learn the tools and the ramifications of the choices they make.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • xsevensinzx - Tuesday, April 17, 2018 6:22 AM

    funbi - Tuesday, April 17, 2018 6:00 AM

    Hugo Kornelis - Tuesday, April 3, 2018 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.

    Ever heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.

    Well, this was the underlying point I was making in my prior post. That stored procedure development can be a bottleneck and the usage of ORM can speed up that process. I guess I didn't explicitly say ORM, but that's ideally what I was referring to overall. There are other approaches than having to rely on a stored procedure to be developed, that will likely be developed by someone else other than the developer working on the application end.

    I'm on the same page as you for a lot of the mundane areas of database interaction. ORM's can handle a good bulk of that. But when it comes to more of the complex tasks that may require more complex SQL, then it's likely going to require a different approach from someone who knows how to get the best out of the underlying code and system through a stored procedure among other things like proper modeling, indexing, and so forth.

    If it weren't for the truly horrid decision-making that ORM's are consistently guilty of, this might be a wholly different discussion.   I would argue that the cost to fix all the problems ORM's create is far greater than the cost of not using them in the first place, provided you at least have the good common sense to pay for the right kind of talent to begin with.   The alternate usually leads to considerably less than mediocrity...   And in reference to my other recent post on this topic, this kind of thinking ... that an ORM can "do the job" adequately for the "simple" stuff, is seriously flawed, and in an of itself qualifies as "bad decision-making".

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson - Tuesday, April 17, 2018 6:32 AM

    xsevensinzx - Tuesday, April 17, 2018 6:22 AM

    funbi - Tuesday, April 17, 2018 6:00 AM

    Hugo Kornelis - Tuesday, April 3, 2018 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.

    Ever heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.

    Well, this was the underlying point I was making in my prior post. That stored procedure development can be a bottleneck and the usage of ORM can speed up that process. I guess I didn't explicitly say ORM, but that's ideally what I was referring to overall. There are other approaches than having to rely on a stored procedure to be developed, that will likely be developed by someone else other than the developer working on the application end.

    I'm on the same page as you for a lot of the mundane areas of database interaction. ORM's can handle a good bulk of that. But when it comes to more of the complex tasks that may require more complex SQL, then it's likely going to require a different approach from someone who knows how to get the best out of the underlying code and system through a stored procedure among other things like proper modeling, indexing, and so forth.

    If it weren't for the truly horrid decision-making that ORM's are consistently guilty of, this might be a wholly different discussion.   I would argue that the cost to fix all the problems ORM's create is far greater than the cost of not using them in the first place, provided you at least have the good common sense to pay for the right kind of talent to begin with.   The alternate usually leads to considerably less than mediocrity...   And in reference to my other recent post on this topic, this kind of thinking ... that an ORM can "do the job" adequately for the "simple" stuff, is seriously flawed, and in an of itself qualifies as "bad decision-making".

    Even then, it's frequently not the fault of the ORM itself.  I used to blame Linq2SQL for doing stupid things like passing the letter "A" (a status in many of our tables) as a NUMERIC(18) (value of 65) and then using WHERE ASCII(Status) = @p1 in the code it created.  Now, I'll admit that it was a totally stupid default setting but it turned out that the people who used the ORM didn't know how to write the objects nor how to use some of the configuration settings of the ORM to prevent such idiotic renditions of simple queries.

    On the more complex things, I agree... there's usually little that can be done to fix ORM code other than "cobbling together" some SQL and, if you're going to do that, you might as well take advantage of the power and security available in stored procedures.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • sgmunson - Tuesday, April 17, 2018 6:32 AM

    xsevensinzx - Tuesday, April 17, 2018 6:22 AM

    funbi - Tuesday, April 17, 2018 6:00 AM

    Hugo Kornelis - Tuesday, April 3, 2018 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.

    Ever heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.

    Well, this was the underlying point I was making in my prior post. That stored procedure development can be a bottleneck and the usage of ORM can speed up that process. I guess I didn't explicitly say ORM, but that's ideally what I was referring to overall. There are other approaches than having to rely on a stored procedure to be developed, that will likely be developed by someone else other than the developer working on the application end.

    I'm on the same page as you for a lot of the mundane areas of database interaction. ORM's can handle a good bulk of that. But when it comes to more of the complex tasks that may require more complex SQL, then it's likely going to require a different approach from someone who knows how to get the best out of the underlying code and system through a stored procedure among other things like proper modeling, indexing, and so forth.

    If it weren't for the truly horrid decision-making that ORM's are consistently guilty of, this might be a wholly different discussion.   I would argue that the cost to fix all the problems ORM's create is far greater than the cost of not using them in the first place, provided you at least have the good common sense to pay for the right kind of talent to begin with.   The alternate usually leads to considerably less than mediocrity...   And in reference to my other recent post on this topic, this kind of thinking ... that an ORM can "do the job" adequately for the "simple" stuff, is seriously flawed, and in an of itself qualifies as "bad decision-making".

    Depends on you. I personally have not found it to be flawed and painted in such a way you are describing. I would say that 90% of the time if used right is good enough as creating very simple queries is not something difficult for a system to figure out. It's that 10% of the time when it stumbles upon some very complex tasks where it does not take the best approach and intervention is needed.

  • Shifting gears a bit and to quote Walt Kelly, "We have met the enemy... and he is us".  As Steve Jones posted about his son taking computer classes, so we do in the world of SQL Server.  I was reminded by an email that I got advertising "Power BI in a Day".  While I agree that you can learn a lot of what can be done using such a tool in a day, there is frequently little time left to teach the WHY and WHEN something should be done.  Technical finesse and teaching people the correct paradigm of thinking needs to be emphasized more during such courses/classes/presentations.  It's like most articles and blog entries that I see concerning the use of Recursive CTEs.  Almost all of them say nothing about when they'll cause performance and resource issues and some of them even claim that their use is a "Best Practice replacement for While loops".   A lot of these articles are written by people that work at SQL Server related consulting companies!!!   Yeah... I'm going to hire them after such dribble in an article. The trouble is that people that don't know better are sucked in by such articles just because they come from a company that claims to be "experts in their field" and they carry it forward to others because they don't know better either!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • xsevensinzx - Tuesday, April 17, 2018 6:53 AM

    sgmunson - Tuesday, April 17, 2018 6:32 AM

    xsevensinzx - Tuesday, April 17, 2018 6:22 AM

    funbi - Tuesday, April 17, 2018 6:00 AM

    Hugo Kornelis - Tuesday, April 3, 2018 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.

    Ever heard of ORMs/Linq? Does anyone cobble together SQL in code any more? Use SP's for heavy lifting for sure but the problems you are listing have been solved in the application layer some time ago.

    Well, this was the underlying point I was making in my prior post. That stored procedure development can be a bottleneck and the usage of ORM can speed up that process. I guess I didn't explicitly say ORM, but that's ideally what I was referring to overall. There are other approaches than having to rely on a stored procedure to be developed, that will likely be developed by someone else other than the developer working on the application end.

    I'm on the same page as you for a lot of the mundane areas of database interaction. ORM's can handle a good bulk of that. But when it comes to more of the complex tasks that may require more complex SQL, then it's likely going to require a different approach from someone who knows how to get the best out of the underlying code and system through a stored procedure among other things like proper modeling, indexing, and so forth.

    If it weren't for the truly horrid decision-making that ORM's are consistently guilty of, this might be a wholly different discussion.   I would argue that the cost to fix all the problems ORM's create is far greater than the cost of not using them in the first place, provided you at least have the good common sense to pay for the right kind of talent to begin with.   The alternate usually leads to considerably less than mediocrity...   And in reference to my other recent post on this topic, this kind of thinking ... that an ORM can "do the job" adequately for the "simple" stuff, is seriously flawed, and in an of itself qualifies as "bad decision-making".

    Depends on you. I personally have not found it to be flawed and painted in such a way you are describing. I would say that 90% of the time if used right is good enough as creating very simple queries is not something difficult for a system to figure out. It's that 10% of the time when it stumbles upon some very complex tasks where it does not take the best approach and intervention is needed.

    Exactly. As I said in my post, like you I would absolutely keep SPs for more complex tasks and processing. What I was getting at is that the reasons Hugo gave in his post are not good reasons to keep them.

  • The way I see it, usage of stored procedures actually makes the application less dependent on the database, meaning more database platform agnostic. From the ADO.NET / Java client perspective, stored procedures work essentially the same between SQL Server and Oracle. Even if they're thinking about retrofitting the application to use a NoSQL database or web service based architecture at some point in the future, stored procedures on the application tier will make the transition easier (at least for the application developers).

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Eric M Russell - Tuesday, April 17, 2018 7:30 AM

    The way I see it, usage of stored procedures actually makes the application less dependent on the database, meaning more database platform agnostic. From the ADO.NET / Java client perspective, stored procedures work essentially the same between SQL Server and Oracle. Even if they're thinking about retrofitting the application to use a NoSQL database or web service based architecture at some point in the future, stored procedures on the application tier will make the transition easier (at least for the application developers).

    Heh... don't programmers refer to that as "abstraction"?  😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 46 through 60 (of 191 total)

You must be logged in to reply to this topic. Login to reply