How to write better SQL queries: Ultimate Guide-Part 3: SQL Part 3

How to write better SQL queries: Ultimate Guide-Part 3: SQL Part 3

This is the last article in the series "how to write better SQL queries.

 

Time Complexity and Big O symbol

Through the first two articles, we have some knowledge about the query plan. Next, we can use the computing complexity theory to further explore and think about performance improvement. The field of Theoretical Computer Science focuses on the classification of computing problems based on difficulty. These computing problems can be algorithm problems or query problems.

For queries, We can classify them according to the time required to run the query and obtain the results instead of the difficulty. This method is also called time complexity classification.

The large O symbol can be used to indicate the running time based on the input growth speed, because the input can be any large. The large O symbol does not include coefficients or low-level items, so that you can focus on the important part of the query Run Time: growth rate. When this method is used, the coefficient and low level items are discarded, and the time complexity is gradually described, which means that the input will change to infinity.

In the database language, the complexity measures the query running time.

Note that the size of the database not only increases with the increase of the data stored in the table, but also affects the size of the database.

 

Estimate the time complexity of a query plan

The Execution Plan defines the algorithm used by each operation, which also enables the execution time of each query to be logically expressed as the function of the data table size in the query plan. In other words, you can use the large O symbol and execution plan to estimate the query complexity and performance.

In the following summary, we will understand four types of time complexity concepts.

Through these examples, we can see that the query time complexity varies according to the running query content.

For different databases, different indexing methods, different execution plans, and different implementation methods must be considered.

Therefore, the concepts of time complexity listed below are very common.

O (1): constant time

There is a query algorithm that requires the same time for execution regardless of the input size. This method is a constant time query. These types of queries are not common. The following is an example:

SELECT TOP 1 t.*FROM t

The time complexity of this algorithm is a constant, because it only selects any row from the table. Therefore, the length of time is irrelevant to the table size.

Linear Time: O (n)

If the execution time of an algorithm is proportional to the input size, the execution time of the algorithm increases with the increase of the input size. For databases, this means that the query execution time is proportional to the table size: as the number of data rows in the table increases, the query time also increases accordingly.

One example is to use the WHERE clause in a non-index column for query: This requires full table scan or sequential scan, which will cause the time complexity of O (n. This means that you need to read each row in the table to find the data with the correct ID. Even if the correct data is found in the first row, the query still reads each row of data.

If no index exists, the query complexity is O (n) I _id:

SELECT i_idFROM item;

• This also means that a COUNT query like COUNT (*) from table has O (n) time complexity. A full TABLE scan is performed unless the total number of rows in the data TABLE is stored. At this time, the complexity will be more like O (1 ).

The time sum of all linear execution plans is closely related to the linear execution time. The following are some examples:

• The complexity of hash join is O (M + N ). The classic hash join algorithm for connecting two internal data tables is to first prepare a hash table for a small data table. The entry to the hash table consists of connection attributes and rows. The hash function is applied to the join attribute to implement access to the hash table. Once a hash table is created, a large table is scanned and related rows in a small table are queried by viewing the hash table.
• The complexity of merge join is O (M + N), but such join is heavily dependent on the index of the connected column, and without an index, sort the keys used in the connection first:
  • If two tables are sorted Based on the keys used in the connection, the query complexity is O (M + N ).
  • If both tables have indexes on the connected columns, the indexes maintain these columns in order and do not need to be sorted. The complexity is O (M + N ).
  • If neither table has an index connected to the column, sort the two tables first, so the complexity will be O (M log M + N log N ).
  • If an index exists in the join column of a table but not in the other table, sort the tables without indexes first. Therefore, the complexity is O (M + N log N ).
• For nested connections, the complexity is usually O (MN ). This join method is particularly effective when one or two tables are very small (for example, less than 10 records.

Remember: Nested join is a join method that compares each record in one table with each record in another table.

Logarithm time: O (log (n ))

If the algorithm execution time is proportional to the input size logarithm, the algorithm is called the logarithm time algorithm. For queries, this means that the execution time is proportional to the database size logarithm.

The scheduled time complexity of performing index Scan or clustered index Scan is the logarithm time. Clustered index is the index of the actual data rows in the table at the leaf level of the index. Clustering is very similar to other indexes: It is defined on one or more columns. This also forms an index primary key. The clustered primary key is the primary key column of the clustered index. Clustered index scan is the basic operation for reading from the beginning to the end of an RDBMS row in a clustered index.

In the following example, an I _id index exists, which also causes O (log (n) Complexity:

SELECT i_stockFROM itemWHERE i_id = N;

If no index is available, the time complexity is O (n ).

Secondary time: O (n ^ 2)

If the algorithm execution time is proportional to the square of the input size, the algorithm is called the logarithm time algorithm. For databases, this means that the query execution time is proportional to the square of the database size.

An example of a query with a quadratic complexity is as follows:

SELECT *FROM item, authorWHERE item.i_a_id=author.a_id

The minimum complexity is O (n log (n), but the maximum complexity is O (n ^ 2) based on the index information of the connection attribute ).

Is a chart that estimates query performance based on time complexity. You can view the performance of each algorithm through charts.

 

SQL Optimization

The query plan and time complexity can be measured from the following aspects, and SQL queries can be further optimized:

• Use index scanning to replace the full table scanning of unnecessary big data tables;
• Make sure that the table connection sequence is the best order;
• Make sure that the index is used in the best way;
• Cache the full table scan of small data tables.

All the content of the "how to write better SQL queries" tutorial is introduced here. I hope that this tutorial will help you compile better and better SQL queries.

Link: https://www.datacamp.com/community/tutorials/sql-tutorial-query#importance

For more information, see grape city control.

 

Related reading:

[Report benefits] More than 100 Report Templates can be downloaded for free.

How to write better SQL queries: Ultimate Guide-Part 1

How to write better SQL queries: Ultimate Guide-Part 2

A SQL statement for dynamic hierarchical Query