Database Optimization Practice: TSQL Chapter

As we discussed earlier on how to use indexes correctly, adjusting indexes is the fastest performance tuning method, but in general, adjusting indexes only improves query performance. In addition, we can adjust the data access code and TSQL, this article describes how the best way to refactor data access code and TSQL.

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

Maybe you don't like my advice, you or your team may already have a default unspoken rule, which is to use ORM (Object relational Mapping, i.e., objects relational mapping) to generate all SQL and put SQL in the application, but if you want to optimize data access performance, Or you need to debug application performance issues, I recommend porting your 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 SQL code in your application can help reduce the drawbacks of SQL replication in your application, because it is now only a single place to focus on SQL, laying a good foundation for future code reuse.

2, the use of database objects to achieve all the TSQL to help analyze TSQL performance problems, but also help you centrally manage TSQL code.

3. After porting TS QL to the database, the TSQL code can be reconstructed better to take advantage of the advanced indexing characteristics of the database. In addition, it is simpler to have SQL code in the application.

Although this step may not be as immediate as the first three steps, the main purpose of this step is to lay the groundwork for the following optimization steps. If you use ORM (such as NHibernate) in your application to implement data access routines, you may find that they work well in a test or development environment, but you may experience problems with the production database, and you may need to rethink the data access logic based on ORM, Using TSQL objects to implement data access routines is a good way to do so, and there are more opportunities to optimize performance from a database perspective.

I assure you, if you spend 1-2 months to complete the migration, there will certainly be more than 1-2 years of cost savings.

Ok! assume you have done as I do, completely will be the TSQL migration to the database up, the following into the business!

Step Fifth: Identify inefficient TSQL, use best practices to reconstruct and apply TSQL

Because each programmer's ability and habits are different, they write the TSQL may be different styles, part of the code may not be the best implementation, for the level of the average programmer may be the first to write the TSQL implementation requirements, as well as the performance problems later, so in development and testing may not find problems.

Some people know best practices, but when they write code, they don't use best practices for a variety of reasons, and wait until the day the user is mad to think about best practices again.

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

1, do not use the "select *" in the query

(1) The retrieval of unnecessary columns would result in additional overhead, with the phrase "province of the province";

(2) The database cannot take advantage of the "overwrite index", so the query is slow.

2. Avoid unnecessary columns in the select list, avoid unnecessary tables in the join condition

(1) Unnecessary columns in a SELECT query can incur additional overhead, especially for LOB-type columns;

(2) The inclusion of unnecessary tables in the join condition forces the database engine to retrieve and match unwanted data, increasing the execution time of the query.

3, do not use count () in the subquery to perform the existence of check

(1) Do not use

Select column_list from table where 0 < (select COUNT (*) from table2 where ...)

Use

Select column_list from table where EXISTS (SELECT * from Table2 where ...)

instead

(2) When you use COUNT (), SQL Server does not know what you want to do is a presence check, it calculates all the matching values, either performs a full table scan or scans the smallest nonclustered index;

(3) When you use exists, SQL Server knows that you want to perform a presence check, and when it finds the first matching value, it returns TRUE and stops the query. Similar applications use in or any instead of count ().

4, avoid using two different types of columns for table connection

(1) When connecting two different types of columns, one of the columns must be converted to another column type, the lower level will be converted to high-level types, the conversion operation will consume a certain amount of system resources;

(2) If you use two different types of columns to join the table, one of the columns can use the index, but after conversion, the optimizer will not use its index. For example:

SELECT column_list from Small_table, large_table where

Smalltable.float_column = Large_table.int_column

In this example, SQL Server converts the int column to a float type, because the int is lower than the float type, and the index on the large_table.int_column is not used, but smalltable.float_ The index on column can be used normally.