SYMPTOM:
Users may notice that the Strategy Intelligence Server in three tier or Strategy Developer in two tier mode uses up a large amount of memory when running certain reports. This may be higher than can be expected for the report, considering the data types of the columns and the number of rows returned for the report.
For example, a report of 40K rows results in a memory allocation of 500 MB. This can be seen by observing the 'Virtual Bytes' counter for the process when the report is run.
When some one or more attributes are removed from the report it is seen that there is a dramatic reduction in the memory allocated for the report even without a change in the number of rows returned.
CAUSE:
The Strategy Query Engine determines the size of the column data width returned after SQL execution by checking the information passed back from the ODBC layer. The engine compares this with the information about the column in the Strategy metadata If there is a difference between the two values, the engine assumes that the data returned from the database reflects the true value for the column width. The engine will then use this value returned from the database when allocating memory to store the results of the query execution.
It has been determined that for certain SQL generated by using database pass through functions or Strategy functions the column length for the data returned may be larger than the length of the column specified in Strategy.
In this example for Oracle, the size returned from the database as shown in the ODBC trace is the maximum length for the character data type:
select a11.CUSTOMER_ID CUSTOMER_ID,
a12.CUST_LAST_NAME CUST_LAST_NAME,
a12.CUST_FIRST_NAME CUST_FIRST_NAME,
a11.ITEM_ID ITEM_ID,
lpad(a13.ITEM_LONG_DESC,length(a13.ITEM_LONG_DESC)+((a13.ITEM_ID-1))*2) CustCol_4,
sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT))) WJXBFS1,
sum((a11.QTY_SOLD * a11.UNIT_COST)) WJXBFS2
from ORDER_DETAIL a11
join LU_CUSTOMER a12
on (a11.CUSTOMER_ID = a12.CUSTOMER_ID)
join LU_ITEM a13
on (a11.ITEM_ID = a13.ITEM_ID)
group by a11.CUSTOMER_ID,
a12.CUST_LAST_NAME,
a12.CUST_FIRST_NAME,
a11.ITEM_ID,
lpad(a13.ITEM_LONG_DESC,length(a13.ITEM_LONG_DESC)+((a13.ITEM_ID-1))*2)
M8MulPrc_32 550-3f4 ENTER SQLDescribeColW
HSTMT 00FD18F0
UWORD 3
WCHAR * 0x00A1FB50
SWORD 256
SWORD * 0x00A1FD5C
SWORD * 0x002FB604
SQLULEN * 0x00B16918
SWORD * 0x00A2371C
SWORD * 0x00A1FD54
M8MulPrc_32 550-3f4 EXIT SQLDescribeColW with return code 0 (SQL_SUCCESS)
HSTMT 00FD18F0
UWORD 3
WCHAR * 0x00A1FB50 "CUSTCOL_4"
SWORD 256
SWORD * 0x00A1FD5C (9)
SWORD * 0x002FB604 (12)
SQLULEN * 0x00B16918 (4000)
SWORD * 0x00A2371C (0)
SWORD * 0x00A1FD54 (1)
ACTION:
This is working as designed. When using functions such as lpad which can result in variable column lengths i.e. there is no fixed length for the result column, the database assigns the maximum length for that datatype (for example, varchar, 4000). To avoid loss of data and memory corruption, Strategy uses the column length provided by the ODBC layer.
To avoid excessive memory usage on the Strategy side, users should consider creating a view on the database side with the correct column width specified for the attribute column data.
