Execute method One, try to use complex SQL instead of a simple heap of SQL.
For the same transaction, a complex SQL completion is more efficient than a bunch of simple SQL finishes. When you have multiple queries, be good at using joins.
[Code]
Ors=oconn.execute ("SELECT * FROM Books")
While not ors.eof
strSQL = "SELECT * from Authors WHERE authorid=" &ors ("Authorid") Ors2=oconn.execute (strSQL)
Response.Write ORs ("Title") & ">>" &ors2 ("Name") & "<br>&q Uot;
Ors.movenext ()
Wend
</div de>
Slower than the following code:
Strsql= "SELECT books.title,authors.name from Books JOIN Authors on Authors.authorid=books.authorid"
Ors=oconn.execute (strSQL)
While not ors.eof
Response.Write ORs ("Title") & ">>" &ors ("Name") & "<br>&qu ot;
Ors.movenext ()
Wend
Method Two, try to avoid using updatable Recordset
Ors=oconn.execute ("select * from Authors WHERE authorid=17", 3, 3)
ORs ("Name") = "Darkman"
Ors.update ()
Slower than the following code:
strSQL = "UPDATE Authors SET name= ' Darkman ' WHERE authorid=17"
Oconn.execute strSQL
Method III, update the database, as much as possible with batch update
Make all the SQL into a large batch SQL and run it one at a time, which is much more efficient than updating data one by one. This also satisfies your need for transaction processing:
Strsql= ""
strsql=strsql& "SET xact_abort on\n";
strsql=strsql& "BEGIN transaction\n";
strsql=strsql& "INSERT into Orders (ordid,custid,orddat) VALUES (' 9999 ', ' 1234 ', GETDATE ()) \ n";
strsql=strsql& "INSERT into Orderrows (ordid,ordrow,item,qty) VALUES (' 9999 ', '", ' G4385 ', 5) \ n ";
strsql=strsql& "INSERT into Orderrows (ordid,ordrow,item,qty) VALUES (' 9999 ', '", ' G4726 ', 1) \ n ";
strsql=strsql& "COMMIT transaction\n";
strsql=strsql& "SET xact_abort off\n";
Oconn.execute (strSQL);
Where the SET xact_abort off statement tells SQL Server to cancel a transaction that has been completed if an error is encountered during the following transaction processing.
Method four, database index
Those fields that will appear in the WHERE clause, you should first consider indexing, and those that need to be sorted should also be considered.
How to index in MS Access: in Access, select the table you want to index, click Design, and then set the index for the corresponding field.
How to index in MS SQL Server: In SQL Server Manager, select the appropriate table, then "Design the table," Right-click, select "Properties", select "Indexes/keys"
Method Five, avoid making the text field too large
Using varchar is better than using char when the value of a string is not fixed. I've seen an example of a program where the field is defined as text (255), but his value is often only 20 characters. This data table has 50k records, so that the database is very large, large database is necessarily slower.
