Entity Framework performance - when query compile phase goes bad!

Kind of suspected it is going to fire when number of Includes in my EF statement will grow above some threshold. But this bad?!

And this is not the final snapshot, at the end that ApplyRulesToSubtree in the Compile of CreateDbCommandDefinition goes to something like 250 seconds and then I have transaction scope timeout firing up (and have no intent to have transaction timeout higher than 60s here!:)).

Some reading points to splitting the query “includes” like here.

In my case I went with splitting the former query:

fromVersion = Scope<ScreensDbContext>.Current.BidVersions.Include(v => v.BidPhones).Include(v => v.BidVariants).Include("BidVariants.Wallets.BidPhones").

   Include("BidPhones.Products").Include("BidVariants.Wallets.Tariff").Include("BidVariants.Wallets.Tariff.TariffPlans").

   Include("BidVariants.VoiceServicesPriceModel.Tariff").

   Include("BidVariants.VoiceServicesPriceModel.WalletVoicePriceModels.SelectedTariffPlan").

   Include("BidVariants.VoiceServicesPriceModel.WalletVoicePriceModels.Wallet").

   Include("BidVariants.VoiceServicesPriceModel.PriceInstanceItems").

   Include("BidVariants.RoamingCallsPriceModel.InRoamingPriceInstances").

   Include("BidVariants.RoamingCallsPriceModel.OutRoamingPriceInstances").

   Include("BidVariants.RoamingCallsPriceModel.Categories").

   Include("BidVariants.InternationalCallsPriceModel.MobilePriceInstances").

   Include("BidVariants.InternationalCallsPriceModel.Categories").

   Include("BidVariants.MobileDataPriceModel.AggregatedPriceInstances").

   Include("BidVariants.MobileDataPriceModel.Categories").

   Include("BidVariants.OtherServicesPriceModel.AggregatedPriceInstances").

   Include("BidVariants.OtherServicesPriceModel.Categories").

   Include("BidVariants.AdslPriceModel.Categories").

   Include("BidVariants.MosPriceModel.Categories").

   Where(v => v.Id == copyInfo.FromVersion).FirstOrDefault();

Into that:

fromVersion = Scope<ScreensDbContext>.Current.BidVersions.Where(v => v.Id == copyInfo.FromVersion).First();

var variants = Scope<ScreensDbContext>.Current.BidVariants.Where(v => v.BidVersion.Id == copyInfo.FromVersion).ToList();

var wallets = Scope<ScreensDbContext>.Current.Wallets.Where(w => w.BidVariant.BidVersion.Id == copyInfo.FromVersion).Include(w => w.BidPhones).ToList();

var phones = Scope<ScreensDbContext>.Current.BidPhones.Where(bp => bp.BidVersion.Id == copyInfo.FromVersion).Include(p => p.Products).ToList();

var tariffs = Scope<ScreensDbContext>.Current.Tariffs.ToList();

var plans = Scope<ScreensDbContext>.Current.TariffPlans.ToList();

var voiceModels = Scope<ScreensDbContext>.Current.VoiceServicesPriceModels.Where(vpm => vpm.BidVariant.BidVersion.Id == copyInfo.FromVersion).Include("WalletVoicePriceModels.SelectedTariffPlan").Include(vm => vm.PriceInstanceItems).ToList();

var roamingPriceModel = Scope<ScreensDbContext>.Current.RoamingCallsPriceModels.Where(pm => pm.BidVariant.BidVersion.Id == copyInfo.FromVersion).Include(pm => pm.InRoamingPriceInstances).Include(pm => pm.OutRoamingPriceInstances).Include(pm => pm.Categories).FirstOrDefault();

var internationalPriceModel = Scope<ScreensDbContext>.Current.InternationalCallsPriceModels.Where(pm => pm.BidVariant.BidVersion.Id == copyInfo.FromVersion).Include(pm => pm.MobilePriceInstances).Include(pm => pm.Categories).FirstOrDefault();

var mobileDataPriceModel = Scope<ScreensDbContext>.Current.MobileDataPriceModels.Where(pm => pm.BidVariant.BidVersion.Id == copyInfo.FromVersion).Include(pm => pm.AggregatedPriceInstances).Include(pm => pm.Categories).FirstOrDefault();

var otherServicesPriceModel = Scope<ScreensDbContext>.Current.OtherServicesPriceModels.Where(pm => pm.BidVariant.BidVersion.Id == copyInfo.FromVersion).Include(pm => pm.AggregatedPriceInstances).Include(pm => pm.Categories).FirstOrDefault();

var mosPriceModel = Scope<ScreensDbContext>.Current.MosPriceModels.Where(pm => pm.BidVariant.BidVersion.Id == copyInfo.FromVersion).Include(pm => pm.Categories).FirstOrDefault();

var adslPriceModel = Scope<ScreensDbContext>.Current.AdslPriceModels.Where(pm => pm.BidVariant.BidVersion.Id == copyInfo.FromVersion).Include(pm => pm.Categories).FirstOrDefault();

So actually, that just populates the context with data from many short queries.

The result? From infinite number of minutes to few seconds!

Why I didn’t use lazy load here? I could not afford having proxies for serialization purposes… As well it was good after all to have those reads in one place as I had to suppress transaction just in one place then.