SQL Server Data Access Program Optimization Guide

After intense investigation, you find that the problem lies in the database. When the application attempts to update data, the database runs slowly. After further investigation, you find that the database tables have grown a lot, and some tables even have tens of millions of rows of data. The test team started testing on the production database and found that the order submission process takes 5 minutes, but it takes 5 minutes on the website

After intense investigation, you find that the problem lies in the database. When the application attempts to access/update data, the database runs quite slowly. After further investigating the database, you find that the database tables have grown a lot, and some tables even have tens of millions of rows of data. The test team started testing on the production database and found that the order submission process takes 5 minutes, but it takes 5 minutes on the website

After intense investigation, you found that the problem was solved. When the application tried to access/update the data, the execution was quite slow. After investigating the database again, you find that database tables have grown a lot, and some tables even have tens of millions of rows of data. The test team started testing on the production database and found that the order submission process took 5 minutes, however, in the test before the website goes online, it takes 2/3 seconds to submit an order.

Similar stories are staged every day in every corner of the world, and almost every developer has encountered such things in his development career. I have also encountered such a situation many times, therefore, I hope to share my experience in solving such problems with you.

If you are in such a project, it is not a way to escape, but to face the reality bravely. First of all, I think your application does not write data access programs. I will introduce how to write the best data access programs in this series of articles, and how to optimize existing data access programs.

Range

Before the official start, it is necessary to clarify the writing boundary of this series of articles. I want to talk about "optimizing data access performance in transactional (OLTP) SQL Server databases ", however, the techniques described in this article can also be used on other database platforms.

At the same time, the skills I introduced are mainly intended for programmers. Although DBA is also a major force in optimizing databases, the optimization methods used by DBA are not covered in my discussion.

When a database-based application runs slowly, 90% of the applications may be due to data access issues, either not being optimized or failing to write code in the best way, therefore, you need to review and optimize your data access/processing programs.

I will talk about 10 steps to optimize the Data Access Program. Let's start with the basic index!

Step 1: Apply the correct index

The reason why I start with the index is that using the correct index will improve the performance of the production system. Another reason is that the creation or modification of the index is performed on the database and will not involve modifying the program, and you can see the results immediately.

Let's take a look at the basic indexing knowledge. I believe you already know what indexes are, but I still don't understand many people. I will give you a story first.

A long time ago, there were thousands of books in the big library of an ancient city, but the books on the shelves were not placed in any order. So whenever someone asks a book, the librarians only have to look for them one by one, and each time it takes a lot of time.

[This is like the fact that a data table does not have a primary key. When searching data in a table, the database engine must perform a full table scan, which is extremely inefficient.]

Worse, there are more and more books in the library, and the work of the librarians has become very painful. One day, a clever young man came up with a solution after seeing the painful work of the librarians, he suggested that each book be numbered and placed on the shelf by number. If someone specifies the book number, the librarians will soon be able to locate it.

[Just like creating a primary key for a table, a clustered index tree is created when a primary key is created. All rows in the table are physically sorted by the primary key value in the file system, when querying any row in a table, the database first uses the clustered index tree to find the corresponding data page (just like the first to find the bookshelves ), then find the target row based on the primary key value on the data page (just like finding a book on the shelf).]

As a result, the librarian began to give the book number and put the book on the shelf according to the number. It took him a whole day, but after the test, he found that the efficiency of searching for the book was greatly improved.

[Only one clustered index can be created for a table, just as books can only be placed according to one rule.]

But the problem is not completely solved, because many people can't remember the book number, but only remember the book name. The bookkeepers can only scan all the book numbers to find one by one, however, this time it took him only 20 minutes, but it took him 2-3 hours before he assigned a book number. However, it was too long to search for a book by number, so he asked the smart guy for help.

[It seems that you have added a primary key ProductID to the Product table, but no other indexes have been created. When you use the Product Name for retrieval, the database engine only needs to scan the entire table, searching one by one.]

The smart guy told the librarians that they had already created a book number. Now they only need to create another index or directory to store the book name and corresponding number together, however, this time it is sorted by book name. If someone wants to find the book "Database Management System", you only need to jump to the directory starting with "D, then you can find the book by number.

As a result, the librarians excitedly spent several hours creating a "book name" directory. After testing, now the time for searching for a book is reduced to 1 minute (30 seconds is used to find the number from the "book name" directory, and 30 seconds is used to find the book by the number ).

The librarians started to think about new things. Readers may also find books based on other attributes of the books, such as the author. So he created directories for the authors in the same way, now you can search for any book in one minute based on the book number, book name, and author. The work of the librarians has become easy and the story is over.