What is the best practice to large scale dynamic select and where query
<p>My current objective is creation of a dynamic select and where query. In our case we have a reference table. Reference table is contains references column values and table name. We create a dynamic query on the other table(s) with these references. For example:</p>

<pre><code>select * from {table} where {pk1} in (...) and {pk2} in {...}
</code></pre>

<p>There was a problem in this generated query. The problem is some rows are returns in the result but they are not in query because in queries are different conditions.</p>

<p>Changed sql generation to like this</p>

<pre><code>select * from {table} where ( {pk1}=(value1) and {pk2}=(value2) ) or ( {pk1}=(value3) and {pk2}=(value4) ) ...) 
</code></pre>

<p>In this case the problem was solved but query execution time is not good instead of "IN" query. It is about 10-20 times slower. And the query string too big instead of "IN" query.</p>

<p>We can not use the table valued queries because we can not create a new TYPE for each table dynamically and the columns are not same type and order. And new table types may be inserted in future.</p>

<p>So, what is the best practice to do this?</p>

<p>Regards</p>