SQL Slow-Speed solution

Source: Internet
Author: User

Here is a summary of my years of experience:

Network programming always has to deal with the database. Dealing with a database is always about getting in touch with SQL. How to make your SQL run a little faster, this article introduces several effective methods:

Method One, try to use complex SQL instead of a simple stack of SQL. For the same transaction, a complex SQL completion is more efficient than a bunch of simple SQL finishes. When you have multiple queries, 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 JOIN Authors on authors.authorid= Books.authorid '); for (;!ors.eof; Ors.movenext ()) {Response.Write oRs (' Title '). value+ ' +ors (' Name ') + '
‘); }

Method II, 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 ();

Slower than the following code: Objdbc.execute (' UPDATE Authors SET name= ' Karl Karlsson ' WHERE authorid=17 ');

Method III, when updating the database, try to use batch update to make all of the SQL into a large batch of SQL, and run; This is much more efficient than updating data one by one. This also satisfies your need for transaction 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 transaction that has been completed if an error is encountered during the following transaction processing.

Method four, database index

Method v. Avoid making the text field too large when the value of the string is not fixed, using varchar is better than using char. I've seen an example of a 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 very large, large database is necessarily slower.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.