Database optimization practice [TSQL]

　 Database Optimization Practice [TSQL]
　 Earlier we introduced how to use indexes correctly. Adjusting indexes is the fastest performance tuning method, but generally speaking, adjusting indexes will only improve query performance. In addition, we can also adjust the data access code and TSQL. This article introduces how to reconstruct the data access code and TSQL in the optimal way.

　Step 4: Migrate the TSQL code from the application to the database

Maybe you don’t like my suggestion. You or your team may already have a default unspoken rule, which is to use ORM (Object Relational Mapping, i.e. object relational mapping) generates all the SQL and puts the SQL in the application, but if you want to optimize data access performance, or need to debug application performance issues, I recommend that you port the SQL code to the database (using stored procedures, Views, functions and triggers) for the following reasons:

　1. Using stored procedures, views, functions and triggers to implement the functions of SQL code in the application helps reduce the disadvantages of SQL replication in the application, because now only one Centralized processing of SQL in one place lays a good foundation for future code reuse.

　2. Using database objects to implement all TSQL helps analyze TSQL performance issues and helps you centrally manage TSQL code.

　3. After transplanting TS SQL to the database, the TSQL code can be better restructured to take advantage of the advanced indexing features of the database. In addition, the application will be cleaner without SQL code.

　Although this step may not be as immediate as the first three steps, the main purpose of doing this step is to lay the foundation for the subsequent optimization steps. If you implement data access routines in your application using an ORM (such as NHibernate), you may find that they work fine in a test or development environment, but may encounter problems on the production database, in which case you It may be necessary to reflect on the data access logic based on ORM. It is a good way to use TSQL objects to implement data access routines. Doing so will give you more opportunities to optimize performance from the database perspective.

I guarantee you that if you spend 1-2 man-months to complete the migration, you will definitely save more than 1-2 man-years of costs in the future.

　OK! Assuming that you have done what I did and completely migrated TSQL to the database, let’s get to the point!

　

Step 5: Identify inefficient TSQL and adopt best practices for reconstruction and application TSQL

Since every programmer has different abilities and habits, the TSQL they write may have different styles, and some codes may not be optimally implemented. For average programmers, the first thing they may think of is writing TSQL implementation requirements. As for Performance issues will be discussed later, so problems may not be discovered during development and testing.

There are also some people who know the best practices, but do not adopt the best practices for various reasons when writing code. They wait until the user gets angry before they re-think about the best practices.

I think it is still necessary to introduce what are the best practices.

　1. Do not use "select *" in queries

　(1) Retrieving unnecessary columns will bring additional system overhead. There is a saying that "the province should be saved";

　(2) The database cannot Take advantage of "covering indexes" so queries are slow.

　2. Avoid unnecessary columns in the select list and avoid unnecessary tables in the connection conditions

The above is the content of database optimization practice [TSQL]. For more related articles, please pay attention to the PHP Chinese website (www.php .cn)!