true... but each part of the UNION is evaluated by the optimizer as a separate query, then the results are "concatenated" together, and then sorted to determine distinct records since you're doing UNION instead of UNION ALL.

I'd agree with Sean here, the first thing is to replace that splitter function you're using, and preferably only parse the strings once. If you need to get rid of the table scans, then add indexes on EOS_Reports(OrgPathID), and EOS_Reports(SharedOrgPathID). Also consider if you need to do the deduplication caused by UNION, or if you can use UNION ALL instead.