columnae (9/16/2014)
@SQLRNNR: That is not the ORM deciding, that is a dev neglecting.Typically a dev that does not grasp the concepts of an ORM and the responsibilities that come with it.
Just like you can screw up performance by writing a really bad SQL statement, you can seriously screw up an ORM by not thinking about performance and only about ease-of-use.
It would be analogous to writing a terrible SQL statement and then being surprised that the DBMS wasn't able to optimize it to something speedy.
An ORM is NOT a silver bullet or a magical now-I-can-shutdown-the-brain kinda contraption.
In this case, I am certain that the dev enumerated a list of records, traversing the relations without pre-loading that set of records with the referenced relations (eager loading).
The ORM had no idea the dev was going to traverse those relations when it fetched the records and had to fetch the related records one-by-one when the dev enumerated through the records.
EF, for instance, fetches those related records by itself (no code needed), to help the dev. This is seriously helpful in most cases, but a new dev WILL create those 10K queries.
The dev should have fetched the records, eagerly loading the relations as well. EF would have issued 1 query to fetch all of it, including the related records.
The dev could then blazingly fast use all the mega complex logic he/she wanted on the set, and after that issue the 'save' instruction to EF. EF would issue one batched request to the DB to store all changes.
I referenced EF, but (N)Hibernate and most of the other ORMs work the same way.
Sometimes I just feel like a one-legged man in an a$$-kicking contest trying to explain this one.
In short: replace the dev, not (necessarily) the ORM.
A typical PEBKAC.
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).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events