Who said MySQL is fast??

[Gensor]

I created an application to compare Microsoft Access and MySQL.

Here are my connections string for both:

For MDB :

Call m_connMDB.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Data.mdb;Persist Security Info=False")

For MySQL :

Call m_connMySQL.Open("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=Data;USER=root;PASSWORD=grb;OPTION=3;")

I have 1 table in both DB.

The table is called "Test" and have 3 integer fields.

I have a loop that run through all records and add 1 to the value and save it.

For MDB :

Set rstMDB = New ADODB.Recordset
  
  Call rstMDB.Open("SELECT * FROM Test", m_connMDB, adOpenDynamic, adLockOptimistic)
  
  Do While Not rstMDB.EOF
    rstMDB.Fields("FIELD1") = rstMDB.Fields("FIELD1") + 1
    rstMDB.Fields("FIELD2") = rstMDB.Fields("FIELD2") + 1
    
    Call rstMDB.Update
    
    Call rstMDB.MoveNext
  Loop
  
  Call rstMDB.Close
  Set rstMDB = Nothing

For MySQL :

Set rstMySQL = New ADODB.Recordset
  
  Call rstMySQL.Open("SELECT * FROM Test", m_connMySQL, adOpenDynamic, adLockOptimistic)
  
  Do While Not rstMySQL.EOF
    rstMySQL.Fields("FIELD1") = rstMySQL.Fields("FIELD1") + 1
    rstMySQL.Fields("FIELD2") = rstMySQL.Fields("FIELD2") + 1
    
    Call rstMySQL.Update
    
    Call rstMySQL.MoveNext
  Loop
  
  Call rstMySQL.Close
  Set rstMySQL = Nothing

The MDB loop takes 3 seconds to execute and the MySQL loop takes 197 seconds to execute.

Am I doing something wrong?

[szlamany]

Aren't you really comparing OLEDB and ODBC - very different technologies...

[Gensor]

What is the OLEDB Connection String for MySQL ?

[szlamany]

This other thread seems to have posts about OLEDB providers for MySQL...

Good luck...

http://www.vbforums.com/showthread.php?t=274868