slow equi-join performance on small database, times out

, ,
1

I have a reasonably straightforward equi-join xQuery:

for $ap in input()/AccidentPackage, $apf in $ap/Form12209, $form in input()/Form12209
let $fid := $form/Identification/FormNumber
where $ap/AccidentPackageNumber/@year = 2003
and $fid/@year = $apf/@year
and $fid/@number = $apf/@number
return $ap

With just over 320 AccidentPackage documents (16 that meet the @year criteria) and about 900 Form12209 documents, the query takes 2 - 3 minutes to run on a W2K machine (and the transaction is aborted by Tamino for taking too long if selecting year=2004, which has 90% of the data). I have verified that both doctypes have standard and text indexes turned on, although the query is only marginally slower indexes off. Each AccidentPackage contains links to 1 to 3 Form12209 documents.

Explain appears to be indicating that all the documents from both doctypes will be read. Even so, I would expect such a small database to be read in less time…

Any suggestions on how to improve performance?

Thanks, eh!

Douglas Kelly,
Principal Consultant
Software AG, Inc
Sacramento, California
AKDOT.tsd (258 KB)

2

Hi,

According to your AKDOT.tsd schema you have defined a standard and a text index on the document root element. This does not imply that there is any index on the element or attributes referenced by the join predicate. In order to enable index-support for you join query you have to define an index on:

/AccidentPackage/AccidentPackageNumber/@year
/AccidentPackage/Form12209/@number
/AccidentPackage/Form12209/@year
/Form12209/Identification/FormNumber/@year
/Form12209/Identification/FormNumber/@number

Best regard,

Thorsten Fiebig

3

ok, that difference was not trivial! Subsecond response now!

Are the indexes on the root elements serving any purpose then? What types of queries would indexes on the root element support?

Douglas Kelly,
Principal Consultant
Software AG, Inc
Sacramento, California

4

Hi,

Defining a standard index on the root level is only useful if you are doing queries of a very specific type such as:

for $a in input()/bib
where string($a) = ?xyz?
return $a

Also the text index on the root level only supports a very restricted set of queries. But in contrast to the standard index I can image real use-cases like full-text search queries on whole documents.

Best regards,

Thorsten Fiebig