SQL Server query with extremely large IN clause results in numerous queries in activity monitor

Question

SQL Server 2014 database. Table with 200 million rows.

Very large query with HUGE IN clause.

I originally wrote this query for them, but they have grown the IN clause to over 700 entries. The CTE looks unnecessary because I have omitted all the select columns and their substring() transformations for simplicity.

The focus is on the IN clause. 700+ pairs of these.

WITH cte AS (
           SELECT *      
           FROM AODS-DB1B
           WHERE  
           Source+'-'+Target
    IN
    (
    'ACY-DTW',
    'ACY-ATL',
    'ACY-ORD',
    :
    : 700+ of these pairs
    :
    'HTS-PGD',
    'PIE-BMI',
    'PGD-HTS'
    )

    )

    SELECT *
    FROM cte
    order by Source, Target, YEAR, QUARTER

When running, this query shoots CPU to 100% for hours - not unexpectedly.

There are indexes on all columns involved.

Question 1: Is there a better, or more effecient way to accomplish this query other than the huge IN clause? Would 700 UNION ALLs be better?

Question 2: When this query runs, it creates a Session_ID that contains 49 "threads" (49 processes that all have the same Session_ID). Every one of them an instance of this query with it's "Command" being this query text.

21 of them SUSPENDED, 14 of them RUNNING, and 14 of them RUNNABLE.

This changes rapidly as the task is running.

WHAT the heck is going on there? Is this SQL Server breaking the query up into pieces to work on it?

Answer 1

I recommend you store your 700+ strings in a permanent table as it is generally perceived as bad practice to store that much meta data in a script. You can create the table like this:

CREATE TABLE dbo.LookUp(Source varchar(250), Target varchar(250))

CREATE INDEX IX_Lookup_Source_Target on dbo.Lookup(Source,Target)

INSERT INTO dbo.Lookup (Source,Target)
SELECT 'ACY','DTW'
UNION
SELECT 'ACY','ATL'
.......

and then you can simply join on this table:

SELECT * FROM [AODS-DB1B] a
INNER JOIN dbo.Lookup lt ON lt.Source = a.Source 
                         AND lt.Target=a.Target
ORDER BY Source, Target, YEAR, QUARTER

However, even better would be to normalise the AODS-DB1B table and store SourceId and TargetId INT values instead, with the VARCHAR values stored in Source and Target tables. You can then write a query that only performs integer comparisons rather than string comparisons and this should be much faster.

Answer 2

Put all of your codes into a temporary table (or permamnent if suitable).....

SELECT *      
FROM AODS-DB1B
INNER JOIN NEW_TABLE ON Source+'-'+Target = NEWTABLE.Code
WHERE  
...
...