BookmarkSubscribeRSS Feed
Haiyan
Calcite | Level 5
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
7 REPLIES 7
deleted_user
Not applicable
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
Haiyan
Calcite | Level 5
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.
AngelaHall
SAS Employee
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
Haiyan
Calcite | Level 5
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.
Cristian
Calcite | Level 5
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
Haiyan
Calcite | Level 5
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.
Cristian
Calcite | Level 5
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 997 views
  • 0 likes
  • 4 in conversation