October 18, 2017 at 3:17 am
Hi all,
I'm posting my question in this 2008 forum because we're running SQLServer 2008R2, but it is more a general question about SQL vs programs (in Java for me).
To briefly explain, over the years I wrote lots of queries in SP, VW and FN, all optimised and mostly according to the best pratices I learned on this site since 10 years. These all run fine and do the job as it should be. Now we're on a merge with another company my cie bought and their IT staff says we should just delete all the SP, FN and VW and do the staff in programs written in Java. I can understand that the logic of some SP should be transferred in programs (and that's what I asked years ago), but not ALL the logic.
As I am not a decider, only the little DBA who did his job in his corner, I need some arguments to convince the management that doing everything in programs is not the best way to improve the IT. But I may be wrong.
So I very much look forward to reading your technical pros and cons on this. Please stay away from discussing around the merge or that I should look for another job; I'd really like to confront my point of view with yours and if they match, have arguments to bring in my next meetings.
Thank you so much for reading and sharing your thoughts with me!
October 18, 2017 at 3:39 am
Personally, I'm on the side of using SPs, functions, etc, and then limiting the service account down as much as possible on it's permissions. It can be a longer "slog" but there's more security there. The development team are in charge of creating the tables, views, functions, that are required, and then the application only has the ability the execute the SPs on the SQL Server. while doing so, it still has the ability to use the SELECT, INSERT, UPDATE, etc, etc, statements in the SPs, however, that's due to inherited permissions when they have the same owner. This means, if someone compromises the account they can only execute SPs. this makes things a lot harder for someone malicious. Injection is also avoided, provided you haven't created any "silly" dynamic SQL statements.
On the other hand, yes, you can have the (parametrised) SQL created on the application, and run that against the Server. Injection can be avoided again, with the use of that parametrisation. This method, however, also means that the Service account will need to be able to run SELECT, UPDATE, INSERT, etc, statements. Effectively a lot more permissions. This, also, means that if the account is compromised that getting the data from your Customer table is as simple as SELECT * FROM dbo.Customer;. Bam! All your customer data. Want those Creditcard numbers that are encrypted in the database use a certificate? Well, if the person is clever enough to find out what the key is it might be as simple as... OPEN SYMMETRIC KEY CreditCards_Key
DECRYPTION BY CERTIFICATE CreditCards_Cert;
SELECT *
FROM certs.CreditCards;
And there go all your Credit Card details...
Now, I'm not saying that that's what's going to happen; in fact, I'm probably being a little over paranoid, however, I'd rather be over cautious. There are plenty of secure ways to use parametrised SQL, but I just always prefer to go that extra step. In the UK, and especially with GDPR on the way, the fines for a breach can be huge, however, you're less likely to get a fine (or reduce it) if you show measures to impede or prevent being compromised. I certainly think that using SPs is one of those ways.
Edit: typoes.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 18, 2017 at 4:40 am
Thanks Thom for your answer; security is the first argument; using "parametrised SQL" could be less secure than storing this process in SPs. I did not think of it. 🙂
When I spoke of transferring the logic to a program, I rather thought of writing a program to create a "calculated" result set, more like a SP using cursors ... versus a well-written query using "strong" SQL features (joins, CTE, ...)
October 18, 2017 at 8:45 am
It's a well known fact that "managed" code can be and frequently is much slower and resource intensive than using properly optimized stored procedures. The key in this case is that no one has proven anything to anyone. What they need to prove is that the shift to "Java Only" queries will not have a detrimental impact on performance or resource usage. When I say, "prove", I mean with code because hearsay weighs nothing. "One proper test is worth a thousand expert opinions".
There's a raft of other things that they're not considering. If functionality in a stored procedure needs to be modified, it's rather trivial to change such a thing and could take only minutes to test and deploy in an emergency (and there WILL be emergencies). If it's all in Java, then you have to go through the throws of building and deploying a WAR file for even a trivial change. Stored procedures, views, and functions are the ultimate in abstraction, which every decent developer should strive for. And, speaking of abstraction, testing modifications to a stored procedure can, in fact, be done at the unit level without having to test everything that calls it. That's frequently NOT the case with "managed" code.
Not that it would matter to the people you work for but I spend about half of each day finding performance challenged/resource intensive queries generated by front end code. It takes a whole lot of time for them to fix such things whereas, like I said, we can repair, test (both for functionality and performance impact), and deploy a stored procedure through the Dev, QA, UAT, and Prod environments very quickly, sometimes in less that 15 minutes if the repair is urgent.
If you were to interview our Dev Team and some of the consultants that we've hired to help us with our backlog of projects, you'll find that they too strongly recommend using stored procedures. In fact, when I find a troublesome bit of code being generated from the front end, especially for something with any complexity more than simple C.R.U.D., our Developers make the fix by changing the code to call a new stored procedure that they've written.
Shifting gears, people will site things like the fact that EBAY uses no stored procedures at all forgetting or not knowing the fact that EBAY maintains more than 600 servers. No one has ever tested there to see whether or not stored procedures would decrease that ridiculous number. My personal opinion is that they could get away with about 1/10th of that. if they used stored procedures.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2017 at 4:22 am
Thanks Jeff for sharing your thoughts with me.
I personnally think that SQL queries scares those who are familiar with algorythmic programming unlike set-based programming.
Did you or anyone else already experience this in your professional life?
October 19, 2017 at 7:48 am
rot-717018 - Thursday, October 19, 2017 4:22 AMThanks Jeff for sharing your thoughts with me.I personnally think that SQL queries scares those who are familiar with algorythmic programming unlike set-based programming.
Did you or anyone else already experience this in your professional life?
Yes but i generally see it as more a sign of bad team work than any bias against a specific technology. At a very basic level there's no difference between calling a stored procedure/view/tvf or calling a query crafted directly in the application except that the first requires the application developer to actually work with someone else to get the end result. I've run into the same situation working entirely within the same development platform, either you work as a team and people can work on their own components that tie together or everyone tries to do everything their own way.
October 19, 2017 at 9:44 am
rot-717018 - Thursday, October 19, 2017 4:22 AMThanks Jeff for sharing your thoughts with me.I personnally think that SQL queries scares those who are familiar with algorythmic programming unlike set-based programming.
Did you or anyone else already experience this in your professional life?
Yes. People tend to work with what they know or what they're comfortable with. A lot of folks are also under many misconceptions because of "personal experience" based on not knowing what they don't know and the resulting supposed "best practices" that become "best practices" only because enough people that don't actually know say it often enough, loud enough, and forcefully enough.
The key is that people also tend to be rather militant in their posture about changing what they do even if it's the right thing to do. They'll even (more frequently than I'll ever understand) resort to ad hominem attacks especially if they can't prove their way is better. That's why I always suggest testing with at least a decent Proof of Principle system to answer such questions in a totally unbiased fashion and without resorting to ad hominem attacks or "in my experience" types of answers myself. Diamonds in the rough are usually only found when you're digging. 😉
There are two keys to all of this... demonstrable code and education. If you (or they) can't demonstrate a clear advantage (and portability should never be considered to be an "advantage" because it's a myth except for some rather crippled mostly-only C.R.U.D. or ANSI-only stuff that doesn't take advantage of SQL extensions and capabilities in every database engine). A test (race) needs to be done on site to determine which is best for performance (under load, if possible) and resource usage along with more abstract decisions about maintainability, ability to rapidly deploy emergency changes, and the opportunities that may arise if both sides were better educated about the other side.
As I alluded to about where I work, initially everything having to do with the GUI was done in the GUI. Between the ORM code (always a "treat") that was generated and the poor understanding of what a database can actually do, we had multiple timeouts every day and very long screen update times. Rather than yell, scream, kick, and complain about the Developers, I started teaching them. They now take it upon themselves to find and make performance improvements (mostly by conversion to stored procedures) and have become justifiably rather proud of what they can now do. Part of what helped was I don't sit in isolation... I sit in the middle of the Developers so that I can both help them instantly and listen for when I think they might need help. Being a gruffy old ex-submarine sailor also helps me keep people off their hinnies and they appreciate that, as well.
As to your problem, remember that if you can make it seem like something is their idea or that they have an input that they can be proud about, it'll be an easier sell. Also remember not to play too many "Aces". Save those for when you really need to say "NO" for the benefit of the server and the data. Always remember that "A man forced against his will, is of the same opinion still'. All things come to those that are patient. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2017 at 3:14 pm
My opinion is that it depends. Some tasks are good to do in a stored procedure or function, while others are better to do in your code. It is a compromise. Finding that optimal condition often relies heavily on trial and error.
Some table manipulation tasks can be done rather quickly in a data table inside .NET. I've not done anything in Java in years, but I imagine it is similar in Java. Other things can be done fast inside SQL. I've seen cases where doing some stuff was faster in SQL and I've seen things run considerably faster in C#. One problem with pushing the stuff off to the application is then it is taxing on the end user machine (excluding things that are directly running against the database).
My preference is if performance is simialr, then stored procedures are better as we can index tables differently and optimize the stored procedure easier. Plus then it doesn't show up as an ad-hoc query and can be precompiled. If performance is faster with one method over the other, then we go with whichever is faster and easier to deploy.
In the end though, it is really whatever my boss says to do we go with.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 20, 2017 at 7:20 am
The first argument is security, specifically separation of powers.
The SQL Server database is the logical place to do anything that:
1) manipulates the data.
2) needs authorization based on the user/user group.
3) absolutely MUST NOT be ignored by the application.
4) needs security granularity (i.e. one user can read data, but not modify it, while a second can do both)
5) enforces referential integrity or data consistency.
On the heels of the first argument is the follow on ramification, SPs called by the user need only *1* permission (Execute) as opposed to one for each CRUD operation.
Of course SPs have a downside, it takes at least 4 (and possibly as many as 8-10) for each table to do simple CRUDE (CRUD + various kinds of lookups). But security is the enemy of convenience, so no pain no gain. 🙂
SPs are also a good way to encapsulate any logic that requires transactions, making rollbacks pretty much painless.
SPs also have the advantage of quick modification without having to recompile the program. That's less work for the developers...
In a related vein developers won't have to do the actual work of creating the SP (assuming a development DBA is available), or tuning performance. It also makes the applications smaller, simpler, and easier to test. (Testing the SPs, on the other hand...)
That should be enough to convince the Java purists. PHBs, on the other hand...eh.
October 20, 2017 at 12:12 pm
Shifting gears, people will site things like the fact that EBAY uses no stored procedures at all forgetting or not knowing the fact that EBAY maintains more than 600 servers. No one has ever tested there to see whether or not stored procedures would decrease that ridiculous number. My personal opinion is that they could get away with about 1/10th of that. if they used stored procedures.
I would love to know more about this, do you have numbers or citations to back up your assertion?
edit slight rephrasing 🙂
October 21, 2017 at 6:06 pm
patrickmcginnis59 10839 - Friday, October 20, 2017 12:12 PMShifting gears, people will site things like the fact that EBAY uses no stored procedures at all forgetting or not knowing the fact that EBAY maintains more than 600 servers. No one has ever tested there to see whether or not stored procedures would decrease that ridiculous number. My personal opinion is that they could get away with about 1/10th of that. if they used stored procedures.
I would love to know more about this, do you have numbers or citations to back up your assertion?
edit slight rephrasing 🙂
Sure... no problem.
This is the link that I saw years ago. It's from 2008 (damn... time flies... it seems like yesterday when I first saw that article during a heated debate about using stored procedures or not)
A lot can happen in nearly a decade. They now use a technique known as "MicroServices", which sounds very similar to what they were doing a decade ago but didn't have a name for it. Here's a later article on the subject.
To the best of my knowledge, they still use no stored procedures, no referential integrity, no ACID, and continue to use an "eventually synchronized" model. They don't even use JOINs in their code. It's all done via the front end. They basically use the database quite literally as "just a place to store data".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2017 at 6:26 pm
Here's one that caught my eye recently although it's already 4 years old. The thing that caught my eye was they were measure watts per transaction and translating that to a carbon footprint. With that, they made note that someone looking at the dashboard "slightly" lowered memory... which allowed them to take 400 servers offline saving nearly a megawatt in electrical consumption. If you do the simple math there, that means that each of those servers consumes nearly 2,500 watts! Could be me but that seems like a lot-o-watts for one server unless they're also including cooling and connectivity in that.
Here's the article...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2017 at 7:01 pm
What are their arguments for embedding the SQL in the application? Even if there were some marginal technical advantage, there is still issue that ownership of the SQL would then be moved from the domain of the DBA to the application developers. Is the app developer the smartest guy in the room when it comes to SQL, or are you?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 22, 2017 at 8:13 pm
Eric M Russell - Saturday, October 21, 2017 7:01 PMWhat are their arguments for embedding the SQL in the application? Even if there were some marginal technical advantage, there is still issue that ownership of the SQL would then be moved from the domain of the DBA to the application developers. Is the app developer the smartest guy in the room when it comes to SQL, or are you?
According to what I read, they only use the database as a place to store data. They "upload" it to some cache using code totally devoid of any joins and then they let the code have its way with the data that's been cached. I can see why they need hundreds of servers to do such a thing.
As for their Developers... who knows? Maybe they have all the Developers in the world that actually know how to get the current date and time and that's why I can't find anyone. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2017 at 5:55 am
Thanks to all of you for your answers ...
I'm sorry I can't reply currently because of closing issues running wrong!
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply