[Isomorphic]

We're not sure why you keep talking about the "driver". setFetchSize() exists on Statement and ResultSet in JDBC. When startRow/endRow are unset, we are either not calling setFetchSize or calling it with 0 (which means use default settings according to JDBC JavaDoc).

[harsha.galla]

Isomorphic,

I keep speaking of JDBC fetch size because that is one jdbc parameter which has been hidden in your default implementation (you use startRow and endRow to manipulate it or set it to 0 in case of export) and there is no way change that. You do a jdbc transaction and don't have access to some of its functionality, as simple as that. How can the framework decide the ideal fetch size here. As mentioned in my earlier posts, you need to leave that parameter to the user (developer) instead.Its a serious limitation.

I'm still surprised how people have not complained of performance issues in export. You know a fetch size of 10 for a export of 50000 or more rows is pathetic (it would need 5000 + round trips).

Thanks.



Thanks.

[Isomorphic]

??

And as we keep telling you:

1. no one can reproduce your claimed performance problem, and the performance characteristics you are seeing are probably specific to your environment, very possibly due to mistakes in your deployment. So it's no wonder no other organization has complained, despite extremely large queries and exports being used - no one else is experiencing your weird issue.

2. we are not changing the default fetchSize, and we've made extensive efforts to give you a way to set it explicitly, which has revealed an Oracle bug as far as we can tell.

So please stop complaining to us, and start addressing your complaints to the right parties: Oracle, and whoever manages your deployment environment.

[harsha.galla]

Isomorphic,

1) Mistakes in deployment ??. If it was to do with deployment why will the same database transaction which when run as per the code in this post:
http://forums.smartclient.com/showpost.php?p=88254&postcount=36

returns with the data set in less than 5 minutes where as when a default export is done it takes more than 45 minutes (i still didn't figure out the upper time limit for this). Also is it not logical that more server trips mean more query time and performance issue for end user. All the observations I have made so far are based on the Oracle trace log and nothing of my own imagination.

Let me know if you need anything else for me to prove that there is a performance concern ( which is due to higher query time) due to higher number of server trips involved. I would be more than happy to oblige.

2) With respect to the Oracle Bug i will validate and let you know. Though if it works, I would still prefer to configure it via JDBC because it is one default standard for database-independent connectivity between the Java programming language and a wide range of databases.

Thanks.

[Isomorphic]

Once again, we did not replicate your performance finding and no one else is hitting the same problem. The super-slow query is something that happens for you and you only, only in your deployment environment.

It could be due to extra server trips, or it could be because someone made mistakes in your Oracle deployment that make server trips far more costly than they should be, or because something is wrong with your Oracle deployment that is preventing a more intelligent default fetchSize from being used when SmartGWT leaves it unset, or due to any number of other factors under your control and not under the control of SmartGWT.

And once again, we have shown an approach that allows to set the fetchSize anyway, and it didn't work for you, which again could be due to a bug in Oracle's driver or to problems in your deployment.

So again, please address complaints to the correct parties.

[harsha.galla]

Isomorphic,

Exactly, in my case the server trips is the issue here for poor performance. I keep telling user should be able to decide the fetchSize for this sole reason. Each user has different setup and connectivity between application server and database. JDBC is a default standard used by most of the java developers to connect to a database, so it would be better if we can configure those properties to suit individual needs. This statement doesn't just hold good for fetch size, though I'm not aware if there are any other parameters which cannot be set (while they can be set on a jdbc connection).



Thanks.

[Isomorphic]

Just to reiterate once again: in your particular deployment environment, setting fetchSize has an impact on performance. Is this because of server trips? Bad Oracle defaults? Bad Oracle settings? Mistakes in the deployment environment? We can't tell, and you don't actually know either.

Regardless, we've already provided you more than one way that you could affect the fetch size, and it is, once again, time to complain to a more appropriately party.

[harsha.galla]

Isomorphic,

Please find the tests run to validate if Oracle is the problem, as you have mentioned.

CASE 1: I ran a test query using only Oracle and JDBC classes (no SmartGWT here) without setting defaultRowPrefetch which means default of 10.

OracleDataSource ods = new OracleDataSource();
String url = "jdbc:oracle:thin:@//xxxxxxxxxxxxxxxxxxxxxxx:1521/xxxxx";
ods.setURL(url);
ods.setUser("xxxxx");
ods.setPassword("xxxxx");
Connection conn = ods.getConnection();
Statement stmt = conn.createStatement();
long startTime = System.currentTimeMillis();
rset = stmt.executeQuery ("SELECT * FROM AIRPORT_DIM");
while (rset.next())
	System.out.println (rset.getString(1));
