参数值过多会使查询变慢 [英] Too many parameter values slowing down query

问题描述

我有一个查询，在正常情况下运行得相当快.但是由于筛选器中有多少个值，它的运行速度非常慢(在SSMS中至少为20分钟).

I have a query that runs fairly fast under normal circumstances. But it is running very slow (at least 20 minutes in SSMS) due to how many values are in the filter.

这是它的通用版本，您可以看到一部分正在过滤8,000多个值，从而使其运行缓慢.

Here's the generic version of it, and you can see that one part is filtering by over 8,000 values, making it run slow.

SELECT DISTINCT 
    column 
FROM 
    table_a a 
JOIN 
    table_b b ON (a.KEY = b.KEY)
WHERE 
    a.date BETWEEN @Start and @End
    AND b.ID IN (... over 8,000 values)
    AND b.place IN ( ... 20 values)
ORDER BY 
    a.column ASC

在生产应用程序中使用它太慢了.

It's to the point where it's too slow to use in the production application.

有人知道如何解决此问题或优化查询吗?

Does anyone know how to fix this, or optimize the query?

推荐答案

您需要的是将过滤值放在临时表中.然后，使用该表以使用INNER JOIN而不是WHERE IN进行过滤.例如:

What you need is to put the filtering values in temporary table. Then use the table to apply filtering using INNER JOIN instead of WHERE IN. For example:

IF OBJECT_ID('tempdb..#FilterDataSource') IS NOT NULL
BEGIN;
    DROP TABLE #FilterDataSource;
END;

CREATE TABLE #FilterDataSource
(
    [ID] INT PRIMARY KEY
);

INSERT INTO #FilterDataSource ([ID])
-- you need to split values

SELECT DISTINCT column 
FROM table_a a 
INNER JOIN table_b b 
    ON (a.KEY = b.KEY)
INNER JOIN #FilterDataSource FS
    ON b.id = FS.ID
WHERE a.date BETWEEN @Start and @End
    AND b.place IN ( ... 20 values)
ORDER BY .column ASC;

重要提示:

• 我们正在使用临时表，以便允许使用并行执行计划
• 如果您有快速的吐痰功能(例如CLR功能)，则可以加入功能本身
• 在许多值中使用IN不好，SQL Server无法始终构建执行计划，这可能会导致超时/内部错误-您可以找到更多信息此处

• we are using temporary table in order to allow parallel execution plans to be used
• if you have fast (for example CLR function) for spiting, you can join the function itself
• it is not good to use IN with many values, the SQL Server is not able to build always the execution plan which may lead to time outs/internal error - you can find more information here

这篇关于参数值过多会使查询变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！