[SQL] Query erg traag

[Sangurash]

Ik heb een SQL qeury opgesteld die uit meerdere tabellen data opvraagt en weer samenvoegt tot 1 rij. Nu is deze query super traag > minuut. Hij is opgebouwd uit 4 unions, voor ieder geval 1.

- Persoon met account en met adres
- Persoon met account en zonder adres
- Persoon zonder account en met adres
- Persoon zonder account en zonder adres

Als een persoon geen account of adres heeft is er ook geen relatie meer tussen de persoonstabellen en account/adres tabel...


Mijn SQL ervaring is bepertk te noemen .

code:

/***************************    Query voor alle relevante persoongegevens **************/
/***************************    Pak alle personen met hun geldige adressen en met meerdere accounts (2 of meer)**************/
SELECT  S_CONTACT.ROW_ID,
    S_CONTACT.PER_TITLE,
    S_CONTACT.FST_NAME,
    S_CONTACT.MID_NAME,
    S_CONTACT.LAST_NAME,
    S_CONTACT.BIRTH_DT,
        S_CONTACT.HOME_PH_NUM,
    S_CONTACT.CELL_PH_NUM,
    S_CONTACT.EMAIL_ADDR,
    S_ADDR_PER.ZIPCODE,
    S_ADDR_PER.ADDR,
    S_ADDR_PER.ADDR_NUM,
    S_ADDR_PER.CITY,
    S_ORG_EXT_X.ATTRIB_47
        FROM S_CONTACT
             INNER JOIN S_ADDR_PER ON S_CONTACT.ROW_ID = S_ADDR_PER.PER_ID
             INNER JOIN S_PER_ORG_UNIT ON S_CONTACT.ROW_ID = S_PER_ORG_UNIT.PER_ID
             INNER JOIN S_ORG_EXT_X ON S_PER_ORG_UNIT.OU_ID = S_ORG_EXT_X.ROW_ID
                WHERE LAST_NAME LIKE '%bOer%' 
                    AND (BIRTH_DT = '12-29-1981' OR BIRTH_DT = '1-1-1900')
                        OR FST_NAME LIKE '%arJan%'
                    AND (BIRTH_DT = '12-29-1981' OR BIRTH_DT = '1-1-1900')
                ORDER BY S_CONTACT.ROW_ID

/***************************    Pak alle personen zonder een adres en met meerdere accounts (2 of meer)**************/
UNION
    SELECT  S_CONTACT.ROW_ID, 
        S_CONTACT.PER_TITLE,
        S_CONTACT.FST_NAME,
        S_CONTACT.MID_NAME,
        S_CONTACT.LAST_NAME,
        S_CONTACT.BIRTH_DT,
            S_CONTACT.HOME_PH_NUM,
        S_CONTACT.CELL_PH_NUM,
        S_CONTACT.EMAIL_ADDR,
        'onbekend',
        'onbekend',
        'onbekend',
        'onbekend',
        S_ORG_EXT_X.ATTRIB_47
            FROM S_CONTACT
                INNER JOIN S_PER_ORG_UNIT ON S_CONTACT.ROW_ID = S_PER_ORG_UNIT.PER_ID
                INNER JOIN S_ORG_EXT_X ON S_PER_ORG_UNIT.OU_ID = S_ORG_EXT_X.ROW_ID
                WHERE   S_CONTACT.PR_PER_ADDR_ID IS NULL AND LAST_NAME LIKE '%bOer%' 
                    AND (BIRTH_DT = '12-29-1981' OR BIRTH_DT = '1-1-1900')
                        OR S_CONTACT.PR_PER_ADDR_ID IS NULL AND FST_NAME LIKE '%arJan%' 
                    AND (BIRTH_DT = '12-29-1981' OR BIRTH_DT = '1-1-1900')

/***************************    Pak alle personen zonder een adres en zonder een account    **************/
UNION
    SELECT  S_CONTACT.ROW_ID, 
        S_CONTACT.PER_TITLE,
        S_CONTACT.FST_NAME,
        S_CONTACT.MID_NAME,
        S_CONTACT.LAST_NAME,
        S_CONTACT.BIRTH_DT,
            S_CONTACT.HOME_PH_NUM,
        S_CONTACT.CELL_PH_NUM,
        S_CONTACT.EMAIL_ADDR,
        'onbekend',
        'onbekend',
        'onbekend',
        'onbekend',
        'Geen'
            FROM S_CONTACT
                WHERE   (LAST_NAME LIKE '%bOer%'
                    AND (BIRTH_DT = '12-29-1981' OR BIRTH_DT = '1-1-1900')
                        OR FST_NAME LIKE '%arJan%'
                    AND (BIRTH_DT = '12-29-1981' OR BIRTH_DT = '1-1-1900')) 
                    AND NOT EXISTS 
                        (SELECT PER_ID 
                            FROM S_PER_ORG_UNIT 
                            WHERE S_CONTACT.ROW_ID = S_PER_ORG_UNIT.PER_ID)

