Shulou(Shulou.com)05/31 Report--

This article focuses on "what is the role of temporary tables in SQL optimization". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn "what is the role of temporary tables in SQL optimization"?

Today we are going to talk about the optimization skills of temporary tables.

Temporary table, as its name implies, is just a table for temporary use, one is a local temporary table, which can only be used on the current query page, the other is a global temporary table, which can be used no matter how many query pages are opened.

0. Test environment

SQL Server 2017

1. Local temporary table

The local temporary table can be preceded by a #. Let's first take a look at the features of the local temporary table.

Let's create a new query page and enter the following code:

SELECT TOP 10 * INTO # temp FROM sales.Temp_Salesorder; SELECT * FROM # temp

The results are as follows:

Let's open a new page and re-enter the following code:

SELECT * FROM # temp

The results are as follows:

Certifies that local temporary tables can only be executed on the current page.

2. Global temporary table

The global temporary table can be preceded by a # # table name, which can be used by opening any query page.

Repeat the above steps:

SELECT TOP 10 * INTO # # temp FROM sales.Temp_Salesorder SELECT * FROM # # temp

The result is the same as above:

Let's open a new page:

SELECT * FROM # # temp

The result is still the same. Prove that all query pages of the global temporary table are available.

3. Optimization method of temporary table.

After introducing the temporary table, let's talk about how to use it for optimization.

The optimization of temporary table is also called nested query when there are many sub-queries. We write a subquery like this:

SELECT * FROM sales.Temp_Salesorder WHERE SalesOrderDetailID IN (SELECT SalesOrderDetailID FROM sales.SalesOrderDetail WHERE UnitPrice IN (SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice > 0))

(hint: code can slide left and right)

This is a relatively simple two-tier nested subquery. Let's take a look at the execution:

You can see that the logical reading here is relatively high.

Let's take a fresh look at the performance with the temporary table. We insert the query results from layer 1 and layer 2 into # temp, and then query the results from the temporary table.

SELECT SalesOrderDetailID INTO # temp FROM sales.SalesOrderDetail WHERE UnitPrice IN (SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice > 0) SELECT * FROM sales.Temp_Salesorder WHERE SalesOrderDetailID IN (SELECT SalesOrderDetailID FROM # temp)

The implementation is as follows:

Compared with the last logical reading, the number of logical reads has been reduced exponentially. When adjusting the performance of the query, if the logical reading value decreases, it indicates that the server resources used by the query are reduced, and the query performance is improved. If the logical reading increases, it indicates that the adjustment measures degrade the performance of the query. Other things being equal, the less logical reads a query uses, the more efficient it is and the faster the query will be.

Therefore, we can see that temporary tables can improve query efficiency in more complex nested queries.

At this point, I believe you have a deeper understanding of "what is the role of temporary tables in SQL optimization". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.