topic excessive query breaks mid tier server in SAS Web Report Studio

excessive query breaks mid tier server

Haiyan — Wed, 13 Jan 2010 21:05:12 GMT

99% of our users use WRS to create or consume reports. We are currently at SAS EBI 9.1.3 and WRS at 3.1. The WRS is hosted using WebSphere on an AIX box. Our problem is that if a user forgets applying a filter for its query(or purposely doing a very big query or export a huge dataset), the query/export can be as big as hundreds of millions. This will unfailingly bring our web application server down for exceeding the heap size we set(3.6G). OutOfMemoryError will be complained for this crash then. This happened to us fairly frequently (once a month or more). I am just wondering if you there have this issue and this frequency, any thing you may do to prevent this happening. Thanks in advance.

-Haiyan

Re: excessive query breaks mid tier server

deleted_user — Thu, 14 Jan 2010 02:14:50 GMT

Hi Haiyan!

What I usually do is add a prompt (I prefer using the report date) before opening the report so that the data is being filtered first before the report is displayed. In effect, the query result that the users will export, if they decide to do so, will also be trimmed down. I think this is a good way for the users to be reminded to always filter the data, otherwise, they will not be able to proceed and view the report 🙂

Hope this helps

Re: excessive query breaks mid tier server

Haiyan — Fri, 15 Jan 2010 15:40:42 GMT

Thanks Murf!
Just wondering how you add that prompt before viewing the report, do you customize the WRS application yourself as admin or as report author? or is it configurable?

Regards.

Re: excessive query breaks mid tier server

AngelaHall — Fri, 15 Jan 2010 20:50:30 GMT

The out of memory error is a result of the webserver's limited memory.

I would recommend that you include a Stored Process in your information map to count the number of records returned in the query and if less than x # run the regular query, otherwise if more than x # are returned then reset the results to 1 row. This row of data could include a message (in all the character fields) "too many results returned to display on website" then the user creating the report will get a message no matter what they have selected to display.

Here is one reference on creating a stored process for use in imap: http://support.sas.com/kb/19/087.html

An example of using SQL Count function to count number of results:
http://www2.sas.com/proceedings/sugi30/257-30.pdf

Angela Hall
http://sas-bi.blogspot.com

Re: excessive query breaks mid tier server

Cristian — Wed, 20 Jan 2010 10:48:43 GMT

Hi Haiyan,

One way to avoid this is by defining the MAX_TUPLE_COUNT low enough, let's say 10000 like below

10000

LocalProperties.xml .

Also, make sure you have the last Hotfix for WRS which is now 17 since mid December.

Cheers,
Cristian

Re: excessive query breaks mid tier server

Haiyan — Wed, 20 Jan 2010 14:30:20 GMT

Thanks Cristian. Yes we do have this directive in place and the MAX_TUPLE_COUNT=10000. I applied the WRS hotfix 17 last Sunday. So I will see if this hotfix may make some difference. Again thanks for mentioning of this.

Re: excessive query breaks mid tier server

Haiyan — Wed, 20 Jan 2010 14:35:44 GMT

HI Angela,
Thank you very much for the idea. Our developer is investigating into this. We have so many maps now to work if we go this direction. But we may pick some maps that involve large volume data to include that stored process. Thanks again.

Re: excessive query breaks mid tier server

Cristian — Wed, 20 Jan 2010 16:13:56 GMT

Hi Haiyan,

You might want to review the JVM params.
In our case
-Xk32000 -Xloratio0.15
proved to give us more stability.

But generally, the OutofMemory issue has been recognised by SAS Support and they tried to address this partially in Hotfix 17.

Good luck,
Cristian