Badly performing DB call - much quicker in toad

[ramsja]

Hi - I'm a bit of a smartGWT newbie and have inherited a large smartGWT application which isn't performing terribly well. I am focusing in on a specific fetch() request which uses IDACall handleDSRequest() to get the response. If I take the sql from that and run it in toad, it takes under a second to run. If I run it by stepping over the hadnleDSRequest() call in the app, it takes 7 seconds. The criteria include a startIndex of 0 and endIndex of 2000, so it is only returning 2001 rows and they are not data intensive. I've tried just about every option I can find in these 2 posts and found no difference in the response time.:
http://forums.smartclient.com/forum/...takes-too-long
http://forums.smartclient.com/forum/.../page3?t=21389

One thing I have been trying to do is set the start and end rows on the DSRequest, but whenever I do that I get the following error in chrome dev console (there doesn't seem to be any errors in the DSResponse, but there are no records either):
SEVERE: com.woodmac.datavisualizer.client.DataVisualizerEntryPoint:RPC Manager Error.java.lang.Exception: 200: //isc_RPCResponseStart-->[{affectedRows:0,data:"ORA-00907: missing right parenthesis\n",invalidateCache:false,isDSResponse:true,queueStatus:-1,status:-1}]//isc_RPCResponseEnd

When I take the sql from this failed call (using: com.isomorphic.sql.SQLDataSource.getSQLClause(com.isomorphic.sql.SQLClauseType.All, dsRequest);) and run that in toad it seems to work fine.

So, two questions:
1) why would adding:
dsRequest.setStartRow(0);
dsRequest.setEndRow(2000);
cause the sql to have a missing right parenthesis error when the sql works perfectly well without them set and there doesn't seem to be a missing right parenthesis in the sql?

2) do you have any other thoughts and ideas as to how I might be able to speed up this call to that of Toad? Given toad and the app are running in the same place, I just don't see why this would happen.

Thanks kindly
Jason

[Blama]

Hi Jason,

I'd have a look at the actual statement sent to the DB. No need to step-through debug.
If you don't see it in the *server* log, enable log level DEBUG for SQLDataSource.

And this basic info is missing:
DB is Oracle I assume (because the notion of Toad). Which version?
SmartClient version (w/ build date), visible in the Developer Console or at server startup in the server log.

To me, setStartRow/setEndRow resulting in a missing parenthesis error sounds to me (I might be wrong) like the framework is generating some outer SQL and doing a ") x WHERE rownum between 1 AND 2001". This is not the way it should be with Oracle (either JDBC windowing or the new Oracle 12 Paging /Offset feature).

Anyway, the easiest way to spot the error and with that perhaps the remedy is to look at the actual SQL statement sent to the DB.

Best regards
Blama

[Blama]

Another thought:
Using setStartRow/setEndRow normally enables the rowcount query. Perhaps this query results in an error. Do you have some special configuration on the operationBinding you are using in your .ds.xml?
Perhaps a tableClause?

Best regards
Blama

[ramsja]

Hi Blama - I managed to work out what was breaking the sql - as you said, framework generating outer sql that I hadn't spotted. However I'm still finding that the sql takes a second through toad and far longer in handleDSRequest. I appreciate it is doing further processing, but it's 7 times more slow for the same sql call, from and to the same environment, which doesn't seem right.

Oracle version: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Smart client version: v11.0p_2016-05-14/EVAL Deployment (expires 2016.07.13_07.36.28) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

Any further thoughts much appreciated!
Jason

[Isomorphic]

Turn on the Detailed Timing information in the RPC tab of the Developer Console (see docs for details). This way you can see whether it's your own logic (which is what happened last time), Oracle, or some framework logic.

[ramsja]

I tried that, but the server roundtrip tree is empty (client processing tree is populated). Am I doing something wrong there?

[Isomorphic]

Did you read the docs that explain how to enable the timings, and follow those instructions? If not, that's the next step.

[ramsja]

I did - the timings tab is there and it has client processing tree populated, but not server roundtrip.

Also, the I have written code to time this call:
dsResponse = super.handleDSRequest(dsRequest, rpc, context);
According to the docs, that is the same as calling dsRequest.execute(), so I don't think it's doing further processing in the app.

[Isomorphic]

Obviously, something went wrong in the process of applying the documented settings. If you need help with that, you should spell out what settings you actually changed and how you applied them to the application.

No, timing handleDSRequest() is not at all indicative that all the time is in the framework. There are still multiple sources of application code, and the DB itself.

If you need help figuring out where the time is being spent, please consider our Consulting services. The last time we did troubleshooting for performance problems for you, a problem that had been blamed on SmartGWT for weeks was rapidly isolated to application code.

[ramsja]

I think you're confusing me with someone else mate, this is my first ever post. Not very helpful.

[Isomorphic]

Yes, we can see where you are posting from, and also the total number of times you've posted. By "you" we meant the same organization, not you as an individual.

Aside from Consulting, we can't do much for you given the limited information you've posted. See our previous post for what to do next - there's nothing we can do when you claim a feature is just outright not working and provide no details, when we know it to be working in a huge variety of environments.

[ramsja]

That was an external company that has left us with no handover.

Maybe you would be kind enough to point me in the direction of the documentation for setting up the timings so I know I have the correct ones and I will double check and get back to you? Maybe you could even point out what I might have done wrong in setting up the timings tab that might cause the server roundtrip to be empty, like I asked earlier?

I don't think we're ready for consulting just yet, I'm just asking for a little bit of help to guide me on the right track. And I'm not "blaming" anyone.

Thanks

[ramsja]

Is there a way for me to see posts that have previously been made by my company? Maybe I can get some information from the sorts of things they've asked you before and not ask the same questions?

[claudiobosticco]

the documentation for setting up the timings

http://www.smartclient.com/smartclie...vConsoleRPCTab

[Isomorphic]

We did refer you to the docs, which you've clearly already found (you've been referring to the "Server Roundtrip" tree and such).

For the fourth time - we need you to tell us what you changed, and what settings you are now using. The docs give a correct procedure which is known to work, so when you ask:

Maybe you could even point out what I might have done wrong in setting up the timings tab that might cause the server roundtrip to be empty, like I asked earlier?

.. because you have not posted a single detail of what you actually did, the only possible answer is: "maybe you didn't do what the docs said".

We can absolutely help you figure out what's wrong.. but not with zero information, and with you repeatedly ignoring specific questions.