Почему использование условия OR вместо Union вызвало проблему с производительностью

Использование UNION ALL для замены OR на самом деле является одним из хорошо известных приемов оптимизации. Лучшая ссылка и объяснение приведены в этой статье: Index Союз.

Суть в том, что OR предикаты, которые могут удовлетворяться двумя поисками по индексу, не могут быть надежно обнаружены оптимизатором запросов (причина в невозможности предсказать непересекающиеся наборы с двух сторон ИЛИ) . Таким образом, при выражении того же условия, что и UNION ALL, у оптимизатора не возникнет проблем с созданием плана, который выполняет два коротких поиска и объединяет результаты. Важно понимать, что a=1 or b=2 может отличаться от a=1 union all b=2, поскольку первый запрос возвращает строки, удовлетворяющие обоим условиям один раз, а второй — дважды. Когда вы пишете запрос как UNION ALL, вы сообщаете компилятору, что понимаете это и у вас нет с этим проблем.

Дополнительные сведения см. в разделе Как анализировать производительность SQL Server.

Query1, левое соединение и предложение where объединяются, чтобы означать, что таблица C эффективно игнорируется (также не упоминается в списке выбора), поэтому у вас в основном есть запрос на внутреннее соединение с двумя таблицами.

Запрос 2, левое соединение теперь является почти внутренним соединением, потому что на эту таблицу ссылаются в предложении where, не допуская также NULL из этой таблицы, но из-за ИЛИ все условия учитываются для результата. Следовательно, запрос на внутреннее соединение с тремя таблицами более или менее.

Запрос 3, вы упростили внутренний запрос UNION ALL до 2 простых внутренних соединений.

Но я подозреваю, что это будет больше, потому что and A.IndigoClientId=@TenantId указывает, что вы все равно не раскрыли полные запросы.

JOIN to TAdditionalOwner использует

TPolicyBusiness.PolicyBusinessId = TAdditionalOwner.PolicyBusinessId 

где JOIN to TPolicyOwner использует

TPolicyBusiness.PolicyDetailId = TPolicyOwner.PolicyDetailId

Убедитесь, что существует соответствующий индекс для PolicyBusinessId.

В 2-way JOIN, который является частью UNION, меньшая таблица TAdditionalOwner будет оптимизирована, если в TPolicyBusiness нет индекса, на который она могла бы ссылаться, из-за небольшого размера. Сервер по-прежнему будет выполнять сканирование таблицы, но использовать значения из меньшей таблицы и проверять, находятся ли они где-нибудь в большой таблице. Если индекса нет, эта оптимизация довольно быстро исчезнет по мере роста маленькой таблицы.

Учитывая, что вы не имеете в виду ни B, ни C в SELECT, вы можете просто перейти к этому

SELECT DISTINCT A.PolicyBusinessId, A.PolicyDetailId 
FROM TPolicyBusiness A
LEFT JOIN TPolicyOwner B ON a.PolicyDetailId = b.PolicyDetailId AND b.CRMContactId = @CRMContactId 
LEFT JOIN TAdditionalOwner C on c.PolicyBusinessId = A.PolicyBusinessId AND C.CRMContactId = @CRMContactId

Таким образом, он будет ПРИСОЕДИНЯТЬСЯ к любой таблице, как и в вашем ОБЪЕДИНЕНИИ, но без выбора ВНЕШНЕГО выбора.

В любом случае убедитесь, что используемые поля проиндексированы.

Оператор объединения, это займет много времени, если данные огромны. Старайтесь избегать оператора объединения.

Пожалуйста, попробуйте ниже попробовать отфильтровать данные при присоединении. Это будет намного лучше.

Select distinct A.PolicyBusinessId, A.PolicyDetailId 
from TPolicyBusiness A
inner join TPolicyOwner B on a.PolicyDetailId=b.PolicyDetailId
and b.CRMContactId = @CRMContactId 
Left Join TAdditionalOwner C on c.PolicyBusinessId=A.PolicyBusinessId
where  (1=1 OR C.CRMContactId = @CRMContactId)