SQL optimalisatie

[Douweegbertje]

Ik heb één tabel met de volgende structuur.

waardeA waardeB waardeC waardeD waardeE waardeF waardeG


Hierop zit de volgende query.

SQL:

SELECT tbl1.waardeF, tbl1.waardeG, tbl2.waardeI, tbl2.waardeH  FROM tabel1 AS tbl1  LEFT JOIN tabel2 AS tbl2 ON tbl1.waardeG = tbl2.waardeH AND tbl2.waardeF = 'het-specifieke-label-nummer'               WHERE tbl1.waardeA = 'een-relatie-nr'  AND tbl1.waardeB    = 'een-volg-nr'  AND tbl1.waardeF = 'het-specifieke-label-nummer'  AND tbl1.waardeC = 0

De join zelf gaat eigenlijk snel, zoals je in bijgevoegde image gaat zien.



Nja in feite is een index scan ook niet verkeerd, maar alles bij elkaar duurt het gewoon enorm lang. Om wat preciezer te zijn: 00:00:02.2049370 (2.2 seconden dus).

Nu heb ik gewoon een probleem dat er door 'bad-data' door de jaren heen het (nog) niet mogelijk is om een unieke index te maken op basis van meerdere kolommen. Mijn doel was om van waarde a-b-c-f en g een unieke index te maken. Die combinatie zou technisch gezien uniek moeten zijn.

Dus ja, vraag 1: is het logisch om dat te gaan doen (als ik de data uiteindelijk op orde heb?). En zou me dat veel tijd schelen, of is het nog te generiek?

Goed, voor nu dan. Stel dat ik een query uitvoer op basis van -alleen- waardeA (en die returned al bijv. 600 records) dan is dat gewoon instant < 1 seconden. Waarom zou specifiek deze bovenstaande query dan zo 'lang' duren?

Indexen:

Clustered index op waarde A-B-C-F-G.
Non-Clustered index op F met included colomns D en E.

Letterlijk 'alles' wat ik opvraag MITS er maar één in de WHERE clause zit duurt < 1 seconden, bijv. SELECT * FROM table1 WHERE waardeG = foo, maar in zo'n query als bovenstaande duurt het opeens weer lang.. Idem als ik dan nog een 'AND waarde A = bar' erbij zet, dan gaat het opeens niet meer.

Nog een detail, als ik de volledige index eraf flikker dan is het weer wat sneller dan met een index.


Wat zouden jullie doen om het tijdelijk wat sneller te maken, oh en vergeet m'n eerste vraag niet. Antwoorden als 'de fik er in' zijn valide, maar daar heb ik nu even niets aan.

ps. table heeft 23377793 records.

[mhaket]

Je kan ook een niet unieke index erop zetten. Wat geeft dat voor verbetering?

[Douweegbertje]

mhaket schreef op woensdag 11 maart 2015 @ 19:44:
Je kan ook een niet unieke index erop zetten. Wat geeft dat voor verbetering?

Ik heb toch ook non-unique indexen zoals staat beschreven?

[mhaket]

Sorry, niet goed gelezen...

Welke database?

[FireDrunk]

Geen idee hoe SQL Server dit tegenwoordig doet, maar is/was het niet verplicht om prepared statements te gebruiken om volledig nut te hebben van een Index?

[Douweegbertje]

mhaket schreef op woensdag 11 maart 2015 @ 20:02:
Sorry, niet goed gelezen...

Welke database?

MSSQL? Maar dat boeit vrij weinig voor de vraagstelling.

FireDrunk schreef op woensdag 11 maart 2015 @ 20:09:
Geen idee hoe SQL Server dit tegenwoordig doet, maar is/was het niet verplicht om prepared statements te gebruiken om volledig nut te hebben van een Index?

Nee. En zoals je misschien deels kan zien vuur ik het direct af op de server zelf dus prepared statements hebben er eigenlijk helemaal niets mee te maken, nimmer ook SP's of wat dan ook. In feite zijn de statements al prepared..

[FireDrunk]

Iets wat je vast zelf al gechecked hebt:

Hoe groot is de index, en past deze in RAM? Staat je SQL Server toe dat er zoveel geheugen gebruikt wordt om de hele index in RAM te zetten?

[Dekaasboer]

Dit lijkt erg veel op de gui van MSSQL.

Als je een seperate index maakt op alleen je clausules in je where join dan kan het efficienter zijn om alleen deze data te controleren en daarna het resultaat er bij te zoeken.

Moet wel gezegd worden dat SQL dan wel een goed beeld moet hebben van het aantal rijen in de tabel en de verwachtte uitkomst. Wat zegt je estimated excecution plan versus je actual? Heb je actuele statistics op beide tabellen?

Ik vind die compute ook vreemd, je joint op 2 predicaten hebben die wel beiden het zelfde datatype in beide tabellen?

En hoe groot is het probleem met deze query? Je kan ook nog testen met eenindexed view..

[ Voor 29% gewijzigd door Dekaasboer op 11-03-2015 20:29 ]

