[jboss-jira] [JBoss JIRA] Commented: (JBCACHE-1261) Redundant SQL queries on JDBCCacheLoader

[gmeroz (JIRA)]

    [ http://jira.jboss.com/jira/browse/JBCACHE-1261?page=comments#action_12394465 ] 
            
gmeroz commented on JBCACHE-1261:
---------------------------------

I used pojo cache with JBossCache2.0.0GA for this example.

the code should be:
for (int i=0 ; i<2 ; i++){
    cache.attach("cat/x"+i,"some_data");
}

the example i gave in the first place is using my own wrapper which do the same.

> Redundant SQL queries on JDBCCacheLoader
> ----------------------------------------
>
>                 Key: JBCACHE-1261
>                 URL: http://jira.jboss.com/jira/browse/JBCACHE-1261
>             Project: JBoss Cache
>          Issue Type: Bug
>      Security Level: Public(Everyone can see) 
>          Components: Cache loaders
>    Affects Versions: 2.0.0.GA
>         Environment: Oracle DB
>            Reporter: gmeroz
>         Assigned To: Mircea Markus
>            Priority: Critical
>
> When JDBCCacheLoader is used (with Oracle), the performance is bad due to the many SQL statement run to the DB.
> many of the SQLs are redundant since they run with the same paramters several times. The problem exist both when inserting/loading to/from DB.
> The performance can be improved by more than 50% if all redudant SQLs are removed.
> You can see the problem when running the following code after setting the log to "debug" on org.jboss.cache.loader.
> // insert only 2 nodes to the cache
> for (int i=0 ; i<2 ; i++){
>         cache.put("cat","x"+i,"some_data");
> } 
> output (52 queries!!! ):
> 1 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/)
> 2 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x0)
> 3 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x0)            // same as 2
> 4 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x0)            // same as 2
> 5 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x0)            // same as 2
> 6 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x0)            // same as 2
> 7 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x0)            // same as 2
> 8 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x0)            // same as 2
> 9 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x0)            // same as 2
> 10 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat/x0/_ID_/5c4oa2y-nzxzy2-fb7uoxai-1-fb7uoxay-2)
> 11 executing sql: insert into ALLERGAN04.jbosscache_test (fqn, node, parent) values (?, ?, ?) (/__JBossInternal__/cat/x0/_ID_/5c4oa2y-nzxzy2-fb7uoxai-1-fb7uoxay-2)
> 12 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat/x0/_ID_)
> 13 executing sql: insert into ALLERGAN04.jbosscache_test (fqn, node, parent) values (?, ?, ?) (/__JBossInternal__/cat/x0/_ID_)
> 14 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat/x0)
> 15 executing sql: insert into ALLERGAN04.jbosscache_test (fqn, node, parent) values (?, ?, ?) (/__JBossInternal__/cat/x0)16 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat)
> 16 executing sql: insert into ALLERGAN04.jbosscache_test (fqn, node, parent) values (?, ?, ?) (/__JBossInternal__/cat)
> 17 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__)
> 18 executing sql: insert into ALLERGAN04.jbosscache_test (fqn, node, parent) values (?, ?, ?) (/__JBossInternal__)
> 19 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/)
> 20 executing sql: insert into ALLERGAN04.jbosscache_test (fqn, node, parent) values (?, ?, ?) (/)
> 21 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat/x0/_ID_/5c4oa2y-nzxzy2-fb7uoxai-1-fb7uoxay-2)
> 22 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat/x0/_ID_/5c4oa2y-nzxzy2-fb7uoxai-1-fb7uoxay-2)
> 23 executing sql: update ALLERGAN04.jbosscache_test set node=? where fqn=?
> 24 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x0)                    // same as 2               
> 25 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x0)                     // same as 2
> 26 executing sql: insert into ALLERGAN04.jbosscache_test (fqn, node, parent) values (?, ?, ?) (/cat/x0)
> 27 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat)
> 28 executing sql: insert into ALLERGAN04.jbosscache_test (fqn, node, parent) values (?, ?, ?) (/cat)
> 29 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/)
> 30 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x1)
> 31 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x1)
> 32 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x1)
> 33 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x1)
> 34 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x1)
> 35 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x1)
> 36 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x1)
> 37 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x1)
> 38 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat/x1/_ID_/5c4oa2y-nzxzy2-fb7uoxai-1-fb7uoxh1-3)
> 39 executing sql: insert into ALLERGAN04.jbosscache_test (fqn, node, parent) values (?, ?, ?) (/__JBossInternal__/cat/x1/_ID_/5c4oa2y-nzxzy2-fb7uoxai-1-fb7uoxh1-3)
> 40 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat/x1/_ID_)
> 41 executing sql: insert into ALLERGAN04.jbosscache_test (fqn, node, parent) values (?, ?, ?) (/__JBossInternal__/cat/x1/_ID_)
> 42 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat/x1)
> 43 executing sql: insert into ALLERGAN04.jbosscache_test (fqn, node, parent) values (?, ?, ?) (/__JBossInternal__/cat/x1)
> 44 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat)
> 45 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat/x1/_ID_/5c4oa2y-nzxzy2-fb7uoxai-1-fb7uoxh1-3)
> 46 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat/x1/_ID_/5c4oa2y-nzxzy2-fb7uoxai-1-fb7uoxh1-3)
> 47 executing sql: update ALLERGAN04.jbosscache_test set node=? where fqn=?
> 48 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x1)
> 49 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x1)
> 51 executing sql: insert into ALLERGAN04.jbosscache_test (fqn, node, parent) values (?, ?, ?) (/cat/x1)
> 52 executing sql: select node from ALLERGAN04.jbosscache_test where fqn=? (/cat)
> Redundant:
> 3 x "select node from ALLERGAN04.jbosscache_test where fqn=? (/)"
> 10 x "select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x0)"
> 3 x "select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat/x0/_ID_/5c4oa2y-nzxzy2-fb7uoxai-1-fb7uoxay-2)"
> 2 x "select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat)"
> 2 x "select node from ALLERGAN04.jbosscache_test where fqn=? (/cat)"
> 10 x "select node from ALLERGAN04.jbosscache_test where fqn=? (/cat/x1)"
> 3 x  "select node from ALLERGAN04.jbosscache_test where fqn=? (/__JBossInternal__/cat/x1/_ID_/5c4oa2y-nzxzy2-fb7uoxai-1-fb7uoxh1-3)"
> in total 27 redudant queries....

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira