Let the optimizer do it's thing -- wrong

Question

Let the optimizer do it's thing -- wrong

Viewing 12 posts - 46 through 56 (of 56 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

ChrisM@Work (12/15/2016)
Indianrock (12/14/2016)
Yes ORM-generated sql. Ours is a version called Domain Objects written by a former developer here. Others include Entity Framework and Hibernate. That sql code was not written by a human being.
So it allows a company to throw up a web product quickly where developers don't have to know any sql.
Might be fine if you start turning away customers once your largest table has 1 million rows.
[/url]
From the article:
"First, some best practices:
• Do not use built-in connection pooling. It is not intended for production use.
• Do not use default caching. It too is not intended for production use.
• For complicated queries, use inline SQL or stored procedures. Hibernate is not made to cover every scenario."

I would say, don't use inline SQL as it requires code changes to the application when changes to the SQL are needed. Use stored procedures when code changes are needed. If the inputs and outputs don't change then no changes to the application when changes are made to the stored procedure(s).

Sergiy SSC Guru Points: 110206 More actions · Answer 2

Indianrock (12/14/2016)
Yes ORM-generated sql. Ours is a version called Domain Objects written by a former developer here. Others include Entity Framework and Hibernate. That sql code was not written by a human being.
So it allows a company to throw up a web product quickly where developers don't have to know any sql.
Might be fine if you start turning away customers once your largest table has 1 million rows.
[/url]

yeah, yeah, yeah.

blah, blah, blah.

ORM, Entity Framework, Hibernate.

Blame tools.

Now, open the code of you project and find what makes ORM generate this piece of s..t (sorry I missed to letters in the middle):

INNER JOIN ( SERVICED_COLLATERAL_GROUP_ITEM AS ServicedCollateralGroupItem13

INNER JOIN ( SERVICED_COLLATERAL_GROUP_ITEM AS ServicedAccount14

....

) ON ServicedCollateralGroupItem13.SERVICED_COLLATERAL_GROUP_ITEM_ID = ServicedAccount14.SERVICED_COLLATERAL_GROUP_ITEM_ID

No tool would invent a join of 2 instances of the same table the same field.

It must be how it's written in the front end code.

Find it and fix it.

So far it's not ORM which created the bad code.

It's the developers who have no idea what are they doing.

_____________
Code for TallyGenerator

Indianrock SSC-Insane Points: 20333 More actions · Answer 3

Regarding the modifications Lynn Pettis made to the OP query. I attached the plan.

------LYNN PETTIS CHANGE

-- 92 rows in QA in 03:29 first run 10 seconds 2nd run Did not use option recompile

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Indianrock (12/16/2016)
Regarding the modifications Lynn Pettis made to the OP query. I attached the plan.
------LYNN PETTIS CHANGE
-- 92 rows in QA in 03:29 first run 10 seconds 2nd run Did not use option recompile

Plan? Original query timing?

Indianrock SSC-Insane Points: 20333 More actions · Answer 5

This query could run as much as 15 minutes before. I attached the plan above, I thought, but again for this post.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Indianrock (12/16/2016)
Regarding the modifications Lynn Pettis made to the OP query. I attached the plan.
------LYNN PETTIS CHANGE
-- 92 rows in QA in 03:29 first run 10 seconds 2nd run Did not use option recompile

Hopefully this is because you are doing that in QA which is underpowered. That run difference is REALLY indicating substantially below-par IO (or the query suffered from extended blocking on the first run).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 7

Lynn Pettis (12/14/2016)
May help show the powers that be that ORMs don't always know what's best.

Precisely. If you can't prove it with code, you can't prove it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

x SSC-Insane Points: 23660 More actions · Answer 8

I like Martin Fowler's take on ORM's, and given the lack of mainstream pedigree of "Domain Objects" I think Mr. Fowler's discussion on this is particularily apt for this case.

Listening to some critics, you'd think that the best thing for a modern software developer to do is roll their own ORM. The implication is that tools like Hibernate and Active Record have just become bloatware, so you should come up with your own lightweight alternative. Now I've spent many an hour griping at bloatware, but ORMs really don't fit the bill - and I say this with bitter memory. For much of the 90's I saw project after project deal with the object/relational mapping problem by writing their own framework - it was always much tougher than people imagined. Usually you'd get enough early success to commit deeply to the framework and only after a while did you realize you were in a quagmire - this is where I sympathize greatly with Ted Neward's famous quote that object-relational mapping is the Vietnam of Computer Science.

https://martinfowler.com/bliki/OrmHate.html

x SSC-Insane Points: 23660 More actions · Answer 9

Indianrock (12/14/2016)
Yes ORM-generated sql. Ours is a version called Domain Objects written by a former developer here. Others include Entity Framework and Hibernate. That sql code was not written by a human being.
So it allows a company to throw up a web product quickly where developers don't have to know any sql.
Might be fine if you start turning away customers once your largest table has 1 million rows.
[/url]

This doesn't seem like advice I would follow here:

So, keep your data model closely aligned to the object model.

I'm thinking that the better path forward would be to keep your object model closely aligned with the data model. Remember, objects are a SUPERSET of what can be stored in a database in a reasonable fashion. RBAR for instance sucks in SQL Server but works fine in OOP, iterating is bread and butter in dot net for instance. You can have REFERENCES in OOP, but how do you implement those in SQL Server? That's right, instead of an object reference native to dot net or whatever, you build a key and an index and pay the cost in traversing the index. You can build any sort of data model in OOP and get away with it, but in SQL Server you really are best served following the relational database paradigm, so why would you try to duplicate objects in SQL Server, without all the nice tools an actual OOP language makes available for use?

Also, I object to indianrock's post in general, he's holding up some obscure "domain objects" I can't even read up on as some strawman we're supposed to use to beat up ORM in general, but that's probably me just being picky 🙂

Indianrock SSC-Insane Points: 20333 More actions · Answer 10

Here is what much of our ORM code is based off of:

[/url] The author used to work for us but apparently hasn't updated it in quite a while.

I'm sure much customization has been done by development in the C# code outside of domain objects. But the real point of my post was that many will find ourselves having to delve into query hints and other tweaks to "help" the optimizer. So many blogs tell you to trust it but there are circumstances that call for more. Tweaking without knowledge is dangerous, no question about it. But I think the sermon preached so often about trusting it may cause many to refrain from doing things that must be done.

No need to tell me that the code and database need a re-design -- finally, that has been driven home by customer calls.

I think the other thing I'm discovering is that when there isn't deep sql knowledge in the dev/qa world at your company, getting approval to make needed changes is like pushing things through a non-functional committee.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

I have had good success at with ISVs and development groups convincing them they need a different/better mousetrap by showing them before and after versions of their code that is thrown at/run on SQL Server. To paraphrase:

Here is your code. Here is my improved version (sizzling 10000X faster). Here's how I got from yours to mine. Any questions??

😎

Repeat that enough and any RATIONAL people can't help but pay attention. I do note there are those who consider the morass "their baby" and just can't let go. More than a few times those people were owners/founders of the company or very senior staff.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

x SSC-Insane Points: 23660 More actions · Answer 12

Indianrock (12/17/2016)
Here is what much of our ORM code is based off of:
[/url] The author used to work for us but apparently hasn't updated it in quite a while.

Interesting project! I see that he posted a few years back that he's worked on a domainobjects to EF migration project, so that probably explains the lack of updates.