Performance implications of the placement of an aggregrate function

This is a good example of how placement of your functions in a query result clause can seriously affect the performance of a query. In this case the original query was written to convert the date to an appropriate format and then get the max result. By doing the date/time formatting first and then doing the aggregate (max) the query has to scan all the rows and format them, then get the max value.

I changed the query to first find the max and then format the result, meaning that only one row had to be retrieved and formatted,  saving a lot of time (almost 10 seconds versus near zero), and allowing the application to keep up with its workload.

Here is the original:

SQL> SELECT MAX(TO_DATE(TO_CHAR(TIMESTAMP_TO_TIMEZONE(START_TIME, 'GMT','MST7MDT' ),'mm/dd/yyyy hh24:mi:ss'),'mm/dd/yyyy hh24:mi:ss'))
  2  FROM PAGE_SCHEDULE
  3  WHERE PAGE_ID = 204
  4  AND TEMPLATE_COMPONENT_ID = 109
  5  AND START_TIME <= sysdate
  6  /

... run multiple times for caching ...

MAX(TO_DATE(TO_CHAR
-------------------
11/20/2008 05:10:34                                                             

Elapsed: 00:00:09.79

Execution Plan
----------------------------------------------------------
Plan hash value: 2876027309                                                     

--------------------------------------------------------------------------------
----------                                                                      

| Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)|
Time     |                                                                      

--------------------------------------------------------------------------------
----------                                                                      

|   0 | SELECT STATEMENT      |                  |     1 |    16 |    54   (6)|
00:00:01 |                                                                      

|   1 |  SORT AGGREGATE       |                  |     1 |    16 |            |
         |                                                                      

|*  2 |   INDEX FAST FULL SCAN| PAGE_SCHEDULE_PK | 41162 |   643K|    54   (6)|
00:00:01 |                                                                      

--------------------------------------------------------------------------------
----------                                                                      

Predicate Information (identified by operation id):
---------------------------------------------------                             

   2 - filter("PAGE_ID"=204 AND "TEMPLATE_COMPONENT_ID"=109 AND
              "START_TIME"<=SYSDATE@!)                                          

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        628 consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

and here is the modified:

SQL> SELECT TO_DATE(TO_CHAR(TIMESTAMP_TO_TIMEZONE(max(START_TIME),'GMT', 'MST7MDT' ),'mm/dd/yyyy hh24:mi:ss'),'mm/dd/yyyy')
  2  FROM PAGE_SCHEDULE
  3  WHERE PAGE_ID = 204
  4  AND TEMPLATE_COMPONENT_ID = 109
  5  AND START_TIME <= sysdate
  6  /

... run multiple times for caching ...

TO_DATE(TO_CHAR(TIM                                                            
-------------------                                                            
11/20/2008 05:10:34                                                             

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------                     
Plan hash value: 2061347836                                                    

--------------------------------------------------------------------------------
-----------------                                                              

| Id  | Operation                    | Name             | Rows  | Bytes | Cost (
%CPU)| Time     |                                                              

--------------------------------------------------------------------------------
-----------------                                                              

|   0 | SELECT STATEMENT             |                  |     1 |    16 |     3
  (0)| 00:00:01 |                                                              

|   1 |  SORT AGGREGATE              |                  |     1 |    16 |      
     |          |                                                              

|   2 |   FIRST ROW                  |                  | 41162 |   643K|     3
  (0)| 00:00:01 |                                                              

|*  3 |    INDEX RANGE SCAN (MIN/MAX)| PAGE_SCHEDULE_PK | 41162 |   643K|     3
  (0)| 00:00:01 |                                                              

--------------------------------------------------------------------------------
-----------------                                                              

Predicate Information (identified by operation id):                            
---------------------------------------------------                            

   3 - access("PAGE_ID"=204 AND "TEMPLATE_COMPONENT_ID"=109 AND "START_TIME"<=SY
SDATE@!)                                                                       

Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
          3  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        526  bytes sent via SQL*Net to client                                  
        399  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed

One response to “Performance implications of the placement of an aggregrate function

  1. I like this weblog very much so much great info.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s