/***************************    Pak alle personen met geldige adres en zonder een account   **************/
UNION
SELECT  S_CONTACT.ROW_ID, 
    S_CONTACT.PER_TITLE,
    S_CONTACT.FST_NAME,
    S_CONTACT.MID_NAME,
    S_CONTACT.LAST_NAME,
    S_CONTACT.BIRTH_DT,
        S_CONTACT.HOME_PH_NUM,
    S_CONTACT.CELL_PH_NUM,
    S_CONTACT.EMAIL_ADDR,
    S_ADDR_PER.ZIPCODE,
    S_ADDR_PER.ADDR,
    S_ADDR_PER.ADDR_NUM,
    S_ADDR_PER.CITY,
    'Geen'
        FROM S_CONTACT
             INNER JOIN S_ADDR_PER ON S_CONTACT.ROW_ID = S_ADDR_PER.PER_ID
                WHERE (LAST_NAME LIKE '%bOer%' OR FST_NAME LIKE '%arJan%')
                    AND (BIRTH_DT = '12-29-1981' OR BIRTH_DT = '1-1-1900')
                    AND NOT EXISTS 
                        (SELECT PER_ID 
                            FROM S_PER_ORG_UNIT 
                            WHERE S_CONTACT.ROW_ID = S_PER_ORG_UNIT.PER_ID)

Iemand die wat optimalisatie manieren kent? Ik heb al veel op internet gezocht helaas zonder succes .

[whoami]

Holy shit.
Misschien moet je eens naar FULL OUTER joins kijken....

Heb je ook de nodige indexen gelegd?

[ Voor 23% gewijzigd door whoami op 13-11-2003 11:50 ]

[Voutloos]

-hier stond onzin-

[ Voor 92% gewijzigd door Voutloos op 13-11-2003 11:55 ]

[Verwijderd]

Grote valkuil: UNION is meteen een DISTINCT over je resultaatset. Als je dat niet nodig hebt UNION ALL gebruiken. Scheelt een stuk.

[Achwel]

Je heb in ieder afzonderlijke query een LIKE zitten. Voor zove ik weet werkt een index dan niet op dat veld en gaat ie een tablescan doen op de desbetreffende tabel. Je hebt dus een leuke hoeveelheid aan tablescans in je query zitten.

Als je tabellen vrij fors zijn, dan verklaart dat je performance.

[whoami]

Achwel schreef op 13 november 2003 @ 11:57:
Je heb in ieder afzonderlijke query een LIKE zitten. Voor zove ik weet werkt een index dan niet op dat veld

Dat hangt ervan af hoe je LIKE is.
Op deze manier gaat een index wel gebruikt worden:

code:

naam LIKE 'blaa%'

Op deze manieren kan er geen index gebruikt worden:

code:

naam LIKE '%blaa'
naam LIKE '%bla%'

Trouwens, naar mijn gevoel kan je die resultset ook zonder UNION verkrijgen, door gebruik te maken van OUTER joins.

[Sangurash]

Okej! Ik heb nu FULL OUTER JOINS erin gedaan, ik wist niet dat ze bestonden. Nu ziet de query er zo uit:

code:

/***************************    Query voor alle relevante persoongegevens **************/
/***************************    Pak alle personen met hun geldige adressen en met meerdere accounts (2 of meer)**************/
SELECT  S_CONTACT.ROW_ID,
    S_CONTACT.PER_TITLE,
    S_CONTACT.FST_NAME,
    S_CONTACT.MID_NAME,
    S_CONTACT.LAST_NAME,
    S_CONTACT.BIRTH_DT,
        S_CONTACT.HOME_PH_NUM,
    S_CONTACT.CELL_PH_NUM,
    S_CONTACT.EMAIL_ADDR,
    S_ADDR_PER.ZIPCODE,
    S_ADDR_PER.ADDR,
    S_ADDR_PER.ADDR_NUM,
    S_ADDR_PER.CITY,
    S_ORG_EXT_X.ATTRIB_47
        FROM S_CONTACT
             FULL OUTER JOIN S_ADDR_PER ON S_CONTACT.ROW_ID = S_ADDR_PER.PER_ID
             FULL OUTER JOIN S_PER_ORG_UNIT ON S_CONTACT.ROW_ID = S_PER_ORG_UNIT.PER_ID
             FULL OUTER JOIN S_ORG_EXT_X ON S_PER_ORG_UNIT.OU_ID = S_ORG_EXT_X.ROW_ID
                WHERE LAST_NAME LIKE '%bOer%' 
                    AND (BIRTH_DT = '12-29-1981' OR BIRTH_DT = '1-1-1900')
                        OR FST_NAME LIKE '%arJan%'
                    AND (BIRTH_DT = '12-29-1981' OR BIRTH_DT = '1-1-1900')

De compilatie is nu ongeveer 3x zo snel!! Mijn dank is zeer groot .

[Sangurash]

Waar een relatie niet bestaat wordt nu een SQl NULL ingevuld, echt geweldig

[whoami]

Nu kan je nog eens kijken of je wel indexen hebt liggen op de velden s_contact.row_id, s_addr_per.per_id, birth_dt, s_per_org_unit.per_id, ....

Is het ook nodig dat je die LIKE's met een wildcard vooraan en achteraan gebruikt? Anders kan je nog een index leggen op last_name en first_name.

[cimbom]

Ik heb niet detailed gekeken naar je query, maar zo te zien hoef je niet unions te gebruiken. Want met outer joins kan je alles met 1 query ophalen, tenminste dat zie ik zo (het kan wel zijn dat je perse union moet gebruiken, al kan ik me dat moeilijk voorstellen met deze query).
Bij je select kolommen iets doen als isnull(veldnaam,'onbekend') zoiets.

Dus key word left/right outer joins waar nodig.

edit: ik zie dat ik telaat ben...
Maar je gebruikt overall outer join? Mischien is het niet overall nodig ?

[ Voor 14% gewijzigd door cimbom op 13-11-2003 12:07 ]

[Sangurash]

Ik moet outer joins gebruiken omdat iedere join null kan zijn en toch moet ik die record hebben.

Ik heb nu een indexje aangemaakt gerangschikt op achternaam, daarna voornaam. Hoe moet ik die nu aanroepen in de query ?

[whoami]

Sangurash2 schreef op 13 november 2003 @ 12:23:
Ik heb nu een indexje aangemaakt gerangschikt op achternaam, daarna voornaam. Hoe moet ik die nu aanroepen in de query ?

Niet, het DBMS gaat zelf gaan bepalen welke indexen hij kan gebruiken.
Weet wel dat, als je een LIKE naam '%blaat%' doet, er geen index kan gebruikt worden.
Weet ook dat als je index op achternaam en op voornaam ligt (in die volgorde), en je zoekt enkel op voornaam, dat de index ook niet kan gebruikt worden.

[majornono]

Sangurash2 schreef op 13 november 2003 @ 12:23:
Ik heb nu een indexje aangemaakt gerangschikt op achternaam, daarna voornaam. Hoe moet ik die nu aanroepen in de query ?

Niet, dat doet SQL voor je. Hij maakt er tijdens het uitvoeren van de query gebruik van om de records te vinden

(te laat )

[ Voor 3% gewijzigd door majornono op 13-11-2003 12:27 . Reden: te laat ]

[Sangurash]

jammer ik wil dus de records die op voornaam of achternaam lijkt. Geen index dus. Bedankt voor je hulp! Kan ik weer verder met mijn database ontdubbel programma .

Wel een gemis op mijn opleiding SQL werd alleen geven in het eerste jaar, meer een introductie.

[EfBe]

Je hebt een OR in je where clause. Dit zorgt voor multi-table scans. Zodra je een OR nodig hebt in een WHERE Clause, moeten de rode lampen gaan branden en de bellen gaan rinkelen, dat daar iig de performance een flinke knauw krijgt.

Ik zou verder naar het query execution plan kijken en kijken waar de bottleneck zit, 10 tegen 1 die OR.

[cimbom]

Die where clause klopt inderdaad niet, je kan beter dit doen

code:

WHERE 
(LAST_NAME LIKE '%bOer%' OR FST_NAME LIKE '%arJan%')
AND 
(BIRTH_DT = '12-29-1981' OR BIRTH_DT = '1-1-1900')

[ Voor 14% gewijzigd door cimbom op 13-11-2003 12:50 ]

[chem]

Doe ook eerst de date, dan de name-check. En doe kleine velden met de where like eerst, en grote later. Zo voorkom je nodeloos gezoek in velden, als toch al vasstaat dat het record niet geldig is.

[whoami]

Die OR moet je idd weglaten.
Je kan het gewoon zo schrijven:

code:

WHERE birth_date = '12-29-1981'

chem schreef op 13 november 2003 @ 13:08:
Doe ook eerst de date, dan de name-check. En doe kleine velden met de where like eerst, en grote later. Zo voorkom je nodeloos gezoek in velden, als toch al vasstaat dat het record niet geldig is.

Dat zou eigenlijk niet mogen uitmaken. De optimizer van het DBMS zou zowiezo het ideale execution plan moeten bepalen.

[ Voor 67% gewijzigd door whoami op 13-11-2003 13:09 ]

[Sangurash]

Helaas kom ik niet om die OR statements heen. Of voornaam of achternaan en datum 29-12-1981 OF 1-1-1900

Het omdraaien van datum en naam hielp niets

[whoami]

Waarom 'of datum 1-1-1900' ?

[EfBe]

whoami schreef op 13 november 2003 @ 13:23:
Waarom 'of datum 1-1-1900' ?

Dat is de default waarde voor DataTime in VB dacht ik. (of beter: als je '0' omzet in een datum krijg je dat )

[Sangurash]

Inderdaad het komt veel voor dat iemand via het web bijvoorbeeld zijn geboortedatum niet invult. Hierdoor krijg je dus de SQL standard waarde 1-1-1900.

[Sangurash]

Naja 37 seconden doet er nu over in een stored procedure. Misschien dat wat meer geheugen uitkomst gaat bieden (128 mb 800mhz SQL server en siebel server erop)

[whoami]

EfBe schreef op 13 november 2003 @ 12:41:
Je hebt een OR in je where clause. Dit zorgt voor multi-table scans. Zodra je een OR nodig hebt in een WHERE Clause, moeten de rode lampen gaan branden en de bellen gaan rinkelen, dat daar iig de performance een flinke knauw krijgt.

Ik zou verder naar het query execution plan kijken en kijken waar de bottleneck zit, 10 tegen 1 die OR.

If you find that SQL Server uses a TABLE SCAN instead of an INDEX SEEK when you use an IN or OR clause as part of your WHERE clause, even when those columns are covered by an index, consider using an index hint to force the Query Optimizer to use the index.

For example:

SELECT * FROM tblTaskProcesses WHERE nextprocess = 1 AND processid IN (8,32,45)

takes about 3 seconds, while:

SELECT * FROM tblTaskProcesses (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45)

returns in under a second

[whoami]

Sangurash2 schreef op 13 november 2003 @ 13:32:
Naja 37 seconden doet er nu over in een stored procedure. Misschien dat wat meer geheugen uitkomst gaat bieden (128 mb 800mhz SQL server en siebel server erop)

Heb je nu al eens indexen gelegd op de datum-columns, en op de velden waarop je joined?

[Sangurash]

De index stonden al goed, dit wordt allemaal beheerd door siebel en is blijkbaar goed ingesteld.

[whoami]

En worden ze wel goed gebruikt? Dit kan je zien in Query Analyzer door het execution plan van de query te tonen.

[Sangurash]

Staat bij alle tijd rovende gedeeltes Clustered Index Scan

[Sangurash]

Heb de query eens op de hoofdserver gedraaid (1024 mb intern) en daar liep deze query <1 seconde en was al klaar. Bedankt voor alle hulp en het verbeteren van deze query. Deze pc moet nodig wat meer geheugen

[Danfoss]

Sangurash2 schreef op 13 november 2003 @ 13:20:
Helaas kom ik niet om die OR statements heen. Of voornaam of achternaan en datum 29-12-1981 OF 1-1-1900

Het omdraaien van datum en naam hielp niets

Dan nog heb je geen OR voor de datum nodig, daar heb je IN voor:
Where datum in (29-12-1981, 1-1-1900)

Waarom gebruik je eigenljk een FULL outerjoin? als ik het zo lees is een enkele left ook goed.

Sangurash2 schreef op 14 november 2003 @ 13:20:
Heb de query eens op de hoofdserver gedraaid (1024 mb intern) en daar liep deze query <1 seconde en was al klaar. Bedankt voor alle hulp en het verbeteren van deze query. Deze pc moet nodig wat meer geheugen

Eigenlijk zou je dat niet moeten doen. Als je een snelle machine pakt maak je nooit optimale queries omdat performance geen issue is.

Ik heb dit een keer met een Oracle database gehad. Een behoorlijk complexe PLSQL procedure duurde op een dual p2-400 met 128mb 6 uur. Na optimaliseren van de code en de sql-statements kon ik de tijd verkorten naar 2 uur.
Toen is op een dag de database vervangen door een dual p4 xeon met 2gb ram en liep alles binnen 15 minuten (ook de oude procedure). Als die snelle database er al was had ik nooit die procedure efficienter gemaakt en zou ik nu dat soort procedures nog steeds op de trage manier maken.

[whoami]

Een left join is idd voldoende. My bad om een full outer join voor te stellen.

Welke zijn de tijdrovende gedeeltes waar je nog over spreekt? Het zoeken op naam zal alleszins nog traag gaan, omdat daar geen indexen zullen gebruikt worden.
Je zoekt nl. als volgt :

code:

LIKE '%blaat%'

[ Voor 36% gewijzigd door whoami op 14-11-2003 13:50 ]

[Sangurash]

Hehe, daar was ik zelf reeks achter gekomen dat left . Het bleek dat de stored procedure veel langere duurde door een full hash, veranderd naar left en nu is de executietijd van de query, 1 seconde geworden!

Veel geleerd, dank u allen!