I have this piece of code:

List<myItem> items = (from x in db.myItems
                        join y in db.tblParents on x.item_parent equals y.id
                        where !y.hidden
                        orderby x.name
                        select x).ToList();

And it is really slow. How can I optimize it? I need it to be faster. I have no idea how to do this.

  • 2
    \$\begingroup\$ Does the equivalent TSQL join run any faster in SSMS? \$\endgroup\$ – Mathieu Guindon Sep 6 '13 at 15:48

I honestly don't see anything particularly wrong with that Linq query, but there are a number of other things you should consider:

  • Converting the query to a List<T> forces immediate evaluation of the query and requires the allocation of an single, contiguous portion of memory to store the results. If there are several thousand records, this can be fairly costly. If you later filter / re-order / group this list, it must be done in memory, which is usually much slower than allowing the database to filter / re-order / group the results. Generally, you should try to put off evaluating results until as late as possible.
  • Even if you're not doing any further processing on the list, it may not be necessary to actually store the result set as a List<T>. You really should only use a List<T> when you need to be able to add or remove items from the result set dynamically. If you only need to be able to access elements in the result set by index (particularly in O(n) time), I'd recommend using an array instead (with ToArray). If all you need to do is iterate through the result set one item at a time, I'd strongly recommend using an IEnumerable<T> instead (with AsEnumerable). This way, your application doesn't need to pull all items in at once, and it doesn't have to allocate a single, contiguous portion of memory to store them.
  • If you've done all you can to speed up the C# code, and it still runs slowly, you should look at optimizing the database—your code can't run any faster than the back-end database, after all. Perhaps you need to add an index to the item_parent column (and probably a foreign key as well).

If you've setup a proper navigation property you could write your query a bit more cleanly, although I doubt this would run any faster, since this will still translate to an INNER JOIN in SQL:

List<myItem> items = 
    (from x in db.myItems
     where !x.tblParent.hidden
     orderby x.name
     select x)

Or in fluent syntax:

List<myItem> items = db.myItems
    .Where(x => !x.tblParent.hidden)
    .OrderBy(x => x.name)
| improve this answer | |
  • 1
    \$\begingroup\$ Somehow the second fluent option in this case just looks cleaner... \$\endgroup\$ – dreza Sep 6 '13 at 20:17

It may be the performance issue of SQL Server itself, not C# code. Try to check execution plan of this query to see what's going on. Additional indexes may help, or database defragmentation.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.