INNER JOIN cez dva stĺpce a OR
Tak je tu ďalšie T-SQL okienko. Tentoraz ide čisto len o problém na SQL Server. Možno na iných databázach sa query optimalizátor správa trochu inteligentnejšie. A možno aj nie :)
Pracujem na systéme postavenom nad MS CRM, kde fyzické osoby sú uložené v entite contact a právnicke v entite account. Pre tých, ktorým MS CRM nič nehovorí, tak každá (alebo väčšina) entita je uložená v dvoch SQL tabuľkách, pričom tieto dve tabuľky sú spojené vo view s rovnakým názvom ako je názov entity. Každý objekt má primárny kľúč uniqueidentifier, teda GUID, takže každý objekt má jedinečné ID v rámci celej databázy.
Ďalej som mal vo vedľajšej databáze v tabuľke zoznam klientov, ktorí sa majú nejak spracovať. Stačí zoznam GUID a typ, či ide o account alebo contact. Klient môže mať nejaké zmluvy, ktoré sú uložené v custom entite new_contract. Problém MS CRM je, že nad jedným stĺpcom môže byť len jeden foreign key. Takže new_contract obsahuje dva stĺpce new_account a new_contact, pričom vždy je vyplnený práve jeden z nich. A tu je kameň úrazu. Chcem vybrať všetky zmluvy klientov, ktorí sú v pomocnej databáze. Tak som spravil jednoduchý SELECT.
SELECT *
FROM ProcessClient PC INNER JOIN – zoznam klientov na spracovanie
New_MSCRM.dbo.new_contract Cn ON Cn.new_account = PC.ClientId OR Cn.new_contact = PC.ClientId
WHERE Cn.DeletionStateCode = 0
Lenže tento príkaz, ktorý sa mal vykonať za sekundu trval niekoľko minút. Aj nad stĺpcom new_account aj new_contact existujú indexy. Tak som predpokladal, že query optimalizátor pre každého klienta vyhľadá zmluvy podľa jedného aj druhého indexu a potom tieto dve množiny zjednotí. Dokonca operácia zjednotenia by bola veľmi jednoduchá, lebo vždy by bola jedna množina prázdna. Lenže podľa query plan sa SQL Server vykašľal na oba indexy a robil full scan nad celou tabuľkou.
Riešenie bolo rozdeliť podmienku OR do dvoch joinov.
SELECT *
FROM ProcessClient PC LEFT JOIN
New_MSCRM.dbo.new_contract CnA ON CnA.new_account = PC.ClientId LEFT JOIN
New_MSCRM.dbo.new_contract CnC ON CnC.new_contact = PC.ClientId
WHERE ISNULL(CnA.DeletionStateCode, CnC.DeletionStateCode) = 0
Problém bol v tom, že celý SELECT bol oveľa zložitejší a na zmluvy som mal naviazané ešte ďalšie tabuľky a tie by som musel tiež zdvojiť. Takže by sa celý príkaz stal zbytočne nečitateľný. Našťastie som si spomenul na čarovné slovíčko CROSS APPLY. Keď nevedel query optimalizátor spraviť UNION automaticky, tak mu ho treba napísať exaktne. Takže výsledok bol nasledovný.
SELECT *
FROM ProcessClient PC CROSS APPLY (
SELECT * FROM New_MSCRM.dbo.new_contract CnA ON CnA.new_account = PC.ClientId
UNION ALL
SELECT * FROM New_MSCRM.dbo.new_account CnC ON CnC.new_contact = PC.ClientId
) AS Cn
WHERE Cn.DeletionStateCode = 0
A naozaj pomohlo. Vykonanie príkazu sa skrátilo na sekundu. Takže vyhnite sa OR operátorom v joinoch a naozaj funkčné a čitateľné riešenie je použiť UNION v CROSS APPLY.