Welcome!

Machine Learning Authors: Pat Romanski, Yeshim Deniz, Zakia Bouachraoui, Elizabeth White, Liz McMillan

Related Topics: Java IoT, Microservices Expo, Machine Learning , Agile Computing, Cloud Security, @DXWorldExpo

Java IoT: Article

Data Access Analysis Tips to Boost Performance

When database log files don’t help

If transaction times jump from less than one second to more than 60 seconds after a software upgrade, it is evident that there is a problem that needs investigation. This scenario was experienced by a customer with its time-tracking application. Our customer identified this performance regression while executing extensive performance tests prior to its production upgrade. The company's "traditional" approach of analyzing these types of problems was analyzing application and database log files as well as infrastructure metrics such as CPU, Memory and Network Throughput. Our customer couldn't find a single slow database query, nor were there any indications of an error in the log files. The infrastructure also seemed to be fine because the application, the database server and the network didn't show any bottlenecks. Analyzing the end-to-end transaction, such as the click on search, however, revealed a problem that turned out to be a "data-driven" performance issue.

Problem: Loading and Processing Large Result Sets
The following screenshot shows parts of the PurePath highlighting where most of the time is spent within the transaction:

60s is consumed by loadDataStore which iterates through a large ResultSet

The SQL Statement to retrieve time-tracking data executed in about 840ms. This doesn't really indicate a hotspot for this transaction. loadDataStore however iterates through the ResultSet of this query. Looking at the PurePath details of that method execution shows that the continuous fetches of the next result sets is the real hotspot of this transaction. Most of the time is spent in I/O to retrieve the next batch of table rows and process this data.

Root Cause: Too Much Data for Test User
After consulting with the third-party vendor of this application our customer came to the conclusion that the user account used in its load testing script had elevated privileges. When accessing a particular web page in the application this user was able to see time-tracking data from all other users in the system. The implementation of this web page was such that it processed all data accessible by the user - instead of just accessing the data needed for display, e.g, for the first page of results.

Solution: "Real" User Account for Testing
Instead of using the special privileged user account the test scripts were modified to use a user account with "normal" privileges - this also reflected the real-life scenario. This reduced the transaction response time significantly - down from 61 seconds to 73 milliseconds - as shown in the following PurePath:

Standard Users process the same request in 73ms instead of 61s

Conclusion
Unsurprisingly, the amount of data that is returned by a SQL statement can have significant impact on transaction performance. There are, however, several lessons learned:

  1. SQL Log Files didn't show this as a problem as they only reported the SQL Execution Time but not the individual Fetch and Processing times
  2. Testing needs to be done with real user accounts and not test, dummy or special privileged accounts
  3. Optimize access to large result sets by only processing what is really needed for display

If you are interested in more typical problem patterns check out the Top 8 Application Performance Landmines.

More Stories By Andreas Grabner

Andreas Grabner has been helping companies improve their application performance for 15+ years. He is a regular contributor within Web Performance and DevOps communities and a prolific speaker at user groups and conferences around the world. Reach him at @grabnerandi

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


CloudEXPO Stories
Most modern computer languages embed a lot of metadata in their application. We show how this goldmine of data from a runtime environment like production or staging can be used to increase profits. Adi conceptualized the Crosscode platform after spending over 25 years working for large enterprise companies like HP, Cisco, IBM, UHG and personally experiencing the challenges that prevent companies from quickly making changes to their technology, due to the complexity of their enterprise. An accomplished expert in Enterprise Architecture, Adi has also served as CxO advisor to numerous Fortune executives.
DevOpsSUMMIT at CloudEXPO, to be held June 25-26, 2019 at the Santa Clara Convention Center in Santa Clara, CA – announces that its Call for Papers is open. Born out of proven success in agile development, cloud computing, and process automation, DevOps is a macro trend you cannot afford to miss. From showcase success stories from early adopters and web-scale businesses, DevOps is expanding to organizations of all sizes, including the world's largest enterprises – and delivering real results. Among the proven benefits, DevOps is correlated with 20% faster time-to-market, 22% improvement in quality, and 18% reduction in dev and ops costs, according to research firm Vanson-Bourne. It is changing the way IT works, how businesses interact with customers, and how organizations are buying, building, and delivering software.
Nicolas Fierro is CEO of MIMIR Blockchain Solutions. He is a programmer, technologist, and operations dev who has worked with Ethereum and blockchain since 2014. His knowledge in blockchain dates to when he performed dev ops services to the Ethereum Foundation as one the privileged few developers to work with the original core team in Switzerland.
It cannot be overseen or regulated by any one administrator, like a government or bank. Currently, there is no government regulation on them which also means there is no government safeguards over them. Although many are looking at Bitcoin to put money into, it would be wise to proceed with caution. Regular central banks are watching it and deciding whether or not to make them illegal (Criminalize them) and therefore make them worthless and eliminate them as competition. ICOs (Initial Coin Offerings) are something most have no idea as to what it means and how you utilize it. Where is the "Stamp of Approval" or "Stamp of Legitimacy" on some of these Bitcoin websites (how do you know you are not dealing with a scammer?)
Business professionals no longer wonder if they'll migrate to the cloud; it's now a matter of when. The cloud environment has proved to be a major force in transitioning to an agile business model that enables quick decisions and fast implementation that solidify customer relationships. And when the cloud is combined with the power of cognitive computing, it drives innovation and transformation that achieves astounding competitive advantage.