What's the most efficient way of querying the database based on a list of values?

Question

I have a list of record Id's that I want to retrieve from Sql Server. I'm trying to figure out what the most performant way doing this would be. For example in code I have this:

var recordsToFind = new List<long>{ 12345, 12346, 45756, 42423 ... }

I want to create a stored proc that does this:

Select * From Puzzles where ID = {any of the integers passed in}

I know there are several options like, table value parameters, converting the list into a comma seperated string and using CharIndex, created a temp table and splitting the string etc...

What would be the best approach keeping in mind this will be used A LOT!

Thanks!

Answer 1

Several must read articles about this can be found here: http://www.sommarskog.se/arrays-in-sql.html Performance considerations are addressed in those articles.

Answer 2

Micah,

is it out of the question to just do the simple sql 'in()' function?? (with obviously your array being used as a parameter, rather then the hardcoded values)

select * From Puzzles where ID in (12345, 12346, 45756, 42423)

maybe i've misunderstood the question mind you :)

[edit] - i notice that this is being used in .net. would it be possible to use linq against the database at all?? if so, then a few well crafted linq (over EF or subsonic for example) methods may do the trick. for example:

var idlist =  new int[] { 12345, 12346, 45756, 42423 };
var puzzleids = Puzzles.Where(x =>
                       idlist.Contains(x.ID));

Answer 3

Do you have the ability to change the code and the stored procedure or are you bound to some limit? If you have the ability to change both, I've seen this done before:

//Convert array/list to a comma delimited string using your own function (We'll call string strFilter)
//Pass strFilter into stored procedure (varchar(1000) maybe, we'll call parameter @SQLFILTER)

DECLARE @SQL AS VARCHAR(2000) --Or VARCHAR(MAX)
SET @SQL = "SELECT * FROM PUZZLES WHERE ID IN (" + @SQLFILTER + ")"
EXEC (@SQL)

I apologize if my syntax is off. My job unfortunately uses Oracle and I haven't used SQL Server a lot in 8 months. Hope this helps.