The client is one of the premier service providers of Commercial Fleet Management Solutions in North America.
The client uses MySQL database for its critical business processes, which, however, was affected due to the changing business environment and prompted a switch to the more commonly used SQL server. A direct swap resulted in poor performance characteristics at greater loads. The client wanted us to optimize its business performance.
With the underlying architecture and code base remaining the same, it was obvious the issue lay with the database. BluePi Consulting was tasked with determining the root-cause of the poorer performance and remediating it. While there could be a myriad of possible causes, we went about eliminating these in the order of likelihood.
However, none of these highlighted the cause. While checking the performance characteristics of queries, we did notice that one of the queries was taking a lot longer on SQL Server than on MySQL. The table being accessed in this query contained a large amount of data and the data fetch was taking much longer. This pointed to the optimization of the indexes – and the realization that the two DBMS treat clustered and un-clustered indexes very differently. (SQL Server doesn’t automatically optimize un-clustered indexes).
MySQL, SQL Server, SQL Prfiler, AWS