July 31, 2018 at 12:05 am
Hi Experts,
Our dev team is using Entity framework and now all the DB calls are written in code. Since there is no procedures created from there side and SQL Code not directly available it is hard to optimize the queries running behind\create indexes.
1. What are all the best practices when calling database from Entity framework?
2.How the queries can be improved?
July 31, 2018 at 2:31 am
VastSQL - Tuesday, July 31, 2018 12:05 AMHi Experts,Our dev team is using Entity framework and now all the DB calls are written in code. Since there is no procedures created from there side and SQL Code not directly available it is hard to optimize the queries running behind\create indexes.
1. What are all the best practices when calling database from Entity framework?
2.How the queries can be improved?
Entity Framework (EF) can be a double-edged sword. It assists developers with generating SQL code without the assistance of a DBA but some of the code it generates is less than optimal. It can be difficult for the developers to change the resultant when wishing to make the code more efficient.
In the past I have written stored procedures, where the EF code is inefficient and have had the EF call the stored procedure instead. It removes a level of complication from the developers and lets the DBA work on that process in isolation - generally making both areas happy.
July 31, 2018 at 3:47 am
VastSQL - Tuesday, July 31, 2018 12:05 AMHi Experts,Our dev team is using Entity framework and now all the DB calls are written in code. Since there is no procedures created from there side and SQL Code not directly available it is hard to optimize the queries running behind\create indexes.
1. What are all the best practices when calling database from Entity framework?
2.How the queries can be improved?
Ideally all your SQL calls should be in stored procedures. This allows better optimisation and tuning without having to do new releases of software. Also, if you are using SQL from the application the database logon the application uses will have to have read, write permissions to the tables. This is bad practice as it leaves an opening for SQL injection. The application database logon should only have permissions to execute stored procedures and no direct access to any of the the tables on the database.
July 31, 2018 at 6:10 am
VastSQL - Tuesday, July 31, 2018 12:05 AMHi Experts,Our dev team is using Entity framework and now all the DB calls are written in code. Since there is no procedures created from there side and SQL Code not directly available it is hard to optimize the queries running behind\create indexes.
1. What are all the best practices when calling database from Entity framework?
2.How the queries can be improved?
When working with EF, or any other Object/Relational Mapping (ORM) tool, there are a few considerations that are important. First, monitor your dev environment very closely in order to see how well the dev team is using the ORM tool. Since something in the neighborhood of 90% go 98% of all queries are just generic SQL, ORM tools do very well with 90% to 98% of all queries. However, first up, the ORM tool can be configured badly. You want to observe two things out of the gate. First, that the code is for sure and certain using parameterized queries. Most ORM tools will do this out of the box, by default, but you can turn it off. Turning it off absolutely exposes you to SQL Injection as well as all sorts of potential performance issues. Second, that they are using the data structure to drive the parameterization of the queries. All too often I see the queries coming with with varchar(3) for the value of 'cat' and varchar(5) for the value of 'horse' and a varchar(whatever) for a value of 'whatever' instead of the varchar(25) that the actual data type is. Again, it's a simple setting in the code to get this right, but lots of people screw it up.
Next, on your server, make darned sure that you're using optimize for ad hoc. ORM tools will lead to more lightly used or unused query plans. Best to reduce their footprint in memory.
Then, keep the monitoring in place so that you can identify the 2-10% of queries that won't generate well from the ORM tool. For those queries, you can write stored procedures. Don't let the developers tell you any different. Every major ORM tool, including EF, supports stored procedures out of the box. They can work with them. They're just going to whine about it.
Watch for common problems such as the N+1 problem. They're well documented and the developers should be able to get around them on their own, but they frequently won't monitor what their ORM tool is generating. That's on you to be the protection for the system.
Finally, do not try to stand on the bridge/server and shout "NONE SHALL PASS". ORM tools are a huge boon to development, speed and accuracy. They're just not without flaws. Instead, help the dev team identify and mitigate the flaws. Be a partner.
"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 2, 2018 at 2:54 am
Thanks everyone for the valuable comments.
August 6, 2018 at 6:16 am
Jonathan AC Roberts - Tuesday, July 31, 2018 3:47 AMIdeally all your SQL calls should be in stored procedures.
As an SQL developer I used to agree, however the whole point of .NET and EF, especially with the MVC pattern is that there should be a disconnect between the business logic and the data repository; you should be able to swap MSSQL for MySQL, Oracle (cough~), Postgres or even a NOSQL database.
The fluent API allows you to define the database tables and field characteristics so that you do not have a 1:1 association between EF models and database tables.
but yes, don't let the developers design the database schema 🙂
August 6, 2018 at 7:12 am
aaron.reese - Monday, August 6, 2018 6:16 AMJonathan AC Roberts - Tuesday, July 31, 2018 3:47 AMIdeally all your SQL calls should be in stored procedures.As an SQL developer I used to agree, however the whole point of .NET and EF, especially with the MVC pattern is that there should be a disconnect between the business logic and the data repository; you should be able to swap MSSQL for MySQL, Oracle (cough~), Postgres or even a NOSQL database.
The fluent API allows you to define the database tables and field characteristics so that you do not have a 1:1 association between EF models and database tables.
but yes, don't let the developers design the database schema 🙂
Sounds good, but I know all the optimisations that are often needed to SQL to get it to run efficiently. It might be ok for some systems but I can see others where you need to fine tune things a bit more. We've got a web system written in Java that uses Hibernate. When it needs to fetch 1,000 rows it issues 1,000 SQL queries.
August 6, 2018 at 10:53 am
aaron.reese - Monday, August 6, 2018 6:16 AMJonathan AC Roberts - Tuesday, July 31, 2018 3:47 AMIdeally all your SQL calls should be in stored procedures.As an SQL developer I used to agree, however the whole point of .NET and EF, especially with the MVC pattern is that there should be a disconnect between the business logic and the data repository; you should be able to swap MSSQL for MySQL, Oracle (cough~), Postgres or even a NOSQL database.
The fluent API allows you to define the database tables and field characteristics so that you do not have a 1:1 association between EF models and database tables.
but yes, don't let the developers design the database schema 🙂
I would say it depends. The database needs to be able to defend itself from rogue elements. If there is a possibility that the database can be updated from outside the application (a very real possibility), then you may still need to have business logic in the database. Also, if you are using stored procedures and there needs to be a change it only has to changed in the database, not where ever the application is installed. This also permits refactoring in the database where the stored procedures provide a consistent interface to application even if the underlying tables change.
August 6, 2018 at 12:47 pm
aaron.reese - Monday, August 6, 2018 6:16 AMJonathan AC Roberts - Tuesday, July 31, 2018 3:47 AMIdeally all your SQL calls should be in stored procedures.As an SQL developer I used to agree, however the whole point of .NET and EF, especially with the MVC pattern is that there should be a disconnect between the business logic and the data repository; you should be able to swap MSSQL for MySQL, Oracle (cough~), Postgres or even a NOSQL database.
The fluent API allows you to define the database tables and field characteristics so that you do not have a 1:1 association between EF models and database tables.
but yes, don't let the developers design the database schema 🙂
My experience holds true with this (not the swapping bit, I've never really seen that happen). 90%+ of all queries are such that a tool can do a great job with them, fully automated. It's only that last percentage that might need procs to deal with issues, whether those issues come from the ORM or vagaries of the data structures, or the business requirements, doesn't really matter. The majority of the work is automated and we only have to get our hands dirty where it really counts.
"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 6, 2018 at 2:19 pm
aaron.reese - Monday, August 6, 2018 6:16 AMAs an SQL developer I used to agree, however the whole point of .NET and EF, especially with the MVC pattern is that there should be a disconnect between the business logic and the data repository; you should be able to swap MSSQL for MySQL, Oracle (cough~), Postgres or even a NOSQL database.The fluent API allows you to define the database tables and field characteristics so that you do not have a 1:1 association between EF models and database tables.
but yes, don't let the developers design the database schema 🙂
The trouble with EF's increasing insistence on "code first" is that it tends to encourage the thinking that the EF model should drive the database schema, but it does a fairly horrible job of it (many-many relationships are implemented in a way you can't apply table constraints for example) and pushes the idea that everything can be handled by automatic "migrations" which is all well and good till something else on the backend is used to access the data. ORMs can be a useful tool in some circumstances, but can cause as many problems as they solve too.
August 7, 2018 at 6:08 am
getting OT now, but the business domain should drive both the EF model and the database schema. EF and the fluent API is one way to build the database schema and not having a DBA on board at the design stage is an oversight. fluent allows (almost) all of the neat optimisations that we do in MSSMS to optimise and protect the database, PKs, FKs, cascade delete constraints and other referential integrity, default values, field size and data types etc, but many of them do not get triggered through the default code-first options. If you are a DBA working with a .NET shop it is definitely worth learning how fluent translates into our more familiar DDL statements.
@Lyn. If you are using EF and .NET then nothing, I repeat - NOTHING, should be updating the database directly, every connection to the database should be through an abstract repository and any requests to update the data should be made through a .NET method calling the relevant repository command, probably driven by an API of some description. the validation logic should be in the .NET code and the database repository should accept any data passed to it, provided it meets referential integrity constraints and if it does not then the .NET method should handle the errors. Transaction scoping then becomes the responsibility of the .NET code base rather than the database. This can come with some operational overhead but this is the price you pay for separation of concerns between UI/UX, Business Logic Layer, Data Access Layer and data storage layer. The comment about only having the change the code in the sproc is valid unless you have a single .NET codebase dynamically connecting to different data repositories depending on the tenant that has logged into the application. Now you might have the change the code (admittedly the same code) in 72 copies of the database instead on in one instance of the codebase.
August 7, 2018 at 7:44 am
aaron.reese - Tuesday, August 7, 2018 6:08 AMgetting OT now, but the business domain should drive both the EF model and the database schema. EF and the fluent API is one way to build the database schema and not having a DBA on board at the design stage is an oversight. fluent allows (almost) all of the neat optimisations that we do in MSSMS to optimise and protect the database, PKs, FKs, cascade delete constraints and other referential integrity, default values, field size and data types etc, but many of them do not get triggered through the default code-first options. If you are a DBA working with a .NET shop it is definitely worth learning how fluent translates into our more familiar DDL statements.@Lyn. If you are using EF and .NET then nothing, I repeat - NOTHING, should be updating the database directly, every connection to the database should be through an abstract repository and any requests to update the data should be made through a .NET method calling the relevant repository command, probably driven by an API of some description. the validation logic should be in the .NET code and the database repository should accept any data passed to it, provided it meets referential integrity constraints and if it does not then the .NET method should handle the errors. Transaction scoping then becomes the responsibility of the .NET code base rather than the database. This can come with some operational overhead but this is the price you pay for separation of concerns between UI/UX, Business Logic Layer, Data Access Layer and data storage layer. The comment about only having the change the code in the sproc is valid unless you have a single .NET codebase dynamically connecting to different data repositories depending on the tenant that has logged into the application. Now you might have the change the code (admittedly the same code) in 72 copies of the database instead on in one instance of the codebase.
YMMV
August 7, 2018 at 11:41 am
Yeah, that's almost always the moment someone asks "Can you hotfix this issue - but only for one client" and you really wish it was a per-database view rather than things directly prodding about in tables....
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply