Salutare,
Am o tabela cu 2 indecsi:
index1 / clustered / pe coloanele effective_date, area_source_id, area_sink_id, data_type_id
index2 / nonclustered, unique, primary key/pe coloana  id

SELECT *
FROM my_table
WHERE 1=1
    AND area_source_id IN (15, 37)
    AND area_sink_id IN (15, 37)
    AND data_status_id = 1
    AND data_type_id = 2
    AND data_source_id = 3
    AND (
                ('2008-09-11 10:00PM' BETWEEN effective_date AND termination_date)
                OR
                (effective_date <= '2008-09-11 10:00PM' AND termination_date IS NULL)
            )

Intrebare:
Pentru SQLul de mai sus, cum sa rescriu ultima conditie din WHERE a.i. sa am index seek pe index1? Acum, in planul de executie se face un index scan pe index2 (asta datorita ultimei conditii pe effective_date, presupun pentru ca effective_date nu este SARGable).

Am folosit hint ca sa fortez folosirea lui index1, dar timpul de executie este si mai mare.

Orice help, sugestie este binevenita.
Multumesc.

2005 / 2008 ?

Primul sfat este să nu foloseşti SELECT * FROM ...

Care este selectivitatea pentru fiecare condiţie WHERE ?

B_gd_n[ ]Sahlean:

2005 / 2008 ?

2005

B_gd_n[ ]Sahlean:

Primul sfat este să nu foloseşti SELECT * FROM ...

da, nu am folosit, am pus steluta pentru simplificare

B_gd_n[ ]Sahlean:

Care este selectivitatea pentru fiecare condiţie WHERE ?

selectivitatea este intre 5 si 50 / 8 milioane

Dacă ediţia de SQL îţi permite, foloseşte partiţionarea tabelelor după termination_date sau effective_date.

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

rremus:

desi in cazul asta nu am nici on explicatie pentru alegerea indexului pe ID

Doh! Indexul ID contine implicit cimpurile din clustered deci poate satisface query-ul aproape complet (mai putin termination_date, data_status_id si data_source_id si '*' din projection list). Si intrucit e mai mic decit cel clustered, e preferat pe motiv ca un full scan pe el cere mai putzin I/O.

---

http://rusanu.com

Eu aş crea un index (non-clustered) care să conţină şi coloana termination_date (în afară de effective_date), de exemplu:

CREATE NONCLUSTERED INDEX index3 ON my_table (area_sink_id, data_status_id, data_type_id, data_source_id, effective_date, termination_date)
-- am trecut mai sus şi celelalte coloane din WHERE, chiar dacă fac parte deja din indexul clustered, în măsura în care ai condiţie cu =, nu cu IN
-- ar fi bun şi eventual un INCLUDE în care treci toate coloanele care sunt în SELECT sau în WHERE, cu excepţia celor scrise mai sus

Ai putea să rescrii condiţia respectivă (eliminând OR-ul) astfel:

'2008-09-11 10:00PM' BETWEEN effective_date AND ISNULL(termination_date,'99991231 23:59:59.997')

... deşi nu cred că o să aibă un efect semnificativ.

Oricum, în cel mai bun caz cred că o să obţii un index seek care are ca "Seek Predicates" doar un "effective_date<=@DataRespectiva", iar condiţia pe termination_date va fi pusă doar la "Predicate" şi asta înseamnă că acel seek ar putea citi o mare parte din paginile din indexul respectiv (dacă mai ales dacă data e mai recentă), aşa că e important ca acel index să includă toate coloanele de care ai nevoie, dar să fie cât mai mic.

De fapt, cred că problema este că obţii acea selectivitate foarte bună doar din combinaţia condiţiilor de mai sus, însă Bogdan a întrebat care ar fi numărul de rânduri obţinute dacă foloseşti fiecare parte a condiţiei respective, de exemplu:
1. câte rânduri obţii dacă foloseşti WHERE area_source_id IN (15, 37) ?
2. câte rânduri obţii dacă foloseşti WHERE area_sink_id IN (15, 37) ?
3. câte rânduri obţii dacă foloseşti WHERE data_status_id = 1 ?
4. câte rânduri obţii dacă foloseşti WHERE data_type_id = 2 ?
5. câte rânduri obţii dacă foloseşti WHERE data_source_id = 3 ?
şi mai ales:
6. câte rânduri obţii dacă foloseşti WHERE effective_date <= '2008-09-11 10:00PM' ?
7. câte rânduri obţii dacă foloseşti WHERE termination_date >= '2008-09-11 10:00PM' ?
8. câte rânduri obţii dacă foloseşti WHERE termination_date IS NULL ?

Răzvan

B_gd_n[ ]Sahlean:

Dacă ediţia de SQL îţi permite, foloseşte partiţionarea tabelelor după termination_date sau effective_date.

Tabela este deja partitionata (anual) dupa effective_date, index1 este pe partitie, index2 este stocat in PRIMARY. Faptul ca este ales index2 care este stocat in PRIMARY duce la performante si mai slabe.

Partitionarea nu ajuta daca planul cere un full scan. Trebuie neaparat sa elimini full-scan-ul. Daca effective_date este cheia de partitionare nu potzi schimba indexul clustered. Eu zic ca este musai sa adaugi la WHERE o conditie de limitare effective_date >= '...'  ca sa creezi un range intre doua date.

Index2 este ales pentru ca genereaza un full-scan pe mai putzine pagini (index mai compact).

---

http://rusanu.com

In primul rand multumesc tuturor pentru raspunsuri.

Revin cu detalii:

rremus:

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

Effective_date este cel mai selectiv.
Pentru un effective_date sunt cam 4000 de randuri, restul campurilor din index au 5-50 valori distincte. La o tabela de cateva milioane de randuri, nu am avut de ales alta ordine a coloanelor din index.

rremus:

Mai sint si probleme care pot apare din auto-parametrizare daca planul a fost generat pe alte valori ale constantelor (mai ales effective_date)

Inainte de script am rulat tot timpul:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

rremus:

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.

Pai am scapat de OR. Am rulat scriptul:
UPDATE cor_time_Serie
SET termination_date = '2050-01-01'
WHERE termination_Date IS NULL

si am schimbat ultima conditie pe data:
AND effective_date BETWEEN '2008-09-11 10:00PM' AND termination_date
Rezultatul este acelasi :(

MihaiBejenariu:

...
'2008-09-11 10:00PM' BETWEEN effective_date AND termination_date
AND effective_date BETWEEN '2008-09-11 10:00PM' AND termination_date
...

?

---

http://rusanu.com

Da, am inversat un pic coloanele, e vina mea. Ca sa clarificam, queryul e acum asa:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

SELECT *
FROM
    my_table
WHERE 1=1
    AND area_source_id IN (15, 37)
    AND area_sink_id IN (15, 35)   
    AND data_status_id = 1
    AND data_type_id = 2
    AND data_source_id = 3
    AND '2008-09-11 10:00PM' BETWEEN effective_date AND termination_date

Deci criteriul principal de selectie este effective_date < '2008-09-11 10:00PM'
Nu o sa mearga, asta selecteaza intreaga tabela practic si rezultatul este full scan (sau range scan de la 0 la 2008-09-11, care e probabil 99.9% din tabela). Trebuie neaparat sa introduci o limita inferioara la effective_date. Ce ai facut cu replace-ul NULL-ului nu ajuta cu nimic.

---

http://rusanu.com

Condiţia "'2008-09-11 10:00PM' BETWEEN effective_date AND termination_date" este înţeleasă de SQL Server sub forma "effective_date<='2008-09-11 10:00PM' AND termination_date>='2008-09-11 10:00PM'", deci două condiţii independente (pe coloane diferite), ambele cu inegalităţi.

Ai spus că "Pentru un effective_date sunt cam 4000 de randuri". Înţeleg că sunt vreo 4000 de rânduri cu valori între 2008-09-11 00:00 şi 2008-09-11 23:59. Totuşi, un index seek pentru condiţia "effective_date<='2008-09-11 10:00PM" nu returnează câteva mii de rânduri, ci milioane, pentru că nu avem nicio valoare minimă pentru effective_date.

Există vreo relaţie între effective_date şi termination_date, cum ar fi "termination_date este la maxim 3 zile după effective_date" ?

Dacă da, atunci putem să ne folosim de condiţia "termination_date>='2008-09-11 10:00PM'" ca să adăugăm încă o condiţie de genul "effective_date>='2008-09-08 10:00PM'". Astfel, avem un interval delimitat la ambele capete, iar un index seek pe effective_date ar returna doar vreo 12000 rânduri, care pot fi prelucrate rapid (cu celelalte condiţii).

Răzvan

rsocol:

De fapt, cred că problema este că obţii acea selectivitate foarte bună doar din combinaţia condiţiilor de mai sus, însă Bogdan a întrebat care ar fi numărul de rânduri obţinute dacă foloseşti fiecare parte a condiţiei respective, de exemplu:
1. câte rânduri obţii dacă foloseşti WHERE area_source_id IN (15, 37) ?
2. câte rânduri obţii dacă foloseşti WHERE area_sink_id IN (15, 37) ?
3. câte rânduri obţii dacă foloseşti WHERE data_status_id = 1 ?
4. câte rânduri obţii dacă foloseşti WHERE data_type_id = 2 ?
5. câte rânduri obţii dacă foloseşti WHERE data_source_id = 3 ?
şi mai ales:
6. câte rânduri obţii dacă foloseşti WHERE effective_date <= '2008-09-11 10:00PM' ?
7. câte rânduri obţii dacă foloseşti WHERE termination_date >= '2008-09-11 10:00PM' ?
8. câte rânduri obţii dacă foloseşti WHERE termination_date IS NULL ?

Răzvan

[1] Intr-adevar.

@Mihai:
[2] O conditie de genul WHERE effective_date <= '2008-09-11 10:00PM' (acelasi lucru si la  '2008-09-11 10:00PM' BETWEEN effective_date ...) nu pare a fi foarte restrictivă.

[3] Regândeşte interogarea ... sau/si explica (daca este posibil) ce doresti sa obtii prin aceasta interogare; ar fi utile ceva explicaţii referitoare la tabela sursa (repet, daca este posibil).