SELECT * WHERE VarCharColumn IN (...) Optimization

I have a list of 3000 strings, and I am passing them (twenty at a time) into a parametrized IN clause. It definitely isn't getting the results I would like to see ~ 500ms per execution.

The column is an index. Do you know a better way than this:

SELECT * FROM [ohb].[dbo].[MasterUrls] WITH (NOLOCK) WHERE Hash 
IN(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19)

A list of 3000 takes between 3 and 5minutes. I really need that down to around 30 seconds. Is this possible?

I am using MSSQL 2008 R2 on a server with 24 gigs of RAM, and dual 8core NUMA Xeons @2.4Ghz running on a 6HDD (@15k/rpm) RAID 10 ISCSI.

The table has 1.4M rows, and the index is a non-clustered index.

Execution plan shows the index scan as 90% of the total execution.

SELECT * will invalidate any optimal use of an index (it isn't covering) even if hash is indexed. Your index scan is most likely on the clustered index because of this.

Personally, I'd start with

• putting the 3000 search values into a table with an index

• Edit: as per Marian's comment, this can be passed in a list or table already

• use this in any of JOIN, IN, EXISTS (same plan usually)

• ensure my index on MasterUrls suits using Hash and covers col1, col2, col3

Something like

CREATE TABLE #foo (Hash ...)
INSERT #foo...
CREATE INDEX IX_FOO ON #foo (hash)

--either
CREATE NONCLUSTERED INDEX IX_Hash ON MasterUrls (hash) INCLUDE (col1, col2, col3)
--or    
CREATE CLUSTERED INDEX IXC_Hash ON MasterUrls (hash)

SELECT col1, col2, col3
FROM [ohb].[dbo].[MasterUrls] M
JOIN
#foo F ON M.Hash = F.Hash

StackExchange Database Administrators Q#1338, answer score: 7