search

sjh37 / EntityFramework-Reverse-POCO-Code-First-Generator

EntityFramework Reverse POCO Code First Generator - Beautifully generated code that is fully customisable. This generator creates code as if you reverse engineered a database and lovingly created the code by hand. It is free to academics (you need a .edu or a .ac email address), not free for commercial use. Obtain your licence from
https://www.reversepoco.co.uk/
Other
700 stars 230 forks source link

Entity Framework query timeout but SQL is instant. #816

Closed stevensrf11 closed 7 months ago

stevensrf11 commented 7 months ago

Apparently when I use a ef reverse dbSet to retrieved information using a where clause on table of 5000 rows the response times out.

If the same operation is done in a select statement to the database say using SQL Manager on the table the response is instant.

Why is this so and how can this be corrected?

sjh37 commented 7 months ago

Hi @stevensrf11, It won't be down to the generator. Use SQL Profiler to look at the query being executed on your database and compare it to the one you manually run.

After you capture the SQL being executed, paste it into SSMS, and substitute any parameters so you are back to raw SQL. Then press CTRL-L to view the query plan in SSMS. You should see what makes the call expensive, and SSMS should show you any index recommendations to speed it up.

It's usually pulling back far too much information, or too many .Include statements. Oh, and the worst is an OR clause in the query, in that case you are better off using two separate queries (one for each side of the OR clause) and join the data together.

You can speed up EF if you are not going to modify any of the data by including .AsNoTracking() as part of your linq query. More info here on AsNoTracking.

stevensrf11 commented 7 months ago

Sir your talking way over my head and I do not have time to research all that information.

If I just use a stored procedure the repsonse is instant.

   

Sent: Sunday, November 12, 2023 at 3:36 PM From: "Simon Hughes" @.> To: "sjh37/EntityFramework-Reverse-POCO-Code-First-Generator" @.> Cc: "stevensrf11" @.>, "Mention" @.> Subject: Re: [sjh37/EntityFramework-Reverse-POCO-Code-First-Generator] Entity Framework query timeout but SQL is instant. (Issue #816)

 

Hi @stevensrf11, It won't be down to the generator. Use SQL Profiler to look at the query being executed on your database and compare it to the one you manually run.

After you capture the SQL being executed, paste it into SSMS, and substitute any parameters so you are back to raw SQL. Then press CTRL-L to view the query plan in SSMS. You should see what makes the call expensive, and SSMS should show you any index recommendations to speed it up.

It's usually pulling back far too much information, or too many .Include statements. Oh, and the worst is an OR clause in the query, in that case you are better off using two separate queries (one for each side of the OR clause) and join the data together.

You can speed up EF if you are not going to modify any of the data by including .AsNoTracking() as part of your linq query. More info here on AsNoTracking.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: @.***>