[harsha.galla]

Thanks isomorphic, i misunderstood your statement. The latest POI library (3.8 version) in fact supports streaming. When will this be available in smartGWT.

[Isomorphic]

We don't have an ETA - use Feature Sponsorship to get a commitment to a date if you need one.

[harsha.galla]

Isomorphic,

I debugged the settings that happen at Oracle jdbc driver level and following is the log file from oracle jdbc jar.

un 18, 2012 7:34:52 PM oracle.jdbc.pool.OracleDataSource getPhysicalConnection
FINE: OracleDataSource.getPhysicalConnection(prop={user=xxxxx, password=xxxxxx, connection_url=jdbc:oracle:thin:@//***********************:1521/xxxxxxxxx})
Jun 18, 2012 7:34:52 PM oracle.jdbc.driver.OracleDriver connect
FINE: OracleDriver.connect(url=jdbc:oracle:thin:@//***********************:1521/xxxxxx, info)
Jun 18, 2012 7:34:52 PM oracle.jdbc.driver.PhysicalConnection <init>
FINE: PhysicalConnection.PhysicalConnection(ur="jdbc:oracle:thin:@//***********************:1521/xxxxxx", us="xxxxx", p="xxxxxx", db="//***********************:1521/xxxxxx", info)
Jun 18, 2012 7:34:52 PM oracle.jdbc.driver.PhysicalConnection initialize
FINE: PhysicalConnection.initialize(ur="jdbc:oracle:thin:@//***********************:1521/xxxxxx", us="xxxxx", access)
Jun 18, 2012 7:34:52 PM oracle.jdbc.driver.PhysicalConnection initialize
FINE: PhysicalConnection.initialize(ur, us):return
Jun 18, 2012 7:34:52 PM oracle.jdbc.driver.PhysicalConnection needLine
FINE: PhysicalConnection.needLine()--no return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection setAutoCommit
INFO: PhysicalConnection.setAutoCommit(autoCommit=true)
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection needLine
FINE: PhysicalConnection.needLine()--no return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection setAutoCommit
INFO: PhysicalConnection.setAutoCommit(autoCommit): return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection <init>
FINE: PhysicalConnection.PhysicalConnection(ur, us, p, db, info):return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleDriver connect
FINE: OracleDriver.connect(url,info):return
Jun 18, 2012 7:34:55 PM oracle.jdbc.pool.OracleDataSource getPhysicalConnection
FINE: OracleDataSource.getPhysicalConnection(Properties): returned oracle.jdbc.driver.T4CConnection@568bf3ec
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection isClosed
FINE: PhysicalConnection.isClosed() returned true
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection createStatement
FINE: PhysicalConnection.createStatement(resultSetType=-1, resultSetConcurrency=-1)
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement <init>
FINE: OracleStatement.OracleStatement(conn, batchValue=1, rowPrefetchValue=10, UserResultSetType=-1)
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection needLine
FINE: PhysicalConnection.needLine()--no return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection getDefaultFixedString
INFO: PhysicalConnection.getDefaultFixedString() returning false
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement <init>
FINE: OracleStatement.OracleStatement(c, batch_value, UserResultSetType):return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection createStatement
FINE: PhysicalConnection.createStatement(resultSetType, resultSetConcurrency):return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement execute
FINE: OracleStatement.execute(sql)
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement execute
FINE: OracleStatement.execute(sql):return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement sendBatch
FINE: OracleStatement.sendBatch()
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleSql initialize
FINE: OracleSql.initialize(select 1 from dual)
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleSql initialize
FINE: OracleSql.initialize:return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleSql computeBasicInfo
FINE: OracleSql.computeBasicInfo(select 1 from dual)
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleSql computeBasicInfo
FINE: OracleSql.computeBasicInfo:return: sqlKind = 0 parameterCount = 0
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement doExecuteWithTimeout
FINE: OracleStatement.doExecuteWithTimeout() needToPrepareDefineBuffer = true
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection needLine
FINE: PhysicalConnection.needLine()--no return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection registerHeartbeat
FINE: PhysicalConnection.registerHeartbeat()
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement executeMaybeDescribe
FINE: OracleStatement.execute_maybe_describe() rowPrefetchChanged = false, needToParse = true, needToPrepareDefineBuffer = true, columnsDefinedByUser = false
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement executeMaybeDescribe
FINE: OracleStatement.execute_maybe_describe():return validRows = 1, needToPrepareDefineBuffer = false 
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement doExecuteWithTimeout
FINE: OracleStatement.doExecuteWithTimeout():return validRows = 1, needToPrepareDefineBuffer = false
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection createStatement
FINE: PhysicalConnection.createStatement(resultSetType=-1, resultSetConcurrency=-1)
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement <init>
FINE: OracleStatement.OracleStatement(conn, batchValue=1, rowPrefetchValue=10, UserResultSetType=-1)
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection needLine
FINE: PhysicalConnection.needLine()--no return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection getDefaultFixedString
INFO: PhysicalConnection.getDefaultFixedString() returning false
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement <init>
FINE: OracleStatement.OracleStatement(c, batch_value, UserResultSetType):return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection createStatement
FINE: PhysicalConnection.createStatement(resultSetType, resultSetConcurrency):return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement setFetchSize
INFO: OracleStatement.setFetchSize(rows=0)
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement setPrefetchInternal
FINE: OracleStatement.setPrefetchInternal(new_value=0, setRowPrefetch=false, statement=true)
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection getDefaultRowPrefetch
INFO: PhysicalConnection.getDefaultRowPrefetch() returned 10
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement setPrefetchInternal
FINE: OracleStatement.setPrefetchInternal(new_value, setRowPrefetch, statement):return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement executeQuery
INFO: OracleStatement.executeQuery(sql) needToPrepareDefineBuffer = true
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement sendBatch
FINE: OracleStatement.sendBatch()
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleSql initialize
FINE: OracleSql.initialize(SELECT COUNT(*) FROM 
                AIRPORT_DIM
             WHERE 
                (('1'='1')) AND AIRPORT_NAME IS NOT NULL
            )
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleSql initialize
FINE: OracleSql.initialize:return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleSql computeBasicInfo
FINE: OracleSql.computeBasicInfo(SELECT COUNT(*) FROM 
                AIRPORT_DIM
             WHERE 
                (('1'='1')) AND AIRPORT_NAME IS NOT NULL
            )
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleSql computeBasicInfo
FINE: OracleSql.computeBasicInfo:return: sqlKind = 0 parameterCount = 0
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement doExecuteWithTimeout
FINE: OracleStatement.doExecuteWithTimeout() needToPrepareDefineBuffer = true
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection needLine
FINE: PhysicalConnection.needLine()--no return
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.PhysicalConnection registerHeartbeat
FINE: PhysicalConnection.registerHeartbeat()
Jun 18, 2012 7:34:55 PM oracle.jdbc.driver.OracleStatement executeMaybeDescribe
FINE: OracleStatement.execute_maybe_describe() rowPrefetchChanged = false, needToParse = true, needToPrepareDefineBuffer = true, columnsDefinedByUser = false
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement executeMaybeDescribe
FINE: OracleStatement.execute_maybe_describe():return validRows = 1, needToPrepareDefineBuffer = false 
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement doExecuteWithTimeout
FINE: OracleStatement.doExecuteWithTimeout():return validRows = 1, needToPrepareDefineBuffer = false
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleResultSetImpl isBeforeFirst
FINE: OracleResultSetImpl.isBeforeFirst: return: true
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleResultSetImpl isAfterLast
FINE: OracleResultSetImpl.isAfterLast: return: false
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleResultSetImpl next
FINE: OracleResultSetImpl.next(): closed=false
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleResultSetImpl getMetaData
INFO: OracleResultSetImpl.getMetaData()
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleResultSetMetaData <init>
FINE: OracleResultSetMetaData.OracleResultSetMetaData(conn, stmt)
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleResultSetMetaData <init>
FINE: OracleResultSetMetaData.OracleResultSetMetaData:return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleResultSetImpl getObject
INFO: OracleResultSetImpl.getObject(columnIndex=1)
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleResultSetImpl next
FINE: OracleResultSetImpl.next(): closed=false
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.PhysicalConnection needLine
FINE: PhysicalConnection.needLine()--no return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement endOfResultSet
FINE: OracleStatement.end_of_result_set()
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement clearDefines
FINE: OracleStatement.clearDefines()
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement clearDefines
FINE: OracleStatement.clearDefines():return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleResultSetImpl close
INFO: OracleResultSetImpl.close()
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement closeOrCache
FINE: OracleStatement.closeOrCache(null)
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement hardClose
FINE: OracleStatement.hardClose(closeCursor)
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement alwaysOnClose
FINE: OracleStatement.alwaysOnClose()
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.PhysicalConnection isClosed
FINE: PhysicalConnection.isClosed() returned true
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.PhysicalConnection needLine
FINE: PhysicalConnection.needLine()--no return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement alwaysOnClose
FINE: OracleStatement.alwaysOnClose : return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.PhysicalConnection isClosed
FINE: PhysicalConnection.isClosed() returned true
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement hardClose
FINE: OracleStatement.hardClose : return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement closeOrCache
FINE: OracleStatement.closeOrCache : return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.PhysicalConnection createStatement
FINE: PhysicalConnection.createStatement(resultSetType=1004, resultSetConcurrency=1007)
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement <init>
FINE: OracleStatement.OracleStatement(conn, batchValue=1, rowPrefetchValue=10, UserResultSetType=1004)
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.PhysicalConnection needLine
FINE: PhysicalConnection.needLine()--no return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.PhysicalConnection getDefaultFixedString
INFO: PhysicalConnection.getDefaultFixedString() returning false
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.ResultSetUtil needIdentifier
INFO: ResultSetUtil.needIdentifier(typeCode=3): return: false
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement <init>
FINE: OracleStatement.OracleStatement(c, batch_value, UserResultSetType):return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.PhysicalConnection createStatement
FINE: PhysicalConnection.createStatement(resultSetType, resultSetConcurrency):return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement setFetchSize
INFO: OracleStatement.setFetchSize(rows=14001)
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement setPrefetchInternal
FINE: OracleStatement.setPrefetchInternal(new_value=14001, setRowPrefetch=false, statement=true)
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement setPrefetchInternal
FINE: OracleStatement.setPrefetchInternal(new_value, setRowPrefetch, statement):return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement executeQuery
INFO: OracleStatement.executeQuery(sql) needToPrepareDefineBuffer = true
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement sendBatch
FINE: OracleStatement.sendBatch()
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleSql initialize
FINE: OracleSql.initialize(SELECT 
                AIRPORT_IATA_CODE, AIRPORT_NAME
             FROM 
                AIRPORT_DIM
             WHERE 
                (('1'='1')) AND AIRPORT_NAME IS NOT NULL
             ORDER BY 
                AIRPORT_IATA_CODE
            )
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleSql initialize
FINE: OracleSql.initialize:return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleSql computeBasicInfo
FINE: OracleSql.computeBasicInfo(SELECT 
                AIRPORT_IATA_CODE, AIRPORT_NAME
             FROM 
                AIRPORT_DIM
             WHERE 
                (('1'='1')) AND AIRPORT_NAME IS NOT NULL
             ORDER BY 
                AIRPORT_IATA_CODE
            )
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleSql computeBasicInfo
FINE: OracleSql.computeBasicInfo:return: sqlKind = 0 parameterCount = 0
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement doScrollStmtExecuteQuery
FINE: OracleStatement.doScrollStmtExecuteQuery()
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement doExecuteWithTimeout
FINE: OracleStatement.doExecuteWithTimeout() needToPrepareDefineBuffer = true
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.PhysicalConnection needLine
FINE: PhysicalConnection.needLine()--no return
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.PhysicalConnection registerHeartbeat
FINE: PhysicalConnection.registerHeartbeat()
Jun 18, 2012 7:34:56 PM oracle.jdbc.driver.OracleStatement executeMaybeDescribe
FINE: OracleStatement.execute_maybe_describe() rowPrefetchChanged = true, needToParse = true, needToPrepareDefineBuffer = true, columnsDefinedByUser = false
Jun 18, 2012 7:34:57 PM oracle.jdbc.driver.T4CStatement allocateTmpByteArray
SEVERE: oracle.jdbc.driver.T4CStatement.allocateTmpByteArray : Re-allocate byte array of size : 50
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleStatement executeMaybeDescribe
FINE: OracleStatement.execute_maybe_describe():return validRows = 13964, needToPrepareDefineBuffer = false 
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleStatement doExecuteWithTimeout
FINE: OracleStatement.doExecuteWithTimeout():return validRows = 13964, needToPrepareDefineBuffer = false
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.ScrollableResultSet <init>
INFO: ScrollableResultSet.ScrollableResultSet(scrollStmt=null, rset=oracle.jdbc.driver.OracleResultSetImpl@3b756456, type=1004, update=1007
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.ScrollableResultSet needIdentifier
FINE: ScrollableResultSet.needIdentifier(type=1004, concur=1007)
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleResultSetCacheImpl <init>
FINE: OracleResultSetCacheImpl(5)
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleResultSetCacheImpl <init>
FINE: OracleResultSetCacheImpl:OracleResultSetCacheImpl(width):return
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleResultSetCacheImpl <init>
FINE: OracleResultSetCacheImpl()
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleStatement doScrollStmtExecuteQuery
FINE: OracleStatement.doScrollStmtExecuteQuery():return
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.ScrollableResultSet absolute
INFO: ScrollableResultSet.absolute(row=1)
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleResultSetImpl next
FINE: OracleResultSetImpl.next(): closed=false
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleResultSetImpl privateGetBytes
INFO: OracleResultSetImpl.privateGetBytes(columnIndex=1)
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleResultSetImpl privateGetBytes
INFO: OracleResultSetImpl.privateGetBytes(columnIndex=2)
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.ScrollableResultSet setFetchSize
INFO: ScrollableResultSet.setFetchSize(rows=14001)
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleResultSetImpl setFetchSize
INFO: ResultSet.setFetchSize(rows=14001)
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleStatement setPrefetchInternal
FINE: OracleStatement.setPrefetchInternal(new_value=14001, setRowPrefetch=false, statement=false)
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleStatement setPrefetchInternal
FINE: OracleStatement.setPrefetchInternal(new_value, setRowPrefetch, statement):return
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.ScrollableResultSet isBeforeFirst
INFO: ScrollableResultSet.isBeforeFirst()
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.ScrollableResultSet isAfterLast
INFO: ScrollableResultSet.isAfterLast()
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.ScrollableResultSet getRow
INFO: ScrollableResultSet.getRow()
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.ScrollableResultSet getMetaData
INFO: ScrollableResultSet.getMetaData()
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleResultSetMetaData <init>
FINE: OracleResultSetMetaData.OracleResultSetMetaData(conn, stmt, ColIndex:0)
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleResultSetMetaData <init>
FINE: OracleResultSetMetaData.OracleResultSetMetaData:return
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.ScrollableResultSet getObject
INFO: ScrollableResultSet.getObject(columnIndex=1)
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.ScrollableResultSet getObject
INFO: ScrollableResultSet.getObject(columnIndex=1, map={})
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.ScrollableResultSet getOracleObject
INFO: ScrollableResultSet.getOracleObject(columnIndex=1): currentRow=1
Jun 18, 2012 7:36:38 PM oracle.jdbc.driver.OracleResultSetImpl getMetaData
INFO: OracleResultSetImpl.getMetaData()

If you see, the fetch size here is being set to 14001, which is nothing but the end row we set on dsRequest + 1. So endRow is directly setting the fetchSize on the query, which shouldn't be the case.

We would like to get results which are of size 1000 to 2000 rows faster but not by actually setting the endRow to a very high number, as very high fetchSize is not advised(implications on network etc). So we would like to set fetchSize say 200 but want the list grid paging to fetch first 1000 rows. List Grid page size is directly impacting the fetchSize here, which shouldn't be the case. In a normal web application jdbc driver provides capability for such a thing and we feel SmartGWT too needs to support it.

As per your previous suggestions, we tried setting the following parameter supported by oracle: defaultRowPrefetch

However we see the following error in the logs:

2012-06-18 19:32:25,563 [81-1] INFO  DataTools - [builtinApplication.retrieveClientID] setProperties: couldn't set:
{
    defaultRowPrefetch:"No such property"
}

Pleas provide next steps to address this.

Thanks.

[harsha.galla]

Isomorphic,

Any update on this ??

[Isomorphic]

You posted a bunch of new information that it takes time to analyze yesterday, so no, no update at this time.

[Isomorphic]

We're not following your reasoning here:

If you see, the fetch size here is being set to 14001, which is nothing but the end row we set on dsRequest + 1. So endRow is directly setting the fetchSize on the query, which shouldn't be the case.

We would like to get results which are of size 1000 to 2000 rows faster but not by actually setting the endRow to a very high number, as very high fetchSize is not advised(implications on network etc).

Rows 1-14000 need to be fetched in order to do an export. So we ask the DB to grab them all once so that it does not, for example, stream them in batches of 1000 as they are requested, which would presumably cause a performance problem.

So it appears we are already doing the right thing and a large batch size like this would not be expected to cause a performance problem.

If you've found Oracle docs indicating that large batch sizes are a bad idea *when the use case is retrieving all data*, let us know. Otherwise, this again is probably looking in the wrong place for the source of your unreproducible performance problem.

[harsha.galla]

Isomorphic,

The log I have provided here is not for an export option. It's for a normal List Grid Fetch. In this scenario we just query for a dimensional table which has fixed rows of around 13800 rows and which we are sure is not gonna change. Hence we have set endRow to 14000, like as you said it would get in one batch and improve performance significantly.

However our problem lies here:

99.99% of the data fetches we do are not from dimensional tables which are neither small nor static in nature. Lots of data is added to the tables on a regular basis and the table size would run into some hundreds of GB's (100-200GB). As a side note, our database is the tune of some 4-5 TB. So user has a interface to generate dynamic reports using this data. We have no problem with the listgrid as we set the dataPageSize to 1000.

But we provide export capability to the user where he/she can download the whole data that would satisfy the criteria he enters (should support 4-6 lakh rows, 1 million would be great :)) and do their analysis offline (i.e without using our tool). As the user criteria is dynamic, there is no way for us to tell what the endRow needs to be set. Also, setting the endRow limits the rows to that. If I set endRow to 50000, the export feature gets only the first 50000 rows. To get the whole data set for the criteria I shouldn't set the endRow, in which case it uses the default oracle fetchSize which is 10, and you can imagine how long it would take to get say 1 lakh rows, with almost like 10,000 round trips between database and application.

So mixing up endRow and fetchSize is the problem here for us. Hope I'm clear, let me know if you still have some concerns.

As for the oracle docs, I will post them as and when I find them. I remember to have read that higher fetch size would need higher cache size in memory. Also with bigger data packets, highly possible that network would be clogged.

Thanks.

[harsha.galla]

Isomorphic,

Please find the details for memory-management for Oracle JDBC in the following white-paper from Oracle.

Page number 2 has the details.

http://www.oracle.com/technetwork/database/enterprise-edition/memory.pdf

Thanks.

[Isomorphic]

The fetchSize is not based on the endRow, it's based on the endRow minus the startRow.

So in the case of exporting all results, setting a large endRow, as we've suggested, sets a very large fetchSize and is efficient.

In the case of paging through a large dataset, the fetchSize is based on the request amount of rows and is again efficient.

Once again, there seems to nothing to change on our end and we have already made extensive efforts trying to reproduce a performance problem and found none. If you want to open this issue again, please provide a clear, unambiguous and easily reproduced problem with SmartGWT.

[harsha.galla]

Isomorphic,

1) What fetchSize (or endRow) do you set for an export feature which doesn't know what the endRow is ?

2) Please refer to the white-paper (page 2) in my previous post on why huge fetch size is not advised. Irrespective of the implications of fetchSize, I don't want to limit rows by setting a endRow value. This is not definitely a good solution.

3) I would say mixing up endRow, fetchSize is a wrong idea. Both have their own purpose. The way I understand it:

