(Excerpt) _ Database Design entry classic: Building a quickly executed database model _ 8.2 writing efficient queries

8.2 write efficient queries
Efficient SQL code mainly refers to the efficient query using the SELECT command. The SELECT command allows the WHERE clause to reduce the amount of data read. The where clause is used to return (or not return) specific records. The update command and the DELETE command can also have a where clause. Therefore, using the WHERE clause can reduce the amount of data access and improve the performance of the two commands.
Tip:
It is usually the work of developers and administrators to adjust the performance of insert commands that add records to databases. This is because end users usually use applications to add data to databases. Metadata modification commands, such as create
The table command and the alter table command are mostly used for database management. Therefore, the INSERT command and Metadata Command are irrelevant to this document.
In OLTP databases, small transactions and high concurrency are the two most important aspects. The accuracy of SQL code and index matching are also critical. Large queries and batch updates are widely used in data warehouses. Therefore, in a data warehouse, as few tables as possible for large and complex queries, so as to reduce the number of tables in the join query. For OLTP databases and data warehouse databases, too many tables are connected simultaneously during queries, which has the greatest impact on query performance. The data warehouse is also deteriorating due to the huge amount of data.
Some common experience in adjusting SQL code performance:
● Database model design supports SQL code --
The quality of SQL code depends on the quality of the database model design. It is not only related to the correctness of standardization and non-standardization, but also to the appropriateness of the table structure. For example, the OLTP database's common over-standardization, granularity, and deep-paradigm tables are not suitable for the Data Warehouse's ultra-large transactions that span multiple tables, therefore, the data warehouse requires the database model design of the data warehouse.
● Kiss Law (keep it simple and stupid, keep simple and plain )--
Application code split into simple (preferably independent) blocks is always easier to understand. Therefore, simple SQL commands of simple clauses are easier to compile and adjust. The longer and more complex the query, the more difficult it is to write, and the more difficult it is to get the correct results. Performance adjustment is only an additional step. If some clumsy queries are slow and need to be adjusted, this is actually incorrect. Only by keeping the query concise can you run the query more quickly. If the problem is too complex, you should first simplify it. At least, keeping it concise will help you know the query progress accurately, and will not cause you to stare at a row of meaningless SQL code.
● Excellent table structure facilitates the construction of SQL code --
Pay attention to all the details related to the SQL code construction and writing methods or forms, except for the database model. In an ideal table structure, you should be able to directly write SQL code from the table structure or the table structure subset, without bypassing. Therefore, writing SQL code should not be difficult. You shouldn't often feel (suspicious or forewarned) that the table structure is inappropriate. Therefore, the structure of the database model should facilitate the construction of SQL code. After all, applications are supported by SQL code. Do not forget the dependency of the SQL code on the database table structure. If the application requirements do not match the database structure, the database model may be incorrect. In this case, tuning the performance of SQL code may cause serious problems.
● Break down to the smallest block --
Break down the structure of SQL code commands, such as query and DML commands (insert, update, and delete commands ). But do not break down non-query and non-DML commands. For example, do not allow every SQL code segment accessing the database to continuously connect to the database and disconnect the database during execution. Each user can be connected for a period of time, or the connection can be maintained from the beginning to the end of the session. On the other hand, try to use subqueries as much as possible to simplify code writing. You can still merge the subquery into the parent query later.
Note:
Most importantly, the SQL code and the ideal execution speed depend entirely on the underlying structure of the database model. Queries are basically based on database tables and the relationship between tables.
When constructing the most basic elements of SQL code, you can use a special set of methods to ensure excellent processing performance. The most basic query performance adjustment rules have many important aspects. The impact of the underlying database model structure on various performance factors is listed below:
● SELECT command --
The number of tables used in the SELECT command. This factor has a significant impact on query performance. The higher the granularity of the database model, the more tables to be retrieved at the same time. The Field Retrieval Method also affects the performance, but the number of connected tables is more important, especially for large databases.
● WHERE clause --
The method related to record filtering. When using comparison conditions, you must use the WHERE clause for the record. For example, you can retrieve the record with the vowel "A" in the author's name. The comparison condition is the main factor that determines where clause construction. There are many different comparison conditions. The method of filtering records in the query can affect the query execution method, and thus has a significant impact on the performance. Indexes have a significant impact on filtering where clauses.
● Group by clause -- Group
By clause aggregates records retrieved from the database into a summary group of records. Direct ing between one-to-multiple tables can effectively implement grouping. Materialized views are commonly used in data warehouses for group
By clause aggregation for pre-calculation and pre-storage.
● Connection --
The connection query retrieves records from multiple tables and connects these tables based on the field value relationship between tables. Generally, the relationship between tables is based on the integrity of the primary key and foreign key between two tables. The most important factor for improving the query execution speed may be the table connection and the number of tables in the connection (see previous discussions ). When considering the database model design, the higher the granularity of the database model (the more tables, the more small pieces will be decomposed), the more tables will be connected to the query. This is especially important for data warehouses, because data warehouses often contain a large amount of data. However, even in OLTP databases, due to a large number of small transactions, the impact of large connections with more than 10 tables on performance is equivalent to the impact of two large tables in data warehouse connection query.
Tip:
Connection is very important to performance. If there are too many small tables in the database model (the granularity is too high or too standardized), the database model design will have a profound impact on the performance of connection queries.