CODEWITHSUNDEEP
c#performanceentity-frameworkentity-framework-6
user3521111
user3521111

Reputation: 11

EF6 takes 5 mins to run query on less then 40,000 records

I am running into a very odd error with EF6. I have uploaded ~38K records on my first pass. Then on my second round, I query the table with a conditional linq statement. That line of code takes about 4 mins to run. This are my entities.

[Table("RAW_ADWORDS")] 
public class AdWord
{
    [Key]
    public int ID { get; set; }
    public bool Processed { get; set; }
    public string Client { get; set; }
    public long ClientID { get; set; }
    public bool Active { get; set; }
    public bool ProcessedAllFile { get; set; }
    public DateTime LastTimeRun{ get; set; }
    public DateTime? LastDateTimeProcessed { get; set; }
    public virtual List<AdWordCampaign> Campaigns { get; set; }
}

[Table("foobar")] 
public class AdWordCampaign
{
    [Key]
    public int ID { get; set; }
    public string Campaign { get; set; }
    public long CampaignID { get; set; }
    public string Day { get; set; }
    public long Clicks { get; set; }
    public string CampaignStatus { get; set; }
    public long Cost { get; set; }
    public long Impressions { get; set; }
    public double CTR { get; set; }
    public long AvgCPC { get; set; }
    public double AvgPosition { get; set; }
    public DateTime DownloadDate { get; set; }
}

}

First I run this:

AdWord objAdWord = adwordsContext.AdWords.Where(c => c.ClientID == iCampaignID).FirstOrDefault();

Then

AdWordCampaign objAdWordCampaign = objAdWord.Campaigns.Where(c => c.CampaignID == iElementCampaignID && c.Day == sElementDate).FirstOrDefault();

The line above seems to load ALL the records first before it does the query. Also it still takes 4 mins if I add a Take(5) in the query.

Upvotes: 1

Views: 197

Answers (4)

Jon Bellamy
Jon Bellamy

Reputation: 3373

I've seen this before with EF when referencing linked objects through a "primary object" - i.e. when you do

AdWordCampaign objAdWordCampaign = objAdWord.Campaigns.Where(...).FirstOrDefault();.

Quite simply it iterates all records one-by-one - and hence the slow query.

If you change to the following, you should get an almost instant response:

AdWord objAdWord = adwordsContext.AdWords.Where(c => c.ClientID == iCampaignID).FirstOrDefault();

AdWordCampaign objAdWordCampaign = <adwordsContext>.Campaigns
.Where(c => <c.AdwordId = objAdWord.Id> && c.CampaignID == iElementCampaignID && c.Day == sElementDate)
.FirstOrDefault();

I've put changes in angular brackets and I'm not sure which property within an AdWordCampaign marks the Id of the AdWord for the relationship from a glance at your model, but I'm sure you get the idea - go direct to Capaigns table via the context, using the AdWord as an addiitonal Where clause, rather than via the AdWord's Campaigns collection.

Upvotes: 0

Tony Vitabile
Tony Vitabile

Reputation: 8614

You need to watch the queries that are generated & executed on the server and make sure they're optimized.

If you're using MS SQL Server, you want to run the SQL Server Profiler tool. Put breakpoints in your code before you call the method that executes the query. Clear the profiler's display, then execute the method. You can capture the SQL from there, then put it into SSMS and view the plan. If the query doesn't use indexes, you need to add indexes that it will use the next time it runs.

I've only ever used Database First, not Code First, so I don't know how you tell Entity Framework to create indexes in the Code First scenario, sorry. But you still need to optimize all of your queries.

Upvotes: 0

NoWar
NoWar

Reputation: 37672

I hope this info will be useful.

  1. Try to add indexes to fields of your table you are including to WHERE in LINQ.

  2. You can always can create extra Views and add it into EF model and do LINQ to them. It will reduce time as well.

  3. If you expect 1 record always try to use SingleOrDefault

Upvotes: 1

Dan
Dan

Reputation: 10548

Try:

objAdWord.Campaigns.FirstOrDefault(c => c.CampaignID == iElementCampaignID && c.Day == sElementDate)

.Where is an O(n) operation, I'm not sure if the Where then FirstOrDefault clause would be optimized but if it's not you're wasting a lot of time using Where. To improve performance further, ensure that CampaignID is indexed

Upvotes: 0

Related Questions