endRow: It's something like rownum in a query where you only want a few rows lying in the range to be fetched.

fetchSize: It's all about how you fetch what you want, whether in a single trip or in multiple trips from the database (implications on network, memory etc).



Thanks.

[dzarras]

I'm following this thread carefully because my company will shortly have the same end-user requirements...

1) What fetchSize (or endRow) do you set for an export feature which doesn't know what the endRow is ?

That is precisely the correct question.

For what it's worth, when we moved from MySQL to Oracle, we noticed all kinds of performance problems that ultimately traced back to Oracle attempting to be smarter-than-us with regards to defaultRowPrefetch and fetchSize. It's been a small nightmare.

MySQL's philosophy seemed to be to send query result rows as fast as the client would take them and would perform that way every single time. Oracle's philosophy is... we concluded that we have no clue what their philosophy is. The server sends rows when it feels like doing so. Setting large fetch sizes helped, sometimes, but in very unpredictable ways. We've made numerous API changes to our codebase to allow for tweaking this parameter under different circumstances -- it's been a mess. Trying to resolve this with Oracle's engineers has been unproductive. Alas, we're committed to Oracle at this point for a host of other reasons.

Harsha.galla, I'd suggest you write a tiny JDBC client-server test case app outside of SmartGWT that executes your query and then clocks the walltime required to send all the rows to the client. Try to do it in a way that you can run it repeatedly without making use of Oracle's caches, and maybe come up with some kind of average rowsAtClient/second statistic over 30+ test cases (considering that you may get a different number of rows back each time). This would at least confirm, or rule out, funky behavior on Oracle's behalf.

