Eclipse Community Forums: BIRT » any solution for limit runaway report queries ?

Home » Archived » BIRT » any solution for limit runaway report queries ?

any solution for limit runaway report queries ? [message #1049967]

Fri, 26 April 2013 10:20

Eclipse User

Hi, i have embedded the BirtWebViewerExample in my spring web-application. I use birt 4.2.2 on a tomcat 6.

i have 2 questions:

QUESTION 1:
How to prevent users calling runaway report queries(sql queries that ask for millions of rows) in birt ?
My dream solution: would be that the user max can ask for 200.000 rows, and that mysql server just processes 200.000 rows,

approach 1: use input parameters to limit the result. example only let the user run reports with specific parameter values. As example preventing user to choose bigger data range parameter than 1 month or 2 months. But the weakness of this approach is that if i example have another query parameter let us say gender. You might be able to use larger date range when using other limiting query parameters.

approach 2: use limit 200.000; in sql-query....the query returns 200.000 rows, but does still run the whole query before limiting the result sent back.

approach 3: in birt report set dataset: max number of rows to fetch from data source to 200.000 rows. does the same thing as the approach above i gusss ?

What is the best solution ? is there a effective way to solve this ?

QUESTION 2:
When i run a big query,that takes more than a minute, the birt report window just stops and shows me blank result after one minute (or just show the report result that was shown last time around). The sql query still keep running in background on the mysql server.

What i want to do is to delay the timeout that seems happen after a minute, any suggestions how to figure out the problem ?

Thanx

MY CONFIGURATION:
in viewer.properties: viewer.session.timeout=0
in web.xml: BIRT_VIEWER_LOG_LEVEL set to FINEST (does not show any logging, maybe i have to change my log4j level to get logging ?)
in web.xml: session.timeout: 120 (minutes)
in DataSet in Birt Report: dataset:query timeout (in seconds): 300

[Updated on: Fri, 26 April 2013 10:30] by Moderator

Re: any solution for limit runaway report queries ? [message #1050299 is a reply to message #1049967]

Fri, 26 April 2013 21:15

Eclipse User

The answer to number 1 might depend on what database type you're using. In SQL Server, there looks like there is a "SET ROWCOUNT 200000" you could set before your select statement. For the sample database, you can do something like: "Select * from employees fetch only first 10 rows".

For number 2, my response would have been to set the last one that you have listed (the query timeout). Let me see if I can find anything else on that. I'll let you know if I find something.

Re: any solution for limit runaway report queries ? [message #1052484 is a reply to message #1050299]

Tue, 30 April 2013 02:20

Eclipse User

Thanks for your reply Michael,

Question 1: i use mysql server and i dont think it has a limiting function....it has limit but the limit is just on rows returned and not limiting the processing of query.

Question 2: hope you find a way to increase the query timeout time

Thanks

Re: any solution for limit runaway report queries ? [message #1053615 is a reply to message #1052484]

Mon, 06 May 2013 17:11

Eclipse User

im still trying to figure out how to prevent the birt report to timeout after approximately 60 seconds. (report running just stops always after 60 seconds (if its a large query), and shows no new result)

the strange thing i found out today:
The environment i am running my birt report in is on tomcat 6 with mysql on a linux.

today i ran the same application on tomcat with mysql on windows. Here i dont get any timeout, and the report keeps running.

im using the same webapp on both servers. cant see anything in the logs, hmm, any ideas ?

all ideas appreciated. thanks.

Re: any solution for limit runaway report queries ? [message #1053877 is a reply to message #1053615]

Wed, 08 May 2013 00:30

Eclipse User

It sounds like a possible bug, to me. You might log a bug for this in the bugzilla.

Limit does seem to be the only option for MySQL.

[Updated on: Wed, 08 May 2013 00:49] by Moderator

Previous Topic:	Hide Detail Row For Nested Table
Next Topic:	Table rows height problem

Goto Forum:

-=] Back to Top [=-

Current Time: Sun Apr 27 13:32:41 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter