Database Management System (DBMS) Optimization

Project Objective:
*database management system (DBMS) optimization for the enterprise content management (ECM) system
*setup offshore development center (ODC) for maintenance and support

Overview:
The client was a gum manufacture located in Brazil, South America. The client specializes in producing healthy and flavorful bubble gums. The client used their enterprise content management system to keep business resources data. Since this system was cheaply developed by another outsourcing company, it was built with speed and lacked stability. After awhile, the client experienced slow system responses with the ECM system, so they wanted a competent team to perform system optimization.

Challenges:
*the client did not have any proper documentation for the ECM system design
*data needed to be transferred to the new optimized system

Solutions:
Fortunately, the enterprise content management system did not have a complex structure. A software testing team was created in a short amount of time. The software testing team focused on checking for system flaws. The team discovered that the instabilities were caused by the poorly designed database management system.

DBMS optimization method:
*thoroughly analyzing the database system
*improve the data tier from the bottom up

For database management system optimization, RayooTech engineers created better index structures that greatly improved the search speed. Large tables were divided into smaller parallel tables for faster performance. Our engineers also removed many usage of in and improper joins. Some triggers were removed and replaced with stored procedures or functions. In order to reduce database CPU usage per user, the DBA instructed the software testing team to collect data for database CPU usage. With the software testing team results, RayooTech DBA found several tuning method to improve efficiency of the storage component. Lastly, DBA modified sg_configure on the server side to add minor boosts in performance.

As soon as the database structure was fixed, another engineering team worked on data transfer. Since we decided to keep on using SQL server, it was easy to transfer data from the old database system. RayooTech engineer team also added data protection mechanisms to the enterprise content management system.

Technology: SQL Server, SQL profiler, Test Director, Java, Tomcat, etc…

Results:
With database management system optimization, the enterprise content management system now performs 75% faster. The client was impressed by RayooTech engineers and decided to open an offshore development center for maintenance and support of the enterprise content management system.

Read More