5 ways to improve SQL query performance

"SQL performance optimization is a black magic
Just like alchemy:
Various formulations are difficult to understand and obscure,
Only a small number of people in the circle can understand. ”

This is a misconception that SQL database uses a known algorithm to implement performance that can be expected. The problem, however, is that it is easy to write SQL query statements that do not perform the most efficient algorithms, and thus are prone to unexpected performance results.

Here are 5 little questions about SQL performance optimization that might make you believe that SQL optimization is a black magic. But the explanations provided in the answers will then let you know that these so-called dark spells are purely scientific.

The SQL used in this test is based on an Oracle database.

From a performance perspective, are these SQL formulations good or bad? 1.

The query is 2012 of all rows in the year:

1. 1. no problem, no major improvement is possible.
   2. ? If there is a problem, you can make a significant optimization.
   ? That 's correct! When a table field is placed in a function to execute a query, the index will not work, and a more efficient notation

2.

Query for a row of data from the most recent date:

1. 1. ? no problem, no major improvement is possible.
   2. If there is a problem, you can make a significant optimization.
   ? That 's correct! This SQL statement will be executed according to the indexed Top-n query, and its efficiency  INDEX UNIQUE SCAN is equivalent, so it will be very fast.

3.

Two query statements, queried by a common column:

1. 1. no problem, no major improvement is possible.
   2. ? If there is a problem, you can make a significant optimization.
   ? That 's correct! the established index can only be used for the first query, and the second SQL cannot use the index to improve efficiency.

4.

Query a string:

1. 1. no problem, query efficiency will be very fast
   2. ? There are problems that can cause serious performance problems
   ? That 's correct! if the query character of like corresponds to the beginning of a wildcard, the index will not function. Nor is there an easy way to optimize this SQL

5.

If thousands of lines are queried from millions of rows of data, we use:

And when we only need to query 10 lines from millions of rows of data, SQL can change to this:

1. 2. similar performance in two cases (+/-10%)
   4. to see what the data
   6. ? the modified SQL will be slow (lower at least 10%)
   8. the modified SQL will be very efficient (increase at least 10%)
   ? That 's correct! In the previous query, the index covered all the query fields, the execution was very high, and the modified SQL, although the returned data was less, but the new B field was not indexed.

5 ways to improve SQL query performance