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
