Welcome!

Article

EsProc Enables Oracle JDBC to Speed Up Retrieve Process

EsProc's New Function

Recently, a performance issue occurs in a big detail report, where the original data volume in the two data tables as it involved has exceeded 3 million rows; the data volume that the report can retrieve after filtering remains at round 2 million rows. As we expected before, by using paged retrieve method, this report would present a much higher performance. However, for the fact that the user also needs to export the data from the report, the only option is that the data retrieve can be achieved from Oracle at a time. As the JDBC of Oracle runs quite slowly, it takes least 6-7 minutes to retrieve the data of more than 2 million rows from the Oracle. In general, such performance cannot meet the expectation of the customer.

Through great efforts we have made for a long period, this issue was finally solved by a way that the retrieve time can be cut down to dozens of seconds, thus to achieve the performance as the user requires. The solution mainly involves the use of parallel mechanism of esProc, and by multiple JDBCs, to achieve the synchronization of data retrieves.

Basic Information

Hardware:

HP ProLiant DL380 G7, CPU Intel Xeon E5620*2, RAM 20G, HDD Raid5 1T

Software:

Oracle11g64-bit, esProc, CentOS 6.0, JDK 1.6u43

Data Volumes of 2 Data Tables:

VM: 100K rows, 168 cols; and 65k rows after filtering

CT: 3 million rows, 16 cols; and 1.95 million rows after filtering

Basic Idea

By utilizing the parallel mechanism of esProc, the task of retrieving data from the two Oracle tables can be divided into a number of sub-tasks, which can be executed synchronously. And then the retrieved results are merged into one for each table. Finally, the two tables with merged results are joined to return the set of results for the report.

Implementation

Main Program Script

Main Program Script

First, the database connection is created, in order to fetch the records from the two tables. Second, the data is segmented as per the number of sub-tasks (the screen shot shows the case of 16 sub-tasks), and the query range is assigned for a subroutine. Third, the subroutine is called by using callx to complete the query process. In the end, the main program is available to merge the results and produce a set of the end results which will then be returned to the report.

Subroutine Script

It is easy for the subroutine to complete the query process within a specified query range.

esProc2

 

Performance Comparison

Pre-parallel (No Parallel)

Post-parallel (Parallel)

4 Parallels

8 Parallels

16 Parallels

436s

113s

81s

43s

 

As a result, here we see a leap from 400s to 40s in the data retrieve process.

Note:

1. What is described here covers only the part on how the data retrieve process can be expedited and how the esProc is used to generate the data source for the report in a fast way. However, the description on design and presentation of report is not mentioned here.

2. When the parallel retrieve process is used, it is required to create a connection with the database by each subtask, for example, connect(conn), which can be found in the subroutine script;

3. Parallel retrieve process is suitable for the case where the Oracle resources are spare (e.g., the number of connections is still far below the upper limit). In other words, this feature can enable the report to make full use of the database resources.

More Stories By Jessica Qiu

Jessica Qiu is the editor of Raqsoft. She provides press releases for data computation and data analytics.

Latest Stories
SYS-CON Events announced today that DXWorldExpo has been named “Global Sponsor” of SYS-CON's 21st International Cloud Expo, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Digital Transformation is the key issue driving the global enterprise IT business. Digital Transformation is most prominent among Global 2000 enterprises and government institutions.
SYS-CON Events announced today that Datera, that offers a radically new data management architecture, has been named "Exhibitor" of SYS-CON's 21st International Cloud Expo ®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Datera is transforming the traditional datacenter model through modern cloud simplicity. The technology industry is at another major inflection point. The rise of mobile, the Internet of Things, data storage and Big...
Kubernetes is an open source system for automating deployment, scaling, and management of containerized applications. Kubernetes was originally built by Google, leveraging years of experience with managing container workloads, and is now a Cloud Native Compute Foundation (CNCF) project. Kubernetes has been widely adopted by the community, supported on all major public and private cloud providers, and is gaining rapid adoption in enterprises. However, Kubernetes may seem intimidating and complex ...
"Outscale was founded in 2010, is based in France, is a strategic partner to Dassault Systémes and has done quite a bit of work with divisions of Dassault," explained Jackie Funk, Digital Marketing exec at Outscale, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"We focus on SAP workloads because they are among the most powerful but somewhat challenging workloads out there to take into public cloud," explained Swen Conrad, CEO of Ocean9, Inc., in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"DivvyCloud as a company set out to help customers automate solutions to the most common cloud problems," noted Jeremy Snyder, VP of Business Development at DivvyCloud, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"We provide IoT solutions. We provide the most compatible solutions for many applications. Our solutions are industry agnostic and also protocol agnostic," explained Richard Han, Head of Sales and Marketing and Engineering at Systena America, in this SYS-CON.tv interview at @ThingsExpo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"I think DevOps is now a rambunctious teenager – it’s starting to get a mind of its own, wanting to get its own things but it still needs some adult supervision," explained Thomas Hooker, VP of marketing at CollabNet, in this SYS-CON.tv interview at DevOps Summit at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"We are still a relatively small software house and we are focusing on certain industries like FinTech, med tech, energy and utilities. We help our customers with their digital transformation," noted Piotr Stawinski, Founder and CEO of EARP Integration, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"We've been engaging with a lot of customers including Panasonic, we've been involved with Cisco and now we're working with the U.S. government - the Department of Homeland Security," explained Peter Jung, Chief Product Officer at Pulzze Systems, in this SYS-CON.tv interview at @ThingsExpo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"We're here to tell the world about our cloud-scale infrastructure that we have at Juniper combined with the world-class security that we put into the cloud," explained Lisa Guess, VP of Systems Engineering at Juniper Networks, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
Your homes and cars can be automated and self-serviced. Why can't your storage? From simply asking questions to analyze and troubleshoot your infrastructure, to provisioning storage with snapshots, recovery and replication, your wildest sci-fi dream has come true. In his session at @DevOpsSummit at 20th Cloud Expo, Dan Florea, Director of Product Management at Tintri, provided a ChatOps demo where you can talk to your storage and manage it from anywhere, through Slack and similar services with...
"We were founded in 2003 and the way we were founded was about good backup and good disaster recovery for our clients, and for the last 20 years we've been pretty consistent with that," noted Marc Malafronte, Territory Manager at StorageCraft, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"At the keynote this morning we spoke about the value proposition of Nutanix, of having a DevOps culture and a mindset, and the business outcomes of achieving agility and scale, which everybody here is trying to accomplish," noted Mark Lavi, DevOps Solution Architect at Nutanix, in this SYS-CON.tv interview at @DevOpsSummit at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"We are an IT services solution provider and we sell software to support those solutions. Our focus and key areas are around security, enterprise monitoring, and continuous delivery optimization," noted John Balsavage, President of A&I Solutions, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.