I received some interesting feedback on my editorial, “Write Polite SQL”. . There were a couple questions asking why you might even use the concept in the first place? The questions were reasonable based on the example I provided. It was too simple, and other techniques, such as a filtered index, were better solutions (as suggested by A.J. Jim).Today I thought I would provide a better example of why you might want to have an inline query to pre-process work, and then use it as the source for additional information.
When using the GROUP BY clause in an SQL Statement, there is often a point in which the performance is increased by using an inline view to pre-process some of the data. The more columns you add to the group by clause, the slower the query performs. The speed difference depends on the size of the table, and the grouping distribution of the data. Let me give an example from a real world report I have produced in the past.
I was creating a report of telephone usage for a large customer. The report returned summarized data by billing unit, a 24 month rolling period, with counts of numbers, calls and minutes used per billing unit. In addition, for each of these billing units, the report returned the individuals assigned to the telephone numbers, and the manager of the billing unit.
I found that if I wrote a query that did all of the summarization first, grouping it by the billing unit, individual assigned to a number, and the phone number, summarizing the aggregates for each group on the count, minutes and fees, then I could take that result, and join it to the people tables to get the individual users information, and the person responsible for the billing unit.
Again, there are other ways to do this same kind of query. I could create a view with the summarized data. I could create a data mart with the summarized data. Those were ultimately solutions that were implemented to increase performance. But, as we were designing the system, it was clear that performing all of the aggregation prior to gathering the individual names was a great optimization technique.
Today, if I were to write the same query, I would use a Common Table Expression (CTE) instead of an inline view. The reason is simply that of readability. I do the summarization first, and then join it to the person tables. Using a CTE, this is the order of the query. If I use an inline view, the query consuming the view comes first, and then the inline view is defined. For me, defining the CTE follows how I think. I really don’t see any performance benefit either way, and the query plan is often identical. So, it’s a personal choice in my opinion.
Cheers,
Ben