我如何优化这个SQL查询?

Question

查询一个类别的所有论坛帖子。我还需要显示每个帖子的最新评论时间。但查询似乎需要更多的时间，因为它。(这是有道理的)。

但我不知道如何优化它，我不知道在哪里寻找这方面的信息。对于我的问题，任何建议或解决方案都将不胜感激。

查询获取类别的初始帖子

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);

对于每一个帖子，我都必须再调用一次数据库。

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();

<<p>评论表/strong>

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; }
}

<<p>文章表/strong>

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; }
}

Answer 1

您是否试图在一个查询中拖动所有内容?为什么不使用分页技术来获取最近的10-20篇文章，这样可以使查询更快，然后使用单独的查询来获取该文章的评论呢?

如果你需要提取大量数据，那么我建议使用存储过程，因为它通过快速执行计划进行了优化。

我不知道为什么你使用连接来拉评论，你可以在评论字段中添加Post ID，然后使用一个简单的查询来拉该帖子的评论，而不使用连接，除非你不试图从Post表中拉其他数据。

Answer 2

试试这段代码。

添加Reqistration_timestamp属性到ForumPostModel

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

查询

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();