|
I don't believe that you were wrong in your answer.
Stored Procedures are, as you say, closer to the database and so can be more efficient. They are also more secure - the only data that is passed are the parameters, and the only data that is returned is the resulting dataset. OTOH, making changes to Stored Procedures requires changes to the database. The DBA therefore becomes the critical path for any changes.
(I assume that the programmers are working on their own development copy of the database, but the database instance is still the single point of change.)
Doing the same work at the application's data level is much slower (the data must be read from the database, processed, and filtered), is less secure (tables must be open to reading/modification over the network, data must be passed over the network and is open to snooping), but the DBA is no longer on the critical path. This may be more important to some organizations than efficiency and security.
Freedom is the freedom to say that two plus two make four. If that is granted, all else follows.
-- 6079 Smith W.
modified 4 days ago.
|
|
|
|
|
I think your answer was correct, but it all depends on the situation of course and how the company's database is managed.
|
|
|
|
|
So they want to change the ordering of data used for a report - they have to,change the app code, recompile, and redeploy - and they say your thinking is outdated ! I wouldn't want to work with them at any price. Walk away - and yes, I think you did dodge a bullet. 
In a closed society where everybody's guilty, the only crime is getting caught. In a world of thieves, the only final sin is stupidity. - Hunter S Thompson - RIP
|
|
|
|
|
Cezar Lamann wrote: Did I dodge a bullet
Yes.
Your response of the use of DB stored procedures being situational was correct, them assuming that you were a proponent of using SPs all the time is their mistake and pretty stupid.
There also maybe a person in the team who could've been your peer or worse, superior who do things their way and won't change for whatever reason. Keep miles away.
|
|
|
|
|
Yeah, you dodged a bullet.
You even told them why it's better than doing it in-code. It seems they've forgotten why you don't do everything in-code.
Their way seems to be a pile of security holes with performance issues. They may also be trying to save money on IT operations by skipping the DBA's required to write and maintain the databases and their SQL code.
Congratulations, Neo.
|
|
|
|
|
Dave Kreskowiak wrote: Their way seems to be a pile of security holes with performance issues.
I'm going to print this on a poster. This sentence is poetry.
GCS/GE d--(d) s-/+ a C+++ U+++ P-- L+@ E-- W+++ N+ o+ K- w+++ O? M-- V? PS+ PE Y+ PGP t+ 5? X R+++ tv-- b+(+++) DI+++ D++ G e++ h--- r+++ y+++* Weapons extension: ma- k++ F+2 X
The shortest horror story: On Error Resume Next
|
|
|
|
|
I think you gave the best answer.
I also prefer to have the code in the application rather than the database, but I consider the particular situation.
|
|
|
|
|
There was one thing you said that would've made me worry too, but I suspect you dodged a bullet. Here's the proper answer to that question... To give you context, I've been in this game since the 90s... 94 is when I started coding and 97 I was working as a coder. Anyway, this being the Internet expect less experienced people to disagree with what I'm about to say... ignore them. But, the proper answer to that question is:
It depends. 
To start with, no expert DBA will accept a code first answer even if you're using an ORM, etc. Only coders that refuse to learn DBs buy into that. Don't ever buy into code first UNLESS they talk about migrations. Then they're smart. But, you can (and should) do migrations in raw SQL. So, that's not an excuse to not learn DBs or DB design. Otherwise, they're mentally lazy, but I digress. So, it would be nice to know "why" they believe their buzzword nonsense.
When to use a sproc (stored procedure)
- When you need custom logic to run that's too big for a db function. Say maybe in response to a trigger or you want to scaffold out something, etc. Never put this logic in code. Yes it's slower than code, but now you tightly coupled your DB to your code. Good luck reusing that DB. Not to mention, you may potentially have to transfer large amounts of data over the wire, which I can promise will be slower if you have a decent server not under heavy load. That's it. That's the only reason. People will talk about security, but a view or function will do the same thing.
When not to use a sproc (stored procedure)
- Don't use a sproc when a DB function would do the job. DB functions are meant to be concise and point driven.
- Not for encapsulation/security. That's what a view is for.
- Not for complex reporting. I suspect this is what made them potentially not like your answer. Sprocs are slow. Reporting is slow. A performant DB should have separate, flattened, read-only reporting DB for complex operations.
Now, all of this is invalidated if we're talking about distributed computing, using NoSQL, etc. Maybe that's their case, but it sounds like they're just following the hype train. This may also be invalidated if we're working with complex graphs in GraphQL. So, this assumes we're speaking of the traditional client/server paradigm.
Edit: Take away point!!!!
Here's the important thing though, if they were smart enough to know the good reasons to break the mold... they would've mentioned in the interview. If they didn't, they're just probably clowns or cannot communicate very well. A good interview with two educated people is awesome. It's a chance for two pros to chat. I've learned things in interviews where peeps liked me. I've taught things in interviews where I still liked peeps. If it feels like they were being zealots... trust your instinct. Two real pros can chat and learn or at least explain their reasoning. It's only the kiddies who think they're talented that can't.
Jeremy Falcon
modified 4 days ago.
|
|
|
|
|
I got your point. And the infamous "It depends" is what I really wanted to say (maybe I should have started my answer with this instead of "Well, sprocs have their place".
I wouldn't blindly rule out a sproc, without knowing what is in front of me and what I should do, functionality-wise (like your last sentence - "all of this is invalidated if we're talking about distributed computing. This may also be invalidated if we're working with complex graphs in GraphQL").
Of course, I would NOT do, in this day and age, just use the .NET code as a shim for exposing sprocs and have the whole business logic in the DB (yes, I worked in an ASP Classic project, that was like this, and I hated every single second I worked on that project).
I have also done my fair share of data warehousing and CQRS, and I understand that for OLAP workloads, a separate DB with denormalized tables/materialized views (e.g. "star schemas") can do wonders.
Heck, I even ask this kind of question when I'm on the interviewer side (e.g. for senior dev positions), to filter out the ones that are 100% on Sproc side or 100% on code-only side. In other words, I knew how to answer this. But to get a rejection based on this (maybe other non-disclosed things as you mentioned), has bummed me out.
|
|
|
|
|
Cezar Lamann wrote: has bummed me out.
Been there, done that. Welcome to life. 
Some people will make knee-jerk choices. Especially if said people don't put much effort into interviews. I've seen a lot of devs just want to Google interview questions a day before and roll with that. No real effort put into it. To some extent, we all do it. Like if you meet a random dude on the street with nasty teeth, your first thought is dude don't brush. You don't always take the time to learn his backstory.
And then sometimes people just suck at their jobs. Including interviews. Even at companies like AWS. Don't me wrong... Amazon/AWS also has some really, really intelligent senior devs. Even some awesome junior ones. But, on that occasion...
Or maybe it was a personality clash. Could've been two egos going at it rather than just a chill chat. Who knows. Either way, I hope you get the next one man.
Jeremy Falcon
|
|
|
|
|
Cezar Lamann wrote: I wouldn't blindly rule out a sproc
I wouldn't blindly rule out any technical solution without context and evaluation.
GCS/GE d--(d) s-/+ a C+++ U+++ P-- L+@ E-- W+++ N+ o+ K- w+++ O? M-- V? PS+ PE Y+ PGP t+ 5? X R+++ tv-- b+(+++) DI+++ D++ G e++ h--- r+++ y+++* Weapons extension: ma- k++ F+2 X
The shortest horror story: On Error Resume Next
|
|
|
|
|
Your answer showed an excellent understanding of the tradeoffs in software design. The fact that they told you otherwise tells me that you didn't dodge a bullet - you dodged a nuclear weapon.
|
|
|
|
|
I recently vented here regarding an issue of SPs vs. code-behind. You are correct, each has pros and cons and the choice depends on the situation. Calling SPs outdated is BS. You dodged a bullet.
"Go forth into the source" - Neal Morse
"Hope is contagious"
|
|
|
|
|
Cezar Lamann wrote: and that they do everything inside application code,
I code to the business and not to odd-ball extremes that some person with no objective data came up with.
Using stored procedures can be much more effective that at some tasks then attempting to do it in code. I have seen systems that attempted to do the work only in the application and their performance was orders of magnitude larger than it could have been.
I have also seen cases where because there was no clean separation of data access (because after all it should be right were it is needed right?) that a conversion to a different database took years (actual years) to do. And that conversion was driven by real business needs and not just because the newest CTO decided they wanted something different.
|
|
|
|
|
After 25 years at CodeProject I'm making a change. I've spent nearly a quarter of a century helping developers share knowledge, learn, and overcome the hugely rewarding challenges of software development. It's been amazing but my focus needs to shift closer to home with a focus on family, wellness and balance. While I'm certainly sad to be moving on, I'm proud of what David Cunningham, myself and the rest of the team achieved.
I must have missed the announcement - I had to see this first over on LinkedOut[^].
And it looks like Matthew's moving on[^] as well.
The end of an era, and we didn't even get the chance to get blind drunk and make fools of ourselves raise a toast. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Let's hope the site won't change too much.
GCS/GE d--(d) s-/+ a C+++ U+++ P-- L+@ E-- W+++ N+ o+ K- w+++ O? M-- V? PS+ PE Y+ PGP t+ 5? X R+++ tv-- b+(+++) DI+++ D++ G e++ h--- r+++ y+++* Weapons extension: ma- k++ F+2 X
The shortest horror story: On Error Resume Next
|
|
|
|
|
Sad to see them go, but they have earned a well-deserved rest and I wish them all the best 
|
|
|
|
|
So do I, but still... there are different ways of doing things. (see my answer below)
M.D.V. 
If something has a solution... Why do we have to worry about?. If it has no solution... For what reason do we have to worry about?
Help me to understand what I'm saying, and I'll explain it better to you
Rating helpful answers is nice, but saying thanks can be even nicer.
|
|
|
|
|
You don't think Kent Sharkey joined them too?
Haven't seen the Insider for two weeks now 
I missed the announcement on CP too, but read it on LinkedIn last week.
|
|
|
|
|
I don't know, but I'm starting to suspect there's something in the air - Chris Alcock of "The Morning Brew[^]" still hasn't returned from the 2-week break he went on at the start of August.
Combined with the terrible redesign of the MS Dev Blogs[^], I'm starting to think I might actually have to stop reading news and start doing work soon! 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard Deeming wrote: I might actually have to stop reading news and start doing work soon!
|
|
|
|
|
Actually seems like the whole team has been on a two week vacation.
At least they left the hamsters.
|
|
|
|
|
Sander Rossel wrote: You don't think Kent Sharkey joined them too?
People said yes, I think several have gone.
The "About us" is way shorter that it used to be and I have been told it is not up to date anymore (several of the people already working in other place as per their profile in LinkedIn)
M.D.V.
If something has a solution... Why do we have to worry about?. If it has no solution... For what reason do we have to worry about?
Help me to understand what I'm saying, and I'll explain it better to you
Rating helpful answers is nice, but saying thanks can be even nicer.
|
|
|
|
|
Wow! @Chris-Maunder it's a surprise, but I guess 25 years is a long time in one job, and anyone is going to get kinda stale after that.
Whatever you do, I hope it's good for you, and remember that we will miss your guiding hand enormously. Thanks for setting the site up, there is a whole generation of developers that has benefited from what you created.
And just remember, Dilbert got there first: Departure notice[^]
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Chris Maunder moving on
In a closed society where everybody's guilty, the only crime is getting caught. In a world of thieves, the only final sin is stupidity. - Hunter S Thompson - RIP
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
