Linq performance query

Question

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

it would be good if you could explain what you want from your query. it looks like too nested and complicated. — Ehsan, Aug 02 '13 at 19:12
It would be helpful to see the query that is being executed. Are you able to intercept it with the profiler? — Keith Payne, Aug 02 '13 at 19:14
Please explain what the query needs to return. It looks like it can be much simpler than this. — ataravati, Aug 02 '13 at 19:17
@jake place a profiler on your database and intercept the query that is executed against your LINQ — Ehsan, Aug 02 '13 at 19:20
@Jake see http://stackoverflow.com/questions/4700323/how-to-get-the-sql-generated-by-the-entity-framework for how to do it — Keith Payne, Aug 02 '13 at 19:26
@Keith I tried the ToTraceString and ToString and always received notsupportexpcetion error for trying to generate the sql — Jake, Aug 02 '13 at 21:19

score 2 · Answer 1 · answered Aug 02 '13 at 20:32

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)

score 0 · Answer 2 · answered Aug 02 '13 at 19:09

0

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.

answered Aug 02 '13 at 19:09

Cam Bruce

5,632
19
34

It is probable that SQL Server chooses a non-indexed query plan, but it is not necessarily because of the function call. – Keith Payne Aug 02 '13 at 19:13
I changed the code to be a Date variable instead of the EntityFunctions and still the same speed – Jake Aug 02 '13 at 19:13
Look at the generated sql for that query and view the query plan. It will tell you any missing indexes or bottlenecks. – Cam Bruce Aug 02 '13 at 19:15

score 0 · Answer 3 · answered Aug 02 '13 at 21:22

0

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();

answered Aug 02 '13 at 21:22

Jake

1,332
5
23
35

Maybe with a small amount of data. What if `ownedPetOwnerNames` contains 10,000 names? The query would not even run. – Gert Arnold Aug 02 '13 at 21:36
@GertArnold - what do you mean the query would not even run? – Jake Aug 03 '13 at 01:20
In linq-to-sql each string in `ownedPetOwnerNames` will be put in a variable. With some 2000 variables you will get an exception that there are too many variables in a procedure call. Besides that, it becomes very slow. – Gert Arnold Aug 03 '13 at 07:21
@GertArnold so what is a better way to make it faster? This is the only approach I saw that actually helped – Jake Aug 03 '13 at 20:25
Please see my proposed solution. – Gert Arnold Aug 03 '13 at 20:43

score 0 · Answer 4 · answered Aug 03 '13 at 20:43

0

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.

answered Aug 03 '13 at 20:43

Gert Arnold

105,341
31
202
291

what do you mean a navigation property. I never tried that before some direction is needed. – Jake Aug 03 '13 at 21:04
A navigation property is just a property that "navigates" from one entity to other entities, like Owner.Pets, or Pet.Owner. They are equivalent to the foreign keys in your database. When you create a linq-to-sql diagram you will see these properties appear as associations between the entities (*if* your database has foreign keys). – Gert Arnold Aug 03 '13 at 21:15
They are already using foreign key relationships so does that mean its already using Navigation property? – Jake Aug 04 '13 at 18:05
Well, have you got things like `Owner.Pets`? – Gert Arnold Aug 04 '13 at 19:56
@GertArnold- I am confused on how I could set this up. I never really gave out the true details of my table because I wanted to simplify it as much as possible; but not sure how to make ur design work in My tables. I have a table with thousands of Pets and some are Owned 'owned=true' and I have the PetSourceName in this table.The PetSourceName is where the person came from that added this pet to the Pet table. I don't track anything else from the PetSource. But Once a PetSourceName has a pet in the table that is Owned I don't what any of its other pets returned in my list. – Jake Aug 06 '13 at 16:06
P 'People' is still the new owner of the Pet. and all the attributes of P. I store the P Id in the Pets table so I can easily reference the new owner, but as I said I want to remove all the Pets with the PetSourceName from returned lists where the PetSourceName has a Pet owned. Only Thing I track within all my tables is the PetSourceName which is unquire from the old source because it is typically throw away data. – Jake Aug 06 '13 at 16:08
You shouldn't store names as reference, only Id values and make them a FK to People. What if a person's name changes and his name is in PetSourceName? Once you've got proper foreign keys you exploit navigation properties in queries. – Gert Arnold Aug 07 '13 at 13:54
you not understanding. I dont have the ID of the PetSourceName. Only the ID, but acts like the name will never change so its just good as an Id. It can be a FK to people. People is a third party. So basically PetSource is not an entity, only information on PetSource is PetSourcename – Jake Aug 07 '13 at 13:59

Linq performance query

4 Answers4