Greetings,
I have two documents, a FuelFiling and a FuelTransaction.
FuelFiling contains header information.
A FuelFiling can have one to many FuelTransactions.
The documents are linked by a ConfirmationNum.
ConfirmationNum is defined as index in both documents, using both standard and text indexes.
I try to find out how many FuelTransaction records there are for each of the FuelFiling record.
My query works fine in development, which has a few dozen records.
But it always time out in production (after a long run time), and resulted in INOXYE9291.
I extended Tamino maximum transaction duration and XML query to 1800 seconds, but it doesn’t help.
My production database has 500 FuelFiling records and 2.7 millions FuelTransaction records.
The database space=4.2GB, index space=500MB, journal=300MB
The server has dual Xeon, 512MB RAM, plenty disk storage .etc.
Here is my simple query:
for $a in input()/FuelFiling
return {
$a/ConfirmationNum,
$a/CompleteDate,
let $b := count(input()/FuelTransaction[ConfirmationNum = $a/ConfirmationNum])
return $b
}
Any advice is greatly appreciated.
Hi,
I guess that your are working with Tamino 4.1.4.4 and that the transaction timeout is caused by the fact that the count expression in the subquery is not executed index-only. In order to enable index-only execution you have to verify that the path /FuelTransaction/ConfirmationNum has multiplicity 1 and that an index is defined on /FuelTransaction/ConfirmationNum. Since Tamino 4.1.4 has some problems with filters in path expressions, you also should eliminate the filter by introducing a FLWR expression. The reformulated query looks like:
for $a in input()/FuelFiling
return {
$a/ConfirmationNum,
$a/CompleteDate,
let $b := count(for $t in input()/FuelTransaction where $t/ConfirmationNum = $a/ConfirmationNum return $t)
return $b
}
To verify the “index-only” execution you have to look for an “XqcUnnestStdIdxScan” element in the explain output that has a “count-only” attribute that has the value “true”.
Best Regards,
Thorsten Fiebig
Hi,
1) I followed your advice and switch to FLWR expression filter, but still get 9291.
The only time the query works is if I limited the outer result set by adding an exact match filter to FuelFiling.
I.e:
for $a in input()/FuelFiling
where $a/ConfirmationNum=“10000003505”
return {
…
}
Anything else would still fail.
2) The ConfirmationNum has both standard and text index defined, with multiplicity of 1.
I attached a screen print of the FuelTransaction schema.
3) I know about X-query ino:explain() function, but not sure how about using it in XQuery.
Can you provide an example of how to get this info from Xquery ?
Thanks.
AfspSchScreen.doc (51 KB)
Hi,
I can’t find anything wrong with your query. Please have a
look at the explain output. You get this, among others, by
adding an explain directive to the query prolog. Thus the
query will not be executed. Instead, an XML representation
of the execution plan for the query expression is returned
in the result document.
Regards,
Juliane.
Hello,
1) Does the XQuery tool suppose to support query prolog ?explain? ?
Because I tried it and it doesn’t work. The query still get executed and the full result set returned.
2) I can get the query prolog to work using URL/X-machine format, but the problem with this is the restricted input buffer length.
I counldn’t quite fit the entire query, so I have to modify the query to get it to fit.
Anyway, I generated the query plan, and according to the result, Tamino didn’t utilize the index.
So my question is how come, and how to overcome this problem ?
According to the schema definition, the element /FuelTransaction/ConfirmationNum has both Standard and Text indexes defined, with multiplicity of 1, Native storage .etc.
Thanks in advance.
XqExpPrd.xml (3.98 KB)
Hi,
The XQuery Tool of Tamino 4.2 allows you to do an XQuery explain and it also provides some assistance to analyze the explain output.
Your explain output shows that an index is used for evaluating the where clause of your subquery, but it also shows that the subquery isn’t executed index-only.
I have a question concerning the multiplicity of /FuelFiling/ConfirmationNum. Does it have multiplicity 1? It would be very nice if you could give us the schemas of your doctypes FuelFiling and FuelTransaction.
Best Regards,
Thorsten
Hi,
I attach the two involved schemas for your review.
FuelFiling and FuelTransaction.
I am a bit unclear on your statement “an index is used for evaluating the where clause of your subquery, but it also shows that the subquery isn’t executed index-only”.
Please explain.
Thanks.
FuelTransactionSchema.TSD (14.8 KB)
I guess the Reply dialog only allows one attachment at a time.
Here is the FuelFiling schema.
FuelFilingSchema.TSD (38.7 KB)
Hi,
Thank you for your schemas! They were very helpful to analyze the problem. By the sentence “an index is used for evaluating the where clause of your subquery, but it also shows that the subquery isn’t executed index-only” I meant that the join operation is performed by accessing the index defined on /FuelTransaction/ConfirmationNum. But the XQuery optimizer assumes that it gets some false positives from the index. So, he decides to do some additional filtering.
In your scenario this is due to the fact that Tamino 4.1 has some problems with join predicates on xs:string elements (attributes).
To improve the performance of your query I would suggest to change the type of the elements /FuelTransaction/ConfirmationNum and /FuelFiling/ConfirmationNum to a numeric type. If this isn’t possible you should consider to use the new Tamino 4.2 version, since it doesn’t have problems with join predicates on xs:string elements. It further provides an “index-only” join method which can help to improve the performance of your query.
Bests regards,
Thorsten