May 9, 2018 at 11:32 am
Steve Thompson-454462 - Wednesday, May 9, 2018 8:33 AM
And since you mentioned that you had 30 years of experience, do you want me to believe that this suddenly makes facts refutable? Or were you just trying to take out your measuring stick? Take the ego elsewhere.
You're completely misreading my comment. My mention of my experience was intended to show that such attempts to equate greater validity of observations to more years in the field are moot, NOT to claim greater authority. You've got 20 (or 35) years of experience and you've seen quite a few things, I have 30 and have also seen things. However, neither one of us can claim to be the final authority on what works or doesn't in tech.Was not in any way an ego play.
The issue is that while I and others are admitting that we too have seen the issues you're expounding on, we have also seen other methods work. No need to indulge in personal attacks simply because someone sees validity in points you don't agree with - that's not the spirit of this community and honestly, having been familiar with your posts for several years, strikes me as unlike you.
May 9, 2018 at 11:36 am
sgmunson - Wednesday, May 9, 2018 10:48 AMLynn Pettis - Wednesday, May 9, 2018 10:26 AMsgmunson - Wednesday, May 9, 2018 10:14 AMSteve Thompson-454462 - Wednesday, May 9, 2018 8:33 AMsgmunson - Wednesday, May 9, 2018 6:55 AMSure... even if I assume you were entirely trying to play the devil's advocate role, your willingness to continue to argue illogically for what is demonstrably a bad idea, seriously devalues that role to nothing more than political nuisance.I've been following this thread closely and, in Patrick's defense, I don't think he's been arguing illogically at all. You suggest that that your 20 years of experience make your opinions irrefutable, but I have coming up on 30 years of experience, and whereas much of my data layer architecture has included a stored procedure based interface just as you advocate for, I have also seen plenty of deployments where CRUD operations are handled just fine by an ORM framework, with business logic being encoded elsewhere. I feel like this is pretty close to what Patrick has been saying and there's nothing illogical about it - these systems are out there, working to spec as I type.
In fact, in microservice architectures, encompassing large amounts of business logic in a monolithic database would be considered an anti-pattern, but restricting an ORM-based DAL to simple CRUD operations works well.
With that said, would I promote tearing down a functioning SP-based interface in a brown field project, as Luis suggests his company is considering? No I would not. And I believe Patrick has said much the same over his posts.
My mention of experience was not intended to be anything other than a statement of fact, and in that respect, I have another 15 years to add to that, having had a front-row seat to watching as database project after database project went wrong because the depth of incompetence was so deep that there wasn't really any attempt to do it even remotely right. Management was cycled in and out, PMs came and went, and very little changed until the upper management level got canned after a multi-million dollar project went so badly that even recovery from it was going to be problematic. Up to that point, there always seemed to be time to do it wrong at least 3 times, if not 4 or 5. ORM and other variations were certainly common elements in most of the problems, and because stored procedures were not being used, even for CRUD, any changes to the applications, which were quite frequent due to the failure to do much in the way of useful database design or user interaction to gather requirements, often took anywhere from a month and a half to as long as 6 months. Then by the time the users noticed there was a problem, it was going to be another couple of months to fix the application in concert with the now necessary database changes. Most of that could have been avoided if stored procedures had been in place, as a few simple changes there could have minimized the impact and the app could have been updated and fixed in a couple of days instead of a month or more. So if you expect me to buy into sprocs ever being unnecessary, forget it. They are just too valuable to ignore, and they can achieve cost avoidance in ways you might not even imagine, when properly designed and implemented.
And since you mentioned that you had 30 years of experience, do you want me to believe that this suddenly makes facts refutable? Or were you just trying to take out your measuring stick? Take the ego elsewhere.
All that experience provides is a single point of view. I have worked for 40 years in the IT field. My experience does not validate or invalidate the experience of others, it just provides another point of view. What you have experienced may not be what I have experienced. In the database side of my career (over 20 years) I have had the pleasure (or displeasure depending on your POV) of working in relatively small and stable environments and not dealing with some of the issues others have experienced. That is one of the reasons I try to stay active here, to see and hopefully learn from other experiences. No ego, just a desire to gain from other peoples POV.
Lynn, I hear ya... BUT ... when I see arguments that don't hold water and/or don't stand up to scrutiny, I call BS on them. That's just part and parcel of what has made me successful, and my overall IT experience is just over 40 years, so we're not all that far apart. I may not have the spit and polish or the just be nice genes, but I'm not about to sacrifice success just to accommodate some other point of view. If folks choose to get bent out of shape over that, well, I guess that's their problem. What bothers me most is the intellectual dishonesty some folks are willing to engage in, and I have pretty much zero tolerance for that kind of foolishness. I've also found that it's not usually some other point of view that I learn from... it's almost exclusively new information. Especially where technical stuff is involved; e.g. science, math, T-SQL, etc...
I know what you mean. I love to hone in on textual specifics and hold them up for examinations, its probably a "tick" of mine to dissect and examine counterarguments to mine in minute detail, yes this has the danger of losing context, but sometimes the context doesn't even help the argument stand. Should I point these out, well it probably depends on who I'm interacting with. Is there a temptation to lump the entire quoted post into a characterization like "intellectual dishonesty," "bs," sure, I feel that, but in my case lately I think I'd just rather take the blame myself for failing to communicate and "abandon thread" so to speak but I suspect that's not your style and why would I not be ok with that? If you think my posts are bs, then bs it is!
I think its up to everyone themselves to cultivate the message they want to send, and I've been characterized perhaps in an unflattering manner before because of what I typed and I'm not unaware of that. But to be honest, any course adjustment I elect to take is not only going to be based on the characterization I have read, but also on both the accuracy of the characterization as I perceive it, and the authority of the source as best I can determine based on my experience of their history and my encounters of them and that last factor is not entirely unimportant.
Heh on the other hand, one of the things I love about presentations from anonymous posters is that it forces me to consider the point itself, and sometimes truth is so logical and self apparent it needs no citation to authority. That's been an interesting thought of mine lately, as I've seen a few examples of that.
edit: I also want to be more concise in the future in my conversations, as I think maybe folks get tired of reading my posts and just try to assume the content is something else and they argue that content instead of what I actually posted. It a goal of mine this year, wish me luck!
May 9, 2018 at 11:42 am
sgmunson - Wednesday, May 9, 2018 10:48 AMWhat bothers me most is the intellectual dishonesty some folks are willing to engage in, and I have pretty much zero tolerance for that kind of foolishness.
Can you be explicit as to what you consider intellectual dishonesty?
May 9, 2018 at 11:46 am
Steve Thompson-454462 - Wednesday, May 9, 2018 11:42 AMsgmunson - Wednesday, May 9, 2018 10:48 AMWhat bothers me most is the intellectual dishonesty some folks are willing to engage in, and I have pretty much zero tolerance for that kind of foolishness.Can you be explicit as to what you consider intellectual dishonesty?
Heh I'm guessing the sections that were TLDR 😉
May 9, 2018 at 12:05 pm
Sergiy - Wednesday, May 9, 2018 11:29 AMUsing SP's vs ad-hoc queries is not an argument here, really.Placing a single CRUD query into a sp does not provide any significant advantages in terms of performance, stability, etc.But avoiding sp's is usually an indicator of lack of knowledge about "DB stuff". Effective code starts with "CREATE TABLE".Designing adequate data structure is crucial, and this is a part which is not in the scope for a typical front end developer.Too often they just create a bunch of tables reflecting object structure with a single key on an identity column. From that point - it does not really matter if you use stored procedures or ad hoc queries, the damage is irreversible.Actually, using CRUD queries against object-like tables in SQL Server does not make any sence from business point of view.With such a data repository it's more effective to use a set of flat files. SQL Server only creates a huge overhead with no apparent advantages. Why pay quite significant fees for functionality you don't understand and don't intent to use - that's the question.
Even simple CRUD against object like tables can still take advantage of ACID and you really save a TON of work that you would otherwise spend managing concurrency etc, you get online backups, triggers, an incredible set of security primitives, snapshots. You could be the least normalizing programmer possible, ignore stored procedures and all the other stuff relational advocates hold dear, and SQL Server still literally throws you the kitchen sink as far as being an "intelligent data store," (yeah sorry if that term really means something else). I'm just saying that even with the most ORM'ed up app, SQL Server is far from useless in my opinion.
May 9, 2018 at 12:09 pm
patrickmcginnis59 10839 - Wednesday, May 9, 2018 12:05 PMSergiy - Wednesday, May 9, 2018 11:29 AMUsing SP's vs ad-hoc queries is not an argument here, really.Placing a single CRUD query into a sp does not provide any significant advantages in terms of performance, stability, etc.But avoiding sp's is usually an indicator of lack of knowledge about "DB stuff". Effective code starts with "CREATE TABLE".Designing adequate data structure is crucial, and this is a part which is not in the scope for a typical front end developer.Too often they just create a bunch of tables reflecting object structure with a single key on an identity column. From that point - it does not really matter if you use stored procedures or ad hoc queries, the damage is irreversible.Actually, using CRUD queries against object-like tables in SQL Server does not make any sence from business point of view.With such a data repository it's more effective to use a set of flat files. SQL Server only creates a huge overhead with no apparent advantages. Why pay quite significant fees for functionality you don't understand and don't intent to use - that's the question.Even simple CRUD against object like tables can still take advantage of ACID and you really save a TON of work that you would otherwise spend managing concurrency etc, you get online backups, triggers, an incredible set of security primitives, snapshots. You could be the least normalizing programmer possible, ignore stored procedures and all the other stuff relational advocates hold dear, and SQL Server still literally throws you the kitchen sink as far as being an "intelligent data store," (yeah sorry if that term really means something else). I'm just saying that even with the most ORM'ed up app, SQL Server is far from useless in my opinion.
Unfortunately, you then end up with an unresponsive, unscalable, data store and if you are the DBA for the database you take the blame for the lack of design and performance, not the people that actually built the mess.
May 9, 2018 at 12:20 pm
Lynn Pettis - Wednesday, May 9, 2018 12:09 PMpatrickmcginnis59 10839 - Wednesday, May 9, 2018 12:05 PMSergiy - Wednesday, May 9, 2018 11:29 AMUsing SP's vs ad-hoc queries is not an argument here, really.Placing a single CRUD query into a sp does not provide any significant advantages in terms of performance, stability, etc.But avoiding sp's is usually an indicator of lack of knowledge about "DB stuff". Effective code starts with "CREATE TABLE".Designing adequate data structure is crucial, and this is a part which is not in the scope for a typical front end developer.Too often they just create a bunch of tables reflecting object structure with a single key on an identity column. From that point - it does not really matter if you use stored procedures or ad hoc queries, the damage is irreversible.Actually, using CRUD queries against object-like tables in SQL Server does not make any sence from business point of view.With such a data repository it's more effective to use a set of flat files. SQL Server only creates a huge overhead with no apparent advantages. Why pay quite significant fees for functionality you don't understand and don't intent to use - that's the question.Even simple CRUD against object like tables can still take advantage of ACID and you really save a TON of work that you would otherwise spend managing concurrency etc, you get online backups, triggers, an incredible set of security primitives, snapshots. You could be the least normalizing programmer possible, ignore stored procedures and all the other stuff relational advocates hold dear, and SQL Server still literally throws you the kitchen sink as far as being an "intelligent data store," (yeah sorry if that term really means something else). I'm just saying that even with the most ORM'ed up app, SQL Server is far from useless in my opinion.
Unfortunately, you then end up with an unresponsive, unscalable, data store and if you are the DBA for the database you take the blame for the lack of design and performance, not the people that actually built the mess.
Well that's sort of along the lines of what I posted, if I planned to take this argument to a meeting or presentation, I'd have to anticipate something similar to "like how?" Can you avoid plan cache bloat without sp's? Is it not possible to join tables at the server without sp's? Filter result sets? Page at the server? Optimize query plans with indexes? Parameterize queries and updates? Scale? Maintain integrity, referential integrity, use constraints, handle concurrency correctly? If you are absolutely sure that this cannot be done without stored procedures then your meeting will go swimmingly and you should be good to go.
May 9, 2018 at 12:31 pm
patrickmcginnis59 10839 - Wednesday, May 9, 2018 12:20 PMLynn Pettis - Wednesday, May 9, 2018 12:09 PMpatrickmcginnis59 10839 - Wednesday, May 9, 2018 12:05 PMSergiy - Wednesday, May 9, 2018 11:29 AMUsing SP's vs ad-hoc queries is not an argument here, really.Placing a single CRUD query into a sp does not provide any significant advantages in terms of performance, stability, etc.But avoiding sp's is usually an indicator of lack of knowledge about "DB stuff". Effective code starts with "CREATE TABLE".Designing adequate data structure is crucial, and this is a part which is not in the scope for a typical front end developer.Too often they just create a bunch of tables reflecting object structure with a single key on an identity column. From that point - it does not really matter if you use stored procedures or ad hoc queries, the damage is irreversible.Actually, using CRUD queries against object-like tables in SQL Server does not make any sence from business point of view.With such a data repository it's more effective to use a set of flat files. SQL Server only creates a huge overhead with no apparent advantages. Why pay quite significant fees for functionality you don't understand and don't intent to use - that's the question.Even simple CRUD against object like tables can still take advantage of ACID and you really save a TON of work that you would otherwise spend managing concurrency etc, you get online backups, triggers, an incredible set of security primitives, snapshots. You could be the least normalizing programmer possible, ignore stored procedures and all the other stuff relational advocates hold dear, and SQL Server still literally throws you the kitchen sink as far as being an "intelligent data store," (yeah sorry if that term really means something else). I'm just saying that even with the most ORM'ed up app, SQL Server is far from useless in my opinion.
Unfortunately, you then end up with an unresponsive, unscalable, data store and if you are the DBA for the database you take the blame for the lack of design and performance, not the people that actually built the mess.
Well that's sort of along the lines of what I posted, if I planned to take this argument to a meeting or presentation, I'd have to anticipate something similar to "like how?" Can you avoid plan cache bloat without sp's? Is it not possible to join tables at the server without sp's? Filter result sets? Page at the server? Optimize query plans with indexes? Parameterize queries and updates? Scale? Maintain integrity, referential integrity, use constraints, handle concurrency correctly? If you are absolutely sure that this cannot be done without stored procedures then your meeting will go swimmingly and you should be good to go.
Most of your concerns in your list of questions have nothing to do with stored procedures at all. While I appreciate being thorough, you are trying to answer questions that are irrelevant to the discussion. Might was well ask if stored procedures will help prevent concussions in youth sports.
_______________________________________________________________
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/
May 9, 2018 at 12:57 pm
Sean Lange - Wednesday, May 9, 2018 12:31 PMpatrickmcginnis59 10839 - Wednesday, May 9, 2018 12:20 PMLynn Pettis - Wednesday, May 9, 2018 12:09 PMpatrickmcginnis59 10839 - Wednesday, May 9, 2018 12:05 PMSergiy - Wednesday, May 9, 2018 11:29 AMUsing SP's vs ad-hoc queries is not an argument here, really.Placing a single CRUD query into a sp does not provide any significant advantages in terms of performance, stability, etc.But avoiding sp's is usually an indicator of lack of knowledge about "DB stuff". Effective code starts with "CREATE TABLE".Designing adequate data structure is crucial, and this is a part which is not in the scope for a typical front end developer.Too often they just create a bunch of tables reflecting object structure with a single key on an identity column. From that point - it does not really matter if you use stored procedures or ad hoc queries, the damage is irreversible.Actually, using CRUD queries against object-like tables in SQL Server does not make any sence from business point of view.With such a data repository it's more effective to use a set of flat files. SQL Server only creates a huge overhead with no apparent advantages. Why pay quite significant fees for functionality you don't understand and don't intent to use - that's the question.Even simple CRUD against object like tables can still take advantage of ACID and you really save a TON of work that you would otherwise spend managing concurrency etc, you get online backups, triggers, an incredible set of security primitives, snapshots. You could be the least normalizing programmer possible, ignore stored procedures and all the other stuff relational advocates hold dear, and SQL Server still literally throws you the kitchen sink as far as being an "intelligent data store," (yeah sorry if that term really means something else). I'm just saying that even with the most ORM'ed up app, SQL Server is far from useless in my opinion.
Unfortunately, you then end up with an unresponsive, unscalable, data store and if you are the DBA for the database you take the blame for the lack of design and performance, not the people that actually built the mess.
Well that's sort of along the lines of what I posted, if I planned to take this argument to a meeting or presentation, I'd have to anticipate something similar to "like how?" Can you avoid plan cache bloat without sp's? Is it not possible to join tables at the server without sp's? Filter result sets? Page at the server? Optimize query plans with indexes? Parameterize queries and updates? Scale? Maintain integrity, referential integrity, use constraints, handle concurrency correctly? If you are absolutely sure that this cannot be done without stored procedures then your meeting will go swimmingly and you should be good to go.
Most of your concerns in your list of questions have nothing to do with stored procedures at all. While I appreciate being thorough, you are trying to answer questions that are irrelevant to the discussion. Might was well ask if stored procedures will help prevent concussions in youth sports.
I can't argue that. I think at some point there has to be something we can talk about though, even when expecting refutations. One of your points would be one I'd go with, its the modularity or layering of it, but the difference would be that I would advocate the difference in the degree enforcement of the data access boundary. If all we can talk about is data layering, that's a common theme in programming because you can enforce (somewhat) the data layer by the modularity of the code, heck maybe you can hide the source code, db login, or simply review code hoping none sneaks through. However, Jeff Moden has a stronger point with stored procedures and that is that the enforcement is a stronger boundary than mere source code availability because the permissions can be attached to the stored procedure itself, its a hard boundary enforced by permissions, if the folks you are meeting with are knowledgeable they'll know whats up. I'm just thinking calling it a data layer needs to come with enough detail to explain why its a BETTER data layer. Additionally I have been unable to refute Sue_H's advantage of deployability. While you can recompile your app, it comes nowhere close to being able to deploy or revert a stored procedure with a matching "velocity" so to speak. We take great advantage with that one at work. You CAN recompile an app and restart the service but swapping a stored procedure is almost like dynamic linking on the fly depending on how well it or the app is written. I've rolled out one update this month in the middle of production with confidence that it would work in the context of our operation. I wouldn't do it blindly but this opportunity is a huge advantage of stored procedures.
I probably lack the organizational skill and experience with SQL Server to be an expert here, but at least in my slow mind the next time this question gets asked, those are two of my conclusions from these and other discussions. But you're right, and that's what I'm saying, is that long list relevant to Luis problem? Can he quote Lynn's post and feel confident in keeping his stored procedures? Can he confidently tell his managers that if they drop stored procedures that they'll end up with an unscaleable, unresponsive data store that they will then blame on him for his poor design and not the actual designers and expect the big meeting win?
And most importantly for me, is my post TLDR?
May 9, 2018 at 2:05 pm
sgmunson - Wednesday, May 9, 2018 10:14 AMSteve Thompson-454462 - Wednesday, May 9, 2018 8:33 AMAnd since you mentioned that you had 30 years of experience, do you want me to believe that this suddenly makes facts refutable? Or were you just trying to take out your measuring stick? Take the ego elsewhere.
You're completely misreading my comment. My mention of my experience was intended to show that such attempts to equate greater validity of observations to more years in the field are moot, NOT to claim greater authority. You've got 20 (or 35) years of experience and you've seen quite a few things, I have 30 and have also seen things. However, neither one of us can claim to be the final authority on what works or doesn't in tech.Was not in any way an ego play.
The issue is that while I and others are admitting that we too have seen the issues you're expounding on, we have also seen other methods work. No need to indulge in personal attacks simply because someone sees validity in points you don't agree with - that's not the spirit of this community and honestly, having been familiar with your posts for several years, strikes me as unlike you.
So let;s see if I understand what you just said... You've seen the issues before, but have also seen the bad ideas work, and apparently, that satisfies you that it's okay, right? ORM's are usually expensive pieces of software, and setups without sprocs usually require considerably more effort to maintain and/or update functionality, pushing the maintenance cost of changes considerably higher. That's often by design. ORM companies need to monetize their software any way they can, and will rarely hesitate to put their customers into the maintenance cost bandwagon. I can hear the "cha-ching" every time I think about it... Usually, by the time the customer figures this out, they're out more than a few hundred K to as much as millions... Sorry, not ever going to go along with that.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 9, 2018 at 2:44 pm
Steve Thompson-454462 - Wednesday, May 9, 2018 11:32 AMsgmunson - Wednesday, May 9, 2018 10:14 AMSteve Thompson-454462 - Wednesday, May 9, 2018 8:33 AMAnd since you mentioned that you had 30 years of experience, do you want me to believe that this suddenly makes facts refutable? Or were you just trying to take out your measuring stick? Take the ego elsewhere.
You're completely misreading my comment. My mention of my experience was intended to show that such attempts to equate greater validity of observations to more years in the field are moot, NOT to claim greater authority. You've got 20 (or 35) years of experience and you've seen quite a few things, I have 30 and have also seen things. However, neither one of us can claim to be the final authority on what works or doesn't in tech.Was not in any way an ego play.
The issue is that while I and others are admitting that we too have seen the issues you're expounding on, we have also seen other methods work. No need to indulge in personal attacks simply because someone sees validity in points you don't agree with - that's not the spirit of this community and honestly, having been familiar with your posts for several years, strikes me as unlike you.
So let;s see if I understand what you just said... You've seen the issues before, but have also seen the bad ideas work, and apparently, that satisfies you that it's okay, right? ORM's are usually expensive pieces of software, and setups without sprocs usually require considerably more effort to maintain and/or update functionality, pushing the maintenance cost of changes considerably higher. That's often by design. ORM companies need to monetize their software any way they can, and will rarely hesitate to put their customers into the maintenance cost bandwagon. I can hear the "cha-ching" every time I think about it... Usually, by the time the customer figures this out, they're out more than a few hundred K to as much as millions... Sorry, not ever going to go along with that.
Actually one of the biggest ones (Entity Framework) now comes with VS2017 which companies would be paying licences for anyway. And when using it for CRUD and other simple operations there is no more maintenance than there would be for any other piece of code.
May 9, 2018 at 2:51 pm
sgmunson - Wednesday, May 9, 2018 2:05 PMORM's are usually expensive pieces of software .
Entity Framework was originally built into .NET and is now available under Apache licensing, I believe.
And really, Steve, all the snark is just muddying up the conversation. I'm doing my best to respond to you respectfully, please show me the same courtesy.
May 9, 2018 at 5:20 pm
Steve Thompson-454462 - Wednesday, May 9, 2018 2:51 PMsgmunson - Wednesday, May 9, 2018 2:05 PMORM's are usually expensive pieces of software .Entity Framework was originally built into .NET and is now available under Apache licensing, I believe.
And really, Steve, all the snark is just muddying up the conversation. I'm doing my best to respond to you respectfully, please show me the same courtesy.
Also Entity Framework does not preclude the use of stored procedures.
I think the initial workload of getting trained in Entity Framework is probably nontrivial, but from what I can tell, ultimately Entity Framework is meant to assist with development productivity.
Also, I didn't want to be the first to mention sgmunson tone, but I'm going to agree that its an added workload to deal with. I've spent a few posts having to at least address some inferences about my employability and intellectual honesty. Do we really need that sort of thing here? I've made similar mistakes before and caused folks to address it with me so I'm not saying I'm better than anybody, but if I got addressed for that sort of thing, that means I can at least relate to you the benefits of taking a lesson from it.
May 9, 2018 at 5:48 pm
patrickmcginnis59 10839 - Wednesday, May 9, 2018 12:05 PMSergiy - Wednesday, May 9, 2018 11:29 AMUsing SP's vs ad-hoc queries is not an argument here, really.Placing a single CRUD query into a sp does not provide any significant advantages in terms of performance, stability, etc.But avoiding sp's is usually an indicator of lack of knowledge about "DB stuff". Effective code starts with "CREATE TABLE".Designing adequate data structure is crucial, and this is a part which is not in the scope for a typical front end developer.Too often they just create a bunch of tables reflecting object structure with a single key on an identity column. From that point - it does not really matter if you use stored procedures or ad hoc queries, the damage is irreversible.Actually, using CRUD queries against object-like tables in SQL Server does not make any sence from business point of view.With such a data repository it's more effective to use a set of flat files. SQL Server only creates a huge overhead with no apparent advantages. Why pay quite significant fees for functionality you don't understand and don't intent to use - that's the question.Even simple CRUD against object like tables can still take advantage of ACID and you really save a TON of work that you would otherwise spend managing concurrency etc, you get online backups, triggers, an incredible set of security primitives, snapshots. You could be the least normalizing programmer possible, ignore stored procedures and all the other stuff relational advocates hold dear, and SQL Server still literally throws you the kitchen sink as far as being an "intelligent data store," (yeah sorry if that term really means something else). I'm just saying that even with the most ORM'ed up app, SQL Server is far from useless in my opinion.
Managing concurrency means locking.
Which is the arch enemy of front end developers.
So, they fight it with NOLOCK right from the first signs of queries blocked by a huge slow UPDATE.
To often they try to replace locking with own "semaphores", "flags", etc.
So, managing concurrency is not an argument here.
Online backups the way they are used by such developers also hold not not much of a value.
Full Backups can be easily replaced with robocopy or any other scripted solution.
Log Backups usually not used at all, because huge "refresh data" updates cause log file to blow beyond any reasonable proportions, so recovery mode quickly gets changed to SIMPLE.
After a ORM developer writes a trigger or two, which "refreshes" a huge and growing table, his/her management quickly issues a policy of "not using triggers".
So, triggers are out of the picture as well.
Security model with unpredictable ad-hoc queries coming from a single user running the application, which may require full access to pretty much any table in the database is so loose, that I'm not sure if it can provide any security at all.
What's left to justify the cost of SQL Server license for the business?
Nothing.
_____________
Code for TallyGenerator
May 9, 2018 at 6:20 pm
Sergiy - Wednesday, May 9, 2018 5:48 PMpatrickmcginnis59 10839 - Wednesday, May 9, 2018 12:05 PMSergiy - Wednesday, May 9, 2018 11:29 AMUsing SP's vs ad-hoc queries is not an argument here, really.Placing a single CRUD query into a sp does not provide any significant advantages in terms of performance, stability, etc.But avoiding sp's is usually an indicator of lack of knowledge about "DB stuff". Effective code starts with "CREATE TABLE".Designing adequate data structure is crucial, and this is a part which is not in the scope for a typical front end developer.Too often they just create a bunch of tables reflecting object structure with a single key on an identity column. From that point - it does not really matter if you use stored procedures or ad hoc queries, the damage is irreversible.Actually, using CRUD queries against object-like tables in SQL Server does not make any sence from business point of view.With such a data repository it's more effective to use a set of flat files. SQL Server only creates a huge overhead with no apparent advantages. Why pay quite significant fees for functionality you don't understand and don't intent to use - that's the question.Even simple CRUD against object like tables can still take advantage of ACID and you really save a TON of work that you would otherwise spend managing concurrency etc, you get online backups, triggers, an incredible set of security primitives, snapshots. You could be the least normalizing programmer possible, ignore stored procedures and all the other stuff relational advocates hold dear, and SQL Server still literally throws you the kitchen sink as far as being an "intelligent data store," (yeah sorry if that term really means something else). I'm just saying that even with the most ORM'ed up app, SQL Server is far from useless in my opinion.
Managing concurrency means locking.
Which is the arch enemy of front end developers.
So, they fight it with NOLOCK right from the first signs of queries blocked by a huge slow UPDATE.
To often they try to replace locking with own "semaphores", "flags", etc.
So, managing concurrency is not an argument here.Online backups the way they are used by such developers also hold not not much of a value.
Full Backups can be easily replaced with robocopy or any other scripted solution.
Log Backups usually not used at all, because huge "refresh data" updates cause log file to blow beyond any reasonable proportions, so recovery mode quickly gets changed to SIMPLE.After a ORM developer writes a trigger or two, which "refreshes" a huge and growing table, his/her management quickly issues a policy of "not using triggers".
So, triggers are out of the picture as well.Security model with unpredictable ad-hoc queries coming from a single user running the application, which may require full access to pretty much any table in the database is so loose, that I'm not sure if it can provide any security at all.
What's left to justify the cost of SQL Server license for the business?
Nothing.
Well I like to code front ends and I don't mind saying that I've used sql successfully, I like to do the optimistic concurrency thing, with a well indexed table the lock lasts just as long as needed for a single row update, thats pretty much all I need for like a data entry or update form. After that, its just programming as usual. I get all the benefits, such as parameterized queries that don't bloat the proc cache. Theres no need for lock flags if you use optimistic concurrency, if the "rows updated" says no update has happened, you know someone else has updated the same row before you did and you code for that eventuality. No flag needed.
Gilamonster has gone into detail on what isolation modes or hints should be used and the information is out there to do multiuser database ops in a safe and predictable manner so I don't need to get into all of that, but I can't declare that messes as you describe don't exist. If people are going to code front ends to databases, they either need to know how to do it right, or otherwise as you said they will create a mess. And I agree, I know how stubborn developers can be, I've talked with some who will simply not accept what I try to tell them so the frustration is real! And stored procedures really do help there if they'll use them because then they can leave the work to someone who's willing to do the homework. I can't argue against that advantage, but it needs to be said that telling a developer they don't know databases in a manner that they will be willing to listen is really really difficult, been there done that 😉
Viewing 15 posts - 166 through 180 (of 191 total)
You must be logged in to reply to this topic. Login to reply