[Feanathiel]

Wat ik in dit plaatje mis, is de key lookup. Als hij dan toch de index 'Non-Clustered index op F met included colomns D en E' pakt, dan mis ik daar alleen al B, C en G in. Die zal hij er bij moeten zoeken. Is dit de query die daadwerkelijk wordt uitgevoerd? En hetzelfde geld voor de index; is dit de index die gebruikt wordt?

Even voor de leesbaarheid de query over meerdere regels uitgeschreven. Verder niks aan gedaan.

SQL:

SELECT
    tbl1.waardeF,
    tbl1.waardeG,
    tbl2.waardeI,
    tbl2.waardeH 
FROM tabel1 AS tbl1
LEFT JOIN tabel2 AS tbl2
    ON
        tbl1.waardeG = tbl2.waardeH
        AND tbl2.waardeF = 'het-specifieke-label-nummer'
WHERE
    tbl1.waardeA = 'een-relatie-nr'
    AND tbl1.waardeB = 'een-volg-nr'
    AND tbl1.waardeF = 'het-specifieke-label-nummer'
    AND tbl1.waardeC = 0

[ Voor 4% gewijzigd door Feanathiel op 11-03-2015 20:25 ]

[SPee]

Wat je ziet is dat een deel van je query supersnel gaat. En de andere helft langzamer.
Daarna worden ze samengevoegd (je join) wat ook al niet zo snel gaat.
Wat je wilt is dat hij voor beide delen een index gebruikt.

Zo kun je ipv de join op de tabel2 deze al veranderen naar een query met een filter.

SQL:

SELECT
    tbl1.waardeF,
    tbl1.waardeG,
    tbl2.waardeI,
    tbl2.waardeH 
FROM tabel1 AS tbl1
LEFT JOIN (
        SELECT *
        FROM tabel2 
        WHERE waardeF = 'het-specifieke-label-nummer'
    ) AS tbl2 ON tbl1.waardeG = tbl2.waardeH
WHERE
    tbl1.waardeA = 'een-relatie-nr'
    AND tbl1.waardeB = 'een-volg-nr'
    AND tbl1.waardeF = 'het-specifieke-label-nummer'
    AND tbl1.waardeC = 0

Wat ook kan helpen is dat je een foreign key relatie hebt tussen de velden waarop je de join doet. Mits deze er is. Dan kan de db voor de join zijn indexen gebruiken.

Maak enkel voor de velden waarop je een filter doet een index. Over meerdere velden zou kunnen, maar dan moet je wel in je query die velden gebruiken. En probeer in je eerste filter het meeste weg te filteren.

[Douweegbertje]

Dekaasboer schreef op woensdag 11 maart 2015 @ 20:19:
Dit lijkt erg veel op de gui van MSSQL.

Als je een seperate index maakt op alleen je clausules in je where join dan kan het efficienter zijn om alleen deze data te controleren en daarna het resultaat er bij te zoeken.

Moet wel gezegd worden dat SQL dan wel een goed beeld moet hebben van het aantal rijen in de tabel en de verwachtte uitkomst. Wat zegt je estimated excecution plan versus je actual? Heb je actuele statistics op beide tabellen?

Ik vind die compute ook vreemd, je joint op 2 predicaten hebben die wel beiden het zelfde datatype in beide tabellen?

En hoe groot is het probleem met deze query? Je kan ook nog testen met eenindexed view..

Het is niet echt mogelijk om de query an sich aan te passen (voor als nog) aangezien het te veel wordt gebruikt. Daarbij dan ook het antwoord omtrent het probleem; relatief groot. Het is gewoon 'traag' en dat zal alleen maar erger worden naar mate er meer data in komt (en dat gaat wel rap).

Estimated is identiek aan actual.

Wat voor statistics?

Is niet zelfde datatype, en dat kan het ook niet worden ook. Varchar(255) vs numeric (6,0).

Feanathiel schreef op woensdag 11 maart 2015 @ 20:20:
Wat ik in dit plaatje mis, is de key lookup. Als hij dan toch de index 'Non-Clustered index op F met included colomns D en E' pakt, dan mis ik daar alleen al B, C en G in. Die zal hij er bij moeten zoeken. Is dit de query die daadwerkelijk wordt uitgevoerd? En hetzelfde geld voor de index; is dit de index die gebruikt wordt?

Even voor de leesbaarheid de query over meerdere regels uitgeschreven. Verder niks aan gedaan.

SQL:

SELECT
    tbl1.waardeF,
    tbl1.waardeG,
    tbl2.waardeI,
    tbl2.waardeH 
FROM tabel1 AS tbl1
LEFT JOIN tabel2 AS tbl2
    ON
        tbl1.waardeG = tbl2.waardeH
        AND tbl2.waardeF = 'het-specifieke-label-nummer'
WHERE
    tbl1.waardeA = 'een-relatie-nr'
    AND tbl1.waardeB = 'een-volg-nr'
    AND tbl1.waardeF = 'het-specifieke-label-nummer'
    AND tbl1.waardeC = 0

Ik doe geen SELECT * dus waarom een key lookup? In feite heb ik een 'covering index'.
Query klopt met het plaatje.

[Douweegbertje]

SPee schreef op woensdag 11 maart 2015 @ 20:40:
Wat je ziet is dat een deel van je query supersnel gaat. En de andere helft langzamer.
Daarna worden ze samengevoegd (je join) wat ook al niet zo snel gaat.
Wat je wilt is dat hij voor beide delen een index gebruikt.

Zo kun je ipv de join op de tabel2 deze al veranderen naar een query met een filter.

SQL:

SELECT
    tbl1.waardeF,
    tbl1.waardeG,
    tbl2.waardeI,
    tbl2.waardeH 
FROM tabel1 AS tbl1
LEFT JOIN (
        SELECT *
        FROM tabel2 
        WHERE waardeF = 'het-specifieke-label-nummer'
    ) AS tbl2 ON tbl1.waardeG = tbl2.waardeH
WHERE
    tbl1.waardeA = 'een-relatie-nr'
    AND tbl1.waardeB = 'een-volg-nr'
    AND tbl1.waardeF = 'het-specifieke-label-nummer'
    AND tbl1.waardeC = 0

Wat ook kan helpen is dat je een foreign key relatie hebt tussen de velden waarop je de join doet. Mits deze er is. Dan kan de db voor de join zijn indexen gebruiken.

Maak enkel voor de velden waarop je een filter doet een index. Over meerdere velden zou kunnen, maar dan moet je wel in je query die velden gebruiken. En probeer in je eerste filter het meeste weg te filteren.

Waar haal je deze data vandaan? Beide tables gebruiken hun indexen.
De join is rete snel, namelijk 0% van de tijd.
Het zoeken naar de correcte values uit table1 gaat enorm ruk, als ik de join weg haal heb ik in feite precies het zelfde, behalve dat de join weg is. Vrijwel identieke looptijd.

Het enige waar ik nu even aan zit te denken is dat de 'exuction structuur' een beetje wank is.
De Nested loop / join is meer voor de parallel zoeken in de index van tbl1.

[Feanathiel]

Douweegbertje schreef op woensdag 11 maart 2015 @ 20:41:
[...]

Ik doe geen SELECT * dus waarom een key lookup? In feite heb ik een 'covering index'.
Query klopt met het plaatje.

Een covering index heb je pas als je gelijk of minder specifiek bent aan een index. De non-clustered index die genoemd werd, bevat alleen F, D en E. In de query zelf zoek je ook nog op A, B en C. G zou daarbij handig zijn als deze ook in de index staat. Een key lookup had ik verwacht omdat je slechts een non-clustered index hebt met F erin (en een losse bak met D en E data). De rest moet er nog bij gezocht worden.

@hieronder: maar dan klopt het weer niet met de afbeelding. Daarin staat een non-clustered index scan. Maakt het lastig om zo een goed antwoord erop te geven. Ergens matched de afbeelding niet met de indexen die gegeven worden. Daar ging het mij om.

Referentie: https://www.simple-talk.c...mprove-query-performance/

[ Voor 22% gewijzigd door Feanathiel op 11-03-2015 21:03 ]

[Douweegbertje]

Hij roept in dit geval de index met A-B-C-F-G aan, wellicht relevant want ik snap je niet helemaal

[ Voor 34% gewijzigd door Douweegbertje op 11-03-2015 20:58 ]

[SPee]

Douweegbertje schreef op woensdag 11 maart 2015 @ 20:47:
[...]
Waar haal je deze data vandaan? Beide tables gebruiken hun indexen.
De join is rete snel, namelijk 0% van de tijd.
Het zoeken naar de correcte values uit table1 gaat enorm ruk, als ik de join weg haal heb ik in feite precies het zelfde, behalve dat de join weg is. Vrijwel identieke looptijd.

Het enige waar ik nu even aan zit te denken is dat de 'exuction structuur' een beetje wank is.
De Nested loop / join is meer voor de parallel zoeken in de index van tbl1.

Als je kijkt zie je een splitsing.
Pad 2 doet een Index seek; dus een index ophalen.
Pad 1 doet een parallelle Index scan; dus zoeken. Je moet zorgen daar ook een Index seek doet. Dit kun je doen door je WHERE condities te matchen met je indexen of je indexen met je query. Dan zal hij ook niet meer die parallel zoeken hoeven te doen.

[Douweegbertje]

Dus een index maken met A-B-C-F?

Overigens staat het parallel zoeken daar totaal los van, aangezien het gewoon een manier is om 'load' te verspreiden over meerdere cores. Iets wat ik ook al hebt getest, maar met parallel zoeken gaat het een stuk sneller.

[Jaymz]

Heeft de index op A,B,C,F zin gehad (zet de 'uniekste' waarde zo links mogelijk). Wat gebeurd er als je G,I,H als included columns toevoegd ?