I see a few issues with your code that could be slowing it down. Let's try to go through them together:
SELECT *- Unless you really need all the columns,
SELECT *is not a good practice: it prevents the optimizer from using indexes because looking up all the columns usually exceeds the cost of scanning the whole table
nolock- It is not a "go faster" switch: it instructs the engine to ignore locks posed by other sessions and not lock rows while reading them. This could end up reading rows more than once or skipping them altogether, besides reading rows not yet committed by other sessions. Make sure you understand what are the effects on your query.
p.col5 in ( -- list of 20 strings ) or p.col5 in ( -- list of 3000 strings )can be collapsed in a single
p.col5 in ( -- list of 3020 strings )
- You should probably save your strings in a temporary table or table parameter, because passing too many literals on a query can exhaust the resources of the query optimizer (it usually times out tryi