Optimization tips for SQL statements with millions of records in ORACLE 1. To optimize queries, try to avoid full table scanning. First, you should consider creating an index on the columns involved in where and order. Www.2cto.com 2. try to avoid null value determination on the field in the where clause. Otherwise, the engine will discard the index and perform full table scanning, for example: select id fr
condition cannot be indexed , such as: YS_DF>CX_DF, cannot be optimizedQC_BH | | Kh_bh= ' 5400250000 ', optimized processing: qc_bh= ' 5400 ' and kh_bh= ' 250000 'Iv. OtherOracle's hints feature is a relatively strong feature and is a complex application, and hints are only a recommendation to Oracle, and sometimes Oracle may not follow the prompts for cost considerations. According to practical application, the developer is generally not recommended to apply the Oracle hint, because the perfor
, the index cannot be used when a null comparison is made to the index in the WHERE clause.(6) In the index column, the use of >= instead of, for example:Select * from Table where hh>ten; // Low Efficiency Select * from Table where hh>=10.0000001// relatively efficientII. Optimization of SQL statementsDescribes the optimization
SQL statement Optimization-query different rows of the two tables NOT IN, NOT EXISTS, Join query Left Join, existsleft
In actual development, we often need to compare the differences between two or more tables and compare the data that is the same and the data is different. At this time, we can use the following three
tables.Careful use of large temporary tables and other large table connection query and modification, reduce the burden on the system table, because this operation will be in one statement multiple times the system table of tempdb.d) Rational use of the algorithm:Based on the SQL optimization techniques mentioned abov
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 tables, cursors are not unusable. Using Fast_forward cursors on small datasets is often preferabl
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 tables, cursors are not unusable. Using Fast_forward cursors on small datasets is often preferable to other progressive processing methods, especially if you must reference several tables to obtain the required data. Routines that include "totals" in the result s
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 tabl
Label:1, what is a subquery, table association query:Subquery: Refers to using a SELECT query statement in the Select or WHERE clause in the main SQL statement; Select A.name, (select B.name from B where b.id=a.id) from a where a.name lik ' a %‘Table Association query: refers to multiple tables union query; Select A.na
Optimization objectives
1, reduce the number of IO
Io is always the easiest place to bottleneck the database, which is determined by the responsibility of the database, most of the database operations over 90% of the time is occupied by IO operations, reducing IO times is the first priority in SQL optimization, of course, is the most obvious
longitude and latitude two columns in the data table to optimize the speed at which the where statement executes.The final SQL statement is as follows
1
$sql=‘select * from users_location where
2
latitude >‘.$lat.‘-1and
3
latitude ‘.$lat.‘+1and
4
longitude >‘.$lon.‘-1and
Tags: Express with reference processing varchar stsmysq reconstruction attention Turn from HTTP://BLOG.163.COM/ZHANGJIE_0303/BLOG/STATIC/9908270620146951355834/1. To optimize the query, avoid full table scanning, first consider the Where and order by An index is built on the column involved. 2. You should try to avoid null values in the WHERE clause to judge the field, otherwise it will cause the engine to abandon using the index for full table sca
therefore be improved. It is important to note that the UNION all will output the same record in the two result set repeatedly. So you still have to analyze the feasibility of using union all from the business requirements. The UNION will sort the result set, which will use the memory of the sort_area_size. The optimization of this memory is also very important. The following SQL can be used to
associated with a table for query. We remove this association and directly return the "resignation process" as processDefineName.
With this association removed, the SQL efficiency has been improved, but the improvement is not obvious. From the logic point of view, we have no room for optimization. Therefore, we hope to optimize the database technology. Before pr
(graphics and text), many things will be clear.
Summary:There may be a variety of SQL statements that implement the same query function. If you determine which optimization method you want to perform, if you only test it in time, it will be affected by many external factors. We understand how to execute MSSQL, i/O logic reading, querying the graph
server| Speed | optimization
MS SQL Server Query optimization method (1) There are many reasons for the slow query speed, common as follows:
1, no index or no index (this is the most common problem of query slow, is the defect of
block according to the pointer on the index block, so that the IO traffic is much less.
Second, index optimization technology
Is there an index that must be retrieved quickly? The answer is yes. Sometimes it's better to use indexes than to index faster. For example, we want to retrieve all the records in the above table, if not indexed, need access to 8,000 x1000 bytes/8k = 1000 pages, if you use the index, first retrieves the index, accesses 8,000 x
rcvblls.balance>0ORDER by Cust.nameInto TEMP cust_with_balanceThen query in the temporary table in the following way:SELECT * from Cust_with_balanceWHERE postcode> "98000"The rows in the staging table are less than the rows in the primary table, and the physical order is the required order, reducing disk I/O, so the query effort can be significantly reduced.Note: Changes to the primary table are not reflec
, including average, minimum, maximum, Rows examined, and number of scanned Rows. It can also generate table reports and store analysis results. Here we will not introduce them one by one.
Through these slow log analysis software, you can find that the slow query statement has completed most of the SQL optimization. Run the explain or desc command in MySQL to che
Original: Set STATISTICS io and set STATISTICS time in SQL Server query performance optimizationIn recent times, has been exploring the SQL Server query performance issues, of course, also aimless search for a lot of information, but also from the online great God's articles learned a lot, here, to the great God salute
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.