过滤器中存在返回的值过多 [英] EXISTS in filter returning too many values

问题描述

我需要编写一个使用EXISTS而不是IN的查询，这样它才能快速运行.过滤器被输入了太多的参数值，以至于EXISTS似乎是唯一的选择.区别在于20分钟以上的查询和5秒的查询.

I need to write a query that uses EXISTS, rather than IN, so that it will run fast. The filter is being fed so many parameter values that EXISTS seems like the only option. The difference is between a 20+ minute query and a 5 second query.

这是我的查询:

SELECT DISTINCT d.GROUP_NAME
FROM [EMPLOYEE] e JOIN [DATA_FACT] d ON (e.KEY = d.KEY)
WHERE d.DATE BETWEEN @Start and @End
AND EXISTS
(              
    select '1234567' -- @ID
) 
AND e.Location IN (@Location)
ORDER BY d.GROUP_NAME ASC

问题在于它返回了太多记录.根据传递给我进行过滤的值，我应该返回1行，但我得到28行.

The problem is that it is returning too many records. Based on the values I'm passing to filter on, I should get 1 row back but instead I am getting 28.

如果我删除EXISTS并添加以下内容，那么我会得到我需要的1条记录:

If I remove the EXISTS and add the following then I get the 1 record I need:

AND e.ID IN ('1234567')

是否有一种方法可以解决该查询以与EXISTS一起使用，以便获得正确的结果?

Is there a way to fix the query to work with EXISTS so that I get the correct results?

推荐答案

如果要尝试使用的话，这实际上是您想要的存在项，用于根据employee表中的参数过滤data_fact表.不确定，但是当您扔掉大量的员工ID时，它会在多大程度上改善您的绩效.

This is essentially what you want if you are going to try to use exists to filter your data_fact table by parameters in your employee table. Not sure how much it's going to improve your performance though when you throw a massive number of employee IDs at it.

SELECT 
    d.GROUP_NAME
FROM [DATA_FACT] AS d
WHERE d.DATE BETWEEN @Start and @End
AND EXISTS
(              
    select 1
    from EMPLOYEE AS e
    WHERE d.[KEY] = e.[KEY]
        AND e.[Location] IN (@Location)
        AND e.ID IN ('1234567')
)
ORDER BY d.GROUP_NAME ASC

这篇关于过滤器中存在返回的值过多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！