To make sure that caching didn’t affect the run time, I ran the queries twice.
The second query creates the same results, but with a window aggregate function. The first query in this example demonstrates one of the traditional methods to get subtotals, using a CTE (common table expression) that joins to the original table. Since the results are stored in temp tables, the only output is the STATISTICS IO and STATISTICS TIME. Here are two queries calculating the subtotal for each product in this table of 31 million rows. To make sure that populating SSMS (SQL Server Management Studio) does not affect the run times, the results are saved in temp tables. To show how much the window aggregate impacts performance, the following queries use bigger tables created from Adam Mechanic’s script Thinking Big Adventure. That’s a big difference, but do the logical reads for a worktable affect query run times? These AdventureWorks tables are too small to see the performance impact, but it does make a difference as you’ll see in the next section. The second query also takes 689 logical reads for scanning the table, and 139,407 logical reads for a worktable. The first query has 0 logical reads for a worktable and 689 logical reads for scanning the entire table since there is no WHERE clause. To show what I mean, take a look at the STATISTICS IO results in Figure 2.įigure 2: The logical reads for the sales orders queries In fact, I’ve been recommending that window aggregates be avoided when they must operate on a large number of rows.
The problem with this technique is that the performance has been disappointing. In this case, I wanted the subtotals for each customer, so I included PARTITION BY CustomerID. I think you would have to agree that calculating the subtotal was easy to do. The results should look like Figure 1.įigure 1: The partial results of the sales orders queries The first query lists the sales orders, and the second query also has a sub total for each customer. I’m running SQL Server 2019 CTP 2.2, and the database is in 2016 compatibility mode. The Performance of Window Aggregates Revisited with SQL Server 2019 - Simple Talk
0 kommentar(er)
