How do I optimize this SQL query?

Question

The following query gets all the forum posts of a category. And I also need to display the latest comment time for each post. But the query seem to take more time to make because of it. (which makes sense).

But I am not able to figure out how I can optimize it, and I'm not sure where to look for information on this. Any tips or solutions to my problem would be greatly appreciated.

Query to get the initial posts for the category

var query = context.ctm_Forum_Post.Where(x => x.Deleted == false && x.FK_Categori_ID == Id)
    .Select(x => new ForumPostModel()
    {
        Id = x.Id,
        Title = x.Title,
        BodyText = x.BodyText,
        Summary = x.Summary,
        Archieved = x.Archieved,
        Created = x.Created,
        Deleted = x.Deleted,
        MemberID = x.FK_Member_ID,
        Sticky = x.Sticky,
        Updated = x.Updated,
        CategoryId = x.FK_Categori_ID
    }).ToList();

foreach (var item in query)
{
    item.LatestCommentTime = this.GetNewestCommentDateByPost(item.Id);
}
return query.OrderByDescending(x=> x.Created);

And for each and every post I have to make one more call to the database.

 var query = (from comments in context.ctm_Comments
                                 join posts in context.ctm_Forum_Post on comments.Page_ID equals posts.Id
                                 where posts.Id == id && comments.Deleted == false
                                 orderby comments.Reqistration_timestamp descending
                                 select comments.Reqistration_timestamp).FirstOrDefault();

Comments table

 public partial class ctm_Comments
    {
        public int ID { get; set; }
        public int Page_ID { get; set; }
        public int Member_ID { get; set; }
        public string Comment { get; set; }
        public Nullable<System.DateTime> Reqistration_timestamp { get; set; }
        public bool Deleted { get; set; }
        public Nullable<System.Guid> Page_Guid { get; set; }
    }

Post table

public partial class ctm_Forum_Post
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string BodyText { get; set; }
        public string Summary { get; set; }
        public int FK_Categori_ID { get; set; }
        public bool Archieved { get; set; }
        public bool Deleted { get; set; }
        public bool Sticky { get; set; }
        public int FK_Member_ID { get; set; }
        public System.DateTime Created { get; set; }
        public System.DateTime Updated { get; set; }
    
        public virtual ctm_Forum_Category ctm_Forum_Category { get; set; }
    }

An [mre] is always nice. Imagine if I had a `Forum_Post`, `Post`, `Comment` class with only key and fk, the used properties (eg deleted, timestamp), and one bogus property to identify them. We could also have simple list initialsiation with 1 post, 2 comments. — Drag and Drop, Feb 22 '21 at 08:07
Here I think I will not try to make LinQ2Sql write the query. I will do a simple procedure or a view for the lazy. But going 100% sql to avoid any issue during the translation. — Drag and Drop, Feb 22 '21 at 08:10
Related : https://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-relationship — Drag and Drop, Feb 22 '21 at 09:21
and for the linq grouped join : https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-grouped-joins — Drag and Drop, Feb 22 '21 at 09:25
Here is how to do it in memory https://dotnetfiddle.net/kZx9Xi. This solution does not care about translated sql. — Drag and Drop, Feb 22 '21 at 09:38

score 1 · Answer 1 · edited Mar 11 '21 at 06:06

1

Are you trying to pull all in one query? Why don't you pull latest 10-20 posts using paging that will make query fast and then use a separate query to pull comments of that post?

And in case you need to pull large data then I would suggest to use a stored procedure as that is optimized with the fast execution plan.

I am not sure why you are using Join to pull comments, you can add Post ID in Comment field and then use a simple query to pull comments of that post without using Joins unless you are not trying to pull other data from Post table..

edited Mar 11 '21 at 06:06

marc_s

732,580
175
1,330
1,459

answered Feb 22 '21 at 09:25

Learning

19,469
39
180
373

I think I joined because I was trying to get it all in one query, and then when I couldn't I just forgot to remove it ^^ – andrelange91 Feb 22 '21 at 09:30

Meysam Asadi · Answer 2 · 2021-02-22T09:28:32.090

try this code.

add Reqistration_timestamp property to ForumPostModel

public class ForumPostModel
{
    //other property............................... 
    public Nullable<System.DateTime> Reqistration_timestamp { get; set; }
}

query

var query = context.ctm_Forum_Posts
         .Join(context.ctm_Comments,
         post => post.Id,
         comment => comment.Page_ID,
         (post, comment) => new
         {
             p = post,
             c = comment
         }
        ).Where(x => x.p.Deleted == false && x.p.FK_Categori_ID == Id).OrderByDescending(x => x.c.Reqistration_timestamp).Take(1)
        .Select(x => new ForumPostModel()
        {
           Id = x.p.Id,
           Title = x.p.Title,
           BodyText = x.p.BodyText,
           Summary = x.p.Summary,
           Archieved = x.p.Archieved,
           Created = x.p.Created,
           Deleted = x.p.Deleted,
           MemberID = x.p.FK_Member_ID,
           Sticky = x.p.Sticky,
           Updated = x.p.Updated,
           CategoryId = x.p.FK_Categori_ID,
           LatestCommentTime = this.GetNewestCommentDateByPost(x.p.Id),
           Reqistration_timestamp = x.c.Reqistration_timestamp
        }).OrderByDescending(x => x.Created).ToList();

How do I optimize this SQL query?

2 Answers2