Calling SQL select statement from C# thousands of times and is very time consuming. Is there a better way?

Question

I get a list of ID's and amounts from a excel file (thousands of id's and corresponding amounts). I then need to check the database to see if each ID exists and if it does check to make sure the amount in the DB is greater or equal to that of the amount from the excel file.

Problem is running this select statement upwards of 6000 times and return the values I need takes a long time. Even at a 1/2 of a second a piece it will take about an hour to do all the selects. (I normally dont get more than 5 results max back)

Is there a faster way to do this?

Is it possible to somehow pass all the ID's at once and just make 1 call and get the massive collection?

I have tried using SqlDataReaders and SqlDataAdapters but they seem to be about the same (too long either way)

General idea of how this works below

for (int i = 0; i < ID.Count; i++)
{
    SqlCommand cmd = new SqlCommand("select Amount, Client, Pallet from table where ID = @ID and Amount > 0;", sqlCon);

    cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID[i];

    SqlDataAdapter da = new SqlDataAdapter(cmd);

    da.Fill(dataTable);
    da.Dispose();           
}

Answer 1

Instead of a long in list (difficult to parameterise and has a number of other inefficiencies regarding execution plans: compilation time, plan reuse, and the plans themselves) you can pass all the values in at once via a table valued parameter.

See arrays and lists in SQL Server for more details.

Generally I make sure to give the table type a primary key and use option (recompile) to get the most appropriate execution plans.

Answer 2

Combine all the IDs together into a single large IN clause, so it reads like:

select Amount, Client, Pallet from table where ID in (1,3,5,7,9,11) and Amount > 0;