title: SQL Union All is too slow
body: <p>I am rewriting an old legacy system. It has a function called <code>checkExisting()</code>. The old system was using queries for extracting objects from the MSSQL database like this (with ADO DB):</p>
  
  <pre><code>SELECT ObjectId, Name..... 
  FROM tblRegisteredIncludes   
  WHERE UPPER("Name") IN ('PROGA.H', 'PROGB.H'...............  list)
  </code></pre>
  
  <p>There are many tables like <code>tblRegisteredIncludes</code> but SQLs are grouped by the tablename and are using the IN clause with list of object names. </p>
  
  <p>This is executing properly fast because SQL Server collects all objects in one scan and there was an index over the <code>Name</code> column in the table.</p>
  
  <p>However, in the new system, I can not use the same SQL because the <code>WHERE</code> condition is more complex. It is also using a Source field and sometimes and other fields in the condition. I have a larger number of single SQL queries:</p>
  
  <pre><code>SELECT ObjectId, Name..... FROM tblRegisteredIncludes   
  WHERE UPPER("Name") = 'PROGA.H' AND UPPER("Source") = "..."
  
  SELECT ObjectId, Name..... FROM tblRegisteredIncludes   
  WHERE UPPER("Name") = ('PROGB.H') AND UPPER("Source") = "..."
  </code></pre>
  
  <p>I have replaced the Name-Index in <code>tblRegisteredIncludes</code> table with a composite index over <code>(Name,Source)</code>. </p>
  
  <p>I have expected even so the total SQLs execution to be a little slower but with no more than 15-20%. Instead it is much, much slower, sometimes up to 100%. I tried to combine the SQLs in a single large SQL query using UNION ALL:</p>
  
  <pre><code>SELECT ObjectId, Name..... FROM tblRegisteredIncludes   
  WHERE UPPER("Name") = 'PROGA.H' AND UPPER("Source") = "..."
  UNION ALL
  SELECT ObjectId, Name..... FROM tblRegisteredIncludes   
  WHERE UPPER("Name") = ('PROGB.H') AND UPPER("Source") = "..."
  </code></pre>
  
  <p>and then pocessing the resulting ADO DB recordset later but it is even slower! </p>
  
  <p>I need to know whether there is some efficient way to execute these queries faster? I need to reach performance similar to the old case when using IN clause and a list of names. I can provide the execution plan.</p>