(858) 586 7777 | About | Testimonials | Contact
vteams vteams vteams vteams
  • How does it work?
    • Startup Process
    • Your First Day
  • Technologies
    • Hire PHP Developer
    • Hire App Developer
    • Hire JavaScript Developer
    • Hire ROR Developer
    • Hire IOS Developer
    • Hire .NET Developer
    • Hire AI Developer
    • Hire Robotics Engineer
  • Sample Budgets
  • Meet The Team
  • Experiments
  • Captain’s Log
  • Blog
vteams vteams
  • How does it work?
    • Startup Process
    • Your First Day
  • Technologies
    • Hire PHP Developer
    • Hire App Developer
    • Hire JavaScript Developer
    • Hire ROR Developer
    • Hire IOS Developer
    • Hire .NET Developer
    • Hire AI Developer
    • Hire Robotics Engineer
  • Sample Budgets
  • Meet The Team
  • Experiments
  • Captain’s Log
  • Blog
Blog
  1. vteams
  2. Blog
  3. Optimization Of Search Query With User Defined Functions
Aug 13

Optimization Of Search Query With User Defined Functions

  • August 13, 2015

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.

  • Facebook
  • Twitter
  • Tumblr
  • Pinterest
  • Google+
  • LinkedIn
  • E-Mail

Comments are closed.

SEARCH BLOG

Categories

  • Blog (490)
  • Captain's Log (1)
  • Closure Reports (45)
  • Experiments (7)
  • How-To (56)
  • Implementation Notes (148)
  • Learn More (156)
  • LMS (8)
  • Look Inside (10)
  • Operations Log (12)
  • Programmer Notes (20)
  • R&D (14)
  • Rescue Log (4)
  • Testimonials (25)
  • Uncategorized (4)

RECENT STORIES

  • GitHub Actions- Automate your software workflows with excellence
  • Yii Framework – Accomplish Repetitive & Iterative Projects with Ease
  • A Recipe for CRM Software Development
  • Are Agile and DevOps the same?
  • The Data Scientist’s Toolset

ARCHIVES

In Short

With the vteams model, you bypass the middleman and hire your own offshore engineers - they work exclusively for you. You pay a reasonable monthly wage and get the job done without hassles, re-negotiations, feature counts or budget overruns.

Goals for 2020

  • Open development center in Australia
  • Complete and Launch the Robot
  • Structural changes to better address Clients' needs

Contact Us

Address: NEXTWERK INC.
6790 Embarcadero Ln, Ste 100,
Carlsbad, CA 92011, USA

Tel: (858) 586 7777
Email: fahad@nextwerk.com
Web: www.vteams.com

© 2020 vteams. All Rights Reserved.

Content Protection by DMCA.com