vteam #397 has been working on the project of a web based commercial, SBA lending, Software Company. While using the reporting module to run a report, end user reported that he faced a timeout exception after waiting for almost 2 minutes. This particular issue occurred when there were approximately 60 records in the database.
After analyzing the query and its overall structure, it was observed that a report data-source contained a complex query (that have multiple UDFs) and two different databases (that were involved for data manipulation). The query (that was run) was taking 18 seconds to fetch the records at database level because the main query was:
- Passing ‘sub-category-id’ as a parameter to scalar UDF (User Defined Function). This UDF was generating a ‘category-id’ based on ‘sub-category-id’. Internally, this UDF was calling three more depth level UDFs from another database in order to process some business logic
- Passing ‘sub-category-id’ as a parameter to another UDF to get some user related business information
- Calling another scalar UDF to return some information based on ‘sub-category-id’
Technical Aspect Considerations
To generate a ‘category-id’ based on ‘sub category-id’ in the UDF was a useless execution because the main query already contained such information. This was an overhead because ‘category-id’ could be fetched directly from the main query.
An ‘inline query’ usually provides better performance as compared to Scalar function for the same work. If the information could be retrieved easily at the main query then it was useless to introduce the overhead of scalar function.
From performance point of view, table valued functions were much better than the scalar functions because they act like an ‘inline query’.
vteams engineer Muhammad Kashif modified the main query and passed ‘category-id’ from main query to UDF instead of reforming ‘category-id’ from ‘sub-category-id’. As a result, the execution time decreased from 5 seconds to 1 second.
Another UDF was removed and the required information was retrieved at the main query rather than creating a new UDF for the same purpose. As a result, the execution time decreased from 4 seconds to 0 second.
Another scalar UDF was changed to table valued function and the execution time decreased from 3 seconds to 0 second.
This solution improved the performance of query from 18 seconds to 2 seconds approximately. The optimized query was tested on almost 1600 records in the database. Time out issue faced by the end user was also resolved for the desired report.