December 2, 2014 at 9:35 am
David Burrows (12/2/2014)
Luis Cazares (12/2/2014)
...it can be easier to start from scratch if we can understand the problem, than fix a complete piece of s...I have this problem every time I look at my old code :w00t:
Shhh, don't let that secret out!! I think all of us would say the same thing 😉
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
December 2, 2014 at 9:40 am
David Burrows (12/2/2014)
Luis Cazares (12/2/2014)
...it can be easier to start from scratch if we can understand the problem, than fix a complete piece of s...
I have this problem every time I look at my old code :w00t:
Assuming by old code you mean anything written older than about 2-3 days I suffer from this too. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2014 at 9:42 am
Sean Lange (12/2/2014)
David Burrows (12/2/2014)
Luis Cazares (12/2/2014)
...it can be easier to start from scratch if we can understand the problem, than fix a complete piece of s...
I have this problem every time I look at my old code :w00t:
Assuming by old code you mean anything written older than about 2-3 days I suffer from this too. 😀
That's why I don't review my code 😛
December 2, 2014 at 9:43 am
Luis Cazares (12/2/2014)
Sean Lange (12/2/2014)
Ed Wagner (12/2/2014)
SQLRNNR (12/2/2014)
<sarcasm>EFW is the bomb and must be worshiped because it saves certain dev types a ton of time to develop. </sarcasm>And reading that makes me want to throw up. I hear about how much time it saves developers. When I ask them about the SQL it generates, they don't know anything about it. They just know that it works. I've never seen EF create a good query. Then again, some people i work with use the query designer and think that's good SQL. After all, Microsoft wrote query designer, so it must be good, right? You see what I'm dealing with.
Oh come on Ed. EF creates awesome queries. One of our devs handed me a query that was running too slow. He said he figured I could fix it because I am "so good at optimizing queries". This beauty was around 6,000 lines long. That was not a typo. It was a "single" query with something on the order of 200 instances of the word "select". I told him I could fix his query as soon as he told me what it is doing. He said "the code is right there". I took a guess that it might take me 3 weeks to unravel that thing and a day or two to start from scratch. He decided the application could just deal with the poor performance. :w00t:
Some people just don't understand that it can be easier to start from scratch if we can understand the problem, than fix a complete piece of s...
Agreed. I encounter this phenomenon myself. I look at someone's code and nearly fall over. Instead of trying to fix what's wrong with it, I recommend starting fresh. I then invariably encounter the "It's used in so many places that you can't change it". As long as the parameters and results are the same, what does it matter if the same thing can be accomplished in 2 seconds instead of 2 minutes? Granted, taking out the 3 loops, 2 cursors and 14 non-SARGable predicates may achieve the same result, but at what cost? It just might run faster than the apps want it to run. 😉
December 2, 2014 at 9:50 am
SQLRNNR (12/2/2014)
Luis Cazares (12/2/2014)
Sean Lange (12/2/2014)
Ed Wagner (12/2/2014)
SQLRNNR (12/2/2014)
<sarcasm>EFW is the bomb and must be worshiped because it saves certain dev types a ton of time to develop. </sarcasm>And reading that makes me want to throw up. I hear about how much time it saves developers. When I ask them about the SQL it generates, they don't know anything about it. They just know that it works. I've never seen EF create a good query. Then again, some people i work with use the query designer and think that's good SQL. After all, Microsoft wrote query designer, so it must be good, right? You see what I'm dealing with.
Oh come on Ed. EF creates awesome queries. One of our devs handed me a query that was running too slow. He said he figured I could fix it because I am "so good at optimizing queries". This beauty was around 6,000 lines long. That was not a typo. It was a "single" query with something on the order of 200 instances of the word "select". I told him I could fix his query as soon as he told me what it is doing. He said "the code is right there". I took a guess that it might take me 3 weeks to unravel that thing and a day or two to start from scratch. He decided the application could just deal with the poor performance. :w00t:
Some people just don't understand that it can be easier to start from scratch if we can understand the problem, than fix a complete piece of s...
+100
I couldn't agree more. Even more frustrating is that once you HAVE optimized the query, it’s sometimes a challenge to get it put back into the code with all the framework wrapped appropriately around it.
To be fair though, I have to provide one counterpoint. Our team is using Hibernate, which is in the same genera as the Entity Framework. I raised my concerns when the project started, but as we considered several different development methodologies we made a conscious decision to start out with Hibernate. Our plan was to take advantage of the increased development speed up front then slowly pick it apart later when performance started to become an issue. I don’t think everyone completely believed the depth of the horror story I painted at the beginning, but over the last 2 years I’ve had several opportunities to appropriately wag a finger at it and they now clearly see the issues it can cause. To be clear, they believed it would be bad, just didn’t realize how bad it would be.
In spite of the issues we have had, the decision we made was still the right one and we would probably make it again. At the outset of the project, the average “depth” of database experience on the team was light to say the least. If we had required everyone to write clean, optimized, well-written SQL, the DB development would have been a nasty bottleneck and there would have been enough rejected code reviews for it to seem arbitrary and over-architected. In addition, we wouldn’t have been able to push the product to market in any reasonable timeframe and our team would have looked bad. We had a tight initial deadline and missing that first deadline may have precluded the chance at a second. We also sent one of our developers to Hibernate training, which really helped. It didn’t eliminate the stink, but we now know where to place the air fresheners.
I completely agree that Hibernate/Entity Framework is roughly equivalent to feeding a Mogwai after midnight (if you haven’t seen “Gremlins”, just know it is bad). It can be particularly frustrating when the impact hasn’t been properly evaluated and the impact on performance is ignored. I would also agree with everything that has been stated so far:
--I’ve never seen Hibernate generate a good query.
--Hibernate will consistently ask for too much data (both in columns/joins and rows).
--Most developers don’t know how Hibernate works and don’t realize what it is doing to their code.
--Using Hibernate makes it really tough to optimize the DB end of the app.
--I would raise serious concerns for any project planning to use it.
However, I don’t think that means there is not a place for it. Of all the development projects I’ve worked on and been a part of, this is the one where it worked like it should – the foundation for an explosive start, with a proper understanding of where (and when) it is going to fail and a plan to mitigate those issues when they appear. We also had a good team of smart people who appropriately placed the blame on Hibernate when it was justified and looked elsewhere when it was not. This is definitely the exception and not the rule. I stand firmly in the “don’t use Hibernate nor the Entity Framework” bandwagon, but I recognize that there are exceptions where if it is implanted with a careful plan and approached with open eyes, it can be successful.
Chad
December 2, 2014 at 9:58 am
Chad Crawford (12/2/2014)
I completely agree that Hibernate/Entity Framework is roughly equivalent to feeding a Mogwai after midnight (if you haven’t seen “Gremlins”, just know it is bad).
Buahahaha
Great quote.
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
December 2, 2014 at 10:07 am
Luis Cazares (12/2/2014)
Sean Lange (12/2/2014)
David Burrows (12/2/2014)
Luis Cazares (12/2/2014)
...it can be easier to start from scratch if we can understand the problem, than fix a complete piece of s...
I have this problem every time I look at my old code :w00t:
Assuming by old code you mean anything written older than about 2-3 days I suffer from this too. 😀
That's why I don't review my code 😛
....very late hours....dark and stormy night, a door is....task number {bigint max}...optimize this query...scroll down to the beefy part..."who wrote this rubbish?"...scroll up to the comment header...Ooooops!
😎
December 2, 2014 at 10:28 am
Sean Lange (12/2/2014)
This beauty was around 6,000 lines long. That was not a typo. It was a "single" query with something on the order of 200 instances of the word "select".
And I thought the 2500 line login request was impressive...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2014 at 10:34 am
Eirikur Eiriksson (12/2/2014)
Luis Cazares (12/2/2014)
Sean Lange (12/2/2014)
David Burrows (12/2/2014)
Luis Cazares (12/2/2014)
...it can be easier to start from scratch if we can understand the problem, than fix a complete piece of s...
I have this problem every time I look at my old code :w00t:
Assuming by old code you mean anything written older than about 2-3 days I suffer from this too. 😀
That's why I don't review my code 😛
....very late hours....dark and stormy night, a door is....task number {bigint max}...optimize this query...scroll down to the beefy part..."who wrote this rubbish?"...scroll up to the comment header...Ooooops!
😎
That's weird, Erikur. I haven't seen you in my office. I'm finding less and less of it as time goes on, but still find it every so often.
December 2, 2014 at 10:36 am
GilaMonster (12/2/2014)
Sean Lange (12/2/2014)
This beauty was around 6,000 lines long. That was not a typo. It was a "single" query with something on the order of 200 instances of the word "select".And I thought the 2500 line login request was impressive...
Yeah, they both sound silly. EF might make things easy for front-end developers, but they sure are hard on the database.
December 2, 2014 at 10:43 am
Ed Wagner (12/2/2014)
GilaMonster (12/2/2014)
Sean Lange (12/2/2014)
This beauty was around 6,000 lines long. That was not a typo. It was a "single" query with something on the order of 200 instances of the word "select".And I thought the 2500 line login request was impressive...
Yeah, they both sound silly. EF might make things easy for front-end developers, but they sure are hard on the database.
Not to defend EF/Hibernate or any other ORM tool, but often times the issue isn't so much the tool as the fact that the developers really don't know how to use it. If the tool is configured and used properly I've seen EF generate acceptable code. Just like we can manipulate T-SQL to make a better plan, developers can access objects in different ways in some of the ORM tools and help it generate better code. We are all anti-RBAR, but I would bet at some point early in our careers we wrote RBAR code because we didn't know better, I know I did. I think Chad's experience should be the goal. Use the tool, understanding that moving forward there will need to be changes made including introducing SP's for complex queries. I've found it important to work to build a partnership with app developers and be willing to compromise. Once I've done that the app devs are usually willing to compromise as well, especially the good ones.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 2, 2014 at 10:45 am
EF/LINQ/Hibernate all have their place. The primary place* for them is to SELECT from a VIEW with limited columns (and no VARCHAR in the WHERE clause!). Anything more and you are going to have serious performance problems.
*yes, I know that they support using STORED PROCEDUREs, but that's a side issue that has problems with disconnecting the data context from the DB.
December 2, 2014 at 10:50 am
Chad Crawford (12/2/2014)
SQLRNNR (12/2/2014)
Luis Cazares (12/2/2014)
Sean Lange (12/2/2014)
Ed Wagner (12/2/2014)
SQLRNNR (12/2/2014)
<sarcasm>EFW is the bomb and must be worshiped because it saves certain dev types a ton of time to develop. </sarcasm>And reading that makes me want to throw up. I hear about how much time it saves developers. When I ask them about the SQL it generates, they don't know anything about it. They just know that it works. I've never seen EF create a good query. Then again, some people i work with use the query designer and think that's good SQL. After all, Microsoft wrote query designer, so it must be good, right? You see what I'm dealing with.
Oh come on Ed. EF creates awesome queries. One of our devs handed me a query that was running too slow. He said he figured I could fix it because I am "so good at optimizing queries". This beauty was around 6,000 lines long. That was not a typo. It was a "single" query with something on the order of 200 instances of the word "select". I told him I could fix his query as soon as he told me what it is doing. He said "the code is right there". I took a guess that it might take me 3 weeks to unravel that thing and a day or two to start from scratch. He decided the application could just deal with the poor performance. :w00t:
Some people just don't understand that it can be easier to start from scratch if we can understand the problem, than fix a complete piece of s...
+100
I couldn't agree more. Even more frustrating is that once you HAVE optimized the query, it’s sometimes a challenge to get it put back into the code with all the framework wrapped appropriately around it.
To be fair though, I have to provide one counterpoint. Our team is using Hibernate, which is in the same genera as the Entity Framework. I raised my concerns when the project started, but as we considered several different development methodologies we made a conscious decision to start out with Hibernate. Our plan was to take advantage of the increased development speed up front then slowly pick it apart later when performance started to become an issue. I don’t think everyone completely believed the depth of the horror story I painted at the beginning, but over the last 2 years I’ve had several opportunities to appropriately wag a finger at it and they now clearly see the issues it can cause. To be clear, they believed it would be bad, just didn’t realize how bad it would be.
In spite of the issues we have had, the decision we made was still the right one and we would probably make it again. At the outset of the project, the average “depth” of database experience on the team was light to say the least. If we had required everyone to write clean, optimized, well-written SQL, the DB development would have been a nasty bottleneck and there would have been enough rejected code reviews for it to seem arbitrary and over-architected. In addition, we wouldn’t have been able to push the product to market in any reasonable timeframe and our team would have looked bad. We had a tight initial deadline and missing that first deadline may have precluded the chance at a second. We also sent one of our developers to Hibernate training, which really helped. It didn’t eliminate the stink, but we now know where to place the air fresheners.
I completely agree that Hibernate/Entity Framework is roughly equivalent to feeding a Mogwai after midnight (if you haven’t seen “Gremlins”, just know it is bad). It can be particularly frustrating when the impact hasn’t been properly evaluated and the impact on performance is ignored. I would also agree with everything that has been stated so far:
--I’ve never seen Hibernate generate a good query.
--Hibernate will consistently ask for too much data (both in columns/joins and rows).
--Most developers don’t know how Hibernate works and don’t realize what it is doing to their code.
--Using Hibernate makes it really tough to optimize the DB end of the app.
--I would raise serious concerns for any project planning to use it.
However, I don’t think that means there is not a place for it. Of all the development projects I’ve worked on and been a part of, this is the one where it worked like it should – the foundation for an explosive start, with a proper understanding of where (and when) it is going to fail and a plan to mitigate those issues when they appear. We also had a good team of smart people who appropriately placed the blame on Hibernate when it was justified and looked elsewhere when it was not. This is definitely the exception and not the rule. I stand firmly in the “don’t use Hibernate nor the Entity Framework” bandwagon, but I recognize that there are exceptions where if it is implanted with a careful plan and approached with open eyes, it can be successful.
Chad
How do you view the queries it generates? Are they persisted somewhere? Do you just see them through Perfmon? Something else?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 2, 2014 at 10:54 am
Stefan Krzywicki (12/2/2014)
How do you view the queries it generates? Are they persisted somewhere? Do you just see them through Perfmon? Something else?
There are all the regular ways in SQL Server: trace, xevents, DMV's. Also all of the ORM tools I am aware of have methods to output the generated SQL. For EF you can get LinqPad[/url] and work on EF queries to see what is being generated and try to tune the EF queries.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 2, 2014 at 10:56 am
Jack Corbett (12/2/2014)
Stefan Krzywicki (12/2/2014)
How do you view the queries it generates? Are they persisted somewhere? Do you just see them through Perfmon? Something else?
There are all the regular ways in SQL Server: trace, xevents, DMV's. Also all of the ORM tools I am aware of have methods to output the generated SQL. For EF you can get LinqPad[/url] and work on EF queries to see what is being generated and try to tune the EF queries.
Cool, thanks
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 46,516 through 46,530 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply