Optimizing CustomSQL Adapter Services


I’ve got a complex ugly query that I can get to run reasonably in Oracle SQL Developer.

However when I run it through WebMethods Developer it seems a lot slower. I’ve got it as CustomSQL with defined InputFields and an Output Field. I’m logging into the same database as the same user (but with NO_TRANSACTION in WebMethods). The difference is from under a second to minutes. The results returned are the same.

Does anyone know if the JDBC Adapter maps CustomSQL services to prepared statements? Some one asked about this a while ago but this question didn’t appear to be resolved. I’m wondering whether the adapter is reusing execution plans or recalculating for every run.

Are there any other tricks I should be doing to optimize JDBC adapter services? I’m developing the SQL in SQL developer, optimizing against explain plan and then copying into WebMethods but I’m not sure if WM is following the same plan.

Thanks for any help

Paul Bristow