When we develop SQL, we will find that sometimes asp execution is slow. We can use the following methods to improve execution efficiency.
[b]Method 1: Try to use complex SQL instead of a bunch of simple SQL[/b]
For the same transaction, a complex SQL can be completed more efficiently than a bunch of simple SQL. 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)&<br>
ors.MoveNext()
wend
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)&<br>
ors.MoveNext()
wend
[b]Method 2: Try to avoid using updatable Recordset[/b]
oRs=oConn.Execute(Select * FROM Authors Where AuthorID=17,3,3)
ors(Name)=DarkMan
ors.Update()
is slower than the following code:
strSQL = Update Authors SET Name='DarkMan' Where AuthorID=17
oConn.Execute strSQL
[b]Method 3: When updating the database, try to use batch updates[/b]
Form all the SQL into one large batch SQL and run it at once; this is much more efficient than updating the data one by one. This will also better 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('9999','1234',GETDATE())/n;
strSQL=strSQL&Insert INTO orderRows(OrdID,OrdRow,Item,Qty) VALUES('9999','01','G4385',5)/n;
strSQL=strSQL&Insert INTO orderRows(OrdID,OrdRow,Item,Qty) VALUES('9999','02','G4726',1)/n;
strSQL=strSQL&COMMIT TRANSACTION/n;
strSQL=strSQL&SET XACT_ABORT OFF/n;
oConn.Execute(strSQL);
Among them, the SET XACT_ABORT OFF statement tells SQL Server that if an error is encountered during the following transaction processing, the completed transaction will be canceled.
[b]Method 4, database index[/b]
For those fields that will appear in the Where clause, you should first consider indexing; those fields that need to be sorted should also be considered.
How to create an index in MS Access: Select the table that needs to be indexed in Access, click Design, and then set the index of the corresponding field.
How to create an index in MS SQL Server: In SQL Server Manager, select the corresponding table, then design the table, right-click, select Properties, and select indexes/keys
[b]Method 5: Avoid making the Text field too large[/b]
When the value size of the string is not fixed, using varchar is better than using char. I once saw an example program where the field was defined as TEXT(255), but its value was often only 20 characters. This data table has 50k records, making this database very large, and large databases are bound to be slower.