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

[gmeroz (JIRA)]

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: Manik Surtani
            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