DSP Blog

Reasons to engage database consultants: Poor Performing Apps

Written by Jon Cowling | 24-Sep-2018 13:10:56

You Are Not Alone

Poor application performance is one of the most common signs that your current database configuration is not coping well. You’re in great hands with DSP-Explorer, though - we get asked to look at optimising application performance every single day.

Our team recently helped a customer in the manufacturing industry with a large production line and associated warehouse operations. They called us to say that their Oracle database had gone from processing each item in five seconds to four minutes and had been experiencing this slowdown for three days by the time they called us.

We were able to respond in four hours, drafting up a discovery session to examine their databases, isolating the issue and fixing the problem within eight hours. In the process, we managed to cut their processing time down to four seconds, meaning their production lines would no longer bottleneck as a result of the database being overloaded.

Four Things To Consider

Usually, performance tuning means “make it go faster”. Before you even log into your system, it’s paramount that you isolate the problem as far as possible. If your users are reporting that a certain application is running slowly, is it just when performing one particular function, or does the whole application feel like it’s hanging?

Other times there are external pressures - payroll needs to be completed by a certain time each month, or regulatory reports need to be compiled, or item lookups are taking to long and causing web prospects to drift to competitors’ sites.

Once we have an accurate idea of the problems you’re experiencing we can look for the bottleneck(s) in your environment. Broadly, there are four basic ways to tune your database performance to deal with an unexpected bottleneck.

These are:

1. Use less resources. This is much easier said than done; but in many cases it’s possible to refine SQL statements to be less resource-intensive and still accomplish the same goals.

2. Increase the resources available. This sounds obvious, but it can be hard to understand where to upgrade your infrastructure. Rather than buying a whole new server, it might be that simply adding more disks to a SAN would increase your bandwidth sufficiently to deal with resource-intensive jobs.

3. Make the resource faster. Again, similarly to the above, it sounds straightforward but there are a variety of solid state and hard disk storage media and RAID architectures to choose from. Swapping out hard disks for solid state drives will have significant impact on the speed and responsiveness of applications.

4. Use the resource at off-peak times. Is it possible to shift low-pressure, resource-intensive jobs to quieter, less busy times? Analysing the way your business works pays off here. Running payroll operations overnight, for instance, would require your finance team to sign off a day earlier, but could mean significant cost savings over buying new hardware.

Let’s go a bit deeper

Was it set up properly?

Taking a fresh look at the setup of the database environment can be really useful. Like any powerful software, it’s often not immediately obvious which configuration best suits you, and it’s entirely possible that the common parameters aren’t quite set properly, or that your disk load could be spread more effectively.

Improve your indexing strategy

While it’s very easy to overlook during the development process, a proper indexing strategy can be the best tool to tune your database, providing faster data retrieval with random lookups and easy access to orderly records. We’ll work with you to determine which of the various options suit your environment best.

Streamline connections

The application might be querying the database in a really inefficient manner, and there might be ways we can redesign requests to streamline performance. Fetching, processing, and writing rows in bulk is much faster than doing it row by row, so redesigning requests to process multiple rows at a time whenever possible will bring significant performance gains.

Who’s connecting to your database?

Taking stock of the applications and services that have access to your database can help you pinpoint performance bottlenecks. Poor performance from a single service may be slowing your whole operation down. If you notice any individual clients that don’t measure up the others, then you should delve deeper into metrics and troubleshoot the problem. If all of your database’s clients appear to be suffering from poor performance, then you should check the health of your host.

What about the Cloud?

Oracle Cloud can be the ideal solution to the problem of poor-performing applications. Over 1,000 enterprise-grade SaaS products are optimised for the latest version of Oracle’s infrastructure, and its variable cost model means there’s no hefty upfront costs for rapidly depreciating hardware. As your needs grow, you’re free to provision increased computing power or more storage space - without any complex configuration requirements or setup changes. If you’re in a position where you value that flexibility, then cloud migration is well worth exploring. 

Once you’ve configured your cloud environment, you’d have none of the ongoing performance tuning and maintenance headaches we’ve outlined in this article to worry about. We can help you along the way, even doing a gradual migration as proof-of- concept, or provisioning a hybrid cloud environment for you. However, cloud isn’t necessarily for everyone. If your poor-performing application is particularly old, and you’re not likely to be changing it anytime soon, then don’t be afraid to stay on-premise.

 

Call Us

If you’d like a free consultation to discuss a potential cloud migration project, or help troubleshooting a poorly-performing application, click here to organise a call.