[harsha.galla]

dzarras,

We use Oracle extensively and have not faced any problems what-so-ever. We have a very huge database and our queries are extremely complex. Also, in one of our application (which is purely J2EE based) we use fetchSize effectively, no issues here.

Our observations: A listgrid query with huge page size say 50,000 returns in less than 5 seconds, whereas when a export is done for the same it takes fore-ever to return (more than 20 minutes). The only difference we see is, in case of ListGrid (due to the setting page size 50000) the endRow is set to 50000 which in turn sets the fetchSize and so it comes out pretty fast. In case of export there is no end row hence it should be using the default fetch size of 10 which will take forever to get back the results.

Irrespective of the performance for export, endRow and fetchSize are two parameters which cannot be made dependent. endRow is a query parameter while fetchSize is a driver parameter. Also, fetch size depends on a lot of factors like the size of packets your network can hold, the amount of memory you have etc. So it will vary from user to user. People having huge network bandwidth between application and database, and huge application memory can afford higher fetch size while the others cannot. The correct mode of operation would be to leave the fetch size manipulation to individual users (who are the best judges of their network and memory).

Jdbc logs already shows endRow is in-fact setting the fetch Size when a fetch happens on a list grid, will create a sample test case to validate that export is in-fact using default fetch size of 10. :)

