Devaten Piloted Experience

Devaten has helped large companies solve performance problems. Often the problem has been, for example, bad SQL statements that read a lot of lines from the database, or another common problem is that data is retrieved from the database, for example by iterating through deep lists and executing hundreds of SQL statements. Devaten’s idea is based on catching these changes, for example.

In the case of a one Pilot client in a test environment, the application was able to process 1700 sql / second. While the production environment is only 400 sql / second. This is because the test was run on only one test at a time while 5 servers and a number of update services are running in production. Reason for slow process was lock waiting time. To minimize lock timeouts, be sure to design your application programs with locking in mind from the start. Limit the number of rows accessed by coding predicates to filter unwanted rows. Doing so reduces the number of locks on pages containing rows that are accessed but not required, thereby reducing timeouts and deadlocks. Also, you should design update programs so the update is issued as close to the COMMIT point as possible. Doing so reduces the time that locks are held during a unit of work, which also reduces timeouts (and deadlocks). 

The performance of the application was improved by focusing on retrieving only the information that is needed from the database, i.e. no additional searches on the database. By doing this there was way to less SQL statements in use cases which leads to less lock waiting time. —> sql/second was increased for 1100sql/sec.

Once the queries that use the most resources have been identified, efforts can be made to reduce the load on their servers and memory usage. In addition, finding out which use case is causing these expensive queries to the database can help you diagnose the underlying problems with the queries and fix them quickly and efficiently.

Continuous automatic tracking of queries helps you identify which host application is causing the problem and also helps diagnose other application issues.

Related Post

Leave a Comment