select - SQL WHERE ID IN (id1, id2, ..., idn) -

i need write query retrieve big list of ids.

we support many backends (mysql, firebird, sqlserver, oracle, postgresql ...) need write standard sql.

the size of id set big, query generated programmatically. so, best approach?

1) writing query using in

select * table id in (id1, id2, ..., idn) 

my question here is. happens if n big? also, performance?

2) writing query using or

select * table id = id1 or id = id2 or ... or id = idn 

i think approach not have n limit, performance if n big?

3) writing programmatic solution:

  foreach (id in myidlist)   {       item = getitembyquery("select * table id = " + id);       myobjectlist.add(item);   } 

we experienced problems approach when database server queried on network. better 1 query retrieve results, better lot of small queries. maybe i'm wrong.

what correct solution problem?

option 1 solution.

why?

• option 2 same repeat column name lots of times; additionally sql engine doesn't know want check if value 1 of values in fixed list. however, sql engine optimize have equal performance in. there's still readability issue though...

• option 3 horrible performance-wise. sends query every loop , hammers database small queries. prevents using optimizations "value 1 of in given list"