Complex MySQL Statement in BIRT

maltpeter1

<p>Hi all together,</p><p> </p><p>we're using Birt as a Tool for prividing several reports in our company. We have have a ticket-system named JIRA, from where we get the data for the reports.</p><p> </p><p>In these reports we also will show the booked times and we ne to aggregate that time over many tickets.</p><p>We use the database from this ticketsystem to get the needed information. As a result we have a veeeery complex SQL Statement over 200lines with many subqueries and joins.</p><p>In out test instance the reports worked with a bad performance (20s and longer to load a report). In out productive instance it doesn't work, because there is too much data in it an the Statement gets a timeout after more than 10minutes.</p><p> </p><p>We thought about usind temporary tables to splitt the whole statements in little fragements that are more performant, but I've read in other forums, that temporary tables are not supportet in BIRT.</p><p> </p><p>Does somebody of here now, how we can handle this Problem?</p><p>I do really need your help.</p><p> </p><p> </p>

micajblock

<p>You are asking for opinions here. I suspect you might get many. Bottom line is it up to you decide what is best for you. The most important thing to understand is that you need to get the data you need for the report in the most efficient manner.</p><p>Obviously your database is designed for the transactional ticket system and not for reporting. </p><p>Your solutions depend on what you are willing to do.</p><p>First I would try to optimize the SQL.</p><p>With commercial Actuate BIRT we have a concept pf a Data Object that will cache the data for the query and then reports can run against the cache (think mini data mart).</p><p>If the database/SQL cannot perform then you might want to consider moving the data (using an ETL process) to another database that os more optimized for reporting. </p>