A web-based commercial and SBA lending software was developed that manages the origination, processing, closing, and monitoring of small business loan portfolios. With the loan management system, lenders can think big and reimagine what modern business lending can be. It was developed by using Microsoft Visual Studio 2013(.NET 4.5), Bitbucket (Git), ASP.NET MVC, C#, Web API, MS SQL Server 2012, Single Page Application, Kendo UI Controls and Charts, Telerik Reporting, jQuery, Backbone.js, Marionette.js, Require.js, Underscore.js tools and technologies.
Problem:
Exisitng SQL code for basic and advanced search query was taking 22 to 25 seconds execution time on database server. In order to process the search query, multiple databases and nested user-defined functions have been involved. Client asked vteam #397 to investigate the issue and optimize it.
Solution:
After thoroughly analyzing the SQL query and overall structure, it was observed that the execution problem was due to one computed column which was taking time and there were some sub-queries reordering issues as well. It was also observed that for small data, the execution time was nearly 22 seconds and for larger set of data, time was increasing accordingly.
At first level, user-defined scalar function which was again calling multiple user defined functions internally was converted to table valued function with minimum dependencies required for result computation; thus execution time drastically decreased from 22 seconds to nearly about 2 seconds.
At second level, reordering of sub-queries was done to further improve the execution time of every query.
Conclusion:
This solution improved the performance of SQL query as well as overall result rendering process at front end by removing extra dependencies in the query and by reordering sub queries.
Comments are closed.