[Exist-open] Need help with Query optimization ways

[vamshi p. <vpa...@ya...>]

Hi All,

I have a collection called "projects". It has 56,000 xml documents. Each document has unique "projectId". I created a range index on the "projectId" element. See below for the index definition (collection.xconf).



    <index>
        <create qname="ProjectId" type="xs:string"/>
    </index>
</collection>


Averages are based on 10 tries. All queries are run in "Query Dialog".


If my query is a simple xpath, which looks like below, it takes around 600 ms. Query profiler shows the use of full index.

xmldb:xcollection('/db/projects/')/Proj[fn:contains(ProjectId,'PXXXXX')] 

  

My requirement is such a way that, given 600 projectId's, I need to return 600 XML's corresponding to each "projectId".

I have tried to write it in three different ways, below you will find I have used only two project id's for convenience.


1) xmldb:xcollection('/db/projects/')/Proj[fn:contains(ProjectId,'PXXXXX') or fn:contains(ProjectId,'PYYYYY')]

2) xmldb:xcollection('/db/projects/')/Proj[fn:contains(ProjectId,'PXXXXX')] | xmldb:xcollection('/db/projects/')/Proj[fn:contains(ProjectId,'PYYYYY')]

3) for $i in ('PXXXXX','PYYYYY')
    let $project := xmldb:xcollection('/db/projects/')/Proj[fn:contains(ProjectId,$i)]
    return if (exists($project)) then 
                $project
           else ()

The first approach on average takes around 11 secs for 2 project Id's and 15 secs for 3 project Id's
The second and third approach takes a around 1200 ms for 2 project Id's and 1800 ms for 3 project Id's

The second and third approach timings are directly proportional to the number of input project id's 

1 project id 600 ms
2 project id's 1200 ms
3 project id's 1800 ms

So, if i extrapolate it to 600 project id's , it will take around 6 minutes. Is there any other way, this query can be optimized ?

I think I gave all the needed information. If you need more, don't hesitate to reply back.

Thanks,
Vamshi