With the increasing reliance on cloud applications to help drive growth in the business, CIOs are increasingly concerned about the need to balance application performance, necessary to deliver a high quality customer experience, with cloud service costs.
To maintain a great customer experience on your website, application slowdowns must be identified immediately, their root cause diagnosed accurately, and the problem rectified quickly, before frustrated users leave and go somewhere else.
With more companies investing in cloud services, application performance also matters in terms of controlling cost. Slow application response times are often attributable to excessive resource consumption which can result in cloud overspend unless performance issues can be resolved quickly.
According to this 2015 survey from JRebel of 1562 software developers, slow database queries were considered to be the #1 cause of poor application performance. What’s interesting is that 70% of respondents were web developers.
CIOs need to ensure their IT operations teams have sound processes and use effective tools to ensure service levels are met and exceeded.
This requires oversight across all their databases in a hybrid environment through a single dashboard and the analytical depth to diagnose the root cause and resolve any issues before customers are aware.
As this survey shows, slow database queries are the greatest contributor to slow performance. But, the task of resolving slow SQL execution performance can be complex and time consuming unless it can be automated.
To illustrate how prevalent SQL is when it comes to application code and its potential impact on website performance in particular, a recent State of the Developer Ecosystem 2020 survey by Jetbrains of 19,696 developers highlighted that SQL is the #3 most popular programming language and is used by application and database developers. That’s higher than Java.
According to research by Dr. Elliot King, research analyst at Unisphere Research, a Division of Information Today, Inc. , the following survey question relates to those tasks that take up a database developer’s time:
In the same survey, only about 5% of DBAs say that performance tuning, including SQL tuning, is generally not performed.
“Industry experts estimate that 60-90% of performance problems in the database stem from poorly written SQL. Fixing problems in production is at least five times more expensive compared to fixing them pre-production. “
According to Noel Yuhanna of Forrester Research in his White Paper, The Future of DBMS Technology, “The key difficulties surrounding performance continue to be poorly written SQL statements, improper DBMS configuration, and a lack of clear understanding of how to tune databases to solve performance issues.”
How Quest solves the SQL tuning problem
SQL optimization technologies from Quest are proven to dramatically reduce the time it takes to optimize SQL execution performance and can yield significant improvements in execution times which can lead to faster response time and lower resource consumption; helping to reduce cloud spend.
By using a patented SQL transformation algorithm, SQL statements are re-written multiple times, automatically, in order to find an alternative that will perform faster. This operation typically takes seconds or minutes compared with the hours it would take to perform the same operation manually. This process augments the way the database internal optimizers work so they make better decisions whenever that SQL statement executes in the database.
The report below shows an example where SQL execution performance was improved with the fastest SQL compared against the original. The improvement (inside red rectangle) is expressed as percentage of time saved and number of times of improvement compared with the original.
It’s easy to see the cost savings that could be realized when running database workloads in the cloud by ensuring that poorly executing SQL transactions are identified and optimized.
Business benefits of tuning SQL
- Reduces overall development and administration costs by automating SQL tuning tasks
- Reduces the Total Cost of Ownership of database investment by reducing rework in development due to performance issues.
- Helps organizations meet and exceed Service Level Agreements (SLAs)
But don’t just take our word for it. Take a look at some of these customer success stories and see how Quest’s SQL optimization technology helped these customers achieve significant gains in application performance.
1. University – US
When a large university began transitioning from its mainframe system to an enterprise resource planning (ERP) system, it turned to Quest for help in efficient writing and optimization of database code.
“Many prospective students wait until the very last minute to submit their enrolment applications, just before the midnight deadline,” explained the university’s development and reporting lead. “And a lot of enrolled students register for classes and check their schedules at the same time. So, it’s one thing to write code and get it working, but it is another thing to get the code optimized so these PeopleSoft-based programs can handle the strain of use by thousands of students.” The sheer size of the university’s databases also required top-performing SQL code. “We are converting a huge database containing detailed student records back to the 1960s,” she stated.
The Quest solution:
The university’s IT staffers carefully evaluated several tools and determined that the features and functionality of Quest’s SQL optimization technology made it worth the investment.
“I haven’t seen much code that SQL Optimizer couldn’t improve. We had one single SQL statement that was almost two pages long. We ran it through SQL Optimizer and got back the different options of how we might be able to fine tune it in development.
We noticed a real difference.”
- Development and reporting lead for a large university
2. Excellent Management Systems Inc. San Francisco, US
Excellent Management Systems was hired by a large company being investigated by the GSA. Its task was to dig into the data related to the company’s sales practices in order to support a GSA litigation defence.
The Quest solution:
Quest’s SQL optimization technology enabled Excellent Management Systems to help the law firm successfully defend its client against the GSA’s allegations. The stakes were enormously high: losing the case would have meant the client would be required to pay millions of dollars in unjustified penalties and fees, and would have put its GSA contract at risk.
“It [SQL Optimizer] always works. This thing is fantastic. It has literally saved me months, if not years, of work. I’ve had queries that people have already settled on taking three hours to run, and I was able to crank that stuff out in two minutes—I’m not kidding you -- just by running the code through SQL Optimizer to produce a better query plan.”
- John Weathington President and CEO Excellent Management Systems
3. Ithaca College – New York, US
The number of high-performance applications that the IT department needed to support was increasing. For instance, students register for courses and select housing over the Web. Because the competition for spaces in classes and dorm rooms is intense, those applications must support a very high volume of activity for brief periods. Therefore, the IT staff needed a tool to help them tune those applications for maximum performance.
At first, the IT staff struggled to tune the applications with using a more manual process, but this approach created challenges. “This turned out to be a very time-consuming process, with a lot of research, guesswork and trial and error involved,” Penfold explained. “The manual process involved some experimentation and since it was so time-consuming it was only performed on high-performance applications or SQL statements that were painfully slow.” Penfold added, “With the manual tuning method, the best I could hope to improve performance by was 20-30 percent.”
The Quest solution:
“It [Quest’s SQL optimization technology] was amazing. It reduced the time for tuning SQL statements from hours or days, to minutes. We had complex queries executed across database links that were taking an unacceptable 10 minutes, and with SQL Optimizer we were able to reduce it to .09 seconds.”
– Wendy Penfold, Programmer Analyst Ithaca College
As this blog clearly illustrates, inefficient SQL execution performance has been a challenging problem for developers and DBAs for decades and one that will become exacerbated with cloud database workloads.
The challenge for organizations and for CIOs in particular, as cloud adoption grows, is how to ensure cloud costs don’t escalate as a consequence of poorly performing database workloads. What’s needed is a combination of cloud database workload performance monitoring and diagnostics combined with automated SQL optimization that can effectively respond to these issues and ensure applications users are kept satisfied.
For more information on database workload performance monitoring and diagnostics, check out our database performance monitoring solutions.
For more information on automated SQL optimization across different database platforms, check out our database development solutions.