INF: Improving Performance in Views with Aggregate Functions

ID: Q89384

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2
Microsoft SQL Server version 4.2x

SUMMARY

To increase performance when selecting from a view, do not evaluate aggregate functions in the view, if possible.

MORE INFORMATION

To illustrate this idea, assume the following table and view definitions:


   CREATE TABLE MyTable (
   col1 INT,
   col2 CHAR(5),
   col3 FLOAT)
   go

   CREATE VIEW MyView
   AS
   SELECT col1, col2, Total = SUM(col3)
   FROM MyTable
   WHERE col1 > 55
   go

If the statement "SELECT * FROM MyView" is executed, SQL Server will need to sum all the values in the table for col3 that match the WHERE condition of the view. However, if the aggregate column in the view ("SUM(col3)") is not included in a SELECT statement, the values in col3 will not be summed. Therefore, if a large number of rows meet the condition(s) of the WHERE clause in the view, a considerable performance gain can be realized by not selecting that column unless it is needed.

The following queries are examples where the summing of col3 from the view will not be performed:


   - SELECT col1 FROM MyView
   - SELECT col2 FROM MyView
   - SELECT col1, col2 FROM MyView

Additional query words:


Keywords          : kbprg kbSQL 
Version           : OS/2:4.2; winnt:4.2x
Platform          : OS/2 winnt 
Issue type        :

Last Reviewed: July 12, 1999