Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » any solution for limit runaway report queries ?
any solution for limit runaway report queries ? [message #1049967] Fri, 26 April 2013 14:20 Go to next message
nari noori is currently offline nari nooriFriend
Messages: 33
Registered: November 2012
Member
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 14:30]

Report message to a moderator

Re: any solution for limit runaway report queries ? [message #1050299 is a reply to message #1049967] Sat, 27 April 2013 01:15 Go to previous messageGo to next message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1663
Registered: July 2009
Senior Member

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.


Michael

Add interactivity, scheduling, sharing, live Excel export, and JS integration to your BIRT app with the new, free BIRT server, BIRT iHub F-Type.
Re: any solution for limit runaway report queries ? [message #1052484 is a reply to message #1050299] Tue, 30 April 2013 06:20 Go to previous messageGo to next message
nari noori is currently offline nari nooriFriend
Messages: 33
Registered: November 2012
Member
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 21:11 Go to previous messageGo to next message
nari noori is currently offline nari nooriFriend
Messages: 33
Registered: November 2012
Member

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 04:30 Go to previous message
Michael Williams is currently offline Michael WilliamsFriend
Messages: 1663
Registered: July 2009
Senior Member

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.


Michael

Add interactivity, scheduling, sharing, live Excel export, and JS integration to your BIRT app with the new, free BIRT server, BIRT iHub F-Type.

[Updated on: Wed, 08 May 2013 04:49]

Report message to a moderator

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


Current Time: Wed Nov 26 06:46:13 GMT 2014

Powered by FUDForum. Page generated in 0.02062 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software