Environment: winxp sp2, mysql5.0.18, mysqlodbc 3.51 driver table uses myisam engine. access 2003
Different places:
1. When inserting data, mysql should try to use the insert into statement and avoid using rs.addnew. If it is necessary to use it, you should set conn.CursorLocation=3 first. Otherwise, an error will definitely be reported, and I found that using insert is about faster than addnew. 3 times. Also, if you use rs.addnew when you rs.update, you cannot immediately get the value of rs (id) in access.
2. Comparison with access:
With the same table structure, when using insert to insert 20,000 pieces of data, MySQL takes 7.5 seconds, and if you use rs.addnew, it takes 24 seconds! In access, using insert took 19.8 seconds, but rs.addnew only took 2.8 seconds!
Query comparison: Access query 400,000 pieces of data took 12 seconds, MySQL query took 14 seconds, which is slightly slower.
Summary: Generally speaking, the insertion speed of access is about three times faster than that of mysql. When querying data, if the amount of data is relatively small, access is about 2 times faster than mysql. However, when the amount of data is large, the query speed of mysql does not change much, but access drops 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 the concurrency performance, I have not 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 properties, you must first conn.CursorLocation=3
Otherwise, none of the above attributes can be used.
Also, in order to prevent garbled characters and Chinese errors from being inserted, you must immediately after conn.open: conn.Execute (SET NAMES 'gb2312')
Otherwise, the insertion error will definitely occur, and the code will be garbled.
Connection method:
'Connect using system DNS:
strconnection=dsn=mysql; OPTION=16384;driver={mysql odbc 3.51 driver};server=127.0.0.1;uid=root;pwd=67918;database=test
'Concatenate directly with strings:
'strconnection=Driver={mysql odbc 3.51 driver}; Server=localhost; Port=3306; Option=0; Socket=; Stmt=; Database=test; Uid=root; Pwd=67918;Option=16384
set conn=server.createobject(adodb.connection)
conn.CursorLocation=3
conn.open strconnection