Looking for a faster query

1

I have a query and as part of the Where clause I want to see if the string OrgCode is contained in a list of strings that get passed in as a parameter @OrgCodeList.
For for example, what I want to do (but doesn't work) is
where OrgCode in (@OrgCodeList).

OrgCodeList is something like ('ABCD','EFGH','IJKL')

I obtained this code, which works but is very slow.
where @OrgCodeList like '%' + OrgCode + '%'

Can anyone tell me how I might get this to run faster?

2

A common approach is to join to a String splitter function:

3

Split the string using an inline-table-valued function, then do an INNER JOIN to the results.

dbo.DelimitedSplit8K is a very fast splitter, so I'll use that. You can Google the source, or I'll provide it here if you prefer.

SELECT mt....
FROM dbo.main_table mt
INNER JOIN (
    SELECT Item AS OrgCode
    FROM dbo.DelimitedSplit8K(@OrgCodeList, ',')
) AS orgcodes ON orgcodes.OrgCode = mt.OrgCode
4

If you're on MSSQL 2016+ you'd probably want to compare speed on the suggested DelimitedSplit8K with the built-in string_split. For your particular case I'd bet the built-in function is faster.

5

For such a tiny list, I don't think you'll see any difference. After all, the split is only done once.

The query's real speed gain will come from being able to do a standard join on the list rather than having to do a LIKE on every row.