无法测试查询，但需要 WHERE OR 的最佳索引策略.或.或子句

Question

应用程序每周运行一次例程。上次运行非常慢，我们知道在下一次运行中将处理更多数据。到目前为止，例程中最慢的语句将临时表连接到产品表。探查器显示读取次数非常多，表明它没有很好地编制索引。在上次运行期间，"产品"表有 200,000 行，临时表有 1200 行。

update tmp      
set tmp.col1 = pd.col1, 
tmp.col2 = pd.col2, 
tmp.col3 = pd.col3 
from #temptable tmp
, Products pd with (nolock)      
where tmp.col2 = pd.col2 
or tmp.col2 = pd.col3 
or tmp.col2 = pd.col4       
or tmp.col2 = pd.col5 

我只有一次机会应用索引策略。临时表是从仅存在一小段时间且不存在副本的数据生成的，因此无法在下次运行之前重新创建。计划缓存没有执行计划。

查询应更新为 ANSI-92，但我正在处理找到的查询。

产品表在每列 col2、col3、col4、col5 上都有索引，但没有涵盖更新值的复合或包含。

临时表没有索引。

我没有尝试任何东西，因为在下一次实时运行之前无法进行测试。

谁能建议我是否应该应用涵盖 4 个产品列的复合索引，或者使用四个索引 - 每列一个索引，包含 col1、col2 和 col3？

Answer 1

在没有关于执行时间或计划的统计数据的情况下，考虑到您希望尽快加快速度(即使索引大小增加)，我建议创建 4 个覆盖索引(每列一个)

CREATE NONCLUSTERED INDEX NCI_Products_col2 ON Products (col2) INCLUDE (col1, col3)
CREATE NONCLUSTERED INDEX NCI_Products_col3 ON Products (col3) INCLUDE (col1, col2)
CREATE NONCLUSTERED INDEX NCI_Products_col4 ON Products (col4) INCLUDE (col1, col2, col3)
CREATE NONCLUSTERED INDEX NCI_Products_col5 ON Products (col5) INCLUDE (col1, col2, col3)

并将更新分为 4 个步骤：

-- join by col2 (skip tmp.col2 update)
update tmp set 
tmp.col1 = pd.col1, 
tmp.col3 = pd.col3 
from 
#temptable tmp
INNER JOIN Products pd with (nolock) ON tmp.col2 = pd.col2 

-- join by col3
update tmp set 
tmp.col1 = pd.col1, 
tmp.col2 = pd.col2, 
tmp.col3 = pd.col3 
from 
#temptable tmp
INNER JOIN Products pd with (nolock) ON tmp.col2 = pd.col3
-- join by col4
update tmp set 
tmp.col1 = pd.col1, 
tmp.col2 = pd.col2, 
tmp.col3 = pd.col3 
from 
#temptable tmp
INNER JOIN Products pd with (nolock) ON tmp.col2 = pd.col4
-- join by col5
update tmp set 
tmp.col1 = pd.col1, 
tmp.col2 = pd.col2, 
tmp.col3 = pd.col3 
from 
#temptable tmp
INNER JOIN Products pd with (nolock) ON tmp.col2 = pd.col5

因此，通过删除OR联接，每个更新都应使用其覆盖索引。如果需要将操作作为一个整体进行处理，则可能需要包装事务。

请验证更新顺序，根据原始查询的性质，以不同的顺序执行可能会产生不同的结果(如果可以在Productcol3中找到tmpcol2，并且col5不同的记录，则要更新的值可能会不同)。

Answer 2

您的问题有局限性，因为您无法获得showplan，我宁愿说遵循这些方法。 注意：如果您的服务器自上次运行 proc 以来未重新启动，这将提供索引和昂贵查询的提示。

如果使用临时表到产品表的内部联接，则会增强查询执行。

遵循这些方法

1)按query_stats检查最昂贵的查询，使用以下脚本隔离该存储的proc是否在昂贵的查询列表中

SELECT TOP 5 t.TEXT AS 'SQL Text'
,st.execution_count
,ISNULL(st.total_elapsed_time / st.execution_count, 0) AS 'AVG Excecution Time'
,st.total_worker_time / st.execution_count AS 'AVG Worker Time'
,st.total_worker_time
,st.max_logical_reads
,st.max_logical_writes
,st.creation_time
,ISNULL(st.execution_count / DATEDIFF(second, st.creation_time, getdate()), 0) AS 'Calls Per Second'
FROM sys.dm_exec_query_stats st
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) t
ORDER BY st.total_elapsed_time DESC

2)还要检查最近在服务器中运行的任何查询的索引要求，这是Pinal的一个优秀脚本，如果您无法手动重新运行查询或没有showplan，则需要索引 https://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Ps：这个解决方案只是指示所需的索引，主要是使用你的直觉