索引字段不会有帮助：如何改善查询时间

The first/uncached query takes a long time if I check the [deliverybody] field contains 'x' or not, to eliminate the rows with 'x'. [deliverytime] is indexed and query completed in a second if I don't check for 'x'.

如果我检查[deliverybody]字段是否包含'x',则第一个/未缓存的查询需要很长时间,以消除带有'x'的行。如果我不检查'x',[deliverytime]会被索引并在一秒钟内完成查询。

With AND [deliverybody] <> 'x' part the query execution time is 10+ seconds, without; 1 second. It seems like indexed field [deliverytime] won't help too much.

使用AND [deliverybody] <>'x'部分,查询执行时间为10+秒,没有; 1秒。看起来索引字段[deliverytime]似乎无济于事。

Query only returns 1600 rows. Scanning extra 1600 fields takes 10+ seconds. What's wrong with the query?

查询仅返回1600行。扫描额外的1600个字段需要10秒以上。查询有什么问题?

UPDATE: Upgraded from NTEXT to NVARCHAR(MAX). Execution plans of inner query:

更新:从NTEXT升级到NVARCHAR(MAX)。内部查询的执行计划:

SLOW Execution plan as saved: http://pastebin.com/7X7uf6iV

已保存的SLOW执行计划:http://pastebin.com/7X7uf6iV

This query takes 10+ seconds:

此查询需要10秒以上:

  SELECT MAX([deliveryid]) AS deliveryid, COUNT(*) AS cnt  
  FROM [_hMaiServer].[dbo].[hm_deliverylog]
  WHERE [deliverytime] > DATEADD(HOUR, -24, GETDATE()) 
  AND [deliverybody] <> 'x' 
  GROUP BY deliverysubject

FAST Execution plan as saved: http://pastebin.com/EEnPGf4X

已保存的快速执行计划:http://pastebin.com/EEnPGf4X

The query completed in a second:

查询在第二个完成:

SELECT MAX([deliveryid]) AS deliveryid, COUNT(*) AS cnt  
FROM [_hMaiServer].[dbo].[hm_deliverylog]
WHERE [deliverytime] > DATEADD(HOUR, -2400, GETDATE()) 
--AND [deliverybody] <> 'x' 
GROUP BY deliverysubject

And the table structure with the indexes: http://pastebin.com/W0PsDnqS

和表结构的索引:http://pastebin.com/W0PsDnqS

My conclusion:

Checking 5000 rows takes 10+ seconds. Smaller {HOUR} value makes the execution time quicker. If this is OK here is no problem but it seems slow to me.

检查5000行需要10秒以上。较小的{HOUR}值使执行时间更快。如果这没关系,这里没问题,但对我来说似乎很慢。

SELECT COUNT(*) FROM [hm_deliverylog]
WHERE [deliverybody] <> 'x' 
AND [deliverytime] > DATEADD(HOUR, -__{HOUR}__ , GETDATE())

1 个解决方案

#1

You haven't provided the requested SET STATISTICS IO ON; results.

您尚未提供请求的SET STATISTICS IO ON;结果。

One possible improvement however might be instead of using

然而,一种可能的改进可能不是使用

WHERE [deliverybody] <> 'x'

to use

WHERE NOT (LEN([deliverybody]) = 1 AND LEFT([deliverybody],1) = 'X')

An example where this is beneficial below.

下面这是有益的一个例子。

CREATE TABLE T1
  (
     Id             INT,
     [deliverybody] VARCHAR(MAX)
  )

INSERT INTO T1
VALUES     (1, Replicate(Cast('A' AS VARCHAR(MAX)), 2000000000)),
           (2,'X')

SET STATISTICS IO ON;

SELECT id
FROM   T1
WHERE  NOT ( Len([deliverybody]) = 1
             AND LEFT([deliverybody], 1) = 'X' )

SELECT id
FROM   T1
WHERE  [deliverybody] <> 'X'

DROP TABLE T1

The IO results for both are below

两者的IO结果如下

Table 'T1'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0
            , lob logical reads 6, lob physical reads 0, lob read-ahead reads 0.

Table 'T1'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0
    lob logical reads 2209665, lob physical reads 0, lob read-ahead reads 642255.

The first one has significantly fewer reads as it avoids dragging out the whole 2GB value in order to discover that it is not X

第一个读取数量明显减少,因为它避免拖出整个2GB值以发现它不是X.

#1