CODEWITHSUNDEEP
c#sqllinq-to-sqlquery-performance
Jake
Jake

Reputation: 1332

Linq performance query

I have this query that gives the correct results but it takes about 15 seconds to run

int Count= P.Pets.Where(c => !P.Pets.Where(a => a.IsOwned == true)
.Select(a => a.OwnerName).Contains(c.OwnerName) && c.CreatedDate >= 
EntityFunctions.AddDays(DateTime.Now, -8)).GroupBy(b=>b.OwnerName).Count();

If I remove this part of the linq 

 '&& c.CreatedDate >= EntityFunctions.AddHours(DateTime.Now, -8)'

It only takes about 3 seconds to run. How can I keep the same condition happening but a lot faster? I need that date criteria because I don't want any Classeses that were created 8 days old to be included in the count

Edit

I have a table by the name of People which is referred to in this query as P and I want to return a count of the total of Pets they are that do not have a owner and remove the ones from the query that don't do have an owner even if they exist in another Pet reference has not the owner of that Pet. Meaning if a person has at least one record in the Pets table to be considered as an owner of a pet than I want to remove all cases where that person exist in the return query and once that is done only return the Pets that have been created newer than 8 days

Upvotes: 2

Views: 219

Answers (4)

Gert Arnold
Gert Arnold

Reputation: 109252

You could use (and maybe first create) a navigation property Pet.Owner: 

var refDate = DateTime.Today.AddDays(-8);

int Count= P.Pets
            .Where(p => !p.Owner.Pets.Any(p1 => p1.IsOwned)
                        && p.CreatedDate >= refDate)
            .GroupBy(b => b.OwnerName).Count();

This may increase performance because the Contains is gone. At least it is better scalable than your two-phase query with a Contains involving an unpredictable number of strings.

Of course you also need to make sure there is an index on CreatedDate.

Upvotes: 0

Jake
Jake

Reputation: 1332

By separating the query and calling ToList() on it and inserting it in the master query make it go 4 times faster

 var ownedPetOwnerNames = P.Pets.Where(a => a.IsOwned == true)
                           .Select(a => a.OwnerName).ToList();


int Count = P.Pets.Where(c => c.CreatedDate >= Date&&

  ownedPetOwnerNames.Contains(c.OwnerName)).GroupBy(b=>b.OwnerName).Count();

Upvotes: 0

AtinSkrita
AtinSkrita

Reputation: 1453

You should cache the date and put that evaluation first (since the DateTime evaluation should be faster than a Contains evaluation). Also avoid recalculating the same query multiple times.

DateTime eightDaysOld = EntityFunctions.AddHours(DateTime.Now, -8);

//calculate these independently from the rest of the query
var ownedPetOwnerNames = P.Pets.Where(a => a.IsOwned == true)
                               .Select(a => a.OwnerName);

                              //Evaluate the dates first, it should be 
                              //faster than Contains()
int Count = P.Pets.Where(c => c.CreatedDate >= eightDaysOld &&

                              //Using the cached result should speed this up
                              ownedPetOwnerNames.Contains(c.OwnerName))
                  .GroupBy(b=>b.OwnerName).Count();

That should return the same results. (I hope)

Upvotes: 2

Cam Bruce
Cam Bruce

Reputation: 5689

You are loosing any ability to use indices with that snippet, as it calculates that static date for every row. Declare a DateTime variable before your query and set it to DateTime.Now.AddHours(-8) and use the variable instead of your snippet in the where clause.

Upvotes: 0

Related Questions