|
On heavily loaded client/server applications, stored procedures can cause major bottlenecks. I was on the fringe of at least two projects where anything more than simple stored procedures were prohibited for this reason (and they had tests to prove it.)
|
|
|
|
|
While I don't doubt what you're saying, the logic escapes me. Either way, you have to hit the database and fetch the data.
What's special about a stored procedure that would make it more of a performance bottleneck than retrieving the same data from the code? Seems like the db is serving up the same amount of bytes either way.
|
|
|
|
|
Because stored procedures take computing time and aren't always trivial. Stored procedures aren't just a few lines of code to do, say, a select or simple update trigger, they can be entire programs which may have little to do with setting/retrieving data, but in manipulating that data. So, who should manipulate the date, the client or the server? (The answer is, of course, it depends.)
|
|
|
|
|
Well, if you're writing entire programs, yeah, I get that. If I want procedural code, I want a procedural language.
But it still sounds to me like if you're just running queries to fetch data or do crud stuff, there's no benefit whatsoever to moving it into the application code. I get the impression people were hot to move that sort of thing to LINQ only because it was new and shiny.
|
|
|
|
|
Christopher Duncan wrote: I get the impression people were hot to move that sort of thing to LINQ only because it was new and shiny.
My impression as well since the alternative to stored procedures is just writing normal queries. A few months ago, I used a tool that refactored some code into LINQ. The end result was only slightly shorter and much more difficult to understand than the original code. Plus, the LINQ code sucked in several more assemblies. I backed out the change.
(My impression is that most things in software development are about being "new and shiny", which is a major headache when you have to maintain the code for someone who constantly got suckered by this.)
|
|
|
|
|
Given that you've been in the game for a long time, I'm glad to hear I'm not the only one who sees this as more resume enhancement / religion than a functional improvement in development.
By the way, just popped over to look at your profile. Do you still fool around with film stuff?
|
|
|
|
|
Christopher Duncan wrote: Do you still fool around with film stuff?
Not much since the 1990s, when I created several interactive videos for edutainment products (and wrote the software.) Lost money, but had a blast. Since then, my biggest project was editing my daughter's wedding video (I should have done a divorce video to bookend that....) I may get back into it one of these days.
|
|
|
|
|
A daughter's wedding is a project in and of itself. I figure the insanity of a major and largely unrehearsed production put on by amateurs is that if you still want to be married by the time it's all done, you should get married. Apparently from your comment that comes with some caveats.
I'm enjoying the video stuff I've been playing with, but monetizing film, or video of any sort for that matter, is about as easy as winning the lottery. I'll do more to support my own creative work as it's just another medium in which to communicate, but it would be nice if there were a way to make a buck at it in the process.
|
|
|
|
|
Joe Woodbury wrote: My impression is that most things in software development are about being "new and shiny"
The band wagon that you see in the distance always looks more shiny than the one that you are on. And since you are not on it and even once you get on, you will have no idea how often it breaks down or why.
|
|
|
|
|
Joe Woodbury wrote: Because stored procedures take computing time and aren't always trivial
Versus for example dragging the entire database across the network, doing calculations on a single client box and then sending the entire database back? (And just to be clear that isn't hyperbole - I actually encountered a system that did that and it would only use one client machine.)
Joe Woodbury wrote: The answer is, of course, it depends
Exactly. Which is far different than claiming that stored procs are always bottlenecks.
|
|
|
|
|
jschell wrote: Exactly. Which is far different than claiming that stored procs are always bottlenecks.
This pisses me off. I NEVER said that stored procedures are always bottlenecks. I said they CAN be bottlenecks and they can be. (Can is a conditional. Unfortunately, this isn't the first time you've distorted what was written, creating a straw man and then attacked the writer. It's getting tiresome. Please learn to read before replying.)
|
|
|
|
|
Joe Woodbury wrote: I NEVER said that stored procedures are always bottlenecks
You certainly did imply that when you also said "where anything more than simple stored procedures were prohibited for this reason ".
You did not qualify that statement nor did you qualify your following statement about the tests used to prove this with the original comment. Your follow on post, and not this one, qualified that you were talking about a specific case with a business model unlikely to ever be applicable to most businesses.
Joe Woodbury wrote: Please learn to read before replying
You said "where anything more than simple stored procedures were prohibited for this reason".
Did you meant that in fact that complex ones were allowed when you said "prohibited"? Did you have some qualification for "simple" which suggested that only really, really complex ones where prohibited or, as I took it, did you mean anything more basic than CRUD?
When you said that there were tests that proved your assertions did you qualify that with the specific business case where it applied? Because I didn't see that when I "read" it. (Nor in this reply either.)
There is of course a difference between qualified and unqualified statements as well as a difference between what one meant and what one wrote.
|
|
|
|
|
jschell wrote: You certainly did imply that when you also said "where anything more than simple stored procedures were prohibited for this reason ".
Here is my comment:
"On heavily loaded client/server applications, stored procedures can cause major bottlenecks. I was on the fringe of at least two projects where anything more than simple stored procedures were prohibited for this reason (and they had tests to prove it.)"
Note "heavily loaded" and can. In the second sentence, the phrase "for this reason" builds on the conditionality of the first sentence. This is all a single paragraph, where one sentence builds on the other.
What you are going, by contrast, is taking my words out of context. You then argue by setting up straw men and knocking them down and finally you blame me for making sure you understand what I wrote. You are a fool.
|
|
|
|
|
Joe Woodbury wrote: stored procedures can cause major bottlenecks
You are claiming that for every single bit of SQL that implementing that in the application is going to be significantly faster?
And no one has actually discovered that until now?
Not to mention of course that requirements, architecture and design have far more impact on systems in terms of performance. A poor data model can completely destroy a system regardless of how it is implemented.
Joe Woodbury wrote: and they had tests to prove it
I am guessing they had "benchmarks" to "prove" it.
I suspect that for most of the major technologies that I am familiar with that I can come up with a benchmark in technology X that proves it is better than technology Y. Naturally I will be able to reverse X and Y.
|
|
|
|
|
You do you understand the meaning of the word "can" right? You are arguing assertions I never made nor even discussed.
To turn this around, are you honestly asserting that stored procedures have zero CPU cost? Moreover, have you seen what some developers put in stored procedures?
jschell wrote:
I am guessing they had "benchmarks" to "prove" it.
No, actual tests with actual databases with terabytes of data which could get under extremely heavy loads due to tens of thousands of clients. Offloading some of the processing to clients helped immensely.
|
|
|
|
|
Joe Woodbury wrote: You do you understand the meaning of the word "can" right?
However you also said "anything more than simple stored procedures were prohibited for this reason"
Joe Woodbury wrote: To turn this around, are you honestly asserting that stored procedures have zero
CPU cost?
Nope. But there is a big leap from there to banning everything.
Joe Woodbury wrote: Moreover, have you seen what some developers put in stored procedures?
And that has what to do with anything? I have seen a C++ class with 200,000 lines of code. I have seen an application that dragged the ENTIRE database across the network, computed on it, and then sent it back and that specific design was a bottleneck. I have seen a VP lock up a entire database and idle 200 call center employees on a weekly basis because he insisted on having direct access to the production database.
All of those however are PROCESS PROBLEMS. They have nothing to do with technology.
Joe Woodbury wrote: actual tests with actual databases with terabytes of data which could get under
extremely heavy loads due to tens of thousands of clients
Which seems like something that qualify the initial post with would have made it much clearer. Most businesses will never see anything like that however.
Joe Woodbury wrote: Offloading some of the processing to clients helped immensely.
One might suppose however that other businesses have other business needs and thus restrictions to one business environment should not be blindly applied to all industries and all businesses.
|
|
|
|
|
jschell wrote: However you also said "anything more than simple stored procedures were prohibited for this reason"
Do you understand the concept of an illustration to make a point? To anyone with the slightest literacy, this was clearly an illustration of why stored procedures CAN be bad. Now, I could understand you misreading one comment, but you continue to argue against assertions I never made and even make statements that support what I wrote, but in condescending way. Despite all this, you never refuted my actual points. Based on this and previous posts by you, I can't help but wonder if you are being intentionally antagonistic and argumentative.
I don't know how old you are or how experienced in the field of computer science, but you come off as very arrogant and immature. When your errors are pointed out, you become combative and change your arguments as well as turning them back on the original poster as though it was all their fault. This borders on narcissism and makes dealing with you very unpleasant.
|
|
|
|
|
Joe Woodbury wrote: Do you understand the concept of an illustration to make a point?
Your original comment did not seem like an example of one case where it could be a problem. It is that comment to which I responded of course.
The statements, taken together, seemed to suggest strongly that in most cases stored procedures should be avoided.
Joe Woodbury wrote: Despite all this, you never refuted my actual points.
I didn't need to because you provide further qualification in follow on posts that made it clear (presumably) that you were referring to a very limited problem domain space.
That however doesn't alter the fact that your original comment did not make that clear.
Joe Woodbury wrote: When your errors are pointed out,
Your original comment was made without qualification. I disputed the totality of that statement. Best I can tell you are now agreeing that your original comment only applies to a limited domain.
I don't see an error in my part in terms of your lack of the original qualification.
But I can assure you that I am more than willing to accept when I am in error - when in fact that is the case. Versus of course someone just repeatedly claiming that that is the case.
Joe Woodbury wrote: ...and makes dealing with you very unpleasant.
Best I can suggest for that is that you park the emotionalism at the door.
|
|
|
|
|
Wasn't LINQ to SQL Dead on Arrival?
modified 20-Oct-19 21:02pm.
|
|
|
|
|
I remember them releasing competing db platforms at the same time. LINQ to SQL, LINQ to Entities, Entity Framwork, Apparition Architect, yada, yada.
That's one of the reasons I stayed away initially. I thought it was the height of stupidity. In other words, coming from MS, I didn't give it a second thought.
|
|
|
|
|
Ok, so here a link to a discussion by people who did use it in production:
LINQ Discussion[^]
"with my experience with EF and Linq,
If you are developing an application for less than 10-20 users and not expecting any huge data, go with EF and LINQ
Otherwise,
Never ever use LINQ and EF. I never saw many developers who care about performance during development. So don't adopt architecture that cost you more in the long run.
"
modified 20-Oct-19 21:02pm.
|
|
|
|
|
I would go with Entity Framework + Stored Procedure. With EF the advantage that we can get is the OR Mapping.
Now the question goes to you, If you want to take advantage of the technology to simplify or make it better then go with EF Designer or Code first approach and also if you want to do some code re-factoring and wish to use your existing store procedure, Yes you can do so.
Some interesting articles
http://msdn.microsoft.com/en-us/data/gg699321.aspx[^]
http://www.dotnetcurry.com/ShowArticle.aspx?ID=938[^]
Thanks,
Ranjan.D
|
|
|
|
|
Don't mean to be dense, but I'm not really following the advantages here.
What benefit do I gain by moving queries out of stored procedures and into compiled code?
|
|
|
|
|
Quote: What benefit do I gain by moving queries out of stored procedures and into compiled code?
I see two different set of peoples arguing about the Usage , Performance, Benefits of Stored Procedures , it's maintainability etc. I'm fine with either way.
I don't know how you really want to go with, I say it depends on how we are deciding to go with. That's the reason I said , The Entity Framework doesn't really restricts the usage of Stored Procs , You can still make a call but what you get with EF is the result set as object. Is it good 
I think if you are planning to move out the queries out of Stored Proc, you will end up in writing tons on code, though it's compiled I don't think there would be a great benefit. Some times you might even end up with performance issues.
I have seen several hundreds to thousands lines of stored procedures, If some one tells me to move them out of it to EF compiled queries , I would question why I'm doing this ?
If you are working on some new products and wish to tryout EF with mix and match SP's it's good. But I don't think it's a good idea to perform code re-factoring for the existing ones.
Thanks,
Ranjan.D
|
|
|
|
|
Actually, I'm starting a couple of new projects, but I don't see trying it out unless there are distinct and tangible benefits. Looks like it would be cool for non database stuff, but for a relational db, I'm not hearing anything it does for me that I don't get out of stored procs.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
