Click here to close now.



esProc Optimizes the Performance of Oracle Datasource Report

New reporting tool to optimize the reporting data source preparation performance in iReport and Oracle database

Description of the Issue

Some reports in a project suffered from very low speed. Despite various iReport and Oracle database optimizations, the situation is not yet satisfying. For example, there is a detail report, involving large data volume, many (dozens of) data tables, and frequent inter-table join (including self join). This report includes inter-cell computing expressions (ratios and sum). Here are some complicated data set SQL statements from this report:

(select * from (select syb.org_abbn as syb, max(xmb.org_abbn) as xmb, sub.org_subjection_id as sub_id, oi.org_abbn as org_abb, rm.rec_notice_org_id, rm.synergic_team as xz_team, xzdw.coding_name as xz_org, l.requisition_cd as req_cd, l.requisition_id as req_id, l.note as req_note, nvl(decode(l.ops_content6, 2000200012, 'Yes', 2000200011, 'No'), '') as sflj, --too long, most part from the select clause is omitted. fromlcr l left join lcrrm on rm.requisition_id = l.master_bill_id andrm.table_type = '0' andnvl(rm.bsflag, 0) != 1
left join cos sub on l.org_id = sub.org_id andnvl(sub.bsflag, 0) != 1 left join coioi on oi.org_id = sub.org_id andnvl(oi.bsflag, 0) != 1
--too long, most part from the join is omitted. wherel.table_type = '1' andl.requisition_state = '0101020304' andnvl(l.bsflag, 0) != 1 andto_char(l.back_date, 'yyyy-MM-dd') between '2012-01-01' and '2012-04-25' group by l.requisition_id, l.note, l.requisition_type, sub.org_subjection_id, syb.org_abbreviation, rm.rec_notice_org_id, oi.org_abbreviation, --too long, most of the group by fields are omitted ) a-- main query a LEFT JOIN crviewve-- viewve ON ve.requisition_id = a.req_id

If you check these SQL statements carefully, you'll find immediately that there are too many tables associated, including a lot of self-join. Meanwhile, there are many sub query embedded in it. To make this worse, it is also associated with a view, which is very complicated.

Currently the data presentation time for this report, when querying against 4 months data volume, is 6 minutes 42 seconds. This is far from what the end-user could accept.

As mentioned before, the report has been optimized several times. The data set SQL and report expressions have gone through careful tuning process. The above data set SQL is very complicated, with no room for further optimization. Meanwhile, as real time query, the use of pre-computed intermediate table for acceleration is also not a feasible approach.

After analyzing the report we find that it involves two stages: 1) the data loading stage (data set SQL execution stage), and 2) report computation and presentation stage. The first stage requires 5 minutes, and the second stage requires more than 1 minute. The reason for the slowing running of data set SQL is caused by the extremely low efficiency of the join in two sub queries (main querya and view ve).

Thus we find a new approach for optimization: we'll mainly optimize the data set loading by improving the efficiency of SQL join. At the same time, we'll optimize the computation and presentation part.

Resolution Process

The esProc approach for resolution of this issue is as following: 1. Split the data set SQL of the report As previously mentioned, the join between the two sub queries is causing the slow running of the SQL. We use esProc to execute the SQL for two sub queries, and then complete the association in esProc with "switch" ("switch" or "join" is used accordingly) statement. After test run we find significant improvement on efficiency. esProc

2. Eliminate inter-cell computing from the report The inter-cell computing (ratios and sum) part in the original report template is moved into esProc, thus the report generation could be speed up due to the removal of grid scanning.
3. Return the result set to the report all together After all data preparation is done through esProc, the result will be returned to reporting tool all together. Once data source is received, the presentation will be done directly, without any computation (such as inter-cell computing) that might affect efficiency.

The complete codes for esProc are as following:

Solution Result

Through the above process, total report presentation time is radically reduced from the original 6 minutes 42 seconds to 57seconds - less than 1 minute. The benefit of this optimization is remarkable. This is what the end-user is happy to see.


In the process of the problem resolution, we found that the main query a and view ve in the original SQL statement requires only 10 to 40 seconds when executed in Oracle separately. However, a join between a and view ve requires several minutes. This is because Oracle cannot always find a reasonable approach when automatic execution plan is used. If human interference is required, it will be very tedious and time consuming.
esProc could improve the performance, because we know that ve is actually a dimensional table of a. Thus we can use a particular method of "switch". This allows human definition of the execution plan for complicated query. In combination with Oracle's basic query statement, it will speed up the process significantly.
About esProc:

More Stories By Jessica Qiu

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

