我在SQL Server中创建了以下视图 . 它在视图中使用两组 ROW_NUMBER OVER PARTITION
查询,因为两个引用的表将在 RowInsertDateTime
日期/时间范围内多次出现相同的 ServerName
并且我只对每个表的最新行感兴趣 .
返回471行需要4秒钟 . 没有表包含索引 . 我希望得到一些帮助,以了解我可以添加哪些索引来提高视图的性能 . 我已经检查了实际的执行计划,两种类型负责查询总成本的11%和35% .
视图定义:
CREATE VIEW ViewInSiteSuperTable
AS
SELECT
sales.ServerName,
GETDATE() AS RowInsertDateTime,
sales.daily_sales,
basket.AvgBasketAmount,
basket.AvgBasketQty,
oos.OutOfStockCount,
tph.transactions_per_hour,
tph.total_transactions
FROM
dbo.InSiteEodSalesPerDayPerStore sales WITH (NOLOCK)
INNER JOIN
(SELECT
ServerName,
RowInsertDateTime,
AvgBasketAmount,
AvgBasketQty
FROM
(SELECT
ServerName,
RowInsertDateTime,
AvgBasketAmount,
AvgBasketQty,
ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY RowInsertDateTime DESC) rn
FROM
InSiteAvgBasketSize) q
WHERE
rn = 1) basket ON basket.ServerName = sales.ServeRName
INNER JOIN
(SELECT
ServerName,
RowInsertDateTime,
transactions_per_hour,
total_transactions
FROM
(SELECT
ServerName,
RowInsertDateTime,
transactions_per_hour,
total_transactions,
ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY RowInsertDateTime DESC) rn
FROM
InSiteTxPerHourPerDayTotals) q
WHERE
rn = 1) tph ON tph.ServerName = sales.ServerName
INNER JOIN
dbo.InSiteOutOfStocksAllStores oos WITH (NOLOCK) ON oos.ServerName = sales.ServerName
WHERE
sales.daily_sales_date = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
执行计划
1 回答
消除2个sotrs的指标是:
但是你应该在你的问题中包含
actual execution plan
,而不是作为图片而是使用Paste The Plan在这种情况下,索引不是要删除
scan
而是删除sort
. 无论如何都要扫描这两个表,你想要枚举所有行,这样你就不能删除scan
,但是你想在每个ServerName
组中枚举,它是第一个index key
,你想在每个组中按RowInsertDateTime
排序,所以这是第二个index key
. 订购时,这两个字段已经具有您想要的内容:它们在s组中按顺序排列 .其他字段是
included
,因为它们不需要按顺序排列,但没有它们,查询的索引不是covering
,即服务器将查找基表以获取它们,因为它们存在于select
子句中 .