SQL Union All is too slow

Question

I am rewriting an old legacy system. It has a function called checkExisting(). The old system was using queries for extracting objects from the MSSQL database like this (with ADO DB):

SELECT ObjectId, Name..... 
FROM tblRegisteredIncludes   
WHERE UPPER("Name") IN ('PROGA.H', 'PROGB.H'...............  list)

There are many tables like tblRegisteredIncludes but SQLs are grouped by the tablename and are using the IN clause with list of object names.

This is executing properly fast because SQL Server collects all objects in one scan and there was an index over the Name column in the table.

However, in the new system, I can not use the same SQL because the WHERE 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:

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") = "..."

I have replaced the Name-Index in tblRegisteredIncludes table with a composite index over (Name,Source).

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:

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") = "..."

and then pocessing the resulting ADO DB recordset later but it is even slower!

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.

Solution

In the union all version, each subquery is resulting in a separate scan of the table.

You should be bringing in all the rows using or conditions:

SELECT ObjectId, Name.....
FROM tblRegisteredIncludes   
WHERE (UPPER("Name") = 'PROGA.H' AND UPPER("Source") = "...") or
      (UPPER("Name") = ('PROGB.H') AND UPPER("Source") = "...") or
      . . .

If you have a situation where all the comparisons are on Name and Source, I would suggest creating a table-on-the-fly using a CTE:

with toinclude as (
   select 'PROGA.H' as name, 'SOURCE' as source union all
   select . . .
)
select ri.ObjectId, ri.Name
from tblRegisteredIncludes join
     toinclude
     on ri.name = toinclude.name and ri.source = toinclude.source

You can leave out the toupper() unless you are specifically concerned that your implementation or fields have overridden the default of case-insensitive behavior. The use of a function in a where clause generally prevents the use of indexes.