3

I have a table which totals 350448 records of which I need to perform multiple, repetitive queries through LINQ with Entity Framework.

The amount of these repetitive queries is approximately 1440, I cannot modify the database.

What would be the best way in terms of performance:

  • Select all 350448 records and query them on the client
  • Perform the 1440 queries against the database
  • Attempt to narrow down the data first, then perform the query on the client

I need to be able to do a lookup for 1440 items basically, but I'm aware of the possible performance problems.

m.edmondson
  • 30,382
  • 27
  • 123
  • 206
  • 1
    One lookup for 1440 items isn't the same as 1440 queries. Please provide more information. – Jon Skeet Feb 29 '12 at 14:49
  • I have a list of 1440 `DateTime` which need to be looked up and a value returned. I can't see any other way then perform a query for each? – m.edmondson Feb 29 '12 at 14:54
  • @m.edmonson: Are these batches matching exact values? – Jon Skeet Feb 29 '12 at 14:56
  • @JonSkeet - No, each lookup will return a unique value - it's basically converting a UTC date to an industry specific date. There are logical rules so I can't just write a function to do the conversion. – m.edmondson Feb 29 '12 at 14:57
  • Best way would be to upload the 1440 values to sql server either by using a table variable (2008 only i think) or a temp table and then running one query against the table containing your 1440 values - I have no idea how you would actually implement using EF though – J Cooper Feb 29 '12 at 14:58
  • @m.edmondson: But it's an exact match, is what I mean? Not some sort of inequality test etc? – Jon Skeet Feb 29 '12 at 14:58
  • @JonSkeet - Sorry yes, there is no ambiguity, each will return exactly one specific date which can be looked up exactly – m.edmondson Feb 29 '12 at 15:00

1 Answers1

0

It sounds like you may be able to get away with:

List<DateTime> dates = ...;
var query = context.MyEntities
                   .Where(x => dates.Contains(x.LastUpdated))
                   .ToList();

However:

  • I'm not sure whether EF supports Contains in the same way that LINQ to SQL does
  • You may need to break the query into a few batches; it depends whether EF is smart enough to use a table-valued parameter (and whether your database supports it).
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • One query batch which returns 1440 records will be a lot faster than 1440 query batches. – Filip De Vos Feb 29 '12 at 15:03
  • @m.edmondson: I'd expect it to be good, yes. Why don't you try it and see whether a) it works and b) it's *good enough* – Jon Skeet Feb 29 '12 at 15:53
  • @m.edmondson: It works with Entity Framework but unfortunately `Contains` is known to be *horrible* slow, especially if the `dates` list has "many" entries: http://stackoverflow.com/a/8108643/270591 – Slauma Feb 29 '12 at 17:26
  • It will be slow, yes, but much faster than doing 1440 individual queries. It will be as fast as it can be expected. – Niels Brinch Jun 25 '12 at 14:01