The SYSDATE variable is a convenient method of retrieving the current date and time. Calls to SYSDATE involve some overhead; therefore, if this variable is needed to log the date of certain processing, the call to this variable should be made once at the start of the program rather than at each iteration. This technique of calling SYSDATE once at the start of the program assumes the date logging is desired at the point in time the program started. The reduction of SYSDATE calls is illustrated in the following example. The example loops through 10,000 iterations, calling SYSDATE (only the date portion of the variable because the TRUNC function is used to truncate the time portion) every iteration.
The following output shows the timing of two executions of the preceding code segment:
The following PL/SQL code segment has been modified to retrieve the SYSDATE only once, at the beginning of the program, and set to another variable each iteration.
The following output shows the timing of two executions of the preceding code segment:
As evident in the preceding example, overhead is associated with the SYSDATE call, and the number of calls to SYSDATE should be reduced, if possible.
Leave a Reply