How to avoid low execution efficiency of asp SQL statements

For the same transaction, the efficiency of a complex SQL statement is higher than that of a bunch of simple SQL statements. When there are multiple queries, you must be good at using join.

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 ")&"

& Q uot;

ORS. movenext ()

Wend

It is 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 ")&"

& Qu ot;

ORS. movenext ()

Wend

Method 2: Avoid using updatable recordset whenever possible

ORS = oconn. Execute ("select * from authors where authorid = 17", 3)

Ors ("name") = "Darkman"

ORS. Update ()

It is slower than the following code:

Strsql = "Update authors set name = 'dark' where authorid = 17"

Oconn. Execute strsql

Method 3: Use batch processing to update the database.

It makes up a large batch of SQL statements and runs them at a time, which is much more efficient than updating data one by one. This will also meet your transaction processing needs:

Strsql = ""

Strsql = strsql & "set xact_abort on \ n ";

Strsql = strsql & "begin transaction \ n ";

Strsql = strsql & "insert into orders (ordid, custid, orddat) values ('20170101', '20160901', getdate () \ n ";

Strsql = strsql & "insert into orderrows (ordid, ordrow, item, qty) values ('20170101', '01', 'g4385', 5) \ n ";

Strsql = strsql & "insert into orderrows (ordid, ordrow, item, qty) values ('20170101', '02', 'g4726', 1) \ n ";

Strsql = strsql & "Commit transaction \ n ";

Strsql = strsql & "set xact_abort off \ n ";

Oconn. Execute (strsql );

The Set xact_abort off statement tells SQL Server to cancel completed transactions if an error occurs during the following transaction processing.

Method 4: database index

For fields that will appear in the WHERE clause, you should first consider creating an index; for those fields that need to be sorted, you should also consider the columns.

How to create an index in MS Access: select the table to be indexed in access, click "design", and set the index of the corresponding field.

How to create an index in ms SQL Server: in SQL Server Manager, select a table, right-click design table, and select Properties ", select "indexes/keys"

Method 5. Avoid making the text field too large

When the value of a string is not fixed, varchar is better than char. I once saw an example program. The field is defined as text (255), but its value is usually only 20 characters. This data table has 50 K Records, which makes the database very large and the database will be slow.