September 26, 2017 at 8:03 am
Jason A. Long - Saturday, September 23, 2017 5:31 PMEntity Framework & LINQ...
recently talked to a company who is looking for a new DBA. As got into the conversation about their environment, it came out that all (or at least the vast majority) of their SQL is coming from the application through the Entity Framework and is all coded in LINQ. Essentially, the C# developers own all of the "sql" code.
My first instinct was to simply maintain eye contact, not make any sudden movements and back out slowly... Later, upon reflection, I began to wander, is this really as bad as it sounds or am I simply projecting my own prejudices onto a perfectly reasonable model that I simply don't understand.
I've always held to the belief that the only access the application should have to the database is the ability to execute stored procedures and that all database logic should be contained in the database itself...
So, my questions are:
Has anyone here had any experience working in this type of environment?
If so, was I way off base with my initial reaction?
Is there an upside to this, that I'm simply not seeing, or is this simply a kludge for companies who don't want to invest in compliant SQL developers?
Any feedback or relevant links on the topic would be appreciated.
>Has anyone here had any experience working in this type of environment? Yes
>If so, was I way off base with my initial reaction? No
>Is there an upside to this, that I'm simply not seeing, or is this simply a kludge for companies who don't want to invest in compliant SQL developers? If ORM is done right, it can be very good. Unfortunately, I've never seen it done right. They have OO programmers that have been doing their loop based programming for so long, that this is all they can think of for solving problems. And I really can't blame the companies - it is usually the developers that don't care to learn SQL. IMO, if they don't want to learn how to do SQL (any variant) right, then they shouldn't be touching it at all. Of course, the company should be involved in teaching them how to do their job correctly (and this includes the other topic that companies just don't invest in: SQL Injection).
>Any feedback or relevant links on the topic would be appreciated.
http://blog.waynesheffield.com/wayne/archive/2012/06/orm-tools/
https://thomaslarock.com/2016/04/data-professionals-guide-contentious-issues/
You might be interested in this. Several years ago, I was working for a fortune 10 company. One division (that I was in) sold a product to hospitals, and they wanted to implement ORM in this product. I objected. We ended up with a challenge: for one sprint, a task was done two ways: ORM and No-ORM. I worked with the team manager for a couple of hours, and had the No-ORM solution completed. Their NHibernate "expert" that they had just hired spent the entire sprint trying to achieve the same performance as what we did in just a few hours. At the end of the sprint, it had improved to being only 300x slower than the No-ORM method.
And my saying about ORM: ORM is the fastest way to a slow database.
One of the things that I said above is that ORM, if done right, can be good. What I'd like to learn is exactly how to do it right in EF and NHibernate. Then I could teach others.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 26, 2017 at 8:21 am
WayneS - Tuesday, September 26, 2017 8:03 AMJason A. Long - Saturday, September 23, 2017 5:31 PMEntity Framework & LINQ...
recently talked to a company who is looking for a new DBA. As got into the conversation about their environment, it came out that all (or at least the vast majority) of their SQL is coming from the application through the Entity Framework and is all coded in LINQ. Essentially, the C# developers own all of the "sql" code.
My first instinct was to simply maintain eye contact, not make any sudden movements and back out slowly... Later, upon reflection, I began to wander, is this really as bad as it sounds or am I simply projecting my own prejudices onto a perfectly reasonable model that I simply don't understand.
I've always held to the belief that the only access the application should have to the database is the ability to execute stored procedures and that all database logic should be contained in the database itself...
So, my questions are:
Has anyone here had any experience working in this type of environment?
If so, was I way off base with my initial reaction?
Is there an upside to this, that I'm simply not seeing, or is this simply a kludge for companies who don't want to invest in compliant SQL developers?
Any feedback or relevant links on the topic would be appreciated.>Has anyone here had any experience working in this type of environment? Yes
>If so, was I way off base with my initial reaction? No
>Is there an upside to this, that I'm simply not seeing, or is this simply a kludge for companies who don't want to invest in compliant SQL developers? If ORM is done right, it can be very good. Unfortunately, I've never seen it done right. They have OO programmers that have been doing their loop based programming for so long, that this is all they can think of for solving problems. And I really can't blame the companies - it is usually the developers that don't care to learn SQL. IMO, if they don't want to learn how to do SQL (any variant) right, then they shouldn't be touching it at all. Of course, the company should be involved in teaching them how to do their job correctly (and this includes the other topic that companies just don't invest in: SQL Injection).
>Any feedback or relevant links on the topic would be appreciated.
http://blog.waynesheffield.com/wayne/archive/2012/06/orm-tools/
https://thomaslarock.com/2016/04/data-professionals-guide-contentious-issues/You might be interested in this. Several years ago, I was working for a fortune 10 company. One division (that I was in) sold a product to hospitals, and they wanted to implement ORM in this product. I objected. We ended up with a challenge: for one sprint, a task was done two ways: ORM and No-ORM. I worked with the team manager for a couple of hours, and had the No-ORM solution completed. Their NHibernate "expert" that they had just hired spent the entire sprint trying to achieve the same performance as what we did in just a few hours. At the end of the sprint, it had improved to being only 300x slower than the No-ORM method.
And my saying about ORM: ORM is the fastest way to a slow database.
One of the things that I said above is that ORM, if done right, can be good. What I'd like to learn is exactly how to do it right in EF and NHibernate. Then I could teach others.
I just came to correct the links. 😉
September 26, 2017 at 9:05 am
WayneS - Tuesday, September 26, 2017 8:03 AMOne of the things that I said above is that ORM, if done right, can be good. What I'd like to learn is exactly how to do it right in EF and NHibernate. Then I could teach others.
I've been looking for some of this. Too much of the docs on ORMs show the quick and easy get started, and not better practices for performance.
I have run across a few links, but really, I'd like to get a stairway or set of guidance for someone getting started to make the ORM work well.
Some dupe stuff, but it's a set of items to look through:
- https://stackoverflow.com/questions/67103/what-is-the-best-way-to-improve-performance-of-nhibernate
- https://www.red-gate.com/simple-talk/dotnet/net-framework/some-nhibernate-best-practices/
- http://geekswithblogs.net/Optikal/archive/2013/03/10/152371.aspx
- https://ayende.com/blog/4137/nhibernate-perf-tricks
- https://www.codeproject.com/Articles/18307/Optimizing-Performance-in-NHibernate-Part-A-Coll
September 26, 2017 at 9:54 am
Steve Jones - SSC Editor - Tuesday, September 26, 2017 9:05 AMWayneS - Tuesday, September 26, 2017 8:03 AMOne of the things that I said above is that ORM, if done right, can be good. What I'd like to learn is exactly how to do it right in EF and NHibernate. Then I could teach others.I've been looking for some of this. Too much of the docs on ORMs show the quick and easy get started, and not better practices for performance.
I have run across a few links, but really, I'd like to get a stairway or set of guidance for someone getting started to make the ORM work well.
Some dupe stuff, but it's a set of items to look through:
- https://stackoverflow.com/questions/67103/what-is-the-best-way-to-improve-performance-of-nhibernate
- https://www.red-gate.com/simple-talk/dotnet/net-framework/some-nhibernate-best-practices/
- http://geekswithblogs.net/Optikal/archive/2013/03/10/152371.aspx
- https://ayende.com/blog/4137/nhibernate-perf-tricks
- https://www.codeproject.com/Articles/18307/Optimizing-Performance-in-NHibernate-Part-A-Coll
Thanks for the links.
A stairway series (for each of the ORM tools) sounds like a good idea. I know that there have been some presentations at SQL Saturdays that cover some things, I'll have to dig into them to see what they cover.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 26, 2017 at 10:30 am
Luis Cazares - Tuesday, September 26, 2017 8:21 AMI just came to correct the links. 😉
veni, vidi, ego fixum
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
September 26, 2017 at 11:14 am
WayneS - Tuesday, September 26, 2017 9:54 AMThanks for the links.
A stairway series (for each of the ORM tools) sounds like a good idea. I know that there have been some presentations at SQL Saturdays that cover some things, I'll have to dig into them to see what they cover.
YW. So far, no good summary of how to set up. It's mostly, hey, I've ruined my db with NHibernate, now what. I need a better dev, or need to learn to be a better dev and show some "as you start" and "as you go" items to work with the framework.
September 26, 2017 at 11:43 am
WayneS - Tuesday, September 26, 2017 8:03 AM>Any feedback or relevant links on the topic would be appreciated.
http://blog.waynesheffield.com/wayne/archive/2012/06/orm-tools/
https://thomaslarock.com/2016/04/data-professionals-guide-contentious-issues/.
While Larock's comments on ORM are pretty accurate, and his view that many of the common arguments that database people get into are pointless and neither side makes sense, he gets two things completely wrong on that page: he stays that using NULL is always wrong and that using triggers is always wrong.
Tom
September 26, 2017 at 11:44 am
jonathan.crawford - Tuesday, September 26, 2017 10:30 AMLuis Cazares - Tuesday, September 26, 2017 8:21 AMI just came to correct the links. 😉veni, vidi, ego fixum
For me its vedi, vidi, WTF!!!???
Dear Microsoft,
Please stop "improving things". While I'm glad that you think that the motto for SQL Server 2016 should be "It just runs faster", you apparently live in a different world than many of us do. Thank goodness you weren't arrogant enough to think that you could just trash the old cardinality estimator and made Trace Flag 9481 available because we tried absolutely everything else and, short of rewriting code, that was the only thing that worked. I can now remove the garden hoses that I had to hook up to the CPU and SAN cooling systems to help them keep up. Your "wonderful" upgrades drove 48 CPUs from an average of 6-8% up to over 40% across the board. It also drove 8 TempDB files into submission from a normal 50KB per second each to over 5MB per second each. We also found that even though you also caused the memory to glow cherry red with nearly 100TB of I/O (logical reads) (NOT a typo... that is Terabytes of IO per hour), merely doing an in-place upgrade from 2012 to 2016 provided one of the most substantial stress tests that our hardware has ever seen. Still, stop such improvements... one such stress test is more than adequate.
Since such things have become a matter of rote for you in every one of your upgrades, please go back to a slower, better tested, more thoughtfully planned release schedule instead of dragging us all behind your pickup truck as you race to wherever the hell it is that you think you're going.
p.s. Similar in nature, I think the absolutely best feature of the Windows 10 upgrade was how easy you made it to instantly go back to my original Windows 7.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2017 at 11:56 am
Jeff Moden - Tuesday, September 26, 2017 11:44 AMFor me its vedi, vidi, WTF!!!???
Dear Microsoft,
Please stop "improving things". While I'm glad that you think that the motto for SQL Server 2016 should be "It just runs faster", you apparently live in a different world than many of us do. Thank goodness you weren't arrogant enough to think that you could just trash the old cardinality estimator and made Trace Flag 9481 available because we tried absolutely everything else and, short of rewriting code, that was the only thing that worked. I can now remove the garden hoses that I had to hook up to the CPU and SAN cooling systems to help them keep up. Your "wonderful" upgrades drove 48 CPUs from an average of 6-8% up to over 40% across the board. It also drove 8 TempDB files into submission from a normal 50KB per second each to over 5MB per second each. We also found that even though you also caused the memory to glow cherry red with nearly 100TB of I/O (logical reads) (NOT a typo... that is Terabytes of IO per hour), merely doing an in-place upgrade from 2012 to 2016 provided one of the most substantial stress tests that our hardware has ever seen. Still, stop such improvements... one such stress test is more than adequate.Since such things have become a matter of rote for you in every one of your upgrades, please go back to a slower, better tested, more thoughtfully planned release schedule instead of dragging us all behind your pickup truck as you race to wherever the hell it is that you think you're going.
p.s. Similar in nature, I think the absolutely best feature of the Windows 10 upgrade was how easy you made it to instantly go back to my original Windows 7.
Sounds like you didn't do enough (any?) pre-upgrade testing 😀 Have you narrowed down the culprits? I'll bet that it's just a handful of objects ....
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 26, 2017 at 12:03 pm
Tom_Hogan - Monday, September 25, 2017 12:29 PMAccording to the asterisk, SQL Server 2017 will be available for download on October 2nd.
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
"According to the asterisk" cracked me up!
Looks like next Monday.
-- Itzik Ben-Gan 2001
September 26, 2017 at 1:04 pm
Phil Parkin - Tuesday, September 26, 2017 11:56 AMSounds like you didn't do enough (any?) pre-upgrade testing 😀 Have you narrowed down the culprits? I'll bet that it's just a handful of objects ....
Agreed. I've done a bunch of upgrades to 14/16 in the last couple of years, when performance tests aren't done before upgrade, this is what happens.
In my experience, a small number of queries degrade severely in performance, the rest either remain stable or improve slightly. There's no way, with the complexity of the cardinality estimator, that no queries anywhere, ever would be negatively impacted.
If you upgrade to 2016 and are on SP1, the handful of problem children are easily addressed with either a query store forced plan or the LegacyCardinalityEstimator hint, if rewrites aren't feasible.
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
September 26, 2017 at 2:53 pm
Phil Parkin - Tuesday, September 26, 2017 11:56 AMJeff Moden - Tuesday, September 26, 2017 11:44 AMFor me its vedi, vidi, WTF!!!???
Dear Microsoft,
Please stop "improving things". While I'm glad that you think that the motto for SQL Server 2016 should be "It just runs faster", you apparently live in a different world than many of us do. Thank goodness you weren't arrogant enough to think that you could just trash the old cardinality estimator and made Trace Flag 9481 available because we tried absolutely everything else and, short of rewriting code, that was the only thing that worked. I can now remove the garden hoses that I had to hook up to the CPU and SAN cooling systems to help them keep up. Your "wonderful" upgrades drove 48 CPUs from an average of 6-8% up to over 40% across the board. It also drove 8 TempDB files into submission from a normal 50KB per second each to over 5MB per second each. We also found that even though you also caused the memory to glow cherry red with nearly 100TB of I/O (logical reads) (NOT a typo... that is Terabytes of IO per hour), merely doing an in-place upgrade from 2012 to 2016 provided one of the most substantial stress tests that our hardware has ever seen. Still, stop such improvements... one such stress test is more than adequate.Since such things have become a matter of rote for you in every one of your upgrades, please go back to a slower, better tested, more thoughtfully planned release schedule instead of dragging us all behind your pickup truck as you race to wherever the hell it is that you think you're going.
p.s. Similar in nature, I think the absolutely best feature of the Windows 10 upgrade was how easy you made it to instantly go back to my original Windows 7.
Sounds like you didn't do enough (any?) pre-upgrade testing 😀 Have you narrowed down the culprits? I'll bet that it's just a handful of objects ....
We did a shedload of pre-upgrade testing, first on the DevReview box, then on the Dev box, and then on the Staging box. Unfortunately, there was no place to drop in a 1.4TB database and the related 800GB database along with assorted sundry other related databases. We had to test with reduced versions of the databases.
It turns out that most of the queries that we previously identified as "needs rework for performance" are the ones that took the biggest hits. Our problem is that there is no time to rework the queries because half of them are for monster imports and the other half are what the bloody ORMs have produced, which we can't even get to to apply the Trace Flag at the query level and certainly don't have the time to do a fix and the related regression testing (the queries have a broad impact throughout the GUI).
Our "handful of objects" is actually 3 or 4 handfuls and most of them are going to be a bitch to refactor either because it's ORM generated or some fairly complex batch code.
I read one of Kendra Little's great articles on the subject where it shows how some of the worst code possible suddenly started running like a pro designed it. With no reflection on Kendra at all, I'm really happy that even more idiots can now say that "know" to write T-SQL without actually knowing much at all but I'm much more happy that MS left an out for the rest of us. The code that we have isn't strange or weird... it just plays against some fairly large tables (most of the code affected involves large UPDATEs). The symptoms are as bad or worse than (and quite similar to) full blown "Halloweening" and, yes... I've already checked... none of the code uses the "illegal" form of UPDATE that I've warned folks of over the years.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2017 at 3:04 pm
Jeff Moden - Tuesday, September 26, 2017 2:53 PMPhil Parkin - Tuesday, September 26, 2017 11:56 AMJeff Moden - Tuesday, September 26, 2017 11:44 AMFor me its vedi, vidi, WTF!!!???
Dear Microsoft,
Please stop "improving things". While I'm glad that you think that the motto for SQL Server 2016 should be "It just runs faster", you apparently live in a different world than many of us do. Thank goodness you weren't arrogant enough to think that you could just trash the old cardinality estimator and made Trace Flag 9481 available because we tried absolutely everything else and, short of rewriting code, that was the only thing that worked. I can now remove the garden hoses that I had to hook up to the CPU and SAN cooling systems to help them keep up. Your "wonderful" upgrades drove 48 CPUs from an average of 6-8% up to over 40% across the board. It also drove 8 TempDB files into submission from a normal 50KB per second each to over 5MB per second each. We also found that even though you also caused the memory to glow cherry red with nearly 100TB of I/O (logical reads) (NOT a typo... that is Terabytes of IO per hour), merely doing an in-place upgrade from 2012 to 2016 provided one of the most substantial stress tests that our hardware has ever seen. Still, stop such improvements... one such stress test is more than adequate.Since such things have become a matter of rote for you in every one of your upgrades, please go back to a slower, better tested, more thoughtfully planned release schedule instead of dragging us all behind your pickup truck as you race to wherever the hell it is that you think you're going.
p.s. Similar in nature, I think the absolutely best feature of the Windows 10 upgrade was how easy you made it to instantly go back to my original Windows 7.
Sounds like you didn't do enough (any?) pre-upgrade testing 😀 Have you narrowed down the culprits? I'll bet that it's just a handful of objects ....
We did a shedload of pre-upgrade testing, first on the DevReview box, then on the Dev box, and then on the Staging box. Unfortunately, there was no place to drop in a 1.4TB database and the related 800GB database along with assorted sundry other related databases. We had to test with reduced versions of the databases.
It turns out that most of the queries that we previously identified as "needs rework for performance" are the ones that took the biggest hits. Our problem is that there is no time to rework the queries because half of them are for monster imports and the other half are what the bloody ORMs have produced, which we can't even get to to apply the Trace Flag at the query level and certainly don't have the time to do a fix and the related regression testing (the queries have a broad impact throughout the GUI).
Our "handful of objects" is actually 3 or 4 handfuls and most of them are going to be a bitch to refactor either because it's ORM generated or some fairly complex batch code.
I read one of Kendra Little's great articles on the subject where it shows how some of the worst code possible suddenly started running like a pro designed it. With no reflection on Kendra at all, I'm really happy that even more idiots can now say that "know" to write T-SQL without actually knowing much at all but I'm much more happy that MS left an out for the rest of us. The code that we have isn't strange or weird... it just plays against some fairly large tables (most of the code affected involves large UPDATEs). The symptoms are as bad or worse than (and quite similar to) full blown "Halloweening" and, yes... I've already checked... none of the code uses the "illegal" form of UPDATE that I've warned folks of over the years.
Must have missed that warning about the "illegal" form of UPDATE.
September 26, 2017 at 3:09 pm
Jeff Moden - Tuesday, September 26, 2017 2:53 PMIt turns out that most of the queries that we previously identified as "needs rework for performance" are the ones that took the biggest hits. Our problem is that there is no time to rework the queries because half of them are for monster imports and the other half are what the bloody ORMs have produced, which we can't even get to to apply the Trace Flag at the query level and certainly don't have the time to do a fix and the related regression testing (the queries have a broad impact throughout the GUI).
Use Query Store.
Drop the compat mode down to SQL 2012's compat mode. Run the query. Use the Query Store plan forcing to force that plan, then turn the compat mode back to 2016. The forced plan will still be forced, and will remain forced unless you do something that makes it invalid (like drop an index)
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
September 26, 2017 at 3:33 pm
Lynn Pettis - Tuesday, September 26, 2017 3:04 PMMust have missed that warning about the "illegal" form of UPDATE.
It's a JOINed update where the target table isn't in the FROM clause and has, instead, had aliases (like you might use in a correlated subquery but not in a subquery) that make the implied "join" work. It frequently works correctly. When it doesn't, it will eat your server. It usually takes the following forms...
UPDATE dbo.SomeTable st
SET SomeColA = ot.SomeColA
FROM db.OtherTable ot
WHERE st.SomePK = ot.SomePK
;
UPDATE SomeTable
SET SomeColA = OtherTable.SomeColA
FROM OtherTable
WHERE SomeTable.SomePK = OtherTable.SomePK
;
You won't find such examples in BOL.
Heh... how ironic it would be if the new CE in 2016 would allow that junk. :sick:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 59,986 through 60,000 (of 66,549 total)
You must be logged in to reply to this topic. Login to reply