Optimizing join statement

I have this piece of code:

List 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.

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 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. You really should only use a List 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 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 items = 
    (from x in db.myItems
     where !x.tblParent.hidden
     orderby x.name
     select x)
    .ToList();

Or in fluent syntax:

List items = db.myItems
    .Where(x => !x.tblParent.hidden)
    .OrderBy(x => x.name)
    .ToList();

StackExchange Code Review Q#30862, answer score: 7