SQL database statement optimization analysis and Optimization techniques Summary (SQL Optimization tool)

Usually SQL database needs optimization analysis, and there are some techniques, SQL optimization of several methods here do not do a detailed introduction, this article will be summarized in SQL statement optimization, followed by an optimization tool SQL Tuning Expert for Oracle and the use of methods, First, we need to follow several principles of database optimization:

1. Try to avoid the calculation on the column, which will cause the index to fail;

2. Using join is a small result set that should be used to drive a large result set while splitting a complex join query into multiple query queries. Otherwise, the more tables you join, the more locks and blockages will result.

3. Note the use of the like fuzzy query, avoiding the use of percent, such as SELECT * from a where name as '%de% ';

Alternative statement: SELECT * from a where name >= ' de ' and name < ' DF ';

4. List only the fields that need to be queried, and do not use SELECT * from ... to save memory;

5. Use BULK INSERT statements to save interaction;

INSERT into a (ID, name) VALUES (2, ' a '), (3, ' s ');

6.limit base is larger, use between ... and ...

7. Do not use the RAND function to fetch records randomly;

8. Avoid using null, which needs to be set to NOT NULL when building the table, improve query performance;

9, do not use COUNT (ID), and should be count (*)

10. Do not do unnecessary sorting, as far as possible in the index to complete the sorting;

Let's start by looking at a sql:

 Select ii.product_id, P.product_name, COUNT (distinct Pim.palle T_ID) count_pallet_id, if (Round (sum (itg.quantity), 2) >-1 && round (sum (itg.quantity), 2) &lt ; 0.005, 0, round (sum (itg.quantity), 2)) quantity, round (Ifnull (SUM (itag.locked_quantity), 0.00000), 2) Loc Ked_quantity, Pc.container_unit_code_name, if (Round (sum (ITG.QOH), 2) >-1 &&am P Round (sum (ITG.QOH), 2) < 0.005, 0, round (sum (ITG.QOH), 2)) Qoh, round (ifnull (SUM (ITAG.LOCKED_QOH), 0.0                         0000), 2) Locked_qoh, P.unit_code, p.unit_code_name from (select                         it.inventory_item_id item_id, sum (it.quantity) quantity,                    SUM (it.real_quantity) Qoh from Ws_inventory_transaction it            where             it.enabled = 1 GROUP BY it.inventory_item_id                                     ) ITG left join (select ita.inventory_item_id item_id, SUM (ita.quantity) locked_quantity, sum (ita.real_quantity) Lo                               Cked_qoh from Ws_inventory_transaction_action ITA                                where 1=1 and Ita.type in (' Locked ', ' release ') Group by ita.inventory_item_id) itag                     On itg.item_id = itag.item_id inner joins Ws_inventory_item II on itg.item_id = ii.inventory_item_id                      Inner join ws_pallet_item_mapping PIM on ii.inventory_item_id = pim.inventory_item_id INNER JOIN Ws_productP on ii.product_id = p.product_id and P.status = "OK" left join Ws_product_container pc on Ii.container _id = pc.container_id//always up to say that there are too many tables, you can design tables with more redundant fields, and reduce the association query between tables; where Ii.inventory_type = ' r                     Aw_material ' and ii.inventory_status = ' in_stock ' and ii.facility_id = ' + ' and                      DateDiff (now (), Ii.last_updated_time) < 3//violates the first principle and P.product_type = ' goods '                And p.product_name like '% fruit '//violation of principle 3 GROUP by ii.product_id Have Qoh < 0.005 ORDER BY Qoh Desc

The above SQL we use subqueries in from, which is very detrimental to the query;

A better approach is the following statement:

Select t.facility_id, F.facility_name, T.inventory_status, W                 Is.inventory_status_name, T.inventory_type, T.product_type, t.product_id,                P.product_name, t.container_id, t.unit_quantity, P.unit_code, P.unit_code_name, Pc.container_unit_code_name, T.secret_key, S Um (t.quantity) quantity, sum (t.real_quantity) real_quantity, sum (t.locked_quantity) locked_q                         Uantity, sum (t.locked_real_quantity) locked_real_quantity from (select                        ii.facility_id, Ii.inventory_status, Ii.inventory_type,                         Ii.product_type, ii.product_id, ii.container_id,      Ii.unit_quantity,                   Ita.secret_key, ii.quantity quantity, Ii.real_quantity re Al_quantity, sum (ita.quantity) locked_quantity, sum (ita.real_quantity) Locke D_real_quantity from Ws_inventory_item II inner join WS                         _inventory_transaction_action ita on ii.inventory_item_id = ita.inventory_item_id where                         ii.facility_id = ' {$facility _id} ' and Ii.inventory_status = ' {$inventory _status} ' and Ii.product_type = ' {$product _type} ' and Ii.inventory_type = ' {$inventory _type                      } ' and ii.locked_real_quantity > 0 and Ita.type in (' Locked ', ' release ') Group by ii.product_id, Ita.secret_key, ii.container_id, ita.inventory_item_i d having locked_real_quantity > 0) as T inner join ws_product p on T.pro duct_id = p.product_id left join ws_facility f in t.facility_id = f.facility_id LEFT Join W S_inventory_status wis on wis.inventory_status = T.inventory_status left join Ws_product_container pc on PC . container_id = t.container_id GROUP by t.product_id, T.secret_key, t.container_id

Note:

1, from the statement must not use sub-query;

2, use more where to limit, narrow the scope of search;

3, the rational use of the index;

4, through the Explain view SQL performance;

Using the tools SQL Tuning Expert for Oracle optimized SQL statement

It is easy for SQL developers and DBAs to write a correct SQL based on business requirements. But what about SQL execution performance? Can you optimize to run faster? If not senior
DBA, estimates that many people are not confident.

Fortunately, automated optimization tools can help us solve this problem. This is the Tosska SQL Tuning Expert for Oracle tool to be introduced today.

Download https://tosska.com/tosska-sql-tuning-expert-tse-oracle-free-download/

The inventor of this tool, Richard to, Dell's former chief engineer, has more than 20 years of experience in SQL optimization.

1. Create a database connection, or you can create it later. Fill in the connection information and click on the "Connect" button.

If you have already installed the Oracle client and have TNS configured on the Oracle client, you can select "TNS" as "Connection Mode" in this window and then select the configured TNS as the database alias in the ' Database alias '.

If you do not have an Oracle client installed or you do not want to install an Oracle client, you can choose "Basic Type" as "Connection Mode", just the database server IP, port and service name.

2. Enter SQL with performance issues

3, click the Tune button, automatically generate a large number of equivalent SQL and start execution. Although the tests have not yet been completed, we can already see a 100% improvement in SQL 20 performance.

Let's take a closer look at SQL 20, which uses two hints to stand out at the fastest execution speed. The original SQL takes 0.99 seconds, and the optimized SQL execution time is close to 0 seconds.

Since this SQL is executed tens of thousands of times a day in the database, it can save about 165 seconds of database execution time after optimization.

Finally, replace SQL with performance issues in your application's source code with equivalent SQL 20. The performance has been improved by recompiling the application.

Tuning task completed successfully!

Related articles:

SQL performance Optimization Summary and SQL statement optimization Chapter

SQL statement Optimization principles, SQL statement optimization

Related videos:

MySQL Optimization video Tutorial-Boolean education