Fine tune some SQL called multiple times

Question

Hi all, I currently have an SQL query which is currently called multiple times like this (pseudo code):

foreach(KeyValuePair kvp in someMapList)
{
    select col1 from table where col2 = kvp.key and col3 = kvp.value;
    //do some processing on the returned value
}

The above could obviously call the database a large number of times if it is a big list of pairs.

Can anyone think of a more efficient piece of SQL which could essentially return a list of specific values based on a list of two unique pieces of information so that the processing can be done in bulk? One obvious one would be to build up a big piece of SQL with ORs but this would probably be quite inefficient?

Thanks

Carl

Answer 1

Insert KeyValuePair into a table and use a JOIN man or (if c#)

from record in table
join key in someMap on record.col2 equals key.key && record.col3 equals key.keyvalue
select col1 //or some anonymous type or a DTO I do not know...

That is WAY more efficeint:D

Answer 2

Assuming that you're stuck with the entity-value pair pattern, your best bet is to either create a stored procedure and pass in a delimited string of your pairs which you can then turn into a temporary table and select out using a join, or if you can pass in a table parameter that would be even better.

Another possibility would be to insert into a work table with some kind of unique connection ID for your values and then join against that.

In any case, your goal is to be able to select the data using a set-based method by getting your pairs into the database in some way.