We help you save on hardware, license and cloud costs.
What is optimization?
Database optimization, i.e. increasing efficiency, consists in accelerating the processes (SQL queries) that are performed in the database. Processes are much shorter so it is assiociated with lower CPU load. This, in turn, means less hardware and license requirements without sacrificing performance or stability.
Usually, the longer a process takes, the more likely it is to speed it up and the greater the effect that can be obtained. E.g. reports and long batch processes are susceptible to optimization.
An additional effect of optimization is the increase in the stability of the application, especially the three-layer application. Thanks to optimization, application processes run faster, the load on application servers is reduced, the number of active sessions in the servers themselves is reduced, which significantly improves their stability.
Thanks to optimization, you can not only shorten, but even make it possible to perform certain processes.
Example: the application upgrade process at one of the largest Polish telco operators.
The time of upgrading the base itself (based on the upgrade times on the test base) with the original application manufacturer’s scripts was estimated at about 30 days. Thanks to optimization, it was possible to shorten it to 2 hours.
How optimization works
During optimization, we monitor the operating parameters of databases, memory configuration, SQL queries sent to the database, existing indexes in terms of their use by applications, ways of storing data in the database, factors relating to the database operation (e.g. buffer hit rate, read rate and others ), fragmentation of tables and indexes and configuration of elements specific for a given database engine.
Based on the collected data, we identify bottlenecks and prepare solutions to the problems found. We test the solutions on the test / development basis and then implement them on the production basis with the participation of the client’s IT department.
The big advantage of optimization is the quick effect. The first effects of increasing efficiency are usually visible a few days after the start of optimization. The greatest gains in performance are most noticeable in the first two weeks. Usually, after such a period, any problems with the stability of the application disappear or significantly decrease.
Our methodology is based on the observation of the natural database load generated by applications. Therefore, we need access to the production base, but only read-only access.
We monitor the database operation using its internal tables and views, the so-called data dictionary.
During optimization, we do not change the application code, we focus on the use of built-in, standard acceleration mechanisms offered by a given database engine (change of initialization parameters, reallocation of individual memory areas, defragmentation, indexes, materialized views, etc.).
Optimization is completely safe. It does not change the application code, it does not affect data, implemented changes can be rolled back.
At the client’s special request (if client has the ability to modify the application code), we can additionally optimize or even rebuild SQL queries and PL / SQL, T-SQL or PL / pgSQL code.
Thanks to optimization, you can significantly speed up the operation of the application. A faster application means greater employee productivity, more satisfied customers, quick access to data on the basis of which strategic business decisions are made.
Another effect of optimization is savings on hardware and licenses.
Faster running SQL queries mean less CPU usage.
Savings on equipment:
More databases can run on the same server without loss of performance, or a given base needs fewer cores / processors. A server with a smaller amount of core can be used, so definitely cheaper.
Savings on licenses:
Oracle and MSSQL licensing most often depends on the number of core / processors (depending on the type of license).
With a 100% increase in efficiency (which is not uncommon), you can save 50% on licensing costs.
Example: in the case of an Oracle EE 32 core license, a 100% performance increase can save PLN 2.35 million * on licenses plus savings on hardware.
Does this apply to my systems?
Out of more than 300 performance audits we conducted, only in two cases did optimization make no business sense – the resulting efficiency gains would be inadequate to the optimization costs.
In all other cases, optimization could bring tangible benefits – reducing hardware and licensing costs or significantly accelerating the operation of the application.
This problem affects all systems, large, medium and small, ERP, MIS, CRM, BI, GIS, billing systems, etc.
It also applies to all database engines – Oracle, MSSQL, PostgreSQL, MySQL, etc.
In the vast majority of database-based systems, performance can be increased by at least 50%.