Slow SQL _ MySQL

The following is a summary of my years of experience: Network programming always deals with databases. Always have access to SQL when dealing with databases. This article describes several effective methods to make your SQL run faster: Method 1. try to use complex SQL instead of a simple pile of SQL. for the same transaction, the efficiency of a complex SQL statement is higher than that of a bunch of simple SQL statements. The following is a summary of my years of experience: Network programming always involves dealing with databases. Always have access to SQL when dealing with databases. This article describes several effective methods to make your SQL run faster: Method 1. try to use complex SQL instead of a simple pile of SQL. 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 = objDBC. Execute ('select * FROM Books ') (;! 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'); (;! ORs. Eof; oRs. MoveNext () {Response. write (oRs ('title'). value' oRs ('name ')'
');} Method 2. avoid using updatable Recordset oRs = objDBC. execute ('select * FROM Authors WHERE AuthorID = 17', (some flags); oRs ('name') = 'Karl Karlsson '; oRs. update (); it is slower than the following code: objDBC. execute ('update Authors SET Name = 'Karl Karlsson 'WHERE AuthorID = 17'); Method 3. when updating a database, use batch update as much as possible to make all SQL statements into a large batch and run them at a time. This is much more efficient than updating data one by one. This also satisfies your TRANSACTION processing needs: (in JScript) strSQL = ''; strSQL = 'set XACT_ABORT ON \ n'; strSQL = 'In in TRANSACTION \ n '; strSQL = 'Insert INTO Orders (OrdID, CustID, OrdDat) VALUES ('000000', '000000', GETDATE () \ n'; strSQL = 'Insert INTO OrderRows (OrdID, ordRow, Item, Qty) VALUES ('20170901', '01', 'g4385', 5) \ n'; strSQL = 'Insert INTO OrderRows (OrdID, OrdRow, Item, qty) VALUES ('20170101', '02', 'g4726', 1) \ n'; strSQL = 'commit TRANSACTION \ n '; StrSQL = 'set XACT_ABORT OFF \ n'; objDBC. execute (strSQL); where the SET XACT_ABORT OFF statement tells SQL Server that if an error occurs during the following transaction processing, the completed transaction is canceled. Method 4: database index method 5. avoid making the Text field too large. when the string value 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.