请帮我解决这个问题（sql server 2008） [英] Please help me with this query (sql server 2008)

问题描述

ALTER PROCEDURE ReadNews

 @CategoryID INT,
 @Culture TINYINT = NULL,
 @StartDate DATETIME = NULL,
 @EndDate DATETIME = NULL,
 @Start BIGINT, -- for paging
 @Count BIGINT -- for paging

AS
BEGIN
  SET NOCOUNT ON;  

  --ItemType for news is 0
  ;WITH Paging AS
  (
   SELECT news.ID,
     news.Title,
     news.Description,
     news.Date,
     news.Url,
     news.Vote,
     news.ResourceTitle,
     news.UserID,

     ROW_NUMBER() OVER(ORDER BY news.rank DESC) AS RowNumber, TotalCount = COUNT(*) OVER()

   FROM dbo.News news
   JOIN ItemCategory itemCat ON itemCat.ItemID = news.ID
   WHERE itemCat.ItemType = 0 -- news item 
     AND itemCat.CategoryID = @CategoryID
     AND (
       (@StartDate IS NULL OR news.Date >= @StartDate) AND 
       (@EndDate IS NULL OR news.Date <= @EndDate)
      )
     AND news.Culture = @Culture
     and news.[status] = 1

  )  
  SELECT * FROM Paging WHERE RowNumber >= @Start AND RowNumber <= (@Start + @Count - 1)
  OPTION (OPTIMIZE FOR (@CategoryID  UNKNOWN, @Culture UNKNOWN))
END  

以下是新闻和 ItemCategory 表格的结构：

CREATE TABLE [dbo].[News](
 [ID] [bigint] NOT NULL,
 [Url] [varchar](300) NULL,
 [Title] [nvarchar](300) NULL,
 [Description] [nvarchar](3000) NULL,
 [Date] [datetime] NULL,
 [Rank] [smallint] NULL,
 [Vote] [smallint] NULL,
 [Culture] [tinyint] NULL,
 [ResourceTitle] [nvarchar](200) NULL,
 [Status] [tinyint] NULL

 CONSTRAINT [PK_News] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [ItemCategory](
 [ID] [bigint] IDENTITY(1,1) NOT NULL,
 [ItemID] [bigint] NOT NULL,
 [ItemType] [tinyint] NOT NULL,
 [CategoryID] [int] NOT NULL,
 CONSTRAINT [PK_ItemCategory] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

此查询读取特定类别（体育，政治......）的新闻。
@Culture 参数指定新闻的语言，如0（英语），1（法语）等。
ItemCategory 表将新闻记录与一个或多个类别相关联。
ItemType 中的列> ItemCategory table指定 itemID 在那里。现在，我们只有 ItemType 0表示 ItemID 指的是 News <中的记录/ code> table。

This query reads news of a specific category (sport, politics, ...). @Culture parameter specifies the language of news, like 0 (english), 1 (french), etc. ItemCategory table relates a news record to one or more categories. ItemType column in ItemCategory table specifies which type of itemID is there. for now, we have only ItemType 0 indicating that ItemID refers to a record in News table.

目前，我在 ItemCategory 表中有以下索引：

Currently, I have the following index on ItemCategory table:

CREATE NONCLUSTERED INDEX [IX_ItemCategory_ItemType_CategoryID__ItemID] ON [ItemCategory] 
(
 [ItemType] ASC,
 [CategoryID] ASC
)
INCLUDE ( [ItemID])

以及以下新闻表的索引（由查询分析器建议）：

and the following index for News table (suggested by query analyzer):

CREATE NONCLUSTERED INDEX [_dta_index_News_8_1734000549__K1_K7_K13_K15] ON [dbo].[News] 
(
 [ID] ASC,
 [Date] ASC,
 [Culture] ASC,
 [Status] ASC
)

使用这些索引，当我执行查询时，查询对于某些参数和其他参数在不到一秒的时间内执行（例如，不同的@Culture或@CategoryID）最多可能需要2分钟！我使用 OPTIMIZE FOR（@CategoryID UNKNOWN，@ Culture UNKNOWN）来防止参数嗅探 @CategoryID 和 @Culture 参数但似乎对某些参数不起作用。

With these indexes, when I execute the query, the query executes in less than a second for some parameters, and for another parameters (e.g. different @Culture or @CategoryID) may take up to 2 minutes! I have used OPTIMIZE FOR (@CategoryID UNKNOWN, @Culture UNKNOWN) to prevent parameter sniffing for @CategoryID and @Culture parameters but seems not working for some parameters.

目前有大约2,870,000条记录新闻表格和4,740,000美元 ItemCategory 表格。

There are currently around 2,870,000 records in News table and 4,740,000 in ItemCategory table.

现在我非常感谢任何建议如何优化此查询或其索引。

Now I greatly appreciate any advice on how to optimize this query or its indexes.

更新：
执行计划：

（在这张图片中，ItemNetwork就是我所说的ItemCategory。它们是相同的）

update: execution plan:

(in this image, ItemNetwork is what I referred to as ItemCategory. they are the same)

推荐答案

请尝试更改

FROM dbo.News news
JOIN ItemCategory itemCat ON itemCat.ItemID = news.ID

to

FROM dbo.News news
HASH JOIN ItemCategory itemCat ON itemCat.ItemID = news.ID

或

FROM dbo.News news
LOOP JOIN ItemCategory itemCat ON itemCat.ItemID = news.ID

我真的不知道你的数据是什么，但加入这些表可能是一个瓶颈。

I don't really know what is in your data, but the joining of this tables may be a bottleneck.

这篇关于请帮我解决这个问题（sql server 2008）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！