This afternoon, while I am doing some Jasper report stuff, I am puzzled by a SQL query that which takes quite a long time to execute. As the time goes, Jasper will easily get a timeout and a blank screen is waiting for me after retrieving data for about 120 seconds.
As it is not appropriate to use the original data table as the example to describe here, I tried to make up a similar scenario which should explain the cause and the solution sufficiently.
Given a table A, with five columns -- Primary Key (pk), Statistics 1A (s1a), Statistics 1B (s1b), Statistics 2A(s2a), Statistics 2B(s2b), we need to select the data out into a form that looks like -- Primary Key (pk), Statistics A(s1a or s2a), Statistics B(s2b or s2b). Essentially, it is trying to convert a row into one column but two rows. Someone may prefer to call it pivot query by the way.
Initially, naively I think the most easiest and intuitive way to do it is by two union queries. That is
select pk, s1a, s1b from table where *** union select pk, s2a, s2b from table where ***.
When the data set is small, and it only depends on the single table, it is fine. However, in my case, the data set is huge, and the union will not be two but eight, and more importantly every union section will consists of another 6 inner join tables. That's the reason why Jasper cannot retrieve what it needs on time.
In this link, as suggested by Scott, introduced a very smart way to handle this issue. What it uses is called: cross join. So basically, we use the existing table to cross join with the different type/criteria to return the expected result.
select
pk,
case
when ite = 's1' then s1a
when ite = 's2' then s2a
end as sa
case
when ite = 's1' then s1b
when ite = 's2' then s2b
end as sb
from
(
select pivoter.ite,
s1a, s1b, s2a, s2b
from
table
cross join (
select 's1' as ite from dual
union all
select 's2' as ite from dual
) pivotel
)
By using the above code, the problem will be solved by the cross join union columns.
Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this "Devops Training in Bangalore"
ReplyDeleteThe effectiveness of IEEE Project Domains depends very much on the situation in which they are applied. In order to further improve IEEE Final Year Project Domains practices we need to explicitly describe and utilise our knowledge about software domains of software engineering Final Year Project Domains for CSE technologies. This paper suggests a modelling formalism for supporting systematic reuse of software engineering technologies during planning of software projects and improvement programmes in Final Year Projects for CSE.
DeleteSoftware management seeks for decision support to identify technologies like JavaScript that meet best the goals and characteristics of a software project or improvement programme. JavaScript Training in Chennai Accessible experiences and repositories that effectively guide that technology selection are still lacking.
Aim of technology domain analysis is to describe the class of context situations (e.g., kinds of JavaScript software projects) in which a software engineering technology JavaScript Training in Chennai can be applied successfully
The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training
http://www.computertraininggeorgia.com/2012/04/10-off-pmp-course-nhatlanta-enroll.html
ReplyDeleteGo with us on the online casino BGAOC and most likely let's win. perfect gambling websites Do not miss the gambling chance to become a great player.
ReplyDeleteThank you for this wonderful post! You are sharing so much of valid information. Keep us updated with more such articles.
ReplyDeletePlacement Training in Chennai
Best Training and Placement institutes in Chennai
Oracle Training in Chennai
Oracle Training institute in chennai
Unix Training in Chennai
Unix Shell Scripting Training in Chennai
Placement Training in Tambaram
Placement Training in Velachery
Thanks to share so many intersting contents.
ReplyDeletehonor mobile service center
honor mobile service centre in chennai
honor mobile service centre
honor service center near me
honor service
Thanks for posting this information. Keep updating.
ReplyDeleteSpoken English Classes in Chennai
Spoken English in Chennai
German Classes in Chennai
Japanese Classes in Chennai
TOEFL Coaching in Chennai
Informatica Training in Chennai
Spoken English Classes in Adyar
Spoken English Classes in Velachery
Excellent post, it will be definitely helpful for many people. Keep posting more like this.
ReplyDeleteSalesforce Training in Chennai
Salesforce Course in Chennai
Salesforce Training
AngularJS Training in Chennai
ccna course in Chennai
ReactJS Training in Chennai
Tally course in Chennai
Salesforce Training in Velachery
Salesforce Training in T Nagar
Salesforce Training in OMR
Delhi Mathura Vrindavan Tour by Bus
ReplyDeleteAgra Mathura Tour Package by Bus
Delhi to Agra tour by Volvo bus
Online Bus Ticket Booking for Agra
Same Day Agra Tour
Same Day Agra Tour by Bus
Informative post indeed, I’ve being in and out reading posts regularly and I see alot of engaging people sharing things and majority of the shared information is very valuable and so, here’s my fine read.
ReplyDeleteclick here for more details
click here for new registration
click here to login
click here 4th std
click here icon
Great blog thanks for sharing Consistent and relevant content is the best ways to drive in massive amounts of traffic to your website through Search Engines. Adhuntt Media’s content creation team has got everything covered for you - from high-grade blogs that engage the reader and hit all the sweet SEO spots, unique design posts for your social media feed and professional videos for video marketing.
ReplyDeletedigital marketing company in chennai
Nice blog thanks for sharing Re-decorate your messy lawn and fix up your boring old garden with Karuna Nursery Gardens. We offer speciality Garden maintenance services in Chennai. It’s time you made a difference to your backyard.
ReplyDeleteplant nursery in chennai
Excellent blog thanks for sharing Run your salon business successfully by tying up with the best beauty shop in Chennai - The Pixies Beauty Shop. With tons of prestigious brands to choose from, and amazing offers we’ll have you amazed.
ReplyDeleteCosmetics Shop in Chennai
And also thanks for sharing this informative. Keep it Sir and I am waiting for your next post on your site blog.
ReplyDeleteBest Training Institute in Bangalore BTM. My Class Training Bangalore training center for certified course, learning on Software Training Course by expert faculties, also provides job placement for fresher, experience job seekers.
Software Training Institute in Bangalore
Thank you so much for the great and very beneficial stuff that you have shared with the world.
ReplyDeleteLearn Hadoop Training from the Industry Experts we bridge the gap between the need of the industry. Softgen Infotech provide the Best Hadoop Training in Bangalore with 100% Placement Assistance. Book a Free Demo Today.
Big Data Analytics Training in Bangalore
Tableau Training in Bangalore
Data Science Training in Bangalore
Workday Training in Bangalore
This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me..
ReplyDeleteDevOps Training | Certification in Chennai | DevOps Training | Certification in anna nagar | DevOps Training | Certification in omr | DevOps Training | Certification in porur | DevOps Training | Certification in tambaram | DevOps Training | Certification in velachery
Wonderful blog with great piece of information. Regards to your effort. Keep sharing more such blogs. Looking forward to learn more from you.
ReplyDeleteangular js training in chennai
angular js online training in chennai
angular js training in bangalore
angular js training in hyderabad
angular js training in coimbatore
angular js training
angular js online training
Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.
ReplyDeleteCyber Security Training Course in Chennai | Certification | Cyber Security Online Training Course | Ethical Hacking Training Course in Chennai | Certification | Ethical Hacking Online Training Course |
CCNA Training Course in Chennai | Certification | CCNA Online Training Course | RPA Robotic Process Automation Training Course in Chennai | Certification | RPA Training Course Chennai | SEO Training in Chennai | Certification | SEO Online Training Course
Thanks mate. I am really impressed with your writing talents and also with the layout on your weblog. Appreciate, Is this a paid subject matter or did you customize it yourself? Either way keep up the nice quality writing, it is rare to peer a nice weblog like this one nowadays. Thank you, check also event marketing and Strategies on Choosing the Best Event Planning Niche
ReplyDeleteI will share it with my other friends as the information is really very useful. Keep sharing your excellent work.African Cosmetics Online Shop
ReplyDelete