需要优化select查询

Question

我有一个查询，它使用多个表的连接进行选择，这些表总共包含大约9000万行。我只需要最近30天的数据。问题是，当我运行选择查询时，sql服务器抛出超时，而查询正在运行，并且在此时间框架内没有创建新记录。此查询大约需要5秒才能完成。

我想优化这个查询，这样它就不会遍历整个表查看日期时间，只会从最新的条目进行搜索。

现在看来，我需要索引datetime列。请告知我是否需要创建索引，或者是否有另一种方法来优化此查询。

SELECT [table1].Column1 AS InvoiceNo,
'ND' AS VATRegistrationNumber,
'ND' AS RegistrationNumber,
Column2 AS Country,
[table2].Column3 + ' ' + [table2].Column4 AS Name,
CAST([table1].Column5 AS date) AS InvoiceDate,
'SF' AS InvoiceType,
'' AS SpecialTaxation,
'' AS VATPointDate,
ROUND([table1Line].Column6, 2) AS TaxableValue,
CASE
WHEN [table1Line].Column7 = 9 THEN 'PVM2'
WHEN [table1Line].Column7 = 21 THEN 'PVM1'
WHEN [table1Line].Column7 = 0 THEN 'PVM14'
END AS TaxCode,
CAST([table1Line].Column7 AS int) AS TaxPercentage,
table1Line.Column8 - ROUND([table1Line].Column6, 2) AS Amount,
'' AS VATPointDate2,
[table1].Column1 AS InvoiceNo,
'' AS ReferenceNo,
'' AS ReferenceDate,
[table1].CustomerPersonID AS CustomerID
FROM [table1]
INNER JOIN [table2] ON [table1].CustomerPersonID = [table2].ID
INNER JOIN [table3] ON [table2].Column9 = [table3].ID
INNER JOIN [table1Line] ON [table1].ID = [table1Line].table1ID
INNER JOIN [table4] ON table1Line.TaxID = Tax.ID
INNER JOIN [table5] ON [table1].CompanyID = Company.ID
INNER JOIN table6 ON [table1].SalesChannelID = table6.ID
WHERE Column5 LIKE '%date%'
AND table6.id = 5
OR table6.id = 2
AND Column5 LIKE '%date%'
ORDER BY Column5 DESC;

Answer 1

首先，每个数据库的运行都略有不同，因为优化器一直在运行，并找出如何改进独特的环境，并不断尝试使常见的东西运行得更好。

版本差异也会影响服务器的性能。

除了这些东西，这里有一些事情要做，以优化这个查询。当使用join时，首先使用已连接表，然后与已指定的表进行比较。

例如t2检查t1:

select t1.name, t2.car
from customers as t1
left join purchases as t2
on t2.customerid = t1.customerid

接下来我看到的是代码Where部分的Like条件。
在您的示例中，它查找的存储日期存储为文本。
我建议将日期处理为日期时间，而不是数据类型的字符串类型。

我会把它包含在下面的代码中，但是我不确定你的文本字符串的格式是什么。

%date%相当于说"Contains date"。
这将获取日期字符串，并尝试查看它是否与从左到右的每个字符位置匹配。

所以如果你的日期文本是20200130，它将检查它是否匹配2date0200130，然后尝试20date200130，然后尝试202date00130，等等。
这会显著增加处理所需的时间。

我还看到日期被意外地搜索了两次，而不是一次。

我建议这样做:

WHERE LTRIM(RTRIM(Column5)) LIKE 'date'

至于内部连接，我不会使用它们。使用左连接，然后在Where中，我将确保该连接数据没有Null值。
这使得左连接与内连接工作相同，并且在运行查询时运行得更优。

例如，第一个Join看起来像这样:

FROM [table1]
LEFT JOIN [table2] ON [table2].ID = [table1].CustomerPersonID
WHERE table2.id IS NOT NULL

我在Where语句中看到一个错误:

AND table6.id = 5
OR tables6.id = 2

应该是:

AND (tables6.id = 5 OR tables6.id = 2)

所以这里应该是你的代码的优化版本:

SELECT [table1].Column1 AS InvoiceNo,
'ND' AS VATRegistrationNumber,
'ND' AS RegistrationNumber,
Column2 AS Country,
[table2].Column3 + ' ' + [table2].Column4 AS Name,
CAST([table1].Column5 AS date) AS InvoiceDate,
'SF' AS InvoiceType,
'' AS SpecialTaxation,
'' AS VATPointDate,
ROUND([table1Line].Column6, 2) AS TaxableValue,
(CASE WHEN [table1Line].Column7 = 9 THEN 'PVM2'
WHEN [table1Line].Column7 = 21 THEN 'PVM1'
WHEN [table1Line].Column7 = 0 THEN 'PVM14'
ELSE '' END ) AS TaxCode,
CAST([table1Line].Column7 AS int) AS TaxPercentage,
table1Line.Column8 - ROUND([table1Line].Column6, 2) AS Amount,
'' AS VATPointDate2,
[table1].Column1 AS InvoiceNo,
'' AS ReferenceNo,
'' AS ReferenceDate,
[table1].CustomerPersonID AS CustomerID
FROM [table1]
LEFT JOIN [table2] ON [table2].ID = [table1].CustomerPersonID
LEFT JOIN [table3] ON [table3].ID = [table2].Column9
LEFT JOIN [table1Line] ON [table1Line].table1ID = [table1].ID
LEFT JOIN [table4] ON [table4].ID = table1Line.TaxID
LEFT JOIN [table5] ON [table5].ID = [table1].CompanyID
LEFT JOIN [table6] ON table6.ID = [table1].SalesChannelID
WHERE table2.ID IS NOT null
AND table3.ID IS NOT null
AND table1Line.ID IS NOT null
AND table4.ID IS NOT null
AND table5.ID IS NOT null
AND table6.ID IS NOT null
AND LTRIM(RTRIM(Column5)) LIKE 'date'
AND (table6.id = 5 OR table6.id = 2)
ORDER BY Column5 DESC;