Using indexes to improve the running performance of the SQL Server View (I)

As we all know, indexes can improve the performance of database tables. However, indexing can also improve the performance of the database view. I am afraid there are not many people who know it. In fact, a view is also called a virtual table. The result set returned by the view is usually the same as that returned by the table, and both columns and rows share the same structure. In addition, the reference method is the same. Each time a database uses a standard view, the view definition is converted into a query statement within the system to query data in the base table. Then return the result set.

When you reference a view, you need to re-query the data from the database every time, even if the data in the base table is not changed. However, when users reference a view, querying the dynamically generated result assembly results in a large amount of system overhead, especially for those views that involve a large amount of complex processing. For example, if you want to collect statistics on the sales table for one year, you need to collect statistics on the monthly sales amount, the amount received by each clerk, and the amount involved in each product, this query consumes a large amount of resources. If this statistical view is queried each time, the database queries from the base table and generates the corresponding result set, which will greatly affect the view performance.

To solve this problem, database experts put forward the concept of index view. That is, the database performance is improved by creating a unique clustered index for the view. After creating a unique index for the view, the result set will be stored in the database table, just like the table with clustered index. In this case, you do not need to reference the view every time and the database performs a query job. The result set can be obtained directly from the database. The execution performance of the view can be improved.

I. When should I use the index view?

Of course, it does not mean that creating a unique clustered index for a view can improve the performance of the view under any circumstances. As the saying goes, if this extreme method is adopted, the opposite result is often obtained. In general, if you encounter the following situations, creating an index in the index view can bring a lot of benefits.

First, the view needs to process the connection and aggregation of a large number of rows. For example, in the invoicing system, there is a sales order shipment statistics table. This report involves a large number of tables and rows. If it is necessary to involve the order header table (obtain order information), order line table (obtain order content, estimated delivery date, and other information) customer Info table, freight info table, shipping ticket header table, shipping ticket table, and invoice info table ,. And you need to reconnect the data rows in the table. In addition, some statistical functions should be implemented, such as the total shipment amount and total invoicing amount of each month. Statistics may also need to be made based on the customer. These statistics generate a large number of aggregation operations. Therefore, if you execute this query, when there is a large amount of data, it will take a long time to run. If you use a view without a unique clustered index, it takes a long time to generate this report. However, if the index view is used for this table, the execution speed will be much faster. Because when referencing this view, you do not need to re-query it in the database.

Second, in some database applications with less frequent data updates, such as decision analysis systems. The decision analysis system has two distinct characteristics: first, the data stored in its database system does not need to be updated and aggregated frequently. Second, a large number of rows need to be connected and aggregated during query. The tables involved in these rows are usually wide tables with a large number of columns or columns. Queries with narrow subsets of these columns can benefit from the index view that only contains the columns in the query or the narrow supersets of these columns. Creating a narrow index view that contains a subset of a single table's columns is called a "vertical partition" policy because it vertically splits the table. However, pay attention to this. If you want to vertically split the entire table, instead of a subset of the table, we recommend that you use the non-clustered index of the table.
The include clause only contains the required columns, rather than the index view. The specific differences between these two aspects will be discussed by the author in the future.Article.

Therefore, the index view is not recommended in any situation. For example, if you only view two base tables and the data volume in these tables is not large, the index view will reduce the overall performance of the database. Therefore, whether to create an index view is based on two criteria: whether to connect and aggregate a large number of rows in a large number of tables; and whether to update data in a database table.

Ii. What are the restrictions on using the index view?

As mentioned above, I have discussed under what circumstances the index view should be used. In the following content, I will also talk about the constraints of the index view. That is, under what circumstances, if an index view is used, it may be half the effort.

First, if the base table corresponding to the view involves frequent update operations, the index view should not be used. Because the cost of maintaining the index view may be higher than the cost of maintaining the table index. If basic data is updated frequently, the cost of maintaining the index View data may exceed the performance gains caused by using the index view. For example, in some decision-making support analysis systems, basic data needs to be updated on a regular basis in the form of batch processing, but the updated data is mainly processed as read-only data. In this case, the database administrator needs to set all the related index views to invalid or deleted before updating the data, and then activate or regenerate them. This avoids the extra burden on the database due to data updates, improves the update performance, and facilitates subsequent query and analysis operations.