Hello all,
I'm developing an VB application which uses the SAX parser and ADO VB to
insert.update data in in MySQL. The perormance is very bad. If I use the
same data and insert/update to a MS Access database it takes 14 minutes. But
using MySQL it takes about 5 hours! Here's the piece of code which takes the
most time. Does anyone have an explanation for this?
Regards,
--
Marco Scheffers
Consultant
Adogis BV, The Netherlands
ma***@gis.nl
http://www.gis.nl
Example code:
Connectionstring I use:
"DRIVER={MySQL ODBC 3.51
Driver};SERVER=bambam;DATABASE=test_globisdata;USE R=root;PASSWORD=mysql;OPTI
ON=3;"
Case "locatie"
'Verwijder alle records in alle tabellen met het gevonden locatie
ID
'De LOCATIE tabel als laatste!
Execute_Query ("DELETE REL_TAB_BESLUIT.* FROM REL_TAB_BESLUIT
WHERE LOCATIE_ID = '" & strLocation_id & "';")
Execute_Query ("DELETE REL_TAB_ZORGMAATREGELEN.* FROM
REL_TAB_ZORGMAATREGELEN WHERE LOCATIE_ID = '" & strLocation_id & "';")
Execute_Query ("DELETE REL_TAB_GEBRUIKSBEPERKING.* FROM
REL_TAB_GEBRUIKSBEPERKING WHERE LOCATIE_ID = '" & strLocation_id & "';")
Execute_Query ("DELETE REL_TAB_BESTEMMINGNASANERING.* FROM
REL_TAB_BESTEMMINGNASANERING WHERE LOCATIE_ID = '" & strLocation_id & "';")
Execute_Query ("DELETE KADASTRALEGEGEVENS.* FROM
KADASTRALEGEGEVENS WHERE LOCATIE_ID = '" & strLocation_id & "';")
Execute_Query ("DELETE VERDACHTEACTIVITEITEN.* FROM
VERDACHTEACTIVITEITEN WHERE LOCATIE_ID = '" & strLocation_id & "';")
Execute_Query ("DELETE ONDERZOEK.* FROM ONDERZOEK WHERE
LOCATIE_ID = '" & strLocation_id & "';")
Execute_Query ("DELETE GEOOBJECT.* FROM GEOOBJECT WHERE
LOCATIE_ID = '" & strLocation_id & "';")
Execute_Query ("DELETE METAINFORMATIE.* FROM METAINFORMATIE WHERE
LOCATIE_ID = '" & strLocation_id & "';")
Execute_Query ("DELETE LOCATIE.* FROM LOCATIE WHERE LOCATIE_ID =
'" & strLocation_id & "';")
'If the location has no Geoobject then it wil not be inserted
into the database
If Not (blnGeoobject = True) Then
Print #1, numLocaties & " Locatie " & strLocation_id & " heeft
geen geoobject : " & Now()
Else
Print #1, numLocaties & " Start insert locatie " & strLocation_id
& " : " & Now()
'Insert Locatie
Dim rs_LOCATIE As ADODB.Recordset
Set rs_LOCATIE = New ADODB.Recordset
rs_LOCATIE.ActiveConnection = cnDB
rs_LOCATIE.LockType = adLockOptimistic
rs_LOCATIE.Source = "LOCATIE"
rs_LOCATIE.Open
rs_LOCATIE.AddNew
rs_LOCATIE("LOCATIE_ID") = strLocation_id
rs_LOCATIE("NAAM") = strNAAM
rs_LOCATIE("STRAAT") = strSTRAAT
rs_LOCATIE("HUISNUMMER") = strHUISNUMMER
rs_LOCATIE("HUISLETTER") = strHUISLETTER
rs_LOCATIE("LETTERTOEVOEGING") = strLETTERTOEVOEGING
rs_LOCATIE("POSTCODE") = strPOSTCODE
rs_LOCATIE("PLAATS") = strPLAATS
If strGEMCODE <> "" Then
rs_LOCATIE("GEMCODE") = CInt(strGEMCODE)
End If
rs_LOCATIE("SANEIND") = strSANEIND
rs_LOCATIE("SANERING") = strSANERING
rs_LOCATIE("STAT_RAP") = strSTAT_RAP
If strSTATUSVER <> "" Then
rs_LOCATIE("STATUSVER") = CInt(strSTATUSVER)
End If
rs_LOCATIE("STAT_BESL") = strSTAT_BESL
If strSTATDYN <> "" Then
rs_LOCATIE("STATDYN") = CInt(strSTATDYN)
End If
If strVOOR87 <> "" Then
'rs_LOCATIE("VOOR87") = CBool(strVOOR87)
rs_LOCATIE("VOOR87") = strVOOR87
End If
rs_LOCATIE("OPPLOCATIE") = strOPPLOCATIE
If strCONVENANT <> "" Then
rs_LOCATIE("CONVENANT") = CInt(strCONVENANT)
End If
rs_LOCATIE("EIGENAAR") = strEIGENAAR
rs_LOCATIE.Update
rs_LOCATIE.Close
Set rs_LOCATIE = Nothing
'Insert Onderzoeken
Dim rs_ONDERZOEK As ADODB.Recordset
Set rs_ONDERZOEK = New ADODB.Recordset
rs_ONDERZOEK.ActiveConnection = cnDB
rs_ONDERZOEK.LockType = adLockOptimistic
rs_ONDERZOEK.Source = "ONDERZOEK"
rs_ONDERZOEK.Open
For i = 0 To (numOnderzoeken_per_Locatie - 1)
rs_ONDERZOEK.AddNew
rs_ONDERZOEK("LOCATIE_ID") = strLocation_id
If arrTYPE(i) <> "" Then
rs_ONDERZOEK("TYPE") = CInt(arrTYPE(i))
End If
rs_ONDERZOEK("RAPPORTAUTEUR") = arrRAPPORTAUTEUR(i)
rs_ONDERZOEK("RAPPORTNR") = arrRAPPORTNR(i)
rs_ONDERZOEK("RAPPORTDATUM") = arrRAPPORTDATUM(i)
rs_ONDERZOEK.Update
Next
rs_ONDERZOEK.Close
Set rs_ONDERZOEK = Nothing
'Insert Verdachteactiviteiten
Dim rs_VERDACHTEACTIVITEITEN As ADODB.Recordset
Set rs_VERDACHTEACTIVITEITEN = New ADODB.Recordset
rs_VERDACHTEACTIVITEITEN.ActiveConnection = cnDB
rs_VERDACHTEACTIVITEITEN.LockType = adLockOptimistic
rs_VERDACHTEACTIVITEITEN.Source = "VERDACHTEACTIVITEITEN"
rs_VERDACHTEACTIVITEITEN.Open
For i = 0 To (numVerdachteactiviteiten_per_Locatie - 1)
rs_VERDACHTEACTIVITEITEN.AddNew
rs_VERDACHTEACTIVITEITEN("LOCATIE_ID") = strLocation_id
If arrUBICODE(i) <> "" Then
rs_VERDACHTEACTIVITEITEN("UBICODE") = CLng(arrUBICODE(i))
End If
rs_VERDACHTEACTIVITEITEN("STARTJAAR") = arrSTARTJAAR(i)
rs_VERDACHTEACTIVITEITEN("EINDJAAR") = arrEINDJAAR(i)
rs_VERDACHTEACTIVITEITEN.Update
Next
rs_VERDACHTEACTIVITEITEN.Close
Set rs_VERDACHTEACTIVITEITEN = Nothing
'Insert Kadastralegegevens
Dim rs_KADASTER As ADODB.Recordset
Set rs_KADASTER = New ADODB.Recordset
rs_KADASTER.ActiveConnection = cnDB
rs_KADASTER.LockType = adLockOptimistic
rs_KADASTER.Source = "KADASTRALEGEGEVENS"
rs_KADASTER.Open
For i = 0 To (numKadastralegegevens_per_Locatie - 1)
rs_KADASTER.AddNew
rs_KADASTER("LOCATIE_ID") = strLocation_id
rs_KADASTER("SECTIE") = arrSECTIE(i)
rs_KADASTER("PERCEEL") = arrPERCEEL(i)
rs_KADASTER("KAD_GEMCODE") = arrKAD_GEMCODE(i)
rs_KADASTER.Update
Next
rs_KADASTER.Close
Set rs_KADASTER = Nothing
'Insert BestemmingenNaSanering
Dim rs_BESTEMMING As ADODB.Recordset
Set rs_BESTEMMING = New ADODB.Recordset
rs_BESTEMMING.ActiveConnection = cnDB
rs_BESTEMMING.LockType = adLockOptimistic
rs_BESTEMMING.Source = "REL_TAB_BESTEMMINGNASANERING"
rs_BESTEMMING.Open
For i = 0 To (numBestemmingenNaSanering_per_Locatie - 1)
rs_BESTEMMING.AddNew
rs_BESTEMMING("LOCATIE_ID") = strLocation_id
If arrBESTEMMING(i) <> "" Then
rs_BESTEMMING("BESTEMMING") = CInt(arrBESTEMMING(i))
End If
rs_BESTEMMING.Update
Next
'rs_BESTEMMING.Close
Set rs_BESTEMMING = Nothing
'Insert Gebruiksbeperkingen
Dim rs_BEPERKING As ADODB.Recordset
Set rs_BEPERKING = New ADODB.Recordset
rs_BEPERKING.ActiveConnection = cnDB
rs_BEPERKING.LockType = adLockOptimistic
rs_BEPERKING.Source = "REL_TAB_GEBRUIKSBEPERKING"
rs_BEPERKING.Open
For i = 0 To (numGebruiksbeperkingen_per_Locatie - 1)
rs_BEPERKING.AddNew
rs_BEPERKING("LOCATIE_ID") = strLocation_id
If arrBEPERKING(i) <> "" Then
rs_BEPERKING("BEPERKING") = CInt(arrBEPERKING(i))
End If
rs_BEPERKING.Update
Next
rs_BEPERKING.Close
Set rs_BEPERKING = Nothing
'Insert Zorgmaatregelen
Dim rs_ZORG As ADODB.Recordset
Set rs_ZORG = New ADODB.Recordset
rs_ZORG.ActiveConnection = cnDB
rs_ZORG.LockType = adLockOptimistic
rs_ZORG.Source = "REL_TAB_ZORGMAATREGELEN"
rs_ZORG.Open
For i = 0 To (numZorgmaatregelen_per_Locatie - 1)
rs_ZORG.AddNew
rs_ZORG("LOCATIE_ID") = strLocation_id
If arrZORG(i) <> "" Then
rs_ZORG("ZORG") = CInt(arrZORG(i))
End If
rs_ZORG.Update
Next
rs_ZORG.Close
Set rs_ZORG = Nothing
'Insert Besluiten
Dim rs_BESLUIT As ADODB.Recordset
Set rs_BESLUIT = New ADODB.Recordset
rs_BESLUIT.ActiveConnection = cnDB
rs_BESLUIT.LockType = adLockOptimistic
rs_BESLUIT.Source = "REL_TAB_BESLUIT"
rs_BESLUIT.Open
For i = 0 To (numBesluiten_per_Locatie - 1)
rs_BESLUIT.AddNew
rs_BESLUIT("LOCATIE_ID") = strLocation_id
rs_BESLUIT("BSL_DAT") = arrBSL_DAT(i)
rs_BESLUIT("BSL_CODE") = arrBSL_CODE(i)
rs_BESLUIT("KENMERK") = arrKENMERK(i)
rs_BESLUIT.Update
Next
rs_BESLUIT.Close
Set rs_BESLUIT = Nothing
'Metainformatie
Dim rs_METAINFORMATIE As ADODB.Recordset
Set rs_METAINFORMATIE = New ADODB.Recordset
rs_METAINFORMATIE.ActiveConnection = cnDB
rs_METAINFORMATIE.LockType = adLockOptimistic
rs_METAINFORMATIE.Source = "METAINFORMATIE"
rs_METAINFORMATIE.Open
rs_METAINFORMATIE.AddNew
rs_METAINFORMATIE("LOCATIE_ID") = strLocation_id
If strLEVERANCIER <> "" Then
rs_METAINFORMATIE("LEVERANCIER") = CInt(strLEVERANCIER)
End If
If strINFORMATIESYSTEEM <> "" Then
rs_METAINFORMATIE("INFORMATIESYSTEEM") =
CInt(strINFORMATIESYSTEEM)
End If
'If strDATABASE <> "" Then
'rs_METAINFORMATIE("DATABASE") = CInt(strDATABASE)
'End If
rs_METAINFORMATIE("DATUM") = strDATUM_METAINFORMATIE
rs_METAINFORMATIE("VERSIE") = strVERSIE
rs_METAINFORMATIE("ORGANISATIE") = strORGANISATIE
rs_METAINFORMATIE.Update
rs_METAINFORMATIE.Close
Set rs_METAINFORMATIE = Nothing
'Geoobject
Dim rs_GEOOBJECT As ADODB.Recordset
Set rs_GEOOBJECT = New ADODB.Recordset
rs_GEOOBJECT.ActiveConnection = cnDB
rs_GEOOBJECT.LockType = adLockOptimistic
rs_GEOOBJECT.Source = "GEOOBJECT"
rs_GEOOBJECT.Open
'Debug.Print
(theGeoObject.colPolygons.Item(1).colParts.Item(2) .colPoints.Count)
For i = 1 To (theGeoObject.colPolygons.Count)
For j = 1 To (theGeoObject.colPolygons(i).colParts.Count)
For k = 1 To
(theGeoObject.colPolygons(i).colParts(j).colPoints .Count)
'Debug.Print i & " " & j & " " & k & " " &
theGeoObject.colPolygons.Item(i).colParts.Item(j). colPoints.Item(k).dblXCoor
d & " ";
theGeoObject.colPolygons.Item(i).colParts.Item(j). colPoints.Item(k).dblYCoor
d
rs_GEOOBJECT.AddNew
rs_GEOOBJECT("LOCATIE_ID") = strLocation_id
If numPoints = 1 Then
'the P of punt
rs_GEOOBJECT("TYPE") = "P"
Else
'the V of vlak
rs_GEOOBJECT("TYPE") = "V"
End If
rs_GEOOBJECT("POLYGON") = CInt(i)
rs_GEOOBJECT("PART") = CInt(j)
rs_GEOOBJECT("POINT") = CInt(k)
rs_GEOOBJECT("XCOORD") =
CDbl(theGeoObject.colPolygons.Item(i).colParts.Ite m(j).colPoints.Item(k).dbl
XCoord)
rs_GEOOBJECT("YCOORD") =
CDbl(theGeoObject.colPolygons.Item(i).colParts.Ite m(j).colPoints.Item(k).dbl
YCoord)
rs_GEOOBJECT.Update
Next
Next
Next
rs_GEOOBJECT.Close
Set rs_GEOOBJECT = Nothing
'Set theGeoObject = Nothing
Print #1, numLocaties & " Einde insert locatie " &
strLocation_id & " : " & Now()
End If 'If (blnGeoobject) = True
End Select
