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

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....