rset.close();
stmt.close();
long endTime = System.currentTimeMillis();
long millis = endTime - startTime;
String timeTaken = String.format("%d min, %d sec", TimeUnit.MILLISECONDS.toMinutes(millis),TimeUnit.MILLISECONDS.toSeconds(millis) -TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(millis)) );
System.out.println(" Connect prefetch Size -" + ((OracleConnection)conn).getDefaultRowPrefetch() +  " Time taken for Query - " + timeTaken);

The above code takes lot of time, around 8 mints to fetch 14000 rows.

CASE2: Ran the same as above except that I set the defaultRowPrefetch to 3000 here.

OracleDataSource ods = new OracleDataSource();
String url = "jdbc:oracle:thin:@//xxxxxxxxxxxxxxxxxxxxxxx:1521/xxxxx";
ods.setURL(url);
ods.setUser("xxxxx");
ods.setPassword("xxxxx");
Connection conn = ods.getConnection();
((OracleConnection)conn).setDefaultRowPrefetch(3000);
Statement stmt = conn.createStatement();
long startTime = System.currentTimeMillis();
rset = stmt.executeQuery ("SELECT * FROM AIRPORT_DIM");
while (rset.next())
	System.out.println (rset.getString(1));
rset.close();
stmt.close();
long endTime = System.currentTimeMillis();
long millis = endTime - startTime;
String timeTaken = String.format("%d min, %d sec", TimeUnit.MILLISECONDS.toMinutes(millis),TimeUnit.MILLISECONDS.toSeconds(millis) -TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(millis)) );
System.out.println(" Connect prefetch Size -" + ((OracleConnection)conn).getDefaultRowPrefetch() +  " Time taken for Query - " + timeTaken);

The query returns in less than 7 seconds. Oracle respects the defaultPrefetchRow parameter here.

CASE3: I take the connection from dsRequest, set defaultRowPrefetch on it to 3000 and use the connection to run the same query as in previous cases.

Connection conn = (Connection)((BasicDataSource)dsRequest.getDataSource()).getTransactionObject(dsRequest);
((OracleConnection)conn).setDefaultRowPrefetch(3000);
Statement stmt = conn.createStatement();
long startTime = System.currentTimeMillis();
rset = stmt.executeQuery ("SELECT * FROM AIRPORT_DIM");
while (rset.next())
	System.out.println (rset.getString(1));
rset.close();
stmt.close();
long endTime = System.currentTimeMillis();
long millis = endTime - startTime;
String timeTaken = String.format("%d min, %d sec", TimeUnit.MILLISECONDS.toMinutes(millis),TimeUnit.MILLISECONDS.toSeconds(millis) -TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(millis)) );
System.out.println(" Connect prefetch Size -" + ((OracleConnection)conn).getDefaultRowPrefetch() +  " Time taken for Query - " + timeTaken);

This returns similar to the above scenario i.e 7 seconds

CASE4: In this scenario I don't run the query but instead let SmartGWT run it.

SQLTransaction.startTransaction(dsRequest.getRPCManager());
Connection conn = ((PoolableConnection)((BasicDataSource)dsRequest.getDataSource()).getTransactionObject(dsRequest)).getDelegate();
((OracleConnection)conn).setDefaultRowPrefetch(3000);
DSResponse response =  dsRequest.execute();

Here again it takes around 8 minutes like in CASE1. This clearly shows that there is nothing wrong with Oracle. The parameter defaultRowPrefetch is respected in case of query run without SmartGWT intervention, but fails for anything that is run by the framework. So here lies the issue.


Also oracle docs here, clearly mention here:

Notes:
1) Do not mix the JDBC 2.0 fetch size API and the Oracle row-prefetching API in your application. You can use one or the other, but not both.

2)Be aware that setting the Oracle row-prefetch value can affect not only queries, but also: 1) explicitly refetching rows in a result set through the result set refreshRow() method available with JDBC 2.0 (relevant for scroll-sensitive/read-only, scroll-sensitive/updatable, and scroll-insensitive/updatable result sets); and 2) the "window" size of a scroll-sensitive result set, affecting how often automatic refetches are performed. The Oracle row-prefetch value will be overridden, however, by any setting of the fetch size.

So I feel since SmartGWT is setting the fetch size to 0 in case of export, the defaultRowPrefetch isn't working.

