August 12, 2022 at 11:02 pm
One more small thing: if we are dealing with poor database design, sometimes neither tuning nor hardware helps.
That's frequently true with the code itself. Sometimes you've just gotta throw it out and start over instead of trying to tune it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2022 at 4:51 am
Our C# developers (who know just enough SQL to be dangerous) write code using Entity Framework and I have trust that Microsoft will generate good SQL. So far so good - and it's getting better with every version on EF Core. This approach of strongly typed code that will give syntax errors when I change something in the model beats stored procedures every time. Maintenance is king!
Occasionally, I would love to get an advice from a humble DBA who can analyze the generated SQL and work with the developers to help them tune their C# code. Instead, I usually get the feedback from a DBA (who doesn't know any C# and is extremely dangerous) that C# code should just invoke a stored procedure that will be much more efficient.
Interesting... you expect that Developers don't need to learn SQL but you expect the DBA to know C#/EF well enough to help the Developers tune it.
Also, consider this... are you and your Developers as "humble" and open to ideas as you expect the DBA to be?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2022 at 3:10 pm
One more small thing: if we are dealing with poor database design, sometimes neither tuning nor hardware helps.
PREACH BROTHER!
Writing the final chapter of the book, and the first section is talking about the importance of proper relational design in performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2022 at 4:21 pm
Interesting... you expect that Developers don't need to learn SQL but you expect the DBA to know C#/EF well enough to help the Developers tune it.
Also, consider this... are you and your Developers as "humble" and open to ideas as you expect the DBA to be?
First, I expect people not put words in my mouth... Bad enough when clients or Internet commenters do it. I certainly don't engineers on my team do it. I say enough controversial things myself - no need to build a strawman.
Second, I expect humble developers and humble DBA's work as a team. As @Grant Fritchey pointed out, EF does about 95 to 98% of the T-SQL code really well (that's my experience as well). In 2-5% where it doesn't, take a look of what got generated, DBA provides feedback to developer what seems to be the bottleneck; developer tweaks the code to solve the problem. If after reasonable effort DBA and Developer come to conclusion that it is limitation of the code generation, or limitation of C# - then replace it with stored procedure.
Third - I think this is very important to keep in mind... The comments here are written by GOOD experienced DBAs. Stored procedures written by good DBAs are better than code written by BAD developers. However, in my management experience, C# code is easier to maintain and debug than T-SQL in 95-98% of the situations, compared to code written by a DBA/SQL developer of the same skill level.
August 13, 2022 at 4:43 pm
Jeff Moden wrote:Interesting... you expect that Developers don't need to learn SQL but you expect the DBA to know C#/EF well enough to help the Developers tune it.
Also, consider this... are you and your Developers as "humble" and open to ideas as you expect the DBA to be?
First, I expect people not put words in my mouth... Bad enough when clients or Internet commenters do it. I certainly don't engineers on my team do it. I say enough controversial things myself - no need to build a strawman.
Second, I expect humble developers and humble DBA's work as a team. As @Grant Fritchey pointed out, EF does about 95 to 98% of the T-SQL code really well (that's my experience as well). In 2-5% where it doesn't, take a look of what got generated, DBA provides feedback to developer what seems to be the bottleneck; developer tweaks the code to solve the problem. If after reasonable effort DBA and Developer come to conclusion that it is limitation of the code generation, or limitation of C# - then replace it with stored procedure.
Third - I think this is very important to keep in mind... The comments here are written by GOOD experienced DBAs. Stored procedures written by good DBAs are better than code written by BAD developers. However, in my management experience, C# code is easier to maintain and debug than T-SQL in 95-98% of the situations, compared to code written by a DBA/SQL developer of the same skill level.
Just to be clear, are you saying that I put words in your mouth or are you just explaining what happens at work for a lot companies?
I'm also a bit confused... at first you talk about a "dangerous" and non-humble DBA... are you talking about a DBA at your current place of work? I ask because it sounded like it. Or was that just an example of what happens in much of the rest of the world based on some personal experiences?
Second, I expect humble developers and humble DBA's work as a team.
Strong second here and totally agree.
As @Grant Fritchey pointed out, EF does about 95 to 98% of the T-SQL code really well (that's my experience as well).
Apparently you have some Developers that are really good at what they do. I work with some good Developers in a mostly symbiotic relationship as well but I've not found EF to be such a friend. I've seen it make a non-SARGable train wreck out of even some simple joins and does crazy stuff like WHERE ASCII(someY/Ncolumn) = ASCII(@p1).
To be absolutely fair though, I don't know what percentage of the code that constitutes but it's frequently difficult to isolate the source of such resulting code in the EF code so that it can be repaired.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2022 at 5:20 pm
One more small thing: if we are dealing with poor database design, sometimes neither tuning nor hardware helps.
Why do you notice the splinter in your brother’s eye, but do not perceive the wooden beam in your own? (Luke, Chapter 6)
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
August 13, 2022 at 6:05 pm
Just to be clear, are you saying that I put words in your mouth...
Yes - I never said or implied that I "expect that Developers don't need to learn SQL but expect the DBA to know C#/EF well enough to help the Developers tune it."
I'm also a bit confused... at first you talk about a "dangerous" and non-humble DBA... are you talking about a DBA at your current place of work? I ask because it sounded like it. Or was that just an example of what happens in much of the rest of the world based on some personal experiences?
The word "dangerous" is a sarcastic play on @m60freeman's comment ("... C# developers (who knew just enough SQL to be dangerous) ... "). I agree - doesn't make sense without original. The non-humble developers or DBAs don't stay long at my current place of work - but I've had a long career and seen many examples. In my personal experience (and I don't pretend to have any representative statistics) - developers are usually more humble than similarly skilled DBAs. DBAs seem to think that they have that voodoo knowledge that other engineers just can't comprehend.
Apparently you have some Developers that are really good at what they do. I work with some good Developers in a mostly symbiotic relationship as well but I've not found EF to be such a friend. I've seen it make a non-SARGable train wreck out of even some simple joins and does crazy stuff like WHERE ASCII(someY/Ncolumn) = ASCII(@p1).
To be absolutely fair though, I don't know what percentage of the code that constitutes but it's frequently difficult to isolate the source of such resulting code in the EF code so that it can be repaired.
Thank you. After 30 years in the industry I developed pretty good hiring skills. I also make sure that senior developers conduct code reviews and mentor juniors. Finally, I try to keep my own powder dry, so when I tell a developer that it is a simple problem - I can solve it myself. (a shot to @skeleton567 ). 8K on Stack Overflow may not be that impressive for the crowd on this board - but I think it's pretty high for business executive.
Now, I don't want to hijack this thread into EF tuning... but there are code smells in LINQ just like there are in T-SQL. My pet peeve is join - if I see join in LINQ then it is either bad database design (for which no tuning will help); or - more frequently - it is very inexperienced engineer took a SQL statement and rewrote it in LINQ. There are others - SelectMany is frequently abused. I am sure you got the point.
August 15, 2022 at 6:19 pm
This is for @skeleton567, post with a Bible quote 🙂
I am not blaming neither DBAs nor developers. It is simply true. Good database design actually frees developers from enforcing data integrity and similar database stuff. From experience, they love it. I myself started as a developer, still occasionally do some app development, mainly working prototypes to help with understanding requirements.
A good database is one with reasonably normalized schema, proper data types, uniqueness constraints and foreign key, and generous sprinkle of check constraints. A very good database design includes stored procedures for data access - developers do not need to construct INSERTS, UPDATES, DELETES, most of SELECTS - there would be a stored procedure for almost everything developers may need. We NULL if we really must, use identities for enumerating records (in Joe Celko's meaning),not for keys. All tables tend to have natural keys identified and physically implemented as keys. By the time we build all FK nd UNIQUE keys, most of indexation is covered. Bad design is more or less all listed things not existing.
I am not saying that ALL our databases are at least good, one has to cut corners sometimes. That is how I learned impact of bad design. We had cases of "overnight query runs" being reduced to minutes or seconds.
Good thing about good setup is less troubles. Bad thing is that problems do appear, and since we do not solve problems often, once they happen it may be more difficult to come with efficient solutions.
It is never ending story, WHILE loop in figuring out who does more damage - dangerous developers or obstinate DBAs who's answer to any question is NO. That is how it works. At the end, we all retire after 30-40 years. The trick is to make those years less unpleasant that necessary.
Zidar's Theorem: The best code is no code at all...
August 15, 2022 at 7:16 pm
Now, I don't want to hijack this thread into EF tuning... but there are code smells in LINQ just like there are in T-SQL. My pet peeve is
join- if I see join in LINQ then it is either bad database design (for which no tuning will help); or - more frequently - it is very inexperienced engineer took a SQL statement and rewrote it in LINQ. There are others -SelectManyis frequently abused. I am sure you got the point.
Since a good database design relies on separate tables and with the understanding that the only thing I truly know about LINQ is how to spell it, do you rely only on objects in LINQ to "join" tables? If so, I'd certainly be interested in learning about that if you have a link you could share.
Oh... the other thing that I know about LINQ is that if the Developers don't know what they're doing, virtually every parameter passed to the database is NVARCHAR(), which makes a whole lot of the code non-SARGable due to "implicit casts". More specifically, I believe it was a product called LINQ2SQL, IIRC.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2022 at 7:39 pm
It is simply true. Good database design actually frees developers from enforcing data integrity and similar database stuff.
This is only partially true, database constraints can prevent bad data from getting into the data base. They do not however free developers from having to both know/understand those constraints and actually enter data properly. And developers who develop to does it throw an error vs is it actually are a problem in their own way. For example just not throwing a FK error doesn't mean that the FK actually links to the correct parent and that's not something that can be handled at the database level.
August 15, 2022 at 8:16 pm
ZZartin: "This is only partially true, database constraints can prevent bad data from getting into the data base. They do not however free developers from having to both know/understand those constraints and actually enter data properly. "
You are right. No constraint can prevent entering my shoe-size instead of valid payment amount. Also, no one can prevent charging my payments to other people accounts. And no amount of tuning queries would help.
From experience also, database schema on conceptual level, before constrains and data types, should 'follow' the business process it attempts to support. If we achieve that level of congruency between business process and database schema, it turns out that understanding by developers of "specifications" goes up and mistakes go down. Bugs happen, some are discovered earlier than others, but eventually we can fix most of them. Missing significant part of business process cannot be easily fixed You know, "that is not what we meant" missing features.
I am afraid we are straying into different discussion on what is needed to happen so the app, rather a system for supporting business needs is delivered successfully. I suggest we leave it for some other time.
Zidar's Theorem: The best code is no code at all...
August 16, 2022 at 2:37 am
...do you rely only on objects in LINQ to "join" tables?
Yes. You will be hard pressed to find join in any EF tutorials; other than just the places where it explains the syntax.
... if the Developers don't know what they're doing, virtually every parameter passed to the database is NVARCHAR(), which makes a whole lot of the code non-SARGable due to "implicit casts".
Huh? why would that be? if parameter type is integer or datetime, then that is what is passed. LINQ2SQL was precursor to EF; but even then I don't remember implicit casts. But it was around 2009; so I may have forgotten
August 16, 2022 at 2:14 pm
Jeff Moden wrote:...do you rely only on objects in LINQ to "join" tables?
Yes. You will be hard pressed to find join in any EF tutorials; other than just the places where it explains the syntax.
Jeff Moden wrote:... if the Developers don't know what they're doing, virtually every parameter passed to the database is NVARCHAR(), which makes a whole lot of the code non-SARGable due to "implicit casts".
Huh? why would that be? if parameter type is integer or datetime, then that is what is passed. LINQ2SQL was precursor to EF; but even then I don't remember implicit casts. But it was around 2009; so I may have forgotten
There actually is a bad way to configure EF so that it does make every string an NVARCHAR. It's not the default behavior (not any more), but it's possible. Also, another bad practice with EF (and note, these aren't standards, they're mess ups, developers, and DBAs, can mess up stuff without EF getting involved, just fine) is that they don't have EF get the correct data types from the database, so string lengths get messed up. You'll see VARCHAR(3) for 'dog' and 'VARCHAR(8)' for 'elephant' when the data length is actually VARCHAR(25) (or whatever). Again, non-standard, code smells, but fairly common mistakes that get made by developers who don't understand well enough how EF works.
Everyone, and I mean everyone, is capable of screwing up. Everyone is more than a little arrogant. To really work together, instead of spotting bad things and categorizing others as BAD because they are <insert job title here>, we need to be understanding and forgiving of each other.
And, EF is like Jessica Rabbit. It's not bad, it's just programmed that way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 16, 2022 at 5:12 pm
Huh? why would that be?
I have no clue about why that would be. It was an observation especially but not limited to what should have been VARCHAR data types. They made some sort of a settings change and the way the created objects and the issue finally went away. This did show me an article on the subject but that was more than a decade ago.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply