Intr-adevar conditiile de pe effective_date nu sint SARGable. Daca SQL alege un plan pe indexul nonclustered pe ID eu cred ca mai exista niste detalii care nu le-ai specificat. Bauiala mea este ca clusterul este pe ID, nu pe effective_date. Potzi sa validezi ca indecsii sint asa cum i-ai descris tu?
O sa consider mai departe ca indecsii sint intr-adevar cum i-ai descris si query-ul este
exact acesta si nu ai schimbat absolut nimic (desi in cazul asta nu am nici on explicatie pentru alegerea indexului pe ID). Deasemenea ar folosi sa ne spui si structura tabelei si marimea cimpurilor si a recordurilor (conform
sys.dm_db_index_physical_stats)
Primul lucru de incercat: daca area_source_id, area_sink_id si data_type_id impreuna sint destule de selective (o combinatie de valori alege doar 5-10% din toate record-urile) si query-ul tau este tipic pentru aplicatie atunci solutia simpla este sa schimbi indexul clustered sa fie in ordinea data_type_id, area_source_id, area_sink_id, efective_date. Faptul prima cheie in index 1 este effective_date si range-ul tau este totdeauna deschis relativ la effective_date face ca indexul 1 sa nu poata fi folosit (decit cel mult pentru un full scan). Rotind effective_date la coada indexului te-ar putea ajuta pentru acest query pentru ca se poate seta un range pe celelate conditii (data_type, area etc).
Daca effectve_date trebuie sa ramina prima cheie in index atunci trebuie sa gasesti un criteriu de limitare a range-ului pe care cautzi effective_date. Conditiile tale sint practic effective_date < '2008-09-11 10:00PM' OR effective_date <= '2008-09-11 10:00PM'. Cum astfel de tabele totdeauna cresc si datele calendaristice din tabela sint mereu in trecut, conditia respectiva este effectiv echivalenta cu 'toate recordurile dinnainte de ultimul' sau ceva foarte asemanator. Oricum, ceva ce nu restrictioneaza deloc numarul de record-uri. Cauta daca potzi gasi o conditie de data minima care sa limiteze, ceva de genul 'numai din luna curenta' sau 'numai din ultimele 3 luni'. Atunci s-ar putea face un range scan si limita sever numarul de record-uri scanate.
Mai sint si probleme care pot apare din auto-parametrizare daca planul a fost generat pe alte valori ale constantelor (mai ales effective_date), dar nu avem cum sa ne dam seama fara sa vedem effectiv planul.
Si nu in utimul rind incearca sa scapi de OR-ul explicit cit si de cele implicite ( IN este de fapt un OR). OR este killer-ul sargabilitatzii. Chiar si un UNION intre doua query-uri poate fi mai optim decit un predicat OR.
http://rusanu.com