EXISTS in filter returning too many values

问题:

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.

This is the query I have:

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

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.

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

AND e.ID IN ('1234567')

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

回答1:

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