[harsha.galla]

Isomorphic,

As per oracle docs:

http://docs.oracle.com/cd/B19306_01/java.102/b14355/oraperf.htm#i1043756

defaultRowPrefetch is a property that enables you to set the number of rows to prefetch into the client while a result set is being populated during a query. This feature reduces the number of round trips to the server. It is similar to fetch size setting on JDBC.

As per your response here:

http://forums.smartclient.com/showpost.php?p=83826&postcount=8

We are trying to set the property.

Thanks.

[Isomorphic]

The Oracle docs indicate this is a per-Connection setting, you have tried to set it on the Driver - not the same thing.

We don't currently support a way to set properties on a per-Connection basis.

If you can't figure out how to set this directly (not involving SmartGWT),
this example shows how you could get access to the current Connection.

[harsha.galla]

Thanks Isomorphic,

I will try to create similar implementation for Export and see if setFetchSize on prepared Statement works.

Since the existing export works automatically, if I have to take the approach as mentioned in the sample , can you guide me on the right approach so that it works for all types of export formats.

[Isomorphic]

Not sure what you mean - see the QuickStart Guide for how to inject DMI business logic while still allowing the default behavior to occur.

[harsha.galla]

Isomorphic,

As suggested I'm trying to manually run a query as done in this:

http://www.smartclient.com/smartgwte...jdbcOperations

But when I try to get the sql using SQLDataSource.getSQLClause(sqlClauseType.All,dsRequest) it still has some $rawValue parameters which are not replaced in the query. How do I get the whole query including the rawValues. (These are the parameters added through addToTemplateContext).

Another observation:

Few methods are missing in the com.isomorphic.util.DataTools class. Can you update the javadocs.

Thanks.

[harsha.galla]

Isomorphic,

I have the following code to get it to work:

SQLTransaction.startTransaction(dsRequest.getRPCManager());
            Connection conn2 = SQLTransaction.getConnection(dsRequest.getRPCManager());
            String sql = SQLDataSource.getSQLClause(SQLClauseType.All, dsRequest).replaceAll("\\n", " ").replaceAll("\\s+", " ");
            Map templateContextValues = dsRequest.getTemplateContext();
            for (Object key : templateContextValues.keySet()) {
                String replaceString = "$" + "rawValue." + key.toString();
                sql = sql.replace(replaceString, templateContextValues.get(key).toString());
            }
            PreparedStatement stmt = conn2.prepareStatement(sql);
            stmt.setFetchSize(2000);
            ResultSet dataOutput = stmt.executeQuery();
            List dataList =  SQLTransform.toListOfMaps(dataOutput);

            stmt.close();
            SQLTransaction.endTransaction(dsRequest.getRPCManager());

In DMI I check if it is export by dsRequest.isExport() and then get the data from dataList in the above code. Let me know if this approach is correct.

I have the following questions:

1) If the whole data is available on the client side in the list grid, another fetch query isn't required. So in DMI how do I know if I have to query or not, also where do I get the client side data to set it to dsResponse in DMI in this particular case.

2) When I did a CSV export for a query that would fetch around 6.5 lakh (0.6 million ) rows with fetch size of 2000, it returned in around 4 minutes which is quite good. However, during profiling the heap memory shot up to more than 1 GB, so what are the next steps for me to avoid this if I have to support around 100 simultaneous csv exports of this kind.

Thanks.

[Isomorphic]

No, that's not the approach we recommended. We pointed to that example only to show you how you could access the Connection, there is no need to re-implement the entire SQL operation and issue the query yourself.

Delete all your code except the part that sets fetch size and this will causing streaming export to work again, lowering memory usage.

However note that a web browser will not wait around for a 4 minute export - browsers will generally sever the connection after 4 minutes, some browsers sooner than that, and some network configs sooner yet (eg 2 minutes or less).

Re: your comments on DataTools - what do you mean by missing? If you mean some methods are not documented, these are not supported methods, and this is true for any method that is not documented.

[shortpasta]

I want to clarify something in case you are relying on java.sql.Statement.setFetchSize ().

Setting the fetch size may have 0 impact on your query.
The reason is that the fetch size is simply a hint to the driver/database, and it has absolutely no guarantee that it will be respected.

It is my experience that some drivers/databases do not respect it at all, and can return the full result set in one shot (easy to spot because you can run into OOM exceptions), or return some arbitrary/hard-coded/whatever number of records per server trip.

Here is the javadoc:
http://docs.oracle.com/javase/6/docs...FetchSize(int)

void setFetchSize(int rows)
  throws SQLException
Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects genrated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.
Parameters:
rows - the number of rows to fetch

In fact, here is some other documentation indicating how setFetchSize () is respected/not by Postgres and depending on how auto-commit is set:
http://stackoverflow.com/questions/9...aded-resultset

Hope this helps.

[harsha.galla]

Isomorphic,

How do I set fetch size on the statement while the actual query statement only gets created in dsRequest.execute method. I have no methods to set it on Connection object directly.

shortpasta,

We have oracle 10g and fetch size always works. We have the functionality working in old legacy app (j2ee app with not smartgwt) :)

Thanks.

[Isomorphic]

The Oracle docs you posted show a method Connection.setDefaultRowPrefetch(). Use this, then you don't need the Statement.

[harsha.galla]

Isomorphic,

I get this object

org.apache.commons.dbcp.PoolableConnection

when I try to get the connection as per the code I posted earlier. I can set oracle rowPrefetechSize only on Oracle Connection.

Thanks.

[Isomorphic]

For anything org.apache.* you can generally just Google to find the JavaDoc. This class has a method getDelegate() to get the underlying SQL connection.

[harsha.galla]

Isomorphic,

It doesn't have any effect on the statement. The oracle trace shows a defaultRowPrefetch of 10 and fetchSize of 0 for export.

Thanks.

[Isomorphic]

Then it would appear it's time to file a bug with Oracle - even if the API on Statement works despite the API on Connection apparently being broken, there is no way to access the Statement and it definitely does not make general sense for the framework to provide this access.

[harsha.galla]

Isomorphic,

Do you want me to validate that it's a bug with Oracle? Before that, can you validate if the framework is internally ignoring the connection parameters.

fetch size can be manipulated in two ways:

1) rowPrefetchSize - a oracle parameter. Lets assume it to be a bug for the moment. I will validate and let you know.

2) fetchSize on JDBC driver.

I can take approach 2 here but jdbc setting is at Statement level. Let me know how I can achieve this.

Thanks.