Thanks.

[Isomorphic]

If there is no startRow or endRow on a DSRequest, as is the case with an export, we do not change the fetchSize on the ResultSet. If you're seeing a fetchSize of 10 in this case, this must be Oracle's default or whatever you have set on the driver. If you want to modify this value, do so via the driver.

[harsha.galla]

Isomorphic,

Please find the fetchSizes for various use cases:

Execute a fetch on datasource (with no endRow set) :

Jun 20, 2012 10:04:17 PM oracle.jdbc.driver.OracleStatement setFetchSize
INFO: OracleStatement.setFetchSize(rows=0)

When endRow is explicitly set on dsRequest of List Grid through DMI:

Jun 20, 2012 10:07:43 PM oracle.jdbc.driver.OracleStatement setFetchSize
INFO: OracleStatement.setFetchSize(rows=14001)

Count(*) query that gets fired for a listgrid with page size 2000 :

Jun 20, 2012 10:13:41 PM oracle.jdbc.driver.OracleStatement setFetchSize
INFO: OracleStatement.setFetchSize(rows=0)

List grid with page size 2000:

Jun 20, 2012 10:15:50 PM oracle.jdbc.driver.OracleStatement setFetchSize
INFO: OracleStatement.setFetchSize(rows=2001)

Export with endRow on dsRequest 50000 :

Jun 20, 2012 10:20:49 PM oracle.jdbc.driver.OracleStatement setFetchSize
INFO: OracleStatement.setFetchSize(rows=50001)

Export with no endRow specified on DSRequest:

Jun 21, 2012 12:25:23 AM oracle.jdbc.driver.OracleStatement setFetchSize
INFO: OracleStatement.setFetchSize(rows=0)

So export in the last case is happening with fetch size of 10 as the defaultRowPrefetch for oracle jdbc driver is 10.

As mentioned in my earlier posts I get error when I set the property on the driver as follows:

getGlobal().put("sql.Oracle.driver.defaultRowPrefetch",1000);

eroor i get:

2012-06-18 19:32:25,563 [81-1] INFO  DataTools - [builtinApplication.retrieveClientID] setProperties: couldn't set:
{
    defaultRowPrefetch:"No such property"
}

Thanks.

[Isomorphic]

Yes, once again, fetchSize is set as endRow-startRow if startRow/endRow are set, otherwise, we do not set it. In the latter case the driver seems to report this as 0. All of your logs are consistent with this (correct) behavior.

As far as setting the default fetch size, we don't know where "defaultRowPrefetch" came from, but you should consult Oracle docs for how this is set. It is most likely settable completely separately from SmartGWT.