Problem with slow Oracle VIEW from SmartGWT

[Blama]

Hi all,

I'm experiencing following problem and would like to know if someone else also hit this one before and has a suggestion to solve it:

I have a pretty complicated SELECT statement in Oracle 11.2 XE that returns per definition only a few rows (~30). With no further optimization it takes ~20 secs to return the dataset in Oracle SQL Developer. If you add the /*+ PUSH_PRED(rzd) */ hint, the statement takes less than 0.5s to execute (still in SQL Developer). I saved the statement with the hint as VIEW. Selecting from the VIEW in SQL Developer is also fast.
Now I added a straightforward ds.xml to my SmartGWT project and bound it to a ListGrid. I can see from the server console that the statement is 1:1 100% the same as the one I execute in SQL Developer. Nevertheless it takes about 20 seconds to complete (I also set "setDataFetchMode(FetchMode.BASIC)" in order to get rid of the "SELECT COUNT(*)" generated by SmartGWT. It's pretty obvious the SmartGWT is doing everything right here as it just handles the SELECT String to the JDBC driver. But I don't know whats going on from there on.

Here my details:

SELECT banner FROM v$version;

BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production              
PL/SQL Release 11.2.0.2.0 - Production                                           
CORE	11.2.0.2.0	Production                                                         
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production                          
NLSRTL Version 11.2.0.2.0 - Production                                           

[B]JDBC Driver used[/B]: some old odbc14.jar as well as
current odbc6.jar for 11.2.0.2.0 from [URL]http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html[/URL]
[B]SQL Developer[/B]: Current version 3.2.20.09

From my reading this could go wrong:

• JDBC doesn't know the VIEW's column data types and Oracle behaves mysterious because of this (=there must be more to the SELECT than just the string, some meta-information)
• For some reason the hint *inside* the VIEW is not used (unlikely)

For point one I have this observation: When you generate a DS from the DB with Batch DS Generator you get these (undocumented) attributes: sqlType and sqlLength for which I did not find any information in the SmartGWT (http://www.smartclient.com/smartgwte...urceField.html) or SmartClient docs.
@Isomorphic: Could you tell me how SmartGWT uses these attributes? Is it good/recommended to have them?

@Others: Any idea how to solve this? I'll try something with Oracle Table Functions as workaround now, but this is definitely not the correct solution (SmartGWT CustomSQL/Oracle Table Function How-To here: http://forums.smartclient.com/showth...ored+procedure).

Thank you & Best regards,
Blama

[Isomorphic]

sqlType and sqlLength are metadata attributes we pick up as part of autoDeriveSchema just in case someone wants to use them in custom code. They have no impact on behavior unless you actually write code that makes use of them.

We don't really have a theory as to how your hint isn't taking effect in this case.

[Blama]

Hi Isomorphic,

thanks for your reply.
Now it gets even more weird.
I changed the Fetch-Operation to CustomSQL in order to call my table function:

ds.xml:

<operationBinding operationType="fetch">
	<customSQL>SELECT * FROM TABLE(v_person_region_type_tablef($criteria.PERSON_ID))</customSQL>
</operationBinding>

The generated SQL is e.g. SELECT * FROM TABLE(v_person_region_type_tablef('4')).
This SELECT takes again the same time (~20s) as before while the SAME SELECT from SQL Developer still takes <1s.

Next thing I'll have to try is log4jdbc (never did that before, https://code.google.com/p/log4jdbc) or a different JDBC driver, I guess :(

Any ideas are greatly appreciated!

Best regards,
Blama