![]() The Table which was being used in this query contained huge data and the data fetch was taking more time in SQL server as compared to MYSQL. Having found this one slow query on MS SQL we questioned how is the same query on MYSQL is performing so well. We calculated the time on all queries which were being executed by application through SQL profiler and we found one query was taking a lot of time and was being called from different places in the application code. The possibility that fetches queries for MS SQL Server was not optimized and was leading to higher query execution time was now the focus of our attention. We decided to park this as MySQL did not have any partitioning. In SQL server this is only possible after creating the table, creating partitioning objects in SQL Server (partition schemes and functions) that are then applied to the corresponding tables. On the other hand SQL Server only offers range partitioning. MYSQL provides different types of partitioning - range, hash, key, list, and composite partitioning which can be defined at the time of creating a table. So we decided to turn our focus on addressing the size by closely looking at the partitioning scheme. Some of the tables were pretty big - millions of rows. Interestingly the index definitions were exactly the same in both MS SQL and MySQL.Īt first look, it appeared that there was nothing wrong with the indexes as MYSQL seemed to be performing really well with the same index definition. Database Indexes may not be properly tuned So application code could be ruled out as a possible cause. We checked each and every request but the application was correctly using the connection pool and releasing connection after transactions. We checked thoroughly the number of connections, the application was using for MS SQL Server through SQL profiler. A poorly optimized connection pool could be leaking and slowing down the database. So the only investigation that we did was around the use of database connection pools. ![]() Network/Disk or any other physical resources may be constrainedĪs the application code does not change between the two databases it is unlikely that the application code would be a problem. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |