12

I'm using EF (dll version is 4.4) to query against a database. The database contains several tables with course information. When having a look what actually is sent to the db I see a massive, almost 1300 line SQL query (which I'm not going to paste here because of it's size). The query I'm running on the context looks like:

entities.Plans
  .Include("program")
  .Include("program.offers")
  .Include("program.fees")
  .Include("program.intakes")
  .Include("program.requirements")
  .Include("program.codes")
  .Include("focuses")
  .Include("codes")
  .Include("exceptions")
  .Include("requirements")
where plans.Code == planCode
select plans).SingleOrDefault();

I want to avoid having to go back to the server when collecting information from each of the related tables but with such a large query I'm wondering if there is there a better way of doing this?

Thanks.

b3n
  • 3,805
  • 5
  • 31
  • 46
  • Well you're doing eager loading and your query is attempting to get all the data at once, thus why it's large. What exactly do you see as a problem there? Other option is to do lazy loading with virtual properties, that will be fetched in the background by EF when you try to access them, but you did specify you don't want roundtrips to database. – Admir Tuzović Mar 21 '13 at 06:36
  • There's a kind of similar answer here http://stackoverflow.com/questions/5521749/how-many-include-i-can-use-on-objectset-in-entityframework-to-retain-performance – PMC Mar 21 '13 at 06:37
  • I guess I was wondering if I'm looking at this correctly. One huge query vs several small queries or whether there is a better way of doing this. – b3n Mar 21 '13 at 08:16
  • do you really need all this information? what are you going to do with it? A human being will probably not able to comprehend it/process it but it might make sense for some automated processing. I think I would ask myself - if I was not using EF (where creating this kind of query is easy) but would have to write SQL by myself - would I create such a query or would I find what I really need and get this information from the database and send additional queries when needed? (9 joins is quite a lot but might be OK if can be justified) – Pawel Mar 21 '13 at 20:12
  • The information from the other tables is aggregated to display as short summary with course information, so yes I really need it. – b3n Mar 21 '13 at 21:56
  • Have you solved your problem on this? Have you considered using a view to have a cleaner sql? – von v. May 07 '13 at 00:10
  • I'd agree with Pawel. Project the actual fields you do require onto a summary class. What you're doing seems to be the equivalent of a SQL "SELECT *"? – Kevin O'Donovan Jun 03 '13 at 14:22
  • I have not actually solved the problem of the large query being generated as this just seems to be the way things are if you do include a lot of references to be loaded initially. I now run the query once only and then cache the result as the data in the tables is only changing once a day. Unfortunately I do need all this information and it needs to be displayed on one page so lazy loading the entities does not really help. – b3n Jun 08 '13 at 09:15

5 Answers5

1

A bit late but, as your data is only changing once a day, look at putting everything you need into an indexed view and place this view in your EF model.

A Aiston
  • 717
  • 5
  • 12
0

You can usually add the .Include() after the where clause. This means that you're only pulling out information that matches what you want, see if that reduces your query any.

Trent
  • 1,595
  • 15
  • 37
  • 1
    This does not make a difference. I ran the query in LINQPad and the SQL generated is the same in both cases. – b3n Apr 29 '13 at 02:28
  • `after` the where clause and it was still the same? Seems odd...Only other thing I can suggest is to try navigation properties, I believe they work slightly different, but I honestly don't fully understand them. Alternatively, you could pull out the information from the plans table, with the filter/where clause. Then run a few join statements to get what you need. Code isn't quite a succinct, but it should speed things up. – Trent Apr 29 '13 at 04:30
0

As you are performing an eager loading, so if you are choosing the required entities then its fine. Otherwise you can go with Lazy Loading, but as you specified that you don't want database round trip, so you can avoid it.

I would suggest, If this query is used multiple times then you can use compiled query. So that it will increase the performance.

Go through this link, if you want.. http://msdn.microsoft.com/en-us/library/bb896297.aspx

Hitesh
  • 3,508
  • 1
  • 18
  • 24
0

If you're using DbContext, you can use the .Local property on the context to look if your entity is already retrieved and therefore attached to the context.

If the query had run before and your root Plan entities are already attached based on Plan.Code == planId, presumably its sub-entities are also already attached since you did eager loading, so referring to them via the navigation properties won't hit the DB for them again during the lifetime of that context.

This article may be helpful in using .Local.

bcr
  • 1,983
  • 27
  • 30
0

You may be able to get a slightly more concise SQL query by using projection rather than Include to pull back your referenced entities:

var planAggregate = 
 (from plan in entities.Plans
  let program = plan.Program
  let offers = program.Offers
  let fees = program.Fees
  //...
  where plan.Code == planCode
  select new {
    plan
    program,
    offers,
    fees,
    //...
  })
  .SingleOrDefault();

If you disable lazy loading on your context this kind of query will result in the navigation properties of your entities being populated with the entities which were included in your query.

(I've only tested this on EF.dll v5.0, but it should behave the same on EF.dll v4.4, which is just EF5 on .NET 4.0. When I tested using this pattern rather than Include on a similarly shaped query it cut about 70 lines off of 500 lines of SQL. Your mileage may vary.)

Steve Ruble
  • 3,875
  • 21
  • 27