jackrabbit-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marek Slama" <msl...@email.cz>
Subject Slow join query
Date Fri, 25 Oct 2013 11:42:55 GMT
Hi,

I am experiencing really slow query when I use join. I have following node 
structure for documents:
/researchRepo/<documentId>[prop:documentType]/services/<serviceNumber>[prop:
serviceName]
/textRepo/<documentId>/services/<serviceNumber>
/eventRepo/<documentId>/services/<serviceNumber>
....

where 
1st level is folder for different document type (possibly with different 
metadata)
2nd level is documentId (unique)
3rd services folder for services for document (some grouping where those 
documents are subscribed from)
4th service number - id for service

There can be more than one service for document even if there is usually 
only one service for document

Now I want to select document node and use also either serviceNumber or 
serviceName in query with LIKE plus some constraint on nodes above so I end 
up with following query. It works fine but it is slow (3-5mins when we have 
about 15000 documents)

SELECT x.* FROM [nt:base] AS x INNER JOIN [nt:base] AS y ON ISCHILDNODE(y,x)
INNER JOIN [nt:base] AS z ON ISCHILDNODE(z,y) WHERE ((ISCHILDNODE(x,['/
researchRepo']) and (x.documentType='RESEARCH_DOCUMENT')) or (ISCHILDNODE(x,
['/textRepo']) and (x.documentType='PRESS_RELEASE')) or (ISCHILDNODE(x,['/
textRepo']) and (x.documentType='IDC_LINK')) or (ISCHILDNODE(x,['/
factSheetRepo']) and (x.documentType='FACT_SHEET')) or (ISCHILDNODE(x,['/
eventRepo']) and (x.documentType='EVENT'))) and ((LOWER(z.serviceName) LIKE 
'%mobile%'))

Is there any way how to improve it? I know LIKE operator can be quite slow 
but when I use LIKE on the same level ie. on document level
without JOIN query takes below 1s.

Thanks

Marek

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message