优化SQL查询 [英] optimising SQL query

问题描述

亲爱的，



我在新闻门户网站中使用以下查询（作为存储过程），问题是此查询的执行速度非常高时间和它被认为是昂贵的查询，我需要帮助优化它。

 选择  Top （ @ Count ）site_News.NewsID，site_News.Title，count（site_Replies.ReplyID）  as  '  replies-count' 
 来自 site_News 内部 加入 site_Replies 
   site_News.NewsID = site_Replies.NewsID 
 内部 加入 site_Categories   site_News.CategoryID = site_Categories.Category ID 
 其中 site_news.AddDate> DATEADD（day，-7，GETDATE（））和 site_Replies.Accept =  1  和 site_Replies.Active =  1  
 和 site_Categories。 LanguageID =  @ LanguageID  
  group   by  site_News.NewsID，site_News.Title 
  order   by  '  replies-count'  desc  

该查询旨在获取活动回复数量最多的新闻，必须在过去7天内添加新闻。

提前致谢。

解决方案

您只需确保将以下列编入索引。如果没有索引它们，那么请为这些列创建索引并在数据库中重新执行查询。

1.site_News.NewsID

2.site_Replies.NewsID < br $>
3.site_News.CategoryID

4.site_Categories.CategoryID

5.site_news.AddDate

6.site_Categories。语言ID

7.site_Replies.Accept

8.site_Replies.Active

这是一个经常被问到的问题，但答案是： 没有人可以为你做这件事。



有要提高查询性能的几项工作 [ ^ ]。

Dear all,

I'm using the following query (as stored procedure) in a news portal, the problem is this query is taking very very high execution time and it's considered as expensive query, i need help optimizing it.

Select Top(@Count) site_News.NewsID, site_News.Title, count(site_Replies.ReplyID) as 'replies-count'
From site_News Inner Join site_Replies
on site_News.NewsID = site_Replies.NewsID
Inner Join site_Categories on site_News.CategoryID = site_Categories.CategoryID
Where site_news.AddDate > DATEADD (day , -7 , GETDATE() ) And site_Replies.Accept = 1 And site_Replies.Active = 1
And site_Categories.LanguageID = @LanguageID
group by site_News.NewsID, site_News.Title
order by 'replies-count' desc

The query aims to get the news with highest number of active replies, the news must be added in the past 7 days.
Thanks in advance.

解决方案

You just make sure following columns are indexed. If any one them is not indexed then pls create indexes for those columns and re execute your query in your database.
1.site_News.NewsID
2.site_Replies.NewsID
3.site_News.CategoryID
4.site_Categories.CategoryID
5.site_news.AddDate
6.site_Categories.LanguageID
7.site_Replies.Accept
8.site_Replies.Active

This is quite often asked question, but the answer is: No one can do it for you.

There are several things to do to improve query performance[^].

这篇关于优化SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！