|By Jessica Qiu||
|April 14, 2014 02:59 PM EDT||
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.
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.
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:
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: http://www.raqsoft.com/product-esproc
Identity is in everything and customers are looking to their providers to ensure the security of their identities, transactions and data. With the increased reliance on cloud-based services, service providers must build security and trust into their offerings, adding value to customers and improving the user experience. Making identity, security and privacy easy for customers provides a unique advantage over the competition.
Aug. 30, 2016 08:15 PM EDT Reads: 2,487
Qosmos has announced new milestones in the detection of encrypted traffic and in protocol signature coverage. Qosmos latest software can accurately classify traffic encrypted with SSL/TLS (e.g., Google, Facebook, WhatsApp), P2P traffic (e.g., BitTorrent, MuTorrent, Vuze), and Skype, while preserving the privacy of communication content. These new classification techniques mean that traffic optimization, policy enforcement, and user experience are largely unaffected by encryption. In respect wit...
Aug. 30, 2016 08:00 PM EDT Reads: 1,927
SYS-CON Events announced today that 910Telecom will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Housed in the classic Denver Gas & Electric Building, 910 15th St., 910Telecom is a carrier-neutral telecom hotel located in the heart of Denver. Adjacent to CenturyLink, AT&T, and Denver Main, 910Telecom offers connectivity to all major carriers, Internet service providers, Internet backbones and ...
Aug. 30, 2016 08:00 PM EDT Reads: 2,031
SYS-CON Events announced today that Pulzze Systems will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Pulzze Systems, Inc. provides infrastructure products for the Internet of Things to enable any connected device and system to carry out matched operations without programming. For more information, visit http://www.pulzzesystems.com.
Aug. 30, 2016 07:15 PM EDT Reads: 341
There is growing need for data-driven applications and the need for digital platforms to build these apps. In his session at 19th Cloud Expo, Muddu Sudhakar, VP and GM of Security & IoT at Splunk, will cover different PaaS solutions and Big Data platforms that are available to build applications. In addition, AI and machine learning are creating new requirements that developers need in the building of next-gen apps. The next-generation digital platforms have some of the past platform needs a...
Aug. 30, 2016 07:00 PM EDT Reads: 932
Data is an unusual currency; it is not restricted by the same transactional limitations as money or people. In fact, the more that you leverage your data across multiple business use cases, the more valuable it becomes to the organization. And the same can be said about the organization’s analytics. In his session at 19th Cloud Expo, Bill Schmarzo, CTO for the Big Data Practice at EMC, will introduce a methodology for capturing, enriching and sharing data (and analytics) across the organizati...
Aug. 30, 2016 06:15 PM EDT Reads: 367
DevOps at Cloud Expo – being held November 1-3, 2016, 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. Am...
Aug. 30, 2016 05:45 PM EDT Reads: 3,594
Traditional on-premises data centers have long been the domain of modern data platforms like Apache Hadoop, meaning companies who build their business on public cloud were challenged to run Big Data processing and analytics at scale. But recent advancements in Hadoop performance, security, and most importantly cloud-native integrations, are giving organizations the ability to truly gain value from all their data. In his session at 19th Cloud Expo, David Tishgart, Director of Product Marketing ...
Aug. 30, 2016 05:00 PM EDT Reads: 899
Fact: storage performance problems have only gotten more complicated, as applications not only have become largely virtualized, but also have moved to cloud-based infrastructures. Storage performance in virtualized environments isn’t just about IOPS anymore. Instead, you need to guarantee performance for individual VMs, helping applications maintain performance as the number of VMs continues to go up in real time. In his session at Cloud Expo, Dhiraj Sehgal, Product and Marketing at Tintri, wil...
Aug. 30, 2016 04:15 PM EDT Reads: 975
Internet of @ThingsExpo, taking place November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 19th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal and enterprise IT since the creation of the Worldwide Web more than 20 years ago. All major researchers estimate there will be tens of billions devices - comp...
Aug. 30, 2016 03:30 PM EDT Reads: 3,781
StarNet Communications Corp has announced the addition of three Secure Remote Desktop modules to its flagship X-Win32 PC X server. The new modules enable X-Win32 to safely tunnel the remote desktops from Linux and Unix servers to the user’s PC over encrypted SSH. Traditionally, users of PC X servers deploy the XDMCP protocol to display remote desktop environments such as the Gnome and KDE desktops on Linux servers and the CDE environment on Solaris Unix machines. XDMCP is used primarily on comp...
Aug. 30, 2016 03:00 PM EDT Reads: 869
SYS-CON Events announced today that StarNet Communications will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. StarNet Communications’ FastX is the industry first cloud-based remote X Windows emulator. Using standard Web browsers (FireFox, Chrome, Safari, etc.) users from around the world gain highly secure access to applications and data hosted on Linux-based servers in a central data center. ...
Aug. 30, 2016 02:30 PM EDT Reads: 963
SYS-CON Events announced today Telecom Reseller has been named “Media Sponsor” of SYS-CON's 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Telecom Reseller reports on Unified Communications, UCaaS, BPaaS for enterprise and SMBs. They report extensively on both customer premises based solutions such as IP-PBX as well as cloud based and hosted platforms.
Aug. 30, 2016 02:30 PM EDT Reads: 1,051
SYS-CON Events announced today that Adobe has been named “Bronze Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. Adobe is changing the world though digital experiences. Adobe helps customers develop and deliver high-impact experiences that differentiate brands, build loyalty, and drive revenue across every screen, including smartphones, computers, tablets and TVs. Adobe content solutions are used daily by millions of co...
Aug. 30, 2016 02:00 PM EDT Reads: 3,815
Why do your mobile transformations need to happen today? Mobile is the strategy that enterprise transformation centers on to drive customer engagement. In his general session at @ThingsExpo, Roger Woods, Director, Mobile Product & Strategy – Adobe Marketing Cloud, covered key IoT and mobile trends that are forcing mobile transformation, key components of a solid mobile strategy and explored how brands are effectively driving mobile change throughout the enterprise.
Aug. 30, 2016 01:45 PM EDT Reads: 696