[vb/adodb] Erg traag, hoe te optimaliseren?

[mor0n]

Ik heb een DBISAM database wordt aangevuld met een Delphi applicatie, mijn taak is om deze database te converteren naar MySQL. Dit is wat maatwerk, want veel gegevens moeten daarbij niet worden meegenomen.

Omdat ik geen held ben in delphi, heb ik ervoor gekozen om dit in VB(6.0) te gaan maken.

De database openen, gaat prima. Ik heb op mijn ontwikkelsysteem een DSN aangemaakt.

Option Explicit

Public MyConnection As ADODB.Connection
Public MyRecordSet As ADODB.Recordset

Public Sub OpenDb()
    Set MyConnection = New ADODB.Connection
    MyConnection.ConnectionString = "DSN=zh"
    MyConnection.CursorLocation = adUseClient
    MyConnection.Mode = adModeRead
    MyConnection.Open
End Sub

Nou wil ik een tabel uitlezen met ong. 7000 records en dat doe ik op de volgende manier:

Private Sub Form_Load()
    OpenDb
End Sub

Private Sub Command1_Click()
    Set MyRecordSet = MyConnection.Execute("SELECT * FROM ZipCode")
    Do
        DoEvents
        '....gegevens verwerken
        MyRecordSet.MoveNext
    Loop Until MyRecordSet.EOF
End Sub

Private Sub Form_Unload(Cancel As Integer)
    MyConnection.Close
End Sub

Maar dit gaat zooo ontzettend traag, terwijl de tabel ZipCode maar uit twee kolommen bestaat:
key, zipcode. Wanneer ik de query uitvoer heeft mijn computer ontzettend veel schijfactiviteit.
Dit is nog maar een klein tabelletje vergeleken met de rest.

Hoe kan ik dit optimaliseren?

[Vesta]

Veel schijfactiviteit kan duiden op te weinig geheugen voor mysql, maar 7000 records zou toch niet superlangzaam moeten zijn. Heb je de query al met een ander programma uitgevoerd, bijvoorbeeld op de commandline? En gaat het wel snel als je de records alleen langsloopt, dus niets verwerkt. Zoals je hierboven hebt gepost dus?

[mor0n]

Vesta schreef op donderdag 11 augustus 2005 @ 11:37:
Veel schijfactiviteit kan duiden op te weinig geheugen voor mysql, maar 7000 records zou toch niet superlangzaam moeten zijn. Heb je de query al met een ander programma uitgevoerd, bijvoorbeeld op de commandline? En gaat het wel snel als je de records alleen langsloopt, dus niets verwerkt. Zoals je hierboven hebt gepost dus?

Het gaat nog niet om MySQL, maar eerst op de DBISAM tabel te kunnen lezen. Het gaat dus juist langzaam als ik die records bijlangsloop in de door mij geposte code.

Ik heb ook een DBISAM tool van EMS (net zoiets als EMS MySQL manager, maar dan voor DBISAM databases), daarmee kan ik vlot door de tabellen heen lopen.

[Woy]

Probeer eerst eens te achterhalen wat er precies zo langzaam gaat. Is het het inserten in de MySQL database, is het het uitvoeren van de Query of mischien het doorlopen van de recordset. Als je niet weet wat de bottleneck is kan je natuurlijk ook niet makkelijk optimaliseren.

Dus meer welke regel exact in jouw code gaat langzaam.

[ Voor 10% gewijzigd door Woy op 11-08-2005 11:45 ]

[mor0n]

Het connecten met de database gaat goed, duurt misschien 2 sec. Wanneer de Query uitgevoerd wordt is het afgelopen. Dan gaat mijn schijf ontzettend ratelen. Het database bestand (zipcode.dat) is maar 1,3 Mb.

Het kan ook zijn dat de ODBC koppeling de hele database gaat openen en die is ruim 1300 Mb en vervolgens de simpele query "select * from zipcode" gaat uitvoeren. Wel erg raar overigens. Ik ga eerst de rest van de tabellen even verwijderen en zet alleen de database bestanden van de tabel zipcode in de database directory.

De database ziet er op bestandsniveau ong. zo uit:

09-08-2005  10:10             2.816 EmailDef.dat
09-08-2005  10:10         1.449.088 History.dat
09-08-2005  13:45         6.325.984 IOZ_AddInfo.dat
09-08-2005  10:13         4.151.072 IOZ_img.dat
09-08-2005  10:10        11.977.200 IOZ_txt.dat
09-08-2005  10:10            31.232 Search.dat
09-08-2005  10:10             1.280 SearchHist.dat
15-11-2004  12:19           321.536 stam.dat
09-08-2005  10:10         1.333.616 ZipCode.dat
09-08-2005  10:10            29.184 EmailDef.idx
09-08-2005  10:10            94.720 History.idx
09-08-2005  13:40           184.832 IOZ_AddInfo.idx
09-08-2005  10:13         5.345.792 IOZ_img.idx
09-08-2005  10:10        10.019.328 IOZ_txt.idx
09-08-2005  10:10            29.184 Search.idx
09-08-2005  10:10            29.184 SearchHist.idx
15-11-2004  12:19           365.056 stam.idx
09-08-2005  10:10           197.120 ZipCode.idx
11-08-2005  10:40               512 EmailDef.blb
07-08-2005  12:27     1.094.178.304 IOZ_img.blb
09-08-2005  10:10        23.347.200 IOZ_txt.blb
09-08-2005  10:10               512 Search.blb

