UDF Execution Time Estimation via Self-Timing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Contemporary database systems do not accurately assign a true cost to the execution of User Defined Functions (UDFs) and User Defined Methods (UDMs), leading to inaccurate time estimates for SQL command execution.
Innovation Solution
The execution time of UDFs/UDMs is timed multiple times during creation, with an average execution time calculated and stored in the data dictionary for the optimizer to factor into query costs, using a self-contained timing mechanism within the UDF/UDM code that does not require database administrator intervention.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional database systems are used without UDF/UDM costing, then the system structure remains simple, but the query cost estimation becomes inaccurate
Solution Approach 1:
The system performs preliminary timing measurements of UDF/UDM execution during the function creation phase. Multiple executions are timed and an average execution time is calculated and stored in the data dictionary before the functions are actually used in queries. This preliminary action enables accurate cost estimation without adding complexity to the query execution phase.
Solution Approach 2:
The UDF/UDM themselves perform the timing measurements and store their own execution time information in the data dictionary. The functions are self-contained with timing mechanisms embedded within them, eliminating the need for external monitoring systems or complex infrastructure to gather execution statistics.
2Extent of automation
If manual statistics gathering by database administrators is required, then costing calculations can be obtained, but the operation becomes time-consuming and requires administrative intervention
Solution Approach 1:
The system automatically performs timing measurements and calculates execution costs without requiring database administrator intervention. The UDF/UDM include embedded timing mechanisms that automatically execute multiple times, record their execution times, calculate averages, and store the results in the data dictionary. This self-service approach eliminates both administrative time investment and ongoing maintenance requirements.
Solution Approach 2:
All statistics gathering and costing calculations are performed automatically during the function creation and initialization phase. By the time the functions are deployed and used in production queries, the timing statistics are already collected and stored, eliminating any need for subsequent manual intervention or ongoing administration.
3Reliability
If UDF/UDM execution is not timed, then the system operation remains simple, but the response time estimates do not reflect actual execution costs
Solution Approach 1:
The UDF/UDM include self-contained timing mechanisms that automatically measure their own execution time. The timing code is embedded within the function body, using standard database timestamp functions to record start and end times. This self-measuring approach provides reliable execution time data without requiring external monitoring systems or complex infrastructure.
Solution Approach 2:
Multiple execution timings are performed and averaged during the function creation phase, establishing a reliable baseline execution time before the function is deployed. This preliminary timing action ensures that the cost estimation data is already available and validated, providing reliable response time estimates from the first production use.
Data Source
AI summary
A system, method, and computer-readable medium for the calculation of execution time estimates of user defined functions/user defined methods are provided. The execution of a UDF or UDM is timed several times at the time of the UDF/UDM creation, and an average execution time of the UDF/UDM is obtained. The resulting average execution time is then stored in a data dictionary where the optimizer may consult this value to factor it into the cost of execution of a query.