[jboss-user] [JBossCache] - Query cache using 1.4.1

[loumaus]

hi there .. 

we are running into a query cache problem and need help:

The log:
 /sql: select /*+ USE_NL(c d cd) */ c.document_id, c.title, c.label, c.released, c.cover_jpg_mid, c.ftm_containertype_id, c.full_artists, c.product_id, c.genre_id, cd.usr_rating_avg from j_ftm_container c, (select row_number() over(order by part, pos nulls last) pos, part, document_id from (select min(part) part, min(pos) pos, document_id from (select * from (select 'a static' part, row_number() over(order by t.sort desc nulls last) pos, t.document_id from j_documenttree t where t.parent_id = (select id from j_documenttree where path = ? and name = ? and state_id = 2) and exists (select 1 from j_ftm_containerdetail d where d.country_id = ? and d.state_id = 2 and d.ftm_containertype_id in (?, ?, ?, ?, ?, ?) and d.ftm_container_document_id = t.document_id and d.released between ? and ?)) where pos <= ? union select * from (select 'b dynamic' part, row_number() over(order by d.released desc nulls last) pos, d.ftm_container_document_id from j_ftm_containerdetail d where d.country_id = ? and d.state_id = 2 and d.released between ? and ? and d.ftm_containertype_id in (?, ?, ?, ?, ?, ?)) where pos <= ?) group by document_id)) d, j_ftm_containerdetail cd where d.pos between ? and ? and d.document_id = c.document_id and c.document_id = cd.ftm_container_document_id; parameters: ; named parameters: {country_id=1, containertype_id3=3, pos_start=1, containertype_id6=6, containertype_id2=2, date_start=Thu Feb 01 00:00:00 CET 1900, containertype_id5=5, containertype_id1=1, pos_end=10, date_end=Thu Nov 22 23:59:59 CET 2007, containertype_id4=4, documenttree_path=root/full_length/catalog/Startpage, documenttree_name=Neuheiten}
item: [11957243932, 6721540, 6934684, 6540921, 6542774, 6713918, 6717515, 6714902, 6714040, 6873547, 6721517]

        /sql: select /*+ USE_NL(c d cd) */ c.document_id, c.title, c.label, c.released, c.cover_jpg_mid, c.ftm_containertype_id, c.full_artists, c.product_id, c.genre_id, cd.usr_rating_avg from j_ftm_container c, (select row_number() over(order by part, pos nulls last) pos, part, document_id from (select min(part) part, min(pos) pos, document_id from (select * from (select 'a static' part, row_number() over(order by t.sort desc nulls last) pos, t.document_id from j_documenttree t where t.parent_id = (select id from j_documenttree where path = ? and name = ? and state_id = 2) and exists (select 1 from j_ftm_containerdetail d where d.country_id = ? and d.state_id = 2 and d.ftm_containertype_id in (?, ?, ?, ?, ?, ?) and d.ftm_container_document_id = t.document_id and d.released between ? and ?)) where pos <= ? union select * from (select 'b dynamic' part, row_number() over(order by d.released desc nulls last) pos, d.ftm_container_document_id from j_ftm_containerdetail d where d.country_id = ? and d.state_id = 2 and d.released between ? and ? and d.ftm_containertype_id in (?, ?, ?, ?, ?, ?)) where pos <= ?) group by document_id)) d, j_ftm_containerdetail cd where d.pos between ? and ? and d.document_id = c.document_id and c.document_id = cd.ftm_container_document_id; parameters: ; named parameters: {country_id=1, containertype_id3=3, pos_start=1, containertype_id6=6, containertype_id2=2, date_start=Thu Feb 01 00:00:00 CET 1900, containertype_id5=5, containertype_id1=1, pos_end=10, date_end=Thu Nov 22 23:59:59 CET 2007, containertype_id4=4, documenttree_path=root/full_length/catalog/Startpage, documenttree_name=Neuheiten}
item: [11957243790, 6721540, 6934684, 6540921, 6542774, 6713918, 6717515, 6714902, 6714040, 6873547, 6721517]

The two lines reveal that the queries are identically and use the same parameters. 

The problem is that the query cache creates two entries which leads to two db queries instead of storing the first one and using it for the second call as well. 

The only difference between both is the internal key (first entry at item ) under which each query is stored internally. 

any idea ???

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4107019#4107019

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4107019