How to optimize this Linq query

Question

I am trying to optimize this query. It is not slow for now but I am expecting a spike to hit this query in the near future. Is there anything else i can do to make this query faster?

var posts = from p in context.post
                        where p.post_isdeleted == false && p.post_parentid == null
                        select new
                        {
                            p.post_date,
                            p.post_id,
                            p.post_titleslug,
                            p.post_title,
                            p.post_descriptionrender,
                            p.userinfo.user_username,
                            p.userinfo.user_userid,
                            p.userinfo.user_GravatarHash,
                            p.userinfo.user_points,
                            p.category.catid,
                            p.category.name,
                            p.post_answercount,
                            p.post_hasbestanswer,
                            p.post_hits,
                            p.post_isanonymous,
                            p.post_votecount,
                            FavoriteCount = context.favorites.Where(x => x.post.post_id == p.post_id).Count(),
                            tags = from tg in context.posttag
                                   where tg.posttag_postid == p.post_id
                                    select new
                                    {
                                        tg.tag.tag_id,
                                        tg.tag.tag_title
                                    }
                        };

Answer 1

In a general sense you could look into caching that information, but nothing is intrinsically "slow" about the query. This will really depend on how the query is being used (how often, what data is being hit, etc). There are a lot of possible optimization solutions for a given problem, and though you may find improvements based on intuition you'll have a much easier time doing so if you have profiling tools in place to nail down problem areas. Plus, you'll have the satisfaction of proving that the areas you improve are worth the time investment.

Answer 2

A possible optimization for this query whould be, only to load the Ids of other related objects (UserInfo, Category, Tag), and initialize this objects only on demand using a lazy loading strategy. Or executing an other query to resolve these objects.

But it depends on, how you use the result of the query. Maybe you need all the information of the related objects, or you need only some information of these objects or the Id of the objects is enough, cause you need the Ids for other queries.

Answer 3

I could make the LINQ cleaner (by using associations instead of psuedo-joins), but it wouldn't make it any faster. To make it faster you probably need to look at DB indexing.