SYS-CON MEDIA Authors: Roberto Medrano, Dmitriy Stepanov, Gilad Parann-Nissany, Srinivasan Sundara Rajan, Sean Houghton

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
DevOps Summit 2015 New York, co-located with the 16th International Cloud Expo - to be held June 9-11, 2015, at the Javits Center in New York City, NY - announces that it is now accepting Keynote Proposals. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time to wait for long development cycles that produce software that is obsolete...
"Matrix is an ambitious open standard and implementation that's set up to break down the fragmentation problems that exist in IP messaging and VoIP communication," explained John Woolf, Technical Evangelist at Matrix, in this SYS-CON.tv interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
SYS-CON Events announced today Isomorphic Software, the global leader in high-end, web-based business applications, will exhibit at SYS-CON's DevOps Summit 2015 New York, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Isomorphic Software is the global leader in high-end, web-based business applications. We develop, market, and support the SmartClient & Smart GWT HTML5/Ajax platform, combining the productivity and performance of traditional desktop software ...
The 3rd International @ThingsExpo, co-located with the 16th International Cloud Expo – to be held June 9-11, 2015, at the Javits Center in New York City, NY – is now accepting Hackathon proposals. Hackathon sponsorship benefits include general brand exposure and increasing engagement with the developer ecosystem. At Cloud Expo 2014 Silicon Valley, IBM held the Bluemix Developer Playground on November 5 and ElasticBox held the DevOps Hackathon on November 6. Both events took place on the expo fl...
We are reaching the end of the beginning with WebRTC, and real systems using this technology have begun to appear. One challenge that faces every WebRTC deployment (in some form or another) is identity management. For example, if you have an existing service – possibly built on a variety of different PaaS/SaaS offerings – and you want to add real-time communications you are faced with a challenge relating to user management, authentication, authorization, and validation. Service providers will w...
The term culture has had a polarizing effect among DevOps supporters. Some propose that culture change is critical for success with DevOps, but are remiss to define culture. Some talk about a DevOps culture but then reference activities that could lead to culture change and there are those that talk about culture change as a set of behaviors that need to be adopted by those in IT. There is no question that businesses successful in adopting a DevOps mindset have seen departmental culture change, ...
There's Big Data, then there's really Big Data from the Internet of Things. IoT is evolving to include many data possibilities like new types of event, log and network data. The volumes are enormous, generating tens of billions of logs per day, which raise data challenges. Early IoT deployments are relying heavily on both the cloud and managed service providers to navigate these challenges. In her session at Big Data Expo®, Hannah Smalltree, Director at Treasure Data, discussed how IoT, Big D...
The Internet of Things is tied together with a thin strand that is known as time. Coincidentally, at the core of nearly all data analytics is a timestamp. When working with time series data there are a few core principles that everyone should consider, especially across datasets where time is the common boundary. In his session at Internet of @ThingsExpo, Jim Scott, Director of Enterprise Strategy & Architecture at MapR Technologies, discussed single-value, geo-spatial, and log time series dat...
The Internet of Things promises to transform businesses (and lives), but navigating the business and technical path to success can be difficult to understand. In his session at @ThingsExpo, Sean Lorenz, Technical Product Manager for Xively at LogMeIn, demonstrated how to approach creating broadly successful connected customer solutions using real world business transformation studies including New England BioLabs and more.
The security devil is always in the details of the attack: the ones you've endured, the ones you prepare yourself to fend off, and the ones that, you fear, will catch you completely unaware and defenseless. The Internet of Things (IoT) is nothing if not an endless proliferation of details. It's the vision of a world in which continuous Internet connectivity and addressability is embedded into a growing range of human artifacts, into the natural world, and even into our smartphones, appliances, a...
The Internet of Things will put IT to its ultimate test by creating infinite new opportunities to digitize products and services, generate and analyze new data to improve customer satisfaction, and discover new ways to gain a competitive advantage across nearly every industry. In order to help corporate business units to capitalize on the rapidly evolving IoT opportunities, IT must stand up to a new set of challenges. In his session at @ThingsExpo, Jeff Kaplan, Managing Director of THINKstrateg...
Fundamentally, SDN is still mostly about network plumbing. While plumbing may be useful to tinker with, what you can do with your plumbing is far more intriguing. A rigid interpretation of SDN confines it to Layers 2 and 3, and that's reasonable. But SDN opens opportunities for novel constructions in Layers 4 to 7 that solve real operational problems in data centers. "Data center," in fact, might become anachronistic - data is everywhere, constantly on the move, seemingly always overflowing. Net...
DevOps Summit 2015 New York, co-located with the 16th International Cloud Expo - to be held June 9-11, 2015, at the Javits Center in New York City, NY - announces that it is now accepting Keynote Proposals. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time to wait for long development cycles that produce software that is obsolete...
WebRTC defines no default signaling protocol, causing fragmentation between WebRTC silos. SIP and XMPP provide possibilities, but come with considerable complexity and are not designed for use in a web environment. In his session at @ThingsExpo, Matthew Hodgson, technical co-founder of the Matrix.org, discussed how Matrix is a new non-profit Open Source Project that defines both a new HTTP-based standard for VoIP & IM signaling and provides reference implementations.
The 3rd International Internet of @ThingsExpo, co-located with the 16th International Cloud Expo - to be held June 9-11, 2015, at the Javits Center in New York City, NY - announces that its Call for Papers is now open. The Internet of Things (IoT) is the biggest idea since the creation of the Worldwide Web more than 20 years ago.