Traditional web-based reporting with SAS BI tools

excessive query breaks mid tier server

Reply
Occasional Contributor
Posts: 6

excessive query breaks mid tier server

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
N/A
Posts: 0

Re: excessive query breaks mid tier server

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 Smiley Happy

Hope this helps
Occasional Contributor
Posts: 6

Re: excessive query breaks mid tier server

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.
SAS Employee
Posts: 238

Re: excessive query breaks mid tier server

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
Occasional Contributor
Posts: 6

Re: excessive query breaks mid tier server

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.
New Contributor
Posts: 2

Re: excessive query breaks mid tier server

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
Occasional Contributor
Posts: 6

Re: excessive query breaks mid tier server

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.
New Contributor
Posts: 2

Re: excessive query breaks mid tier server

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
Ask a Question
Discussion stats
  • 7 replies
  • 334 views
  • 0 likes
  • 4 in conversation