Method 1, try to use complex SQL to replace simple SQL.
The same transaction, a complex SQL completion efficiency is higher than the efficiency of a pile of simple SQL. When there are multiple queries, it is good at using Join.
ORS = Oconn.execute ("Select * from books")
While not ics.eof
Strsql = "Select * from authors where authorid =" & ORS ("authorid") ics2 = oconn.execute (strsql)
Response.write ("Title" & ">>" & ORS2 ("Name") & "
& q UOT;
Ors.movenext ()
Wend
It is slow to slower than the code below:
STRSQL = "Select books.title, authors.name from books join authors on authors.authorid = books.authorid"
ORS = Oconn.execute (strsql)
While not ics.eof
Response.write ORS ("Title" & ">>" & ORS ("Name") & "
& quary;
Ors.movenext ()
Wend
Second, try to avoid using updated Recordset
ORS = Oconn.execute ("Select * from authors where authorid = 17", 3, 3)
ORS ("Name") = "Darkman"
Ors.update ()
It is slow to slower than the code below:
strsql = "Update Authors Set Name = 'DARKMAN' Where Authorid = 17"
Oconn.execute strsql
Method 3, when updating the database, try to use batch renewal
Make all SQLs a large batch of SQL and run again; this is more efficient than one-way update data. This is also more satisfied with the 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', '01', 'G4385', 5) / N";
Strsql = strsql & "INSERT INTO ORDERROWS (qty) VALUES ('9999,' 02 ',' 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 if the following transaction is encountered, if you encounter an error, cancel the completed transaction.
Method 4, database index
Those fields that will appear in the WHERE clause, you should first consider establishing an index; those those who need sorting should be considered.
Method for establishing an index in the MS Access: Select the table you need to index in Access, click "Design", and set the index of the corresponding field.
Method for establishing an index in MS SQL Server: In the SQL Server Manager, select the corresponding table, then "Design Table", click Right click, select "Properties", select "INDEXES / Keys"
Method 5, avoiding the TEXT field too much
When the value of the string is not fixed, it is better to use VARCHAR than using char. I have seen an example program, the field is defined as text (255), but his value is often only 20 characters. This data table has 50K records, so that this database is large, and the large database is inevitable.
