为什么“在"?sql 存储过程中的查询标记如此昂贵? [英] Why "IN " query tag is so costly in sql stored procedures?

问题描述

如何改善我的性能问题?我有一个带有IN"的 sql 查询，我猜IN"会产生一些代价高昂的性能问题.但是我需要索引我的 sql 查询?

How can I improve my performance issue? I have an sql query with 'IN' I guess 'IN' making some costly performance issue. But I need index my sql query?

我的sql查询:

SELECT [p].[ReferencedxxxId]  
FROM [Common].[xxxReference] AS [p]  
WHERE ([p].[IsDeleted] = 0)  
  AND (([p].[ReferencedxyzType] = @__refxyzType_0)
  AND [p].[ReferencedxxxId] IN ('42342','ffsdfd','5345345345'))

我的解决方案:(但我需要您的帮助以获得更好的建议)哪个是正确的聚集索引或非聚集索引?

My solution: (BUT I NEED YOUR HELP FOR BETTER ADVISE) Whichone is correct clustered or nonclustred index?

USE [xxx]
GO
CREATE NONCLUSTERED INDEX IX_NonClusteredIndexDemo_xxxId
ON [Common].[xxxReference](xxxId)
INCLUDE ([ID],[ReferencedxxxId])
WITH (DROP_EXISTING=ON, ONLINE=ON, FILLFACTOR=90)
GO

第二:

CREATE INDEX xxxReference_ReferencedxxxId_index
ON [Common].[xxxReference]  (ReferencedxxxId)[/code]

哪个是正确的，或者您有更好的解决方案?

Whichone is correct or do you have better solution?

推荐答案

两件事:

1. 如果你在 IN 中有一个 SELECT 语句，那应该避免并且应该替换为 EXISTS 子句.但在你上面例如，这不相关，因为您在 IN 中有直接值.

1. If you have a SELECT statement inside the IN, that should be avoided and should be replaced with an EXISTS clause. But in your above example, that is not relevant as you have direct values inside IN.

使用 EXISTS 和 NOT EXISTS 代替 IN 和 NOT IN 有助于 SQL服务器不需要扫描每个列的每个值IN/NOT IN 内的值，而是可以短路搜索一次找到匹配或不匹配的内容.

Using EXISTS and NOT EXISTS instead of IN and NOT IN helps SQL Server to not needing to scan each value of the column for each values inside the IN / NOT IN and rather can short circuit the search once a match or non-match found.

避免隐式转换.它们会降低性能，因为许多原因包括 i> SQL Server 无法找到正确的指数的统计数据，因此无法利用指数和宁愿使用表中可用的聚集索引(可能未涵盖您的查询)，ii> 未正确分配存储查询的内存分配阶段所需的 RAM引擎，iii> 基数估计变得错误，因为 SQL Server不会有关于该列的计算值的统计信息，并且更可能有关于该列的统计数据.

Avoid the implicit conversion. They degrade the performance due to many reasons including i> SQL Server not able to find proper statistics on an index and hence not able to leverage an index and would rather go make use of a clustered index available in the table (which may not be covering your query), ii> Not assigning proper required RAM during memory allocation phase of the query by storage engine, iii> Cardinality estimation becomes wrong as SQL Server would not have statistics on the computed value of that column, and rather probably had statistics on that column.

如果您查看上面发布的执行计划，您会看到您的选择"中的黄色标记.如果你将鼠标悬停在它上面，你会看到一个/多个警告消息.如果您的警告与隐式有关转换，在比较时尽量使用正确的数据类型.

If you look at your execution plan posted above, you will see a yellow mark in your 'SELECT'. If you hover over it, you will see one/more warning messages. If your warning is related to implicit conversion, try to use proper datatypes during comparison.

例如.[ReferencedxxxId]"列的数据类型是什么?如果它不是 NVARCHAR 而是 VARCHAR，那么我建议:

Eg. What is the datatype of the column '[ReferencedxxxId]'? If it is not an NVARCHAR and is rather a VARCHAR, then I would suggest:

将 IN 中的值设为 VARCHAR(目前您将它们设为 NVARCHAR).这样，您仍然可以充分利用在 [ReferencedxxxId] 列上创建的行存储索引.

Make the values inside the IN as VARCHAR (currently you are making them NVARCHAR). This way you will still be able to take full advantage of the rowstore index created on [ReferencedxxxId] column.

如果您必须在 IN 子句中使用 NVARCHAR 形式的值，那么您应该:

If you must have the values as NVARCHAR inside the IN clause, then you should:

• 转换/转换 IN 子句中的 [ReferencedxxxId] 列.这将摆脱隐式转换，但您将无法再充分利用 [ReferencedxxxId] 列上的行存储索引.

• CONVERT/CAST the column [ReferencedxxxId] in your IN clause. This is going to get rid of the Implicit conversion but you will no longer be able to take full advantage of the rowstore index on [ReferencedxxxId] column.

+

而是在覆盖查询中使用的列的表上创建聚集/非聚集列存储索引.这应该会显着提高 SELECT 查询的性能.

Rather create a clustered/nonclustered columnstore index on the table covering the columns used in the query. This should significantly enhance the performance of your SELECT query.

如果您决定通过更正 IN 中的值来使用行存储索引，您需要确保创建一个覆盖查询的聚集/非聚集索引.这意味着索引涵盖了您正在搜索的列([ReferencedxxxId]、[ReferencedxxxType]、[IsDeleted])，然后在 INCLUDE 子句下包含 SELECT 语句中使用的列(如果它是非聚集索引)

If you decided to go with the route of using rowstore index by correcting the values inside the IN, you need to make sure that you create a clustered/nonclustered index which covers the query. Meaning the index covers the columns on which you are doing search ([ReferencedxxxId], [ReferencedxxxType], [IsDeleted]) and then including the columns used in SELECT statement under INCLUDE clause (if it is a nonclustered index)

此外，在创建复合行存储索引时，请尝试将索引内的列顺序从左到右保持从高基数到低基数，以充分利用该索引.

Also, when you are creating a composite rowstore index, try to keep the order of columns inside the index high cardinality to low cardinality from left to right to make the best use of that index.

这篇关于为什么“在"?sql 存储过程中的查询标记如此昂贵?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！