SQL Server 2017 Developer's Guide

Writing efficient queries

Before finishing this section, I also need to mention that no join, compression algorithm, or any other feature that SQL Server offers can help you if you write inefficient queries. A good example of a typical DW query is one that involves running totals. You can use non-equi self joins for such queries, which is a very good example of an inefficient query. The following code calculates the running total for the profit ordered over the sale key with a self join. The code also measures the IO and time needed to execute the query. Note that the query uses a CTE first to select 12,000 rows from the fact table. A non-equi self join is a quadratic algorithm; with double the number of the rows, the time needed increases ...