September 17, 2014 at 6:53 am
You jumped to some pretty big conclusions there.
1. I said ORM implementation - didn't say it was the fault of the ORM per se.
2. The query in question is not loading any relations or traversing anything additional. It is a set based return to display a simple result set from a stored procedure call. This call by EF is being executed by EF 30 times for each single click. There is nothing in code to call it that many times. Nor is there any reason for it to execute 30 times except that there is a bug in EF.
3. ORMs are great. Many clients implement them, run into massive performance issues and then call for help. I am more than happy to help a client get around the performance issues imposed by such implementations.
Can an ORM help a DEV code faster? Sure! But coding faster does not equate to faster code. Hence my previous comment in agreement that it should be perf tuned - as with all code. In this case, the code definitely needs to run through the hands of a seasoned DBA (a little garlic sea salt seasoning would do).
May come as a surprise that I largely agree.
I DID jump to conclusions. At least 95% of all problems I see with ORMs are the one that I describe, but there are other common mistakes.
All that I have seen are dev related.
I agree
* ORMs cause performance degradation, even if it's only a little, depending on what you're asking it to do. And THAT depends on your level of expertise (complex DB stuff-> use SPs, otherwise you can opt to use the ORM query language).
* ORMs are widely abused, causing you and me a lot of work, usually because of performance. Wether you like that or not is your own decision.
* Seasoned DBA's are always necessary. Period. But please do not underestimate good devs - they use query profilers and monitoring, even (or maybe more so) when using an ORM. I do. And I am by no means the sharpest tool in the shed.
Argument #2 really misses the point though. This discussion is not about EF, but about ORMs in general (please re-read the OPs statement if in doubt).
You can't say cars are bad just because drivers make a lot of accidents.
Nor can one say DBs are bad just because wannabe DBA's screw up the tuning.
You can't say ORMs are bad just because the shaggy devs fail to RTFM and fail to use it correctly.
I have seen horribly performing SQL Server boxes more than I care to, but you don't see me generalising that SQL Server is bad.
Please practice what you preach, even though I agree that I jumped to conclusions aswell.
However, if you think the issue with EF is important anyway....
You sound like you're close to that offending piece of code.
Of course I cannot guarantee there is no bug in EF (my weapon of choice), and although I have seen some quirks in the early days, and it still has some .... urr .. interesting constructs, I have never seen a blatant bug like you describe. I think it would not be such a widely used ORM of it had one like that. But then again, I could be wrong.
But, seriously, I am happy to have a look at the code to make sure it's not one of the 95%.
No pun intended.
In general (not directed at you):
Don't get me wrong. I am no ORM advocate, but as a dev and a DBA, the amount of FUD about ORMs in this thread does not make this discussion any better or more realistic.
It is sad to see that somehow devs seriously underestimate the work of DBA's or their importance in product development and maintainability, and see DBA's do the same thing vice versa. Mostly because of lack of knowledge on both sides.
Work together and have the best of both worlds. You CAN. I am doing it. Nobody's putting anyone out of a job here.
Devs can be pretty obnoxious to DBA's, but I have seen DBA's just refusing to work with devs altogether.
Why people? Why?
September 17, 2014 at 7:02 am
Evil Kraig F (9/16/2014)
courtney.smith (9/16/2014)
At the end of the day stored procs or views start to become a maintenance nightmare on any sufficiently large system.I would posit that it's easier to maintain the views and procs on a large system where the calls are explicitly made from the component in question then having to root through the ORM and abstractions to figure out what needs to be modified for a front end change.
I have maintained databases with 200-300 tables and roughly a 1000-1200 procs for a massive online E-Commerce site. Though, admittedly, we had about 4 views and the majority of the work was concentrated into around 100 procs (or so, I never did finish my accounting on that). The rest were for EOY reports or taxes, one shot procs for delivering the un-mailed invoices to the mailing room, stuff like that. Once an hour kind of items, at most.
Naming conventions, intelligent Schema usage for module identification, and communication between the front and back end of the code for determination of usage keep that maintenance from being a nightmare. You do have to bring your brain to work every day though. It can be easy to trip. I can't see a sufficiently large front end being any better, though, ORM or not.
My experience definitely and clearly leads me to believe that there is a sweet spot for ORM usage where it is literally 10 times clearer and easier to maintain business rules than through a complex series of tightly coupled procs and views. I'm not trying to convince anyone - it's just my experience on the projects I've worked on. When you have so many procs working together they are are essentially one of a piece but not intrinsically linked (as in a compiled code library) and - for my experience - it can be an absolute nightmare to find the problem when something goes wrong or when change happens. You can say this is just poor construction, like the prior example of badly written ORM access that repeats queries, but it appears to happen more easily in the proc only realm.
Of course I accept that others experience to the contrary is quite valid for their own experience - I guess it all comes down to the scarring experiences with dross code (of whatever nature) we have had.
@Yet Another DBA
I am starting to feel a bit sorry for you! :hehe:
September 17, 2014 at 2:56 pm
"Wah. Wah. It should all just automatically work. Wah Wah."
Lazy.
Showing my age here but YES. The whining seems to come from those who have very quiet keyboards and mouths that swing between their ears like a skipping rope.
I come from an era where stored procs were widely considered to make things easier. Think of it as a public or friend method and tables/views as a private objects.
The hypocrisy of saying SQL stands for "Scarcely Qualifies as a Language" to saying that maintaining stored procs is "too hard"!!!!!
Have you tried reading Java code pre Java8?!?!? Clojure anyone? C++ is not one for the faint hearted. I know that the older we get the better we were but I don't remember old school developers moaning so much. Applications don't seem to be developed any faster although there seems to be a plethora of tools and frameworks to help.
I've found that ORMs can work perfectly acceptably to all concerned but it takes a competent DBA and a competent developer (if they aren't the same person) to configure them and establish good working practices for the development teams (consisting of multi-skilled practitioners). PBCAK.
Most OLTP stored procs are of simple to moderate complexity. The "too hard" stuff I've seen has generally been in the overnight maintenance and data prep jobs.
Business logic in the database? What if what you want to do is naturally set based? Are you going to use a purist argument as an excuse for doing something just plain daft!?!!?
I had a meeting with someone who, when asked why they use Hive/Impala rather than MapReduce or some of the newer programming frameworks, turned round and said that although the frameworks were technically superior, SQL got the job done in the timescales the customer needed and successfully delivered revenue and could be easily modified if required. The skills gap between SQL and the business was tiny compared to the skills gap between MapReduce and the business. I can't tell you who the person was due to NDA but most people would kill to get to see him talk at QCON.
June 6, 2018 at 3:03 am
SQLRNNR - Tuesday, September 16, 2014 11:09 AM2. The query in question is not loading any relations or traversing anything additional. It is a set based return to display a simple result set from a stored procedure call. This call by EF is being executed by EF 30 times for each single click. There is nothing in code to call it that many times. Nor is there any reason for it to execute 30 times except that there is a bug in EF.
Very old thread, sorry, but just pointing out MiniProfiler will catch these problems straight away, and they are invariably easy to fix. (We used to profile everything directly but MiniProfiler and similar make it so much easier nowadays).
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply