|any solution for limit runaway report queries ? [message #1049967]
||Fri, 26 April 2013 14:20
| nari noori
Registered: November 2012
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:
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 ?
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 ?
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
| Michael Williams
Registered: July 2009
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.
Add interactivity, scheduling, sharing, live Excel export, and JS integration to your BIRT app with the new, free BIRT server, BIRT iHub F-Type.
Powered by FUDForum
. Page generated in 0.01728 seconds