nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Report runs without result

dzish

Hi,

I have "a little" problem with complicated sql query. On sql client query runs about 50 minutes and returns 30 columns and about 10000 rows.

But my BirtViewer runs all the time even sql query is finished as I see on "show processlist". I have BirtViewer on Tomcat with meny gigas of memory for it. I know it's an older BIRT 2.6.2 but is there any general suggestion about it?

Find more posts tagged with

Comments

kclark

Can you post the SQL please?

dzish

<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="113983" data-time="1360179719" data-date="06 February 2013 - 12:41 PM">
Can you post the SQL please? </blockquote>
 
Hi, 
 
I can but I'm afraid it's useless. I use column-oriented relational database "InfoBright" where is better a little different optimization of query. 
 
Others columns are computed in dataset. 
 
For one day period it takes 40sec and duration of query is linear to period length. For example 10 days period takes 8min. 
My target is one month period which takes on sql client about 30minutes. 
 
SELECT 
m.channelId, 
m.channelName, 
(s.site_id + 0) AS siteId, 
CONCAT(s.site_name,'') AS siteName, 
(s.section_id + 0) AS sectionId, 
CONCAT(s.section_name,'') AS sectionName, 
(s.position_id + 0) AS positionId, 
CONCAT(s.position_name,'') AS positionName, 
(s.position_bannertype_id + 0) AS bannerTypeId, 
CONCAT(s.position_bannertype_description,'') AS bannerTypeName, 
SUM(IF(m.tagId = 21, m.impressions, 0)) AS packageImpressions, 
SUM(IF(m.tagId = 20, m.impressions, 0)) AS cptImpressions, 
0 AS floatingImpressions, 
SUM(IF(o.tagId = neexistuje, m.impressions, 0)) AS floatingImpressions, 
SUM(IF(m.tagId = 12, m.impressions, 0)) AS selfpromoImpressions, 
SUM(IF(a.defaultCampaign, m.impressions, 0)) AS defaultImpressions, 
SUM(IF(a.commercialCampaign && ISNULL(m.tagId), m.impressions, 0)) AS notagImpressions, 
SUM(IF(a.noCampaign, m.impressions, 0)) AS fallbackImpressions, 
MAX(mcUU.impressionUUs) AS impressionUUs, 
MAX(cUU.impressionUUs) AS impressionUUsForChannel 
FROM ( 
SELECT 
ch.channel_id AS channelId, 
ch.channel_name AS channelName, 
agg.ad_pk, 
agg.ad_space_pk, 
( 
SELECT tagId FROM object_tag AS o 
WHERE objectType = "CAMPAIGN" AND agg.campaign_id = o.objectId AND o.installId=ch.channel_install_id 
) AS tagId, 
SUM(fact_count) AS impressions 
FROM AdServerDwhCampaignAggregates.agg_campaign_by_ad_positions AS agg 
JOIN ad_space_channel AS adspch 
ON (adspch.ad_space_pk = agg.ad_space_pk) 
JOIN channel AS ch 
ON (ch.channel_id = adspch.channel_id) 
WHERE (agg.sql_date BETWEEN ? AND ?) 
AND (agg.ad_transaction_type_pk = 1) 
AND (ch.channel_install_id = ?) 
GROUP BY ch.channel_id, agg.ad_pk, agg.ad_space_pk, tagId 
) AS m 
JOIN ( 
SELECT agg.ad_space_pk, 
COUNT(DISTINCT agg.user_pk) AS impressionUUs 
FROM agg_user_general AS agg 
JOIN ad_space_channel AS adspch 
ON (adspch.ad_space_pk = agg.ad_space_pk) 
JOIN channel AS ch 
ON (ch.channel_id = adspch.channel_id) 
WHERE (agg.sql_date BETWEEN ? AND ?) 
AND (agg.ad_transaction_type_pk = 1) 
AND (ch.channel_install_id = ?) 
GROUP BY agg.ad_space_pk 
) AS mcUU 
ON (mcUU.ad_space_pk=m.ad_space_pk) 
JOIN ( 
SELECT 
ch.channel_id AS channelId, 
COUNT(DISTINCT agg.user_pk) AS impressionUUs 
FROM agg_user_general AS agg 
JOIN ad_space_channel AS adspch 
ON (adspch.ad_space_pk = agg.ad_space_pk) 
JOIN channel AS ch 
ON (ch.channel_id = adspch.channel_id) 
WHERE (agg.sql_date BETWEEN ? AND ?) 
AND (agg.ad_transaction_type_pk = 1) 
AND (ch.channel_install_id = ?) 
GROUP BY ch.channel_id 
) AS cUU 
ON (cUU.channelId=m.channelId) 
JOIN ( 
SELECT ad_pk, 
IF(campaign_state IN ('DEFAULT'),1,0) AS defaultCampaign, 
IF(campaign_state NOT IN ('DEFAULT') && ad.campaign_id <> 0,1,0) AS commercialCampaign, 
IF(ad.campaign_id = 0, 1, 0) AS noCampaign 
FROM ad 
) AS a 
ON a.ad_pk = m.ad_pk 
JOIN ad_space AS s 
ON s.ad_space_pk = m.ad_space_pk 
GROUP BY 
channelId, 
siteId, 
sectionId, 
positionId, 
bannerTypeId;

kclark

You could try <a class='bbc_url' href='http://digiassn.blogspot.com/2007/10/birt-progressive-viewing-during-render.html'>this solution</a> that allows you to view the current page while the rest of the report is still rendering.

dzish

<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="114021" data-time="1360260795" data-date="07 February 2013 - 11:13 AM">
You could try <a class='bbc_url' href='http://digiassn.blogspot.com/2007/10/birt-progressive-viewing-during-render.html'>this solution</a> that allows you to view the current page while the rest of the report is still rendering. </blockquote>
 
Interesting. I'll try it. 
 
My current hack is to export query data to another table and read from this table. It works but it's only fast hack.