2

in my application i´m using SQL CE 4.0 and Entity Framework Code First. 

public class Customer : IEntityPoco
{
    public  int Id { get; set; }

    public  string Sex          { get; set; }
    public  string Titel        { get; set; }
    public  string FirstName    { get; set; }
    public  string SecondName   { get; set; }
    public  string LastName     { get; set; }
    public  string Fax          { get; set; }
    public  string Notice       { get; set; }
    public  DateTime LastChange { get; set; }

    public  int AddressId   { get; set; }
    public  Address Address { get; set; }

    public  ICollection<Sale> Sales { get; set; }
    public  ICollection<Customer> Partner1 { get; set; }
    public  ICollection<Customer> Partner2 { get; set; }
    public  ICollection<Phone> PhoneNumbers { get; set; }
    public  ICollection<Email> EmailAddresses { get; set; }
    public  ICollection<Corrospondence> Corrospondence { get; set; }
    public  ICollection<ShippingAddress> ShippingAddresses { get; set; }
}

thats what my model looks like,

var query = context.TeacherCustomers
    .Include(i => i.EmailAddresses)
    .Include(i => i.Corrospondence)
    .Include(i => i.Address.City.Country)
    .Include(i => i.ShippingAddresses)
    .Include(i => i.Sales.Select(f => f.BuildVersion.Product))
    .Include(i => i.PhoneNumbers)
    .Include(i => i.Partner1.Select(f => f.Address.City.Country))
    .Include(i => i.Partner2.Select(f => f.Address.City.Country))
    .AsNoTracking();

and that´s what my query look´s like. In my Business Logic i need all Email Addresses, Phone Numbers, ... If i comment all Include Statements this query is executed in about 82ms, if all include statements are not commented the query takes about 52000ms (and it is not the first query so the database model is already compiled).

This post here explains the problem very good: How many Include I can use on ObjectSet in EntityFramework to retain performance?, but the solution given there is also not an option for me because my Database contains more then 10 000 entries and when i execute the query and then stepping through all customers returned by the query and calling context.Entry(customer).Reference(i => i.EmailAddresses).Load() takes also a lot of time.

So how can i create a query that has the same result as the query above but is executed faster?

help would be appreciated

Community
  • 1
  • 1
Julius
  • 192
  • 1
  • 9
  • Did you try it without `AsNoTracking`? It can be counterproductive with respect to performance: http://stackoverflow.com/a/9843614/270591 But it probably won't make a big difference. And do you have appropriate indexes on all the involved FKs in the database? – Slauma Aug 24 '12 at 15:23
  • without AsNoTracking it´s even worse. about the indexes i´m not sure. the complete database is generated from DbContext. all relationships have an foreign key property. – Julius Aug 24 '12 at 16:53

1 Answers1

0

I am not sure if you will be able to get much performance out of this model. You are essentially cross joining all the tables, which will give you a huge table and a lot of records.

I suggest to rethink your model, may be denormalize a bit, do you really need tables for emails, and phone numbers cant they be just multiple columns in the table (most apps do not need more than 2 mails or phone numbers).

If there is nothing you can do about the model, I suggest load all tables separately like use one query to load all Customers, and one to load all emails and so forth then join them in the model. based on the statistics you have provided it will take you less than 400 ms to load all the data.

Ovais
  • 276
  • 1
  • 16
  • thanks for the fast reply. do you think it could be faster with another OR Mapper? Possible NHibernate? – Julius Aug 25 '12 at 10:36