Improving speed and efficiency of data access from the database.
Database optimization refers to a variety of strategies for reducing database response times. Databases can store huge volumes of data, and smart database design is required to navigate the data and create the appropriate result sets. The SQL script directs the SQL engine to access specific chunks of data. Database optimization involves maximizing the speed and efficiency with which data is retrieved, by improving database design and SQL constructs so that they work hand-in-hand.
On the one hand, SQL optimization analysis might be trivial, with a relatively simple solution to improve response times. On the other hand, the analysis might require a significant effort in analyzing, measuring, comparing, and testing to expose the root cause of the slow response. After that, a considerable effort may be required to create solutions to the problem. Finally, there are occasions when a cost-effective way to improve the situation simply can not be found.
To begin with, we will establish if this is a site-wide problem, or whether we will address specific areas.Site-wide problems usually require a team approach and would be happy to be part of this effort. Specific bottlnecks are where we can be most useful.
We use a variety of techniques to analyze both the database design, SQL scripts requesting the data, and other environment constraints. Database access methods and the SQL engines continuously evolve and improve over time. Consequently, older coding practices will sometimes lag and be a contributing factor to the problem. Also, part of this analysis might be to create a stand-alone script cloned from the production system.
Once a pattern emerges, we apply current best-practices in our proposed solution.
Massive database: The client had a massive database with several tables containing millions of rows. The overnight processes consisted of complex SQL scripts that frequently over-ran the maintenance window, resulting in penalties for failing to meet SLA’s. A review identified several changes would be beneficial to the database and SQL scripts. After applying the changes, run-times improved to between 20% - 50% of the original times.
Complicated SQL Script: The client was running a 3rd Party query tool over the DB2 database. The author of the query was a business expert but lacked in-depth knowledge of SQL. Consequently, the query was not well written, exacerbated by more than ten JOINs. The query would run for ten minutes then time out. A review revealed several changes could be made, mostly relating to the JOINs. After implementing the changes, the query ran in mere seconds.