[VB6 & MySQL]Query traag in VB applicatie

[pkouwer]

Ik zit met het volgende probleem:
tot voor kort had ik een applicatie die een Access-database gebruikte. Om dit voor grotere databases ook geschikt te maken, heb ik het programma verbouwd dat ook MySQL-database ondersteund wordt. Tot zover geen probleem. Momenteel bevinden zich ca. 30.000 records in de tabel registraties. Met Access is hij op zich vrij snel (voor Access), maar MySQL valt mij enorm tegen. Ik heb even de search gebruikt en gezocht naar o.a. joins, maar ik weet nu niet of joins sneller of trager werken.

Als ik de recordset ophaal met:

code:

sSql = "SELECT id, datum,tijd,netlijn,toestel,wektijd,duur,telefoonnummer " _
        & "FROM registraties " _
        & "WHERE oproeptype='1' " _
        & "AND simulatie='False' " _
        & "AND (datum)>='" & Format(frmAgenda.mntStartDatum.Value, "YYYY-MM-DD") & "' " _
        & "AND (datum)<='" & Format(frmAgenda.mntEindDatum.Value, "YYYY-MM-DD") & "' " '_
rst.Open sSql, cnn, adOpenStatic, adLockOptimistic

dan duurt het ca. 50 seconden voordat de data in een listview staat (die maak ik eerst invisible, daarna visible). Nu heb ik een ander programma, MySQLFront en daar voer ik dezelfde query in. Na 1 maal knipperen met je ogen staat alle (slechts) 145 records in een gridje.

Ik heb het idee dat het door VB6 komt, maar durf dit niet met zekerheid te zeggen. Wat kan ik er nogmeer aan doen om snelheid te winnen ?

[Verwijderd]

heb je ook netjes de indexen overgenomen vanuit Access naar MySQL ? Wel of geen indexen kan erg veel verschil uitmaken.

[pkouwer]

Ik heb indexen geplaatst op de velden in de WHERE-clause.

Hoe zit het met het overhalen van de data? Ik dacht dat je bij MySQL alleen tijdens het uitvoeren van de query

code:

rst.open sSql,cnn,...

een verbinding met de database hebt en bij het vullen van de listview niet meer ? Het tegendeel blijkt het geval te zijn.

[whoami]

Is het de query die traag is, of het vullen van de listview?
Voer de query eens gewoon uit.

Ben je er zeker van dat de goede indexen gebruikt worden?
Waarom zet je quotes rond 1, en False ? Ik ga er vanuit dat oproeptype een numeriek veld is, dus die quotes rond die 1 moeten daar weg.
Als simulatie een BOOL is, dan moeten die quotes rond False ook weg, en hetzelfde geld voor die datums.

[Verwijderd]

Hoe open je de connectie? (wat staat er dus in de cnn variabele)

Misschien dat daar iets verkeerd staat

[_js_]

Je zegt dat je indexen hebt gelegd over de velden in de where clausule, maar in mysql kan een query maar 1 index tegelijk gebruiken voor de where. Je hebt dus ook 1 index nodig over de velden in de where clausule. Waarschijnlijk zal een index over oproeptype,simulatie,datum beter werken.

Edit: Trouwens als MysqlFront wel snel werkt ligt het niet aan indexen, maar aan hoe je je verbinding maakt met VB, of wat je in VB allemaal doet met de resultaten.

[ Voor 23% gewijzigd door _js_ op 23-03-2004 11:43 ]

[whoami]

_js_ schreef op 23 maart 2004 @ 11:37:
Je zegt dat je indexen hebt gelegd over de velden in de where clausule, maar in mysql kan een query maar 1 index tegelijk gebruiken voor de where.

Meen je dat nu? Ik gebruik zelf geen MySQL, en ik weet dat MySQL heel wat tekortkomingen heeft, maar dit kan ik toch moeilijk geloven.

[_js_]

Het lijkt me eigenlijk logisch dat dat zo is in elke database... Maar het is in ieder geval zo in mysql.

[Verwijderd]

Voordat je de Open functie aanroept van de Recordset moet je eens proberen deze regel te plaatsen.

Visual Basic:

' Om de data in 1 keer op te halen
rst.CursorLocation = adUseClient

' Of

' Om de data per record op te halen
rst.CursorLocation = adUseServer

rst.Open "SELECT blablabla"

De bovenste is vaak veel sneller maar neemt ook veel meer geheugen in beslag. Je moet deze statements wel uitvoeren voordat je de recordset opent.

[ Voor 11% gewijzigd door Verwijderd op 23-03-2004 12:03 ]

[whoami]

_js_ schreef op 23 maart 2004 @ 11:55:
Het lijkt me eigenlijk logisch dat dat zo is in elke database... Maar het is in ieder geval zo in mysql.

Dat is totaal niet logisch.
Een serieuze RDBMS bepaalt het optimale execution plan voor het uitvoeren van een query en kijkt welke indexen hij kan gebruiken.
Als hij 5 indexen kan gebruiken, en als die indexen bruikbaar zijn, dan zal hij niet nalaten om dat te doen.

Als je't niet gelooft: voer eens een query uit in Query Analyzer op een SQL Server, en bekijk het execution plan eens.

[ Voor 12% gewijzigd door whoami op 23-03-2004 12:05 ]

[Bosmonster]

Is inderdaad nogal een tekortkoming in MySQL. Het betekent grofweg dat je voor bijna iedere query een losse gecombineerde index kan maken. Resultaat is heel veel dubbele informatie in indexen.

[_js_]

whoami schreef op 23 maart 2004 @ 12:05:
Een serieuze RDBMS bepaalt het optimale execution plan voor het uitvoeren van een query en kijkt welke indexen hij kan gebruiken.
Als hij 5 indexen kan gebruiken, en als die indexen bruikbaar zijn, dan zal hij niet nalaten om dat te doen.

Als je't niet gelooft: voer eens een query uit in Query Analyzer op een SQL Server, en bekijk het execution plan eens.

Ik zal sql server eens installeren om te kijken hoe het daar werkt.

Als je zin hebt (en tijd teveel) wil je misschien ondertussen uitleggen hoe je meerdere indexen kunt gebruiken op een tabel die er uit ziet als:

Veld1	Veld2
1	a
1	b
2	a
2	b

Stel een index op Veld1 en een index op Veld2, je zoekt waar Veld1=1 en Veld2=b, als je eerst index over Veld1 gebruikt om de velden met Veld1=1 te vinden, hoe zou je dan nog de index over Veld2 kunnen gebruiken? Het lijkt me (blijkbaar is dit dus fout) dat je een tijdelijke tabel moet maken met de resultaten van Veld1=1 en daarin verder kunt zoeken, dat zal langzamer zijn dan wanneer je met je index in een keer op beide waarden kunt zoeken....

[whoami]

Als je altijd zoekt op veld1 en veld2 (of toch in de meeste gevallen), dan leg je beter een samengestelde index op veld1 en op veld2, en niet 2 aparte indexen.
Echter, als je meestal enkel op veld1 of op veld2 zoekt, dan kan je 2 aparte indexen maken.
Als je een samengestelde index hebt die er zo uit ziet : (veld1, veld2), en je hebt geen aparte index op veld2, en je doet een query die enkel op veld2 zoekt, dan wordt je index niet gebruikt.

Als je, in jouw concreet geval, de records zoekt waarvoor veld1 = 1 is, en veld2 = b; dan gaat het DBMS eerst die ene index gebruiken om daaruit de records te filteren waarvoor veld1 = 1 is. Het DBMS weet waar die records zich bevinden, en gebruikt dan nog eens de 2de index om verder te filteren.
Als je in dit geval echter een samengestelde index gebruikt het, is dit performanter: het DBMS kan in 1x de juiste records vinden.
Voor jouw voorbeeld is een samengestelde index performanter dan 2 aparte, maar dit is dus niet altijd zo.

[_js_]

Ok, ik geloof dat ik dan begrijp hoe sql server werkt, maar ik probeer te bedenken waar een stel losse indexen sneller zou zijn dan een gecombineerde index, maar ik kan niet iets bedenken. Misschien een voorbeeld?

[whoami]

_js_ schreef op 23 maart 2004 @ 13:35:
Ok, ik geloof dat ik dan begrijp hoe sql server werkt

Niet alleen SQL Server werkt zo hoor.

maar ik probeer te bedenken waar een stel losse indexen sneller zou zijn dan een gecombineerde index, maar ik kan niet iets bedenken. Misschien een voorbeeld?

Stel dat je een samengestelde index hebt op 4 velden:
(veld1, veld2, veld3, veld4).
Stel dat je een query hebt, waarbij je enkel op veld2, veld3 of veld4 filtert: je samengestelde index wordt niet gebruikt.
Stel dat je op die velden een aparte query hebt, dan wordt die index wel gebruikt.
Het hangt gewoon allemaal van situatie tot situatie af.

[_js_]

Jouw voorbeeld is alleen sneller dan de gecombineerde index, omdat de gecombineerde index helemaal niet relevant is voor de query. Zijn er ook queries waar losse indexen beter zijn dan een relevante gecombineerde index?

Of is het in het kort: maak gecombineerde indexen voor je queries, en nog een paar losse voor het geval je een gecombineerde index vergeten bent/er zoveel mogelijke zoek velden zijn dat het aantal indexen de pan uitrijst?

[whoami]

Hoe meer indexen je hebt, hoe trager inserts, deletes en updates gaan. Je moet dus ook niet overdrijven in het aantal indexen dat je creeërt.

Als je in bovenstaand voorbeeld een SELECT doet op veld1 en veld3, of op veld1 en veld4, dan zullen aparte indexen ook sneller zijn.
Als je veel queries hebt waarbij je altijd op een bepaalde velden-combinatie filtert, kan je daar een samengestelde query op leggen.

[Ashtaroth]

ff een compliment voor whoami voor zijn heldere uitleg.

[pkouwer]

whoami schreef op 23 maart 2004 @ 13:55:
Hoe meer indexen je hebt, hoe trager inserts, deletes en updates gaan. Je moet dus ook niet overdrijven in het aantal indexen dat je creeërt.

Als je in bovenstaand voorbeeld een SELECT doet op veld1 en veld3, of op veld1 en veld4, dan zullen aparte indexen ook sneller zijn.
Als je veel queries hebt waarbij je altijd op een bepaalde velden-combinatie filtert, kan je daar een samengestelde query op leggen.

alvast een korte vraag voordat ik morgen verder ga, hoe stel je zo'n samengestelde index dan in ?

kleine update/test
VPN-verbinding met de zaak @ 128kbps aansluiting; 150 records in listview in 14 seconden, met cursorlocation=aduseclient. Zonder aduseclient ca. 63 hele seconden

[ Voor 14% gewijzigd door pkouwer op 23-03-2004 21:36 ]

[_js_]

http://www.mysql.com/docu..._Syntax.html#CREATE_INDEX