sql query optimization techniques

Yesterday's SQL Server query performance optimization-index creation principle (I) mainly introduced the principle. today are some of the main principles and checks the created indexes. Iii. indexing principles In general, building indexes depends on the data usage scenarios. In other words, which SQL statements are co

bytes;2. Data item: Whether there is a large number of paragraphs, the values of those fields are often updated;3. Data query SQL Condition: The column names of which data items often appear in the Where, GROUP by, and ORDER by clauses are medium;4. Data Update class SQL Condition: How many columns often appear in the WHERE clause of update or delete;5.

result set type returned by the subquery is a simple value. b) Single-row sub-query. The result set type returned by the subquery is 0 or one unit group. Similar to the scalar subquery, but may return 0 tuples. c) Multiline single-row subquery. The result set type returned by a subquery is a multi-tuple but has only one simple column. d) Table sub-

Index optimization, query optimization, query caching, Server Setup optimization, operating system and hardware optimization, application-level optimization (Web server, caching), and m

31514.27 optimizing multiple subqueries with LEFT JOIN (i) 31714.28 optimizing multiple subqueries with LEFT JOIN (ii) 32014.29 optimizing multiple subqueries with LEFT JOIN (iii) 32214.30 removing the filter 324 caused by the exists14.31 Overlap Time Count 32514.32 overriding optimizations with analytic functions 32814.33 parts Suppliers of equal set 33414.34 Hangar of equal sets with pilot 33514.35 overriding the maximum filter condition with an analytic function 33814.36 finding data at a sp

the size of the data in the table, if there is a lot of data in the table, count is the int type is problematic, so you need to return the default type (the value is usually the default type is big) to the int type. ) Summary: With SET SHOWPLAN_ALL on we can see what the SQL Server aggregation function does for this effect when it gives us the final effect. 2.3. Scalar aggregation optimization

there are usually several ways to tune a database: 1) Manual tuning. The main reliance on people, inefficient, requiring the operator to fully understand the principle of common sense depends on, but also the application, database management system, operating system and hardware have a broad and deep understanding. 2) Case-based tuning . This paper summarizes the recommended configuration values of database parameters and the design of

in the table, count is the int type is problematic, so you need to return the default type (the value is usually the default type is big) to the int type. ) Summary: With SET SHOWPLAN_ALL on we can see what the SQL Server aggregation function does for this effect when it gives us the final effect. 2.3. Scalar aggregation optimization techniques: Let'

MySQL's subquery optimization has been not very friendly, has been criticized by the industry more than I have encountered in the SQL optimization of one of the most problems, you can click here, here to get some information, MySQL processing subqueries, the subquery will be rewritten, usually, we hope that from the inside out, That is, the result of the subquery

and xh_bz, while in the second SQL 0.5% records are DY_DJ and xh_bz comparisons, so that the second SQL CPU utilization is significantly lower than the first one.3. Influence of query Table orderThe order of the list in the table following the from will have a performance impact on SQL, and with no indexes and no stat

table and prefix the alias to each column. This reduces the time to parse and reduces the syntax errors caused by column ambiguity.(15) Replace in with exists with not exists instead of in:In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) will usually improve the efficiency of the query. In a subquery, the NOT IN clause performs an internal sort

, the use of >= instead of, for example:SELECT * FROM table where hh>10;//inefficient select * from table where hh>=10.0000001//relatively efficientII. Optimization of SQL statementsDescribes the optimization techniques for several SQL statements:(1) The Order of links in th

Tags: SQL optimizationThe most common way to improve the efficiency of SQL statements is to build indexes and avoid full table scans as much as possible. Let's tidy up some common SQL optimization techniques to avoid full table scanning. A simple

First of all, let's look at one of the steps here that translates into banner, which requires pivot, step-by-step Then look at the overall query results for the two query methods after the main table join So take a look at the latter query method is also through the industry after the conversion to do the join execution plan, you can see only the Word table

example: SELECT/*+full (BSEMPMS) Cahe (BSEMPMS) * * Emp_nam from Bsempms; /*+nocache (TABLE) * * When a full table scan is performed, the cache hint can place the retrieved block of the table in the buffer cache with the most recent LRU of the least list For example: SELECT/*+full (BSEMPMS) Nocahe (BSEMPMS) * * Emp_nam from Bsempms; /*+append*/. You can increase the speed by inserting it directly into the end of the table. Insert/*+append*/into test1 select