I'm running into a performance issues with an entity framework linq query.
DLL versions:
npgsql 4.0.8
EntityFramework6.Npgsql 3.2.1.1
DotNet 4.6.1
As addon I use Entity Framework Plus (Z.EntityFramework.Plus.QueryIncludeFilter.EF6)
I'm running a big query in C# (web api). This request will be called many times per hour (>2000 times) via a web api Visual studio 2015 project. After almost every call the execution time will be higher. I'm closing the database contexts in a right way (I Believe). I really don't have a clue why the application pool of IIS getting slower and slower. Recycling the application pool will bring back a good performance. Same happens when restarting IIS express in Visual studio (debug mode).
My code;
Stopwatch stopWatch = new Stopwatch();
//It's a multi tentant application. So every customer has it's own connection string.
// For this test I only run with the same
using (var context = new appEntities("app" + organization))
{
stopWatch.Start();
context.Configuration.EnsureTransactionsForFunctionsAndCommands = false;
context.Configuration.ProxyCreationEnabled = false;
context.Configuration.LazyLoadingEnabled = false;
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;
beeldkwaliteit_formulieren form =
context.beeldkwaliteit_formulieren
.Where(x => x.id == 34)
.IncludeFilter(x => x.inspectie_categorie
.Where(y => y.id_typebeeldkwaliteit == id_typebeeldkwaliteit))
.IncludeFilter(x => x.inspectie_categorie
.Where(i => i.id_form == 34).Select(z => z.inspectie_sub_categorie))
.IncludeFilter(x => x.beeldkwaliteit_insp_form_fields
.Where(y => y.id_form == 34))
.IncludeFilter(x => x.beeldkwaliteit_insp_form_fields
.Select(y => y.beeldkwaliteit_insp_form_values
.Where(z => z.id_insp == id_inspectie)))
.IncludeFilter(x => x.beeldkwaliteit_insp_form_fields
.Select(y => y.beeldkwaliteit_fields_activated_by))
.IncludeFilter(x => x.beeldkwaliteit_insp_form_fields
.Select(y => y.beeldkwaliteit_waardenlijst)
.Where(z => z.id_typebeeldkwaliteit == id_typebeeldkwaliteit))
.IncludeFilter(x => x.beeldkwaliteit_insp_form_fields
.Select(y => y.beeldkwaliteit_waardenlijst.beeldkwaliteit_waardenlijst_values))
.IncludeFilter(x => x.beeldkwaliteit_insp_form_fields
.Select(y => y.beeldkwaliteit_waardenlijst.beeldkwaliteit_waardenlijst_values
.Select(z => z.beeldkwaliteit_waardenlijst_values_linked_values)))
.FirstOrDefault();
stopWatch.Stop();
Debug.WriteLine("SW:" + stopWatch.ElapsedMilliseconds);
}
My connect string (every customer has one)
<add name="app_customer" connectionString="metadata=res://*/Models.****DBModel.csdl|res://*/Models.appNameDBModel.ssdl|res://*/Models.*****DBModel.msl;provider=Npgsql;provider connection string="Host=www.******.eu;Database=dev_*****;Username=******;Password=*****;Search Path=public"" providerName="System.Data.EntityClient" />
The stopwatch times:
Start: SW:1652
SW:112
SW:106
SW:109
SW:168
SW:116
-- Skip 30
SW:117
-- Skip 30
SW:167
-- Skip 30
SW:258
-- Skip 30
SW:271
-- skip 30
SW:303
-- skip 30
SW:376
-- skip 30
SW:578
-- skip 30
SW:571
-- skip 30
SW:617
-- skip 30
SW:655
I'm running into a performance issues with an entity framework linq query.
DLL versions:
npgsql 4.0.8
EntityFramework6.Npgsql 3.2.1.1
DotNet 4.6.1
As addon I use Entity Framework Plus (Z.EntityFramework.Plus.QueryIncludeFilter.EF6)
I'm running a big query in C# (web api). This request will be called many times per hour (>2000 times) via a web api Visual studio 2015 project. After almost every call the execution time will be higher. I'm closing the database contexts in a right way (I Believe). I really don't have a clue why the application pool of IIS getting slower and slower. Recycling the application pool will bring back a good performance. Same happens when restarting IIS express in Visual studio (debug mode).
My code;
My connect string (every customer has one)
<add name="app_customer" connectionString="metadata=res://*/Models.****DBModel.csdl|res://*/Models.appNameDBModel.ssdl|res://*/Models.*****DBModel.msl;provider=Npgsql;provider connection string="Host=www.******.eu;Database=dev_*****;Username=******;Password=*****;Search Path=public"" providerName="System.Data.EntityClient" />The stopwatch times:
Start: SW:1652
SW:112
SW:106
SW:109
SW:168
SW:116
-- Skip 30
SW:117
-- Skip 30
SW:167
-- Skip 30
SW:258
-- Skip 30
SW:271
-- skip 30
SW:303
-- skip 30
SW:376
-- skip 30
SW:578
-- skip 30
SW:571
-- skip 30
SW:617
-- skip 30
SW:655