How to improve this query?

, ,
1

Hello everyone. I need to improve this query because it takes too much time to get a response. I need to solve it soon and i can’t get a better way to do it :frowning:

There’s algo a bigger problem: when the result has lots of entries, it gives the following error:

Database access failure (9291, INOXYE9291, Transaction aborted because it has taken too long)

The info in the database is like this:

0123 2008-06-15 16:44:42.87 28 01

And my query is:

declare namespace tf=‘http://namespaces.softwareag.com/tamino/TaminoFunction’
declare namespace xs=‘XML Schema’
for $i in input()/Petition
let $endDate := $i/endingDate
where $i/Info[tf:containsText(ZoneCode,‘28’) and tf:containsText(AdminCode,‘01’)]
and xs:string($endDate) >= xs:string(‘2008-06-01 00:00:00’) and xs:string($endDate) <= xs:string(‘2008-06-31 23:59:59’)
sort by ($i/IdPetition)
return {$i/IdPetition/text()}

If someone knows the way to make the query plus effective, or the way to limit the results so that i don’t get the error…
Thank you so much!

2

Hi,

I do not know much about your schema, you should definitely consider defining indexes. Nevertheless, some remarks:


where $i/Info[tf:containsText(ZoneCode,'28') and tf:containsText(AdminCode,'01')]

first lets me wonder why you use tf:containsText rather than a simple equality (eq or =)
in addition, combining where and filter is possible, but gives the optimizer hard times. Alternatively, this could be stated as


for $i in input()/Petition
let $endDate := $i/endingDate
let $info := ( for $inf in $i/Info
where tf:containsText($inf/ZoneCode,'28') and tf:containsText($inf/AdminCode,'01'))
where $info and...

or, if you have only one Info node per document, even simpler:


for $i in input()/Petition
let $endDate := $i/endingDate
let $info := $i/Info
where tf:containsText($info/ZoneCode,'28') and tf:containsText($info/AdminCode,'01') and ...

respectivley, if text functionality is not really needed


for $i in input()/Petition
let $endDate := $i/endingDate
let $info := $i/Info
where $info/ZoneCode='28' and $info/AdminCode = '01' and ...

I any case, you should have an combined index on $info/ZoneCode and $info/AdminCode, a text index if text functionality is really needed, a standard index otherwise

next thing to wonder about is


xs:string($endDate) >= xs:string('2008-06-01 00:00:00') and xs:string($endDate) <= xs:string('2008-06-31 23:59:59')

why do you convert $endDate to String? Tamino can do comparison on dates. And also here, you might consider defining an index

3

The basic questions are:

  1. which indexes are specified in the schema ?

  2. why are you using containsText ???

  • this is the freetext-search (i.e. “does ZoneCode contain the text 28 at some position”? - not the direct match)

  1. the date appear to be “dateTime” fields in the schema, so perhaps you should use that format for your search

  2. ZoneCode and AdminCode - are these defined as string or some numeric format
    quoute should only be used if they are strings !

I don’t have any Tamino on this machine so I can’t do any syntax verification - but the version below might work better.

  • especially if you have index on endingDate, ZoneCde and AdminCode :wink:

Finn

for $i in input()/Petition
let $endDate := $i/endingDate
where $i/Info[ZoneCode =‘28’ and AdminCode =‘01’]
and ($endDate >= xs:dateTime(‘2008-06-01 00:00:00’) and $endDate <= xs:dateTime(‘2008-06-31 23:59:59’)
sort by ($i/IdPetition)
return {$i/IdPetition/text()}