INF: Improving Performance in Views with Aggregate Functions

 Last reviewed: April 3, 1997
Article ID: Q89384

The information in this article applies to: 

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server versions 4.2, 4.21, and 4.21a

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: 4.20 4.21 4.21a Windows NT
Keywords : kbprg
Version : 4.2 4.21 4.21a
Platform : OS/2 WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.