asked on Performance Question on Entity Framework
We are building a Web Portal, OurPortal. The IT Infrastructure includes a pair of Virtual Servers: AppSvr and DBSvr running Windows Server 2019. The database is on SQL Server 2019.
The database has been designed with many tables, PKs, and FKs.
The development team has chosen to use Entity Framework for development speed.
Here is a simple test case and my question:
Two Entities are Torganization and TContact
OurPortal issues a request to retrieve a subset of records from Tcontacts.
Select * from Tcontacts where (FirstName = ‘Bill’) or (LastName like ‘%son%’);
Three EDM variants for implementing the query:
- SQL Query for a specific entity type.
- SQL Query for a primitive data type.
- SQL commands.
The question: With each variant, which server is executing the query and applying the WHERE clause: AppSvr or DBSvr?
Windows OSWindows Server 2019Databases* Entity Framework Core
Last Comment
Chinmay Patel
I am not an expert, but I believe that in all cases the SQL server will execute the query. The application server is the SQL client.
There are certain where clauses which will not be applied on the SQL Server. Especially, where you modify the condition in C#, for example DateTime manipulation, string manipulation and so on.
ASKER
Ah, interesting, Chinmay.
Is this a valid restatement of your point?
In certain cases the where clause needs to get applied on the App Server so the raw Select content gets transferred to the app server.
Is this a valid restatement of your point?
In certain cases the where clause needs to get applied on the App Server so the raw Select content gets transferred to the app server.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Yes, we're on the same page. As you say, doing the filter on the client app is tolerable for a small dataset.
This portal we are building already has a rather complex database and we expect it to grow to be quite a large one. I expect we will end up creating and using a lot of stored procedures containing 'raw SQLstatements.
Regards,
Doug
This portal we are building already has a rather complex database and we expect it to grow to be quite a large one. I expect we will end up creating and using a lot of stored procedures containing 'raw SQLstatements.
Regards,
Doug
Most probably yes but I have a feeling that EF Core may surprise you. When done right, it can do wonders even against a large volume and complexity.