Practical Implementations

Practical Implementation

The solutions I presented in the main article produce the required results in sub-seconds because the Sales table has so few rows. Obviously, a production environment stores the raw data as transactions of sales or orders spread across several tables, and the number of rows in the base tables can be very large. However, the business problem I presented is searching for only summarized data, which always contains a small number of rows. It doesn’t matter if the base tables contain millions of rows. A SELECT INTO query that performs a single scan of the base tables can aggregate the base data to monthly sales data; then, you just run the solution code I presented against the summary table. For example, say that 10 years of data produces 120 rows of monthly sales, which fit nicely into one or two 8KB pages. And suppose you want to implement the solutions I discussed in your production system, which has tables similar to the Orders and Order Details tables from the Northwind sample database. The following query produces the MonthlyOrders summary table, which is structurally similar to the Sales table I presented in Listing 1:

USE NorthwindSELECT  CONVERT(CHAR(6), OrderDate, 112) AS omonth,  SUM(Quantity) AS qtyINTO MonthlyOrdersFROM Orders AS O  JOIN "Order Details" AS OD    ON O.OrderID = OD.OrderIDGROUP BY CONVERT(CHAR(6), OrderDate, 112)CREATE UNIQUE CLUSTERED INDEX IDX_UC_omonth ON MonthlyOrders(omonth)

If the base tables are so large that even the single scan required to produce the summary tables is problematic and you can afford the performance degradation resulting from modifying the base tables, you have another option: using indexed views. (See Kalen Delaney, "Introducing Indexed Views," May 2000, InstantDoc ID 8410, for information about indexed views.)

Running the script that Listing A shows creates a view containing the monthly orders summary and the index on the view. After you run Listing A's script, SQL Server stores the summarized monthly orders on disk in the IDX_UC_omonth index. Now, you can implement the solutions I discussed in the main article against the VMonthlyOrders view. I recommend adding the hint NOEXPAND after each reference to the VMonthlyOrders view to make sure that the optimizer uses the index on the view and doesn’t access the base tables. The code in Listing B implements the first step of Solution 1. To implement Steps 2 and 3, just revise the table and column names as Listing C shows.