[Info-ingres] Ingres 10.1, JDBC parameters issue

[dominik.jesiolowski at gmail.com]

On Friday, 15 August 2014 20:43:32 UTC+1, zarafiq  wrote:

Managed to find a workaround:

String sql = "SELECT * FROM table_x WHERE column_x = 'A1234' ";  // fast

String sql = "SELECT * FROM table_x WHERE column_x = ?       ";  // slow
pstmt.setString(1, "A1234"); 

String sql = "SELECT * FROM table_x WHERE column_x = text(?) ";  // fast
pstmt.setString(1, "A1234"); 
 
The trace files generated by slow and fast versions:

---------------------------------------------
-------------- SLOW -------------------------
---------------------------------------------
    LOCK:   MVCC  PHYS                     Mode: S   Timeout: 0
            Key: (livedb,table_x)
    LOCK:   TABLE PHYS                     Mode: S   Timeout: 0
            Key: (livedb,table_x)
Fetch Cursor <1525.,1.,jdbc_crsr_0_1> Statement
 pre-fetch 10 row(s)
Close Cursor <1525.,1.,jdbc_crsr_0_1> Statement

----------
| 0|
ORIG      
at        1
et   368744
ad     3863
ed     8428
ac       39
ec   368744
et        5

---------------------------------------------
-------------- FAST -------------------------
---------------------------------------------
    LOCK:   MVCC  PHYS                     Mode: S   Timeout: 0
            Key: (livedb,table_x)
    LOCK:   TABLE PHYS                     Mode: IS  Timeout: 0
            Key: (livedb,table_x)
Fetch Cursor <1526.,1.,jdbc_crsr_0_1> Statement
 pre-fetch 10 row(s)
    LOCK:   PAGE  PHYS,LOCL,NOIN,QUTM      Mode: S   Timeout: 0
            Key: (livedb,table_x,0.0)
    LOCK:   PAGE  PHYS,LOCL,NOIN,QUTM      Mode: S   Timeout: 0
            Key: (livedb,table_x,0.18162)
    UNLOCK: PAGE  Key: (livedb,table_x,0.0)
    UNLOCK: PAGE  Key: (livedb,table_x,0.18162)
    LOCK:   PAGE  STAT,LOCL,QUTM           Mode: S   Timeout: 0
            Key: (livedb,table_x,0.22275)
    LOCK:   PAGE  STAT,LOCL,QUTM           Mode: S   Timeout: 0
            Key: (livedb,table_x,0.22285)
Close Cursor <1526.,1.,jdbc_crsr_0_1> Statement
----------
| 0|
ORIG      
at        1
et        1
ad        0
ed        4
ac        9
ec        1
et        0

Regards,
zarafiq