How to solve the slow SQL _php tutorial

Here is a summary of my years of experience: Network programming is always about dealing with databases. Dealing with a database always touches SQL. How to make your SQL run faster, this article describes several effective methods: Method One, try to use complex SQL instead of a simple heap of SQL. In the same transaction, the efficiency of a complex SQL completion is higher than the efficiency of a bunch of simple SQL finishes. When you have multiple queries, you should be good at using joins.
Ors=objdbc.execute (' select * from Books ') for (;!ors.eof; Ors.movenext ()) {Ors2=objdbc.execute (' select * from Authors WHE RE 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 joins Authors on authors.authorid= Books.authorid '); for (;!ors.eof; Ors.movenext ()) {Response.Write (ORs (' Title '). Value ' ORs (' Name ') '
'); } method Two, try to avoid the use of 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 Three, when updating the database, try to use batch update to make all the SQL a large batch of SQL, and run one at a time, this is more efficient than updating data one by one. This also satisfies your need for transactional processing: (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 ', ' ', ' G4385 ', 5) '; strSQL = ' INSERT into orderrows (ordid,ordrow,item,qty) VALUES (' 9999 ', ' ', ' G4726 ', 1) '; strSQL = ' COMMIT TRANSACTION '; strSQL = ' SET xact_abort OFF '; Objdbc.execute (strSQL); Where the SET xact_abort OFF statement tells SQL Server to cancel a completed transaction if an error is encountered during the following transaction processing.
Method Four, database index method Five, avoid making the text field too large when the value of a string is not fixed, it is better to use varchar than char. I have seen an example 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 large, the large database is necessarily slower.
　

http://www.bkjia.com/PHPjc/631182.html www.bkjia.com true http://www.bkjia.com/PHPjc/631182.html techarticle here is a summary of my years of experience: Network programming is always about dealing with databases. Dealing with a database always touches SQL. How to make your SQL run faster, this article describes a few lines of ...