I'm also curious as to why the framework sets fetchsize to endRow-startRow (in case these parameters are set), if fetch size has nothing to do with improvement in query time. :)

Let me know what are the next steps to get this thing resolved.



Thanks.

[Isomorphic]

We have never said, and would never say, that fetchSize cannot affect performance. Please re-read previous messages, we have been very clear about this.

Your findings seem to indicate that the Oracle bug is that JDBC setFetchSize(0) incorrectly overrides your call to set defaultPrefetchRow. Please try adding such a call to see if you can reproduce the effect.

[harsha.galla]

Isomorphic,

I have run the following scenario where I set defaultRowPrefetch to 3000 on Connection and fetchSize to 0 on the statement.

SQLTransaction.startTransaction(dsRequest.getRPCManager());
            conn = ((PoolableConnection)((BasicDataSource)dsRequest.getDataSource()).getTransactionObject(dsRequest)).getDelegate();
            ((OracleConnection)conn).setDefaultRowPrefetch(3000);
            stmt = conn.createStatement();
            stmt.setFetchSize(0);
            long startTime = System.currentTimeMillis();
            rset = stmt.executeQuery ("SELECT * FROM AIRPORT_DIM");
            while (rset.next())
                System.out.println (rset.getString(1));
            rset.close();
            stmt.close();
            long endTime = System.currentTimeMillis();
            long millis = endTime - startTime;
            String timeTaken = String.format("%d min, %d sec", TimeUnit.MILLISECONDS.toMinutes(millis),TimeUnit.MILLISECONDS.toSeconds(millis) -TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(millis)) );
            System.out.println(" Connect prefetch Size -" + ((OracleConnection)conn).getDefaultRowPrefetch() +  " Time taken for Query - " + timeTaken);

The above code returns back in 10 seconds which further clarifies that defaultRowPrefetch parameter is working.

Thanks.

[Isomorphic]

Remember, we see no performance difference for fetchSize settings even with a join across large tables. We have no way to test this scenario and figure out what trips the bug in the Oracle driver.

Please try some further possibilities such as also setting fetchSize to 0 on the ResultSet, or reversing the order of calls, and let us know if you find a pattern where the driver ends up ignoring your defaultRowPrefetch call.

[harsha.galla]

Isomorphic,

By performance I mean, large query time due to low fetch size. We have application server and database in different server locations and the environment is quite complex with firewalls in-between etc. So round-trip is a costly affair in our case.

Also as suggested I tried this scenario:

 SQLTransaction.startTransaction(dsRequest.getRPCManager());
            conn = ((PoolableConnection)((BasicDataSource)dsRequest.getDataSource()).getTransactionObject(dsRequest)).getDelegate();
            stmt = conn.createStatement();
            stmt.setFetchSize(0);
            ((OracleConnection)conn).setDefaultRowPrefetch(3000);
            long startTime = System.currentTimeMillis();
            rset = stmt.executeQuery ("SELECT * FROM AIRPORT_DIM");
            while (rset.next())
                System.out.println (rset.getString(1));
            rset.close();
            stmt.close();
            long endTime = System.currentTimeMillis();
            long millis = endTime - startTime;
            String timeTaken = String.format("%d min, %d sec", TimeUnit.MILLISECONDS.toMinutes(millis),TimeUnit.MILLISECONDS.toSeconds(millis) -TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(millis)) );
            System.out.println(" Connect prefetch Size -" + ((OracleConnection)conn).getDefaultRowPrefetch() +  " Time taken for Query - " + timeTaken);

This time it takes 6-7 minutes again, indicating that the order in which fetchSize and defaultPrefetchRow are invoked matters.

Thanks.

[Isomorphic]

Great, OK, so the Oracle bug is that setFetchSize(0) will cause it to ignore an explicit setting.

This may also explain why such a poor fetchSize is chosen by default.

We'll add a workaround to avoid calling setFetchSize() at all when we just want the driver to use its default behavior.

[harsha.galla]

Isomorphic,

Your work-around would be great. That would work for us. But if you have to address this to a wide variety of audience, providing users to decide the fetch size on jdbc would be great option.

As a side note, Oracle wouldn't accept this as a bug as the oracle docs clearly mentioned to use one and that both cannot be used. :)


Thanks.

[Isomorphic]

As we explained at great length, we already allow you to determine the fetch size.

Oracle is quite wrong - the JDBC docs are 100% explicit that this method should have no effect if called with zero - but we are not surprised that Oracle declines to regard this as a bug.