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.