Latest Stories
Apps and devices shouldn't stop working when there's limited or no network connectivity. Learn how to bring data stored in a cloud database to the edge of the network (and back again) whenever an Internet connection is available. In his session at 17th Cloud Expo, Ben Perlmutter, a Sales Engineer with IBM Cloudant, demonstrated techniques for replicating cloud databases with devices in order to build offline-first mobile or Internet of Things (IoT) apps that can provide a better, faster user e...
Container technology is shaping the future of DevOps and it’s also changing the way organizations think about application development. With the rise of mobile applications in the enterprise, businesses are abandoning year-long development cycles and embracing technologies that enable rapid development and continuous deployment of apps. In his session at DevOps Summit, Kurt Collins, Developer Evangelist at, examined how Docker has evolved into a highly effective tool for application del...
Cloud computing delivers on-demand resources that provide businesses with flexibility and cost-savings. The challenge in moving workloads to the cloud has been the cost and complexity of ensuring the initial and ongoing security and regulatory (PCI, HIPAA, FFIEC) compliance across private and public clouds. Manual security compliance is slow, prone to human error, and represents over 50% of the cost of managing cloud applications. Determining how to automate cloud security compliance is critical...
Internet of @ThingsExpo, taking place June 7-9, 2016 at Javits Center, New York City and Nov 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with the 18th International @CloudExpo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world and ThingsExpo New York Call for Papers is now open.
The cloud. Like a comic book superhero, there seems to be no problem it can’t fix or cost it can’t slash. Yet making the transition is not always easy and production environments are still largely on premise. Taking some practical and sensible steps to reduce risk can also help provide a basis for a successful cloud transition. A plethora of surveys from the likes of IDG and Gartner show that more than 70 percent of enterprises have deployed at least one or more cloud application or workload. Y...
With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo 2016 in New York and Silicon Valley. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be! Internet of @ThingsExpo, taking place Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 17th Cloud Expo and will feature technical sessions from a rock star conference faculty ...
We are rapidly moving to a brave new world of interconnected smart homes, cars, offices and factories known as the Internet of Things (IoT). Sensors and monitoring devices will touch every part of our lives. Let's take a closer look at the Internet of Things. The Internet of Things is a worldwide network of objects and devices connected to the Internet. They are electronics, sensors, software and more. These objects connect to the Internet and can be controlled remotely via apps and programs. ...
SYS-CON Events announced today that Alert Logic, Inc., the leading provider of Security-as-a-Service solutions for the cloud, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Alert Logic, Inc., provides Security-as-a-Service for on-premises, cloud, and hybrid infrastructures, delivering deep security insight and continuous protection for customers at a lower cost than traditional security solutions. Ful...
Today air travel is a minefield of delays, hassles and customer disappointment. Airlines struggle to revitalize the experience. GE and M2Mi will demonstrate practical examples of how IoT solutions are helping airlines bring back personalization, reduce trip time and improve reliability. In their session at @ThingsExpo, Shyam Varan Nath, Principal Architect with GE, and Dr. Sarah Cooper, M2Mi’s VP Business Development and Engineering, explored the IoT cloud-based platform technologies driving t...
We all know that data growth is exploding and storage budgets are shrinking. Instead of showing you charts on about how much data there is, in his General Session at 17th Cloud Expo, Scott Cleland, Senior Director of Product Marketing at HGST, showed how to capture all of your data in one place. After you have your data under control, you can then analyze it in one place, saving time and resources.
As organizations shift towards IT-as-a-service models, the need for managing & protecting data residing across physical, virtual, and now cloud environments grows with it. CommVault can ensure protection & E-Discovery of your data - whether in a private cloud, a Service Provider delivered public cloud, or a hybrid cloud environment – across the heterogeneous enterprise.
The Internet of Things (IoT) is growing rapidly by extending current technologies, products and networks. By 2020, Cisco estimates there will be 50 billion connected devices. Gartner has forecast revenues of over $300 billion, just to IoT suppliers. Now is the time to figure out how you’ll make money – not just create innovative products. With hundreds of new products and companies jumping into the IoT fray every month, there’s no shortage of innovation. Despite this, McKinsey/VisionMobile data...
In recent years, at least 40% of companies using cloud applications have experienced data loss. One of the best prevention against cloud data loss is backing up your cloud data. In his General Session at 17th Cloud Expo, Sam McIntyre, Partner Enablement Specialist at eFolder, presented how organizations can use eFolder Cloudfinder to automate backups of cloud application data. He also demonstrated how easy it is to search and restore cloud application data using Cloudfinder.
Just over a week ago I received a long and loud sustained applause for a presentation I delivered at this year’s Cloud Expo in Santa Clara. I was extremely pleased with the turnout and had some very good conversations with many of the attendees. Over the next few days I had many more meaningful conversations and was not only happy with the results but also learned a few new things. Here is everything I learned in those three days distilled into three short points.
DevOps is about increasing efficiency, but nothing is more inefficient than building the same application twice. However, this is a routine occurrence with enterprise applications that need both a rich desktop web interface and strong mobile support. With recent technological advances from Isomorphic Software and others, rich desktop and tuned mobile experiences can now be created with a single codebase – without compromising functionality, performance or usability. In his session at DevOps Su...