[ Voor 53% gewijzigd door mor0n op 11-08-2005 11:54 ]

[pjonk]

rwb schreef op donderdag 11 augustus 2005 @ 11:45:
Probeer eerst eens te achterhalen wat er precies zo langzaam gaat. Is het het inserten in de MySQL database, is het het uitvoeren van de Query of mischien het doorlopen van de recordset. Als je niet weet wat de bottleneck is kan je natuurlijk ook niet makkelijk optimaliseren.

Dus meer welke regel exact in jouw code gaat langzaam.

Tip benchmarking kan je als volgt in VB6 doen:

code:

Debug.Print "Before routine: " & Now & " "; Timer
... uit te voeren code

Debug.Print "After routine: " & Now & " "; Timer

Timer is wat exacter dan Now en is handig voor tijdmetingen in milliseconden.

[mor0n]

Dat heb ik inderdaad geprobeerd, maar zodra de routine start is het afgelopen. Ik krijg geen reactie meer van visual basic. Het enige wat rest op dat moment is Taak beeindigen.

[ Voor 48% gewijzigd door mor0n op 11-08-2005 11:58 ]

[pjonk]

deejayalwin schreef op donderdag 11 augustus 2005 @ 11:58:
Dat heb ik inderdaad geprobeerd, maar zodra de routine start is het afgelopen. Ik krijg geen reactie meer van visual basic. Het enige wat rest op dat moment is Taak beeindigen.

In dat geval kun je je timings beter in een file loggen.

[mor0n]

Nou het programma doet het nou wel weer, na wat aanpassingen in het DSN, maar het gaat ontzettend traag. Ik denk aan zo'n 1000 results per minuut.

[Wilfred]

Is in de tabel de postcode geindexeert? Anders doet SQL een full table scan.
Dat zou voor 7000 records ook niet zo lang mogen duren maar toch..

Is dit de enige query die lang duurt of duurt alles zo lang? Dan zou ik het in de configuratie van je MySQL server of database-instance zoeken.

SUC6

[Woy]

als het dus deze regel is die zolang duurt

Visual Basic:

Set MyRecordSet = MyConnection.Execute("SELECT * FROM ZipCode")

dan zit het probleem niet in deze code maar in de ODBC/Database Dan moet je dus daar gaan zoeken wat het probleem is.

[Verwijderd]

Een paar opmerkingen ...

Een ISAM database heeft een zeer ongunstige manier van disk gebruik, waardoor de files altijd blijven groeien en gefragmenteerd raken. Daarom moet je ze eens in de zoveel tijd compacter maken. Met Access heb je daar 'Compact & Repair' voor, waarschijnlijk is er voor deze database ook wel een tool voor. 1300 MB is ook belachelijk groot voor zo'n database, dus ik denk dat deze hoognodig compact gemaakt moet worden. Voer daarna ook een defrag uit op je disk om alles wat beter georganiseerd te krijgen op je schijf. Sowieso loop je wel een beetje tegen de beperkingen van dit type database op als je zoveel data erin stopt.

Probeer ipv een ODBC driver een OLEDB driver te vinden voor je database. Deze zijn meestal een stuk beter geoptimaliseerd.

Je gebruikt nu als cursor lokatie adUseClient. Maak daar eens adUseServer van en kijk of het uitmaakt.

Ik hoop dat dit wat helpt. Succes!

[ Voor 8% gewijzigd door Verwijderd op 11-08-2005 12:47 ]

[Brahiewahiewa]

Heb je die .CursorLocation nodig?
Volges mijn niet als je alleen maar .MoveNext gebruikt.
Nu ga je bij iedere .MoveNext weer de hele tabel door om de positie van de cursor te bepalen.

[pjonk]

Een ForwardOnly cursor gebruiken kan ook veel resources schelen. Lees onderstaande artikelen maar eens door:
http://www.4guysfromrolla.com/webtech/062799-3.shtml
http://www.4guysfromrolla.com/webtech/062799-3.report.shtml

[ Voor 17% gewijzigd door pjonk op 11-08-2005 13:14 ]

[Verwijderd]

Brahiewahiewa schreef op donderdag 11 augustus 2005 @ 12:59:
Heb je die .CursorLocation nodig?
Volges mijn niet als je alleen maar .MoveNext gebruikt.
Nu ga je bij iedere .MoveNext weer de hele tabel door om de positie van de cursor te bepalen.

Hij specificeert geen RecordSet type, dus het is standaard een adOpenForwardOnly. Dat is dus een hele lichte cursor. Als je deze op de client zet zal hij alsnog alle tabeldata in 1 keer overhalen en wellicht is een cursor op de server sneller. Hoe dan ook, een recordset is een cursor, dus je zult een beslissing moeten nemen over waar je deze plaatst.

Meer leesvoer hierover: http://www.windowsitpro.c...cleID/5918/5918.html?Ad=1

[BertS]

Ter aanvulling op de reeds gemaakte opmerkingen: zet die DoEvents eens in comment. Ik kan me niet voorstellen dat die hier nodig is (zeker niet op elke record).