Home » Archived » BIRT » Massive queries
| Massive queries [message #62066] |
Wed, 27 July 2005 10:11  |
Eclipse User |
|
|
|
Originally posted by: benn.tilby.tangozebra.com
Hi,
Has anyone else had problems when using BIRT with massive queries spanning
many tables? I have a query that works fine up until the point where a SUM
is required on a column. But this sum computes if I take other conditions
out of the query like reference to a couple of tables. I am wondering if all
this might 'overload' BIRT. I am sure it is not the logic of my query which
is crashing BIRT, as these extra tables should not prevent the extraction of
records when combined with using the sum. And BIRT does not generate an sql
error it just goes into thinking mode and then crashes Eclipse.
The specifics wont be of any use here, but to show the query size (if that
is of any indication to the problem cause), it is:
SELECT
s.name as s_name,
r.id as referrer_id,
r.display_name as r_name,
c.id as creative_id,
c.name as c_name,
ar.booked AS r_booked,
advert_sites.booked AS s_booked,
sacs.event_supertype AS event_supertype,
sacs.event_subtype AS event_subtype,
mpe.mb_rate,
mpe.basis
FROM
adverts a,
referrers r,
creatives c,
sites s,
advert_referrer_creatives arc,
advert_referrers ar,
stats_advert_criteria_sets sacs,
stats_advert_tallies sat,
media_plan_entry mpe
LEFT JOIN advert_sites ON (a.id = advert_sites.advert_id AND s.id =
advert_sites.site_id)
WHERE sacs.id = sat.criteria_set_id
AND a.job_number = ?
AND s.id = ?
AND a.active <> 'discarded'
AND arc.advert_id = a.id
AND arc.creative_id = c.id
AND arc.referrer_id = r.id
AND arc.suppressed = 'false'
AND ar.referrer_id = r.id
AND ar.advert_id = a.id
AND r.site_id = s.id
AND mpe.site_id = s.id
AND mpe.advert_id = a.id
AND r.internal = 'no'
AND c.advert_id = a.id
AND sacs.advert_id = a.id
AND sacs.creative_id = c.id
AND sacs.event_supertype in ('c', 'v', 'g')
GROUP BY
s.id, referrer_id, creative_id, event_supertype, event_subtype
|
|
|
| Re: Massive queries [message #62137 is a reply to message #62066] |
Wed, 27 July 2005 13:44  |
| Eclipse User |
|
|
|
What type of error do you see when your query "breaks" BIRT? Did JVM run out
of memory? Have you tried starting Eclipse with increased JVM heap size?
It's certainly possible for a large query to break Birt with the 1.0
release, since Birt does all calculations in memory, which means the entire
query's result set must fit in memory. This will likely change in a future
release - the Birt team is working on adding support for larger queries by
using disk file-based processing.
regards,
Gary Xue
Birt Committer
"Benn Tilby" <benn.tilby@tangozebra.com> wrote in message
news:dc84lu$39e$1@news.eclipse.org...
> Hi,
>
> Has anyone else had problems when using BIRT with massive queries spanning
> many tables? I have a query that works fine up until the point where a SUM
> is required on a column. But this sum computes if I take other conditions
> out of the query like reference to a couple of tables. I am wondering if
all
> this might 'overload' BIRT. I am sure it is not the logic of my query
which
> is crashing BIRT, as these extra tables should not prevent the extraction
of
> records when combined with using the sum. And BIRT does not generate an
sql
> error it just goes into thinking mode and then crashes Eclipse.
>
> The specifics wont be of any use here, but to show the query size (if that
> is of any indication to the problem cause), it is:
>
> SELECT
>
> s.name as s_name,
>
> r.id as referrer_id,
>
> r.display_name as r_name,
>
> c.id as creative_id,
>
> c.name as c_name,
>
> ar.booked AS r_booked,
>
> advert_sites.booked AS s_booked,
>
> sacs.event_supertype AS event_supertype,
>
> sacs.event_subtype AS event_subtype,
>
> mpe.mb_rate,
>
> mpe.basis
>
> FROM
>
> adverts a,
>
> referrers r,
>
> creatives c,
>
> sites s,
>
> advert_referrer_creatives arc,
>
> advert_referrers ar,
>
> stats_advert_criteria_sets sacs,
>
> stats_advert_tallies sat,
>
> media_plan_entry mpe
>
> LEFT JOIN advert_sites ON (a.id = advert_sites.advert_id AND s.id =
> advert_sites.site_id)
>
> WHERE sacs.id = sat.criteria_set_id
>
> AND a.job_number = ?
>
> AND s.id = ?
>
> AND a.active <> 'discarded'
>
> AND arc.advert_id = a.id
>
> AND arc.creative_id = c.id
>
> AND arc.referrer_id = r.id
>
> AND arc.suppressed = 'false'
>
> AND ar.referrer_id = r.id
>
> AND ar.advert_id = a.id
>
> AND r.site_id = s.id
>
> AND mpe.site_id = s.id
>
> AND mpe.advert_id = a.id
>
> AND r.internal = 'no'
>
> AND c.advert_id = a.id
>
> AND sacs.advert_id = a.id
>
> AND sacs.creative_id = c.id
>
> AND sacs.event_supertype in ('c', 'v', 'g')
>
> GROUP BY
>
> s.id, referrer_id, creative_id, event_supertype, event_subtype
>
>
|
|
|
| Re: Massive queries [message #62231 is a reply to message #62137] |
Thu, 28 July 2005 06:23 |
| Eclipse User |
|
|
|
Originally posted by: benn.tilby.tangozebra.com
Thanks for this... I have now been able to try this option - increasing the
stack size - but still crashes. So I guess I am awaiting support for larger
queries - which will be good because then we can consider moving all our
reporting to BIRT.
Thanks,
BT.
"Gary Xue" <gxue@actuate.com> wrote in message
news:dc8h3v$jk7$1@news.eclipse.org...
> What type of error do you see when your query "breaks" BIRT? Did JVM run
> out
> of memory? Have you tried starting Eclipse with increased JVM heap size?
>
> It's certainly possible for a large query to break Birt with the 1.0
> release, since Birt does all calculations in memory, which means the
> entire
> query's result set must fit in memory. This will likely change in a future
> release - the Birt team is working on adding support for larger queries by
> using disk file-based processing.
>
> regards,
> Gary Xue
> Birt Committer
>
> "Benn Tilby" <benn.tilby@tangozebra.com> wrote in message
> news:dc84lu$39e$1@news.eclipse.org...
>> Hi,
>>
>> Has anyone else had problems when using BIRT with massive queries
>> spanning
>> many tables? I have a query that works fine up until the point where a
>> SUM
>> is required on a column. But this sum computes if I take other conditions
>> out of the query like reference to a couple of tables. I am wondering if
> all
>> this might 'overload' BIRT. I am sure it is not the logic of my query
> which
>> is crashing BIRT, as these extra tables should not prevent the extraction
> of
>> records when combined with using the sum. And BIRT does not generate an
> sql
>> error it just goes into thinking mode and then crashes Eclipse.
>>
>> The specifics wont be of any use here, but to show the query size (if
>> that
>> is of any indication to the problem cause), it is:
>>
>> SELECT
>>
>> s.name as s_name,
>>
>> r.id as referrer_id,
>>
>> r.display_name as r_name,
>>
>> c.id as creative_id,
>>
>> c.name as c_name,
>>
>> ar.booked AS r_booked,
>>
>> advert_sites.booked AS s_booked,
>>
>> sacs.event_supertype AS event_supertype,
>>
>> sacs.event_subtype AS event_subtype,
>>
>> mpe.mb_rate,
>>
>> mpe.basis
>>
>> FROM
>>
>> adverts a,
>>
>> referrers r,
>>
>> creatives c,
>>
>> sites s,
>>
>> advert_referrer_creatives arc,
>>
>> advert_referrers ar,
>>
>> stats_advert_criteria_sets sacs,
>>
>> stats_advert_tallies sat,
>>
>> media_plan_entry mpe
>>
>> LEFT JOIN advert_sites ON (a.id = advert_sites.advert_id AND s.id =
>> advert_sites.site_id)
>>
>> WHERE sacs.id = sat.criteria_set_id
>>
>> AND a.job_number = ?
>>
>> AND s.id = ?
>>
>> AND a.active <> 'discarded'
>>
>> AND arc.advert_id = a.id
>>
>> AND arc.creative_id = c.id
>>
>> AND arc.referrer_id = r.id
>>
>> AND arc.suppressed = 'false'
>>
>> AND ar.referrer_id = r.id
>>
>> AND ar.advert_id = a.id
>>
>> AND r.site_id = s.id
>>
>> AND mpe.site_id = s.id
>>
>> AND mpe.advert_id = a.id
>>
>> AND r.internal = 'no'
>>
>> AND c.advert_id = a.id
>>
>> AND sacs.advert_id = a.id
>>
>> AND sacs.creative_id = c.id
>>
>> AND sacs.event_supertype in ('c', 'v', 'g')
>>
>> GROUP BY
>>
>> s.id, referrer_id, creative_id, event_supertype, event_subtype
>>
>>
>
>
|
|
|
Goto Forum:
Current Time: Mon Jun 02 21:52:22 EDT 2025
Powered by FUDForum. Page generated in 0.01887 seconds |
Home