0

We are updating our project from .net framework 4.7 to .net core 6, but are experiencing some performance issues. Everything was working fine in .net framework 4.7, but now requests are taking a very long time and some even get a timeout exception. Calling the metadata goes very fast (19 ms). Does someone knows what the problem could be? Should we disable something, like LazyLoading, on our DbContext?

This payload is now taking 4,5 seconds than our .net framework version (315 ms)

{"where":{"Deleted":false,"or":[{"OrderStatus":"Processing","OrderNumber":{"contains":"189670"}},{"OrderStatus":"OnHold","Reference":{"contains":"189670"}},{"Customer.Name":{"contains":"189670"}}]},"orderBy":["Id desc"],"expand":["Customer"],"take":10,"inlineCount":true}:

this is a screenshot taken from dotTrace

Nesse
  • 373
  • 4
  • 14
  • Look at SQL Server's execution-plan for your queries - my guess is you're getting tripped-up by Parameter Sniffing - also, it looks like you aren't using async methods when you should be: you should use `await query,ToListaAsync(...)` with `IQueryable` (and `DbSet`), and `await dbContext.SaveChangesAsync()` to save - if you're using `SqlCommand`/`SqlDataReader` directly then you should use `await cmd.ExecuteReaderAsync(...)` and `await rdr.ReadAsync(...)`. – Dai Jul 11 '23 at 11:28
  • Aside from the performance, are your query results the same? – Steve Schmitt Jul 11 '23 at 16:28
  • @SteveSchmitt yes. I did not change anything the request is the same. Only changed the Breeze packages and followed the instructions. Instead of working with odata now using json... – Nesse Jul 12 '23 at 07:07
  • Ok thanks @Nesse. I just wanted to make sure that you weren't returning 10x the rows because filtering wasn't working, or something like that. – Steve Schmitt Jul 12 '23 at 16:15
  • @Dai be careful when changing to async if you've got blob data, because of this issue: https://github.com/dotnet/SqlClient/issues/593 – Steve Schmitt Jul 12 '23 at 16:19
  • @SteveSchmitt Thanks for the tip - I didn't know about this issue - and _damn_ it looks bad. – Dai Jul 12 '23 at 16:21
  • @Nesse can you use SQL Server Profiler (part of SQL Server Management Studio) to compare the generated queries between .NET 4.7 and 6? – Steve Schmitt Jul 13 '23 at 22:08
  • @SteveSchmitt I did some profiling and It looks like my breeze server is translating the request to a badly formed SQL command. .NET Framework 4 + breeze EF6 (2.0.1) => returns 228 rows (1,2 seconds) .NET 7 + breeze EFCore (7.1.0) => returns 466560 rows (49,3 seconds) – Nesse Jul 25 '23 at 12:51
  • .NET Framework 4 => `$filter=(Id eq 51798) or (Id eq 51799) or (Id eq 51800) or (Id eq 51801) or (Id eq 51802) or (Id eq 51803)&$expand=ProductPictures,ProductCategories,ProductManufacturers,ProductSpecificationAttributes,ProductTierPrices,StockItems/StockUnits/WarehouseLocation,RelatedProducts,CrossSellProducts,BundledProducts,GroupedProducts,ProductPurchasePrices,ProductGroup,ProductTranslations` – Nesse Jul 25 '23 at 13:28
  • .NET 7 => `{"where":{"or":[{"Id":51798},{"Id":51799},{"Id":51800},{"Id":51801},{"Id":51802},{"Id":51803}]},"expand":["ProductPictures","ProductCategories","ProductManufacturers","ProductSpecificationAttributes","ProductTierPrices","StockItems.StockUnits.WarehouseLocation","RelatedProducts","CrossSellProducts","BundledProducts","GroupedProducts","ProductPurchasePrices","ProductGroup","ProductTranslations"],"inlineCount":true}` – Nesse Jul 25 '23 at 13:30
  • You are getting 2000x the rows in your .NET 7 query. It seems like the `where` clause is not being applied. Do you have the `[BreezeQueryFilter]` attribute on your controller? – Steve Schmitt Jul 25 '23 at 17:04
  • @SteveSchmitt Thx for your feedback. the `[BreezeQueryFilter]` is applied to our controller. I've looked at the SQL commands and I see that the command generated by the .NET 4 version is using `UNION` and the .NET 7 version only `LEFT JOIN` ... – Nesse Jul 26 '23 at 06:33
  • @SteveSchmitt Would you know if we are doing something wrong? – Nesse Jul 28 '23 at 06:26
  • Please update your question to include code from your controller method and the resulting SQL. – Steve Schmitt Jul 28 '23 at 17:28

0 Answers0