Environment: winxpsp2, mysql5.0.18, mysqlodbc3.51driver table uses the myisam engine. access2003
Different places:
1. When inserting data, mysql should try to use insertinto statements to avoid using rs.addnew. If you want to use it, you should set conn.CursorLocation=3 first. Otherwise, an error will definitely be reported. I found that using insert is about 3 times faster than addingnew. Also, if you use rs.addnew when you update, you cannot immediately get the value of rs ("id") in access.
2. Comparison with access:
For the same table structure, when inserting 20,000 pieces of data, mysql was dissolved for 7.5 seconds, and if you use rs.addnew, it will take 24 seconds! In access, it was transformed for 19.8 seconds with insert, but rs.addnew only transformed for 2.8 seconds!
Query comparison: Access query 40w pieces were digitized for 12 seconds, and mysql was 14 seconds, which was a little slower.
Summary: In general, the insertion speed of access is about three times faster than mysql. When querying data, if the data volume is relatively small, access is about 2 times faster than mysql. However, when the data volume is large, the query speed of mysql does not change much, but the access decreases significantly.
Of course, mysql should be faster than access. I guess the problem lies in its odbc driver, and the access driver uses ole. Microsoft has made a lot of optimizations for it, so in this test, mysql was wronged! As for concurrency performance, I haven't tested it, but I think access should be much worse than mysql.
If you want to use
rs.pagecount
rs.pagesize
rs.AbsolutePage
rs.recordcount
For such a property, you must first conn.CursorLocation=3
Otherwise, all the above attributes cannot be used.
Also, in order to prevent garbled code and insert Chinese errors, you must immediately after conn.open: conn.Execute("SETNAMES'gb2312'")
Otherwise, there will definitely be errors in insertion, and the garbled code will not be negotiated.
Connection method:
'Connect with system DNS:
strconnection="dsn=mysql;OPTION=16384;driver={mysqlodbc3.51driver};server=127.0.0.1;uid=root;pwd=67918;database=test"
'Connect directly with strings:
'strconnection="Driver={mysqlodbc3.51driver};Server=localhost;Port=3306;Option=0;Socket=;Stmt=;Database=test;Uid=root;Pwd=67918;Option=16384"
setconn=server.createobject("adodb.connection")
conn.CursorLocation=3
conn.openstrconnection