RpcTabTiming: SQLTransform takes too long?

[claudiobosticco]

SmartClient Version: v10.0p_2014-12-17/EVAL Deployment (expires 2015.02.15_07.40.12) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

Hello, I'm using the RpcTabTiming feature to analyze a fetch which seems to take too much time, respect to the time taken to execute in a oracle client.

In the 'Raw Response' tab, I see that actually it's the SQLTransform operation which takes most of the time:

{
                                name:"Unpaged fetch", 
                                start:1418987042485, 
                                children:[
                                    {
                                        name:"get connection", 
                                        start:1418987042485, 
                                        children:[
                                        ], 
                                        end:1418987042508
                                    }, 
                                    {
                                        name:"query", 
                                        start:1418987042508, 
                                        children:[
                                        ], 
                                        end:1418987042623
                                    }, 
                                    {
                                        name:"SQLTransform", 
                                        start:1418987042623, 
                                        children:[
                                        ], 
                                        end:1418987054845
                                    }
                                ], 
                                end:1418987054845
                            }

actually the select fetches 10k rows, so I'm asking:
- is it an expected processing time?
- is it possible to optimize it with SmartClient settings?

this is a sample of a fetched record:

 {
            UPGRADE_WIP:false, 
            PREZZO_NETTO_IVA:2603.31, 
            SCONTO_PERC:0, 
            DESCRIZIONE_SETTORE:"Trib. O.FooBar 1Liv.", 
            ID_REC:123456, 
            ARCHIVIATO_PER_STATS:false, 
            ID_STATO_CONTRATTO_FK:1102190, 
            FL_CONFERMATO:true, 
            ID_SETTORI_FK:183757, 
            DATE_CREATOR:"2013-02-05T09:44:07.000", 
            PREZZO_LISTINO_SCONTO:3150, 
            VALORE_SCONTATO:3150, 
            ACCOUNT:"foobar", 
            VALORE_PREZZO:3150, 
            CONTRATTO_NUM:4133, 
            ID_VOCE_LISTINO_PER_SCONTO_FK:185902, 
            ID_POSTI_FK:184020, 
            CODICE_SETTORE:"104", 
            IS_ANNULLATO:false, 
            SOGGETTO_DESC:"FooBar Food", 
            ID_CONTRATTI_FK:123456, 
            SERVIZI_LORDO:1150, 
            NUMERO_POSTO:9, 
            SCONTO_CALCOLATO:0, 
            SERVIZI:950.41, 
            STATO_CONTRATTO:"APP", 
            IS_OMAGGIO:false, 
            TICKETING:1652.89, 
            ID_FILE_FK:184013, 
            STAGIONE:"2013/2014", 
            DATA_FATTURA:"2013-03-28", 
            DATA_INCASSO:"2013-03-21", 
            TIPO_SOGGETTO:"Azienda", 
            eliminaRigaTransitorio:true, 
            VOCE_LISTINO_SCONTO:"Trib. O.FooBar 1Liv.", 
            SEGNAPOSTO_TESSERA_CONSEGNATA:false, 
            PREZZO_LISTINO_SCONTO_NO_IVA:2603.3, 
            SCONTO_PROX_STAGIONE:false, 
            ID_STAGIONI_FK:182001, 
            TICKETING_LORDO:2000, 
            NUMERO_FILA:16, 
            ACCOUNT_MOD:"safoobar", 
            DATE_MOD:"2013-06-22T09:50:44.000", 
            DATA_SOTTOSC_CLIENTE:"2013-02-25", 
            NUM_TESSERA:"123456789123"
        },

[claudiobosticco]

may it be the same problem described in this thread? http://forums.smartclient.com/showthread.php?t=21389

Actually if I run my query as in the 'case 1' of this post
http://forums.smartclient.com/showpost.php?p=88489&postcount=53
it is very fast.

setting:

        Map<String, String> props = new HashMap<String, String>(); // non sembra avere effetto...
        props.put("defaultRowPrefetch", "1000");
        Config.getGlobal().put("sql.dbJpcEP.driver.connectionProperties", props);

doesn't seem to help.

[claudiobosticco]

starting tomcat with this setting:
-Doracle.jdbc.defaultRowPrefetch=1000

actually makes the difference.
Even with a value of 100 the improvement is big.

Now I have to understand if it could be worse in other situations...

[claudiobosticco]

also, if I don't use the setting
-Doracle.jdbc.defaultRowPrefetch=1000

but I set startRow= 0 and endRow = 10000 in the request, the execute is very fast.
Isn't it possible to obtain an analogous effect without setting endRow (I don't know how much rows I need to fetch...)?

[Isomorphic]

As you've discovered, the time taken is actually the Oracle JDBC driver.

It looks like the Oracle bug identified in that other thread isn't affecting you. In the other thread they couldn't even get the defaultRowPrefetch setting to work, although there were lots of problems in that thread..

As far as your setting for defaultRowPrefetch, it's probably a good idea to set it to at least 75 system-wide, as that's what grids default to fetching (based on resultSet.resultSize).

As far as mimicking the effect having an endRow for just one specific request, the semi-hack mentioned in the other thread was just to set endRow to a very very large number. This will of course never fetch more rows than are actually available.

[claudiobosticco]

As you've discovered, the time taken is actually the Oracle JDBC driver.

It looks like the Oracle bug identified in that other thread isn't affecting you. In the other thread they couldn't even get the defaultRowPrefetch setting to work, although there were lots of problems in that thread..

As far as your setting for defaultRowPrefetch, it's probably a good idea to set it to at least 75 system-wide, as that's what grids default to fetching (based on resultSet.resultSize).

thank you very much for the suggestion

As far as mimicking the effect having an endRow for just one specific request, the semi-hack mentioned in the other thread was just to set endRow to a very very large number. This will of course never fetch more rows than are actually available.

Ok, but since I don't know what's going on under the covers, related to the jdbc driver, when I set endRow to a very large number, I wonder:
could it create problems if I set (for those specific requests involving at least thousands of records):

 
dsRequest.setStartRow(0);
dsRequest.setEndRow(Long.MAX_VALUE);

or I'll have to make an educated guess for each request?

[Isomorphic]

There shouldn't be an issue with always setting a very large endRow in this specific use case (exporting all results).

[claudiobosticco]

thank you very much.