带有“WHERE”和“”子句的SQL查询优化

Question

我试图优化一个长查询，并提高它的可读性。带和关键字的WHERE子句是否没有正确读取索引或影响读取的性能？

示例:表tblTransactions包含61,795条记录

DECLARE @DateTypeId INT = 1 
DECLARE @FromDate DATETIME = '01/01/2000' 
DECLARE @ToDate DATETIME = '01/01/2019'

SELECT *
FROM tblTransactions ts
WHERE (((ts.TransactionDate BETWEEN @FromDate AND @ToDate) AND @DateTypeId = 1)
        OR ((ts.PostingDate BETWEEN @FromDate AND @ToDate) AND @DateTypeId = 2))

执行的总时间是9秒。这是查询的另一个示例。

DECLARE @DateTypeId INT = 1
DECLARE @FromDate DATETIME = '01/01/2000'
DECLARE @ToDate DATETIME = '01/01/2019'

IF @DateTypeId = 1
    SELECT *
    FROM tblTransactions ts
    WHERE ts.TransactionDate BETWEEN @FromDate AND @ToDate

ELSE IF @DateTypeId = 2
    SELECT *
    FROM tblTransactions ts
    WHERE ts.PostingDate BETWEEN @FromDate AND @ToDate

此查询的执行时间为8-9秒。就可读性而言，我更喜欢第一个查询，但就性能而言，我更喜欢第二个查询。但是，如果查询太长，建议执行的查询是哪一个？

Answer 1

优化是一项微妙的任务。这在很大程度上取决于学科的能力。

了解表结构(列、数据、索引)非常重要，重要的是知道要执行的查询(选择和联接中的列和/或所涉及的函数)。

我们也应该讨论一下您的硬件和sql-server版本，但是它将成为一个非常深入的分析。

让我们试着简化一下。

如果索引对于检索所选列没有用处(我不是说过滤行)，优化器将(几乎)不使用它们，并且它将直接对聚集索引进行完整的表扫描(至少必须具有此功能)。

如果您的选择与您的问题(select * from tblTransactions)一样简单，我们必须考虑表结构。

因此，重点是，如果您只选择了几个列，您就可以构建一个包含这些列的索引，并且它将被使用，并且您会非常高兴(确定？)。

当索引中包含列时，它们不是索引键的一部分，而是用作有效负载数据。

问题是，包含列，在索引中占用的空间与表中的空间一样大。因此，如果您有数千条包含大列的记录，您将为所涉及的数据复制生成巨大的索引。

在考虑了所有这些之后，您可以建立超级索引，其中包括所有选定的列。也许您可以发现，总的查询执行时间几乎是相同的。

所花费的大部分时间不是找出要提取的数据，而是查找要检索的I/O操作，并将它们放在输出中。

好的，现在有一些建议和选择：

1)我将保留IF/ELSE版本，越简单越好。

2)尝试添加包含在SELECT中的列的索引(使用名称..)并运行您的查询，检查它们是否被使用，以及是否有性能改进。

CREATE NONCLUSTERED INDEX [IX_TRAN_DATE-INCL-COLS]
ON [dbo].[tblTransactions] ([TransactionDate])
INCLUDE ([PostingDate],[Col1],[Col2],[Col3])

CREATE NONCLUSTERED INDEX [IX_POST_DATE-INCL-COLS]
ON [dbo].[tblTransactions] ([PostingDate])
INCLUDE ([TransactionDate],[Col1],[Col2],[Col3])

3)您可以尝试混合解决方案、更简单的索引和复杂的查询。

CREATE NONCLUSTERED INDEX [IX_TRAN_DATE]
ON [dbo].[tblTransactions] ([TransactionDate])

CREATE NONCLUSTERED INDEX [IX_POST_DATE]
ON [dbo].[tblTransactions] ([PostingDate])

DECLARE @IDX TABLE (ID INT PRIMARY KEY)

IF @DateTypeId = 1 BEGIN

    INSERT INTO @IDX
    SELECT ts.TransactionID
    FROM tblTransactions ts
    WHERE ts.TransactionDate BETWEEN @FromDate AND @ToDate

END ELSE IF @DateTypeId = 2 BEGIN

    INSERT INTO @IDX
    SELECT ts.TransactionID
    FROM tblTransactions ts
    WHERE ts.PostingDate BETWEEN @FromDate AND @ToDate

END

以上每一项都要测试..。而且(也许)它可能是无用的。

我希望这将是有用的，了解一点，优化的大问题。

Answer 2

请尝试更好的表现：

 DECLARE @DateTypeId INT = 1
 DECLARE @FromDate DATETIME = '01/01/2000'
 DECLARE @ToDate DATETIME = '01/01/2019'

 IF @DateTypeId = 1
 SELECT *
 FROM tblTransactions ts
 WHERE  @FromDate >= ts.TransactionDate <= @ToDate

 ELSE IF @DateTypeId = 2
 SELECT *
 FROM tblTransactions ts
 WHERE  @FromDate >= ts.PostingDate <= @ToDate

Answer 3

由于单独的查询运行的时间与合并的查询一样长，所以我将继续使用合并的查询(即您也喜欢的第一个查询)。

查询中的唯一条件是日期，在一种情况下是TransactionDate，在另一种情况下是PostingDate。确保对它们有单独的索引，以便给予DBMS查找日期范围的选项：

create index idx_transact_transaction_date on tbltransactions(transactiondate);
create index idx_transact_posting_date on tbltransactions(postingdate);