Methods to solve slow SQL speed_PHP tutorial

The following is a summary of my many years of experience: Network programming always involves dealing with databases. When dealing with databases, you always come into contact with SQL. How to make your SQL run faster. This article introduces several effective methods: Method 1. Try to use complex SQL instead of a bunch of simple SQL. For the same transaction, a complex SQL can be completed more efficiently than a complex SQL. The efficiency of heap simple SQL completion. When there are multiple queries, be good at using JOIN.
oRs=objDBC.Execute('SELECT * FROM Books') for(; !oRs.Eof; oRs.MoveNext()) { oRs2=objDBC.Execute('SELECT * FROM Authors WHERE AuthorID='' oRs (' AuthorID').value '''); Response.write(oRs('Title').value ' ' oRs2('Name') '
'); } is slower than the following code: oRs=objDBC. Execute('SELECT Books.Title,Authors.Name FROM Books JOIN Authors ON Authors.AuthorID=Books.AuthorID'); for(; !oRs.Eof; oRs.MoveNext()) { Response.write(oRs('Title' ).value ' ' oRs('Name') '
'); } Method 2: Try to avoid using updatable Recordset oRs=objDBC.Execute('SELECT * FROM Authors WHERE AuthorID=17',(some flags) ); oRs('Name')='Karl Karlsson'; oRs.Update(); is slower than the following code: objDBC.Execute('UPDATE Authors SET Name='Karl Karlsson' WHERE AuthorID=17'); Method 3 . When updating the database, try to use batch update to form all SQL into one large batch SQL and run it at once; this is much more efficient than updating data one by one. This also better meets your transaction processing needs: (in JScript) strSQL=''; strSQL ='SET XACT_ABORT ON '; strSQL ='BEGIN TRANSACTION '; strSQL ='INSERT INTO Orders(OrdID,CustID,OrdDat) VALUES('9999', '1234',GETDATE()) '; strSQL ='INSERT INTO OrderRows(OrdID,OrdRow,Item,Qty) VALUES('9999 ','01','G4385',5) '; strSQL ='INSERT INTO OrderRows(OrdID,OrdRow,Item,Qty) VALUES('9999 ','02','G4726',1) '; strSQL ='COMMIT TRANSACTION '; strSQL ='SET XACT_ABORT OFF '; objDBC.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.
Method 4. Database index Method 5. Avoid making the Text field too large. 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.

http://www.bkjia.com/PHPjc/631182.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631182.htmlTechArticleThe following is a summary of my many years of experience: Network programming always involves dealing with databases. When dealing with databases, you always come into contact with SQL. How to make your SQL run faster, this article introduces several practical methods...