I must create a query to get a lengthy list of IDs. I must create a standard SQL because we support a wide range of backends (MySQL, Firebird, SQLServer, Oracle, PostgreSQL, etc.).
The query would be generated programmatically, therefore the size of the id set could be substantial. So what is the best course of action?
1) Writing a query using IN
SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)
My question here is. What happens if n is very big? Also, what about performance?
2) Writing a query using OR
SELECT * FROM TABLE WHERE ID = id1 OR ID = id2 OR ... OR ID = idn
I think that this approach does not have n limit, but what about performance if n is very big?
3) Writing a programmatic solution:
foreach (var id in myIdList)
var item = GetItemByQuery("SELECT * FROM TABLE WHERE ID = " + id);
When the database server is accessed across the network, we ran into several issues with this method. Typically, it is preferable to make a single, comprehensive query as opposed to numerous, narrower ones. Maybe I'm off base.
What would be the best response to this issue?