sql query optimization tips

your entire site from stopping accordingly. Because these two operations will lock the table, the table is locked, the other operations are not in. Apache will have a lot of child processes or threads. So, it works quite efficiently, and our servers don't want to have too many child processes, threads and database links, which is a huge amount of server resources, especially memory. If you lock your watch for a period of time, say 30 seconds, then for a site with a high volume of traffic, the 3

procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables. 25. Avoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data. 26. Before using a cursor-based method or temporal table method, you should first look for a set-based solution to solve the problem, and the set-based approach is generally more efficient. 27. As with temporary tab

In this article, we'll cover the concepts available in two SQL Servers, which are noteworthy techniques when using SQL Server. 1. OPTIMIZE for Unknown The OPTIMIZE for hint (hint) is added to the SQL Server 2005 version to allow the DBA to determine the literal value used for cardinality evaluation and optimization. If

Tags: blog using io data problem div time SQLWhen you use a like fuzzy query in an SQL statement, you should try to avoid the percent of percent, because the fuzzy query is relatively slow, and when this happens, you should consider optimization.For example, I query a table for records created in 2012SELECT * from ' co

SQL data query optimization select nbsp;. id, rkno,. packageno nbsp; as nbsp; packageno, cust_no, lotno, count (*) nbsp; as nbsp; count nbsp; from nbsp; 'rk _ record ' SQL data query optimization Select. id, rkno,. package

cursors. If development time permits, a cursor-based approach and a set-based approach can all be tried to see which method works better. 28. Set NOCOUNT on at the beginning of all stored procedures and triggers, set NOCOUNT OFF at the end. You do not need to send a DONE_IN_PROC message to the client after each statement that executes the stored procedure and trigger. 29. Try to avoid large transaction operation and improve the system concurrency ability. 30. Try to avoid the return of large da

differ only from the primary column-Create hash clusters-creating split tables and Views-using parallel optionsTenusing the union all instead of the union all operation does not include the sort unique operation, the first row retrieves a fast response, and in most cases no temporary segment is completed, and the Union all build view can be integrated into the query syntax in the query to improve efficienc

Database has always been its own weak item, and now with this article slowly accumulate some of the techniques of Oracle SQL optimization.1. First of all, it is easy to think of all the optimization techniques-index, index What is the use? Indexes do increase query speed when the table data is large, and the index

operation is the same as in 1, and the merge into method is recommended if you make a decision. Because merge into accesses only one emp_new1. Iii. Deleting delete operations 1. Delete duplicate records: There are many kinds of methods, here only one, using the analysis function grouping, determine whether the group number is greater than 1. Delete from emp where rowid in (select Rid from (select rowID as RIDs, row_num

Querying case-insensitive data in MySQL often uses subqueries and uses the upper function in a subquery to convert the condition to uppercase. Such as: The code is as follows:SELECT * FROM Staticcatalogue WHERE UPPER (source) in (select UPPER (source) to Staticcatalogue GROUP by UPPER (source) hav ing count (UPPER (source)) >1) Order by UPPER (source) DESC; The execution efficiency of this statement is very low, especially if the source field is not indexed. In particular, the most taboo to u

Querying case-insensitive data in MySQL often uses subqueries and uses the upper function in a subquery to convert the condition to uppercase. Such as: Copy Code code as follows: SELECT * FROM Staticcatalogue WHERE UPPER (source) in (select UPPER (source) to Staticcatalogue GROUP by UPPER (source) hav ing count (UPPER (source)) >1) Order by UPPER (source) DESC; The execution efficiency of this statement is very low, especially if the source field is not indexed. In part

time to group product data by merchant ID and remove the latest data Then update the SQL query statement to: SelectT_usercollectmerchant.collectid,t_usercollectmerchant.merchantid,t_usercollectmerchant.userid, LastUpdate asLastnewtime fromt_usercollectmerchant Left JoinV_goodsinfo_merchantcount onV_goodsinfo_merchantcount.merchantid=T_usercollectmerchant.merchantidwhereUserid= + Order byCollectiddes

to this:CREATE TABLE #t (...)13, a lot of times with exists instead of in is a good choice:Select num from a where num in (select num from B)Replace with the following statement:Select num from a where exists (select 1 from b where num=a.num)14, not all indexes are valid for the query, SQL is based on the data in the table to query

SQL data Query optimization Select A.id,rkno,a.packageno as Packageno,cust_no,lotno,count (*) as Count from ' Rk_record ' as a right join ' sys_barcode_1d _y ' as B on a.packageno = B.packageno where rkno = ' 201401030031 ' GROUP by Packageno,lotno ORDER by Packageno,lotno ASC The Rkno,packageno index is included. Currently the

One, the database design angle1. Avoid full table queries as much as possible, first consider indexing on the column of the Where level order by.2. Try to avoid null judgments in the WHERE clause, causing the engine to discard the index for a full table scan. Columns such as: Select ID from t where num is NULL, can be set to 0 in num, ensure no null appears3. Not all indexes are valid for queries, and if the fields contain large amounts of duplicate data, SQ