1. Try to use transaction processing to update the database
VB transaction processing includes multiple database operation instructions starting with BeginTrans and ending with CommitTrans or Rollback. In addition to ensuring the integrity of the database, transaction processing can also greatly improve the efficiency of database batch updates. This is because if the database update operation does not use transaction processing, each Update operation will cause the database to be written to disk once. After using transaction processing, updates are only performed in the memory buffer, and all modifications are written back to the disk at once when CommitTrans is executed. There are a few points to note when using transaction processing:
(1) Transaction processing must have a complete error checking mechanism;
(2) Because VB uses page locks on the database before the transaction ends, in a multi-user environment, if the transaction is suspended, other users will not be able to access the locked data.
2. Try to use code decomposition for Select retrieval operations
Using Select for database operations is simple and easy to use, but if some retrieval operations are decomposed into equivalent manual retrieval codes, the retrieval speed of the database will be greatly accelerated. The basic method of decomposition is to index the retrieval key fields and use Seek After the method is located, based on the sorted characteristics of the indexed database, Row traversal search. When the traversal range is not very wide, this method can improve the database access speed dozens of times. For example: Select*frompersonwherevol='123'andbirth=#11-02-73# is decomposed into the following operations , the access speed can be greatly improved:
Table.Index=volTable.Seek=, 123ifnottable.nomatchthen
whilenottable.eof
iftable(vol)=123then
table.movelast
elseiftable(Birth)=#11-02-73#then
'Find record
endif
table.movenext
wendendif
3. Use attach to bind the database table
When using ODBC to connect to database servers such as MSSQLServer, Oracle and Sysbase, we can bind the tables on the server to the MSAcess database through the attach function of MSAcess, because the MSAcess database can cache the structure of the table on the database server. When we access ODBC data source can improve the system's access performance.
4. Use the dbSQLPassThrough option
When connecting to MSSQLServer, Oracle and Sysbase database servers, use the dbSQLPassThrough option to send commands directly to the database server, thereby reducing the middleware's inspection and interpretation of commands and improving database access performance. The following example uses a stored procedure to create Dynaset:
dimmydbasdatabasedimmydsasdynasetsetmydb=opendatabase(..........)'Open the database setmyds=mydb.openrecordset(name, dbOpendynaset, dbSQLPassThrough,...)
The disadvantage of using dbSQLPassThrough is that the returned recordset is read-only.
5. Correctly use the database access object Table.
Supports SEEK commands and indexes. The combination of the two can find records the fastest. The author once did a speed comparison test, and it took 3.5 seconds for Table to retrieve 100,000 records, 9 seconds for Dynaset, and 10 seconds for Snapshot. Supports read and write operations. No Supports SORT and FILTER, and must use Index instead. Corresponding to the physical table in the database, addition, deletion, and modification will directly affect the database table.
Dynaset. The most flexible database object. It supports read and write operations and is a dynamic subset of records. In ODBC, write operations require setting a unique index. It supports SORT and FILTER. When using the JOIN command to associate multiple tables, you can only use Dynaset.
Snapshot. Can only read but not write, cannot lock. Does not support transaction processing. Does not support Edit, Addnew and Update methods.
->