Thank you both! I was in the middle of typing a response to Bridger when this came through, so while I digest both of your messages & test index addition/moving things around, I'll leave the output from the GUI info panel here - if I knew how to read it, it would no doubt point straight at the issue! I'll note that it runs much quicker locally as opposed to on server+via the python BaseXClient, but I don't know if that's because the GUI is stopping at 500k results, or what.
Compiling: - rewrite context value to document-node() sequence: . -> (db:open-pre("umg-118061851424", 0), ...) - rewrite util:root(nodes) to document-node() sequence: util:root((db:open-pre("umg-118061851424", 0), ...)) -> (db:open-pre("umg-118061851424", 0), ...) - move where clause: $track_release_1/ReleaseLabelReference = $party_2/PartyReference - swap operands: PartyReference = $track_release_1/ReleaseLabelReference - rewrite to predicate: PartyReference = $track_release_1/ReleaseLabelReference - move where clause: $track_release_1/ReleaseResourceReference = $sound_recording_3/ResourceReference - swap operands: ResourceReference = $track_release_1/ReleaseResourceReference - rewrite to predicate: ResourceReference = $track_release_1/ReleaseResourceReference - swap operands: ResourceGroup/ResourceGroup/ResourceGroupContentItem/ReleaseResourceReference = $track_release_1/Rel... - rewrite to predicate: ResourceGroup/ResourceGroup/ResourceGroupContentItem/ReleaseResourceReference = $track_release_1/Rel... - inline for $release_4 in $r_0/ReleaseList/Release[ResourceGroup/ResourceGroup/ResourceGroupContentItem/Rele...
Optimized Query: for $r_0 in (db:open-pre("umg-118061851424", 0), ...)/ernm:NewReleaseMessage for $track_release_1 in $r_0/ReleaseList/TrackRelease for $party_2 in $r_0/PartyList/Party[PartyReference = $track_release_1/ReleaseLabelReference] for $sound_recording_3 in $r_0/ResourceList/SoundRecording[ResourceReference = $track_release_1/ReleaseResourceReference] return $r_0/ReleaseList/Release[ResourceGroup/ResourceGroup/ResourceGroupContentItem/ReleaseResourceReference = $track_release_1/ReleaseResourceReference] ! <identity> <isrc> { $track_release_1/ReleaseId/ISRC/text() } </isrc> <artist> { string-join($sound_recording_3/DisplayArtistName, "/") } </artist> <title> { $sound_recording_3/DisplayTitleText/text() } </title> <album> { DisplayTitleText/text() } </album> <icpn> { ReleaseId/ICPN/text() } </icpn> <sublabel> { $party_2/PartyName/FullName/text() } </sublabel> </identity>
Query: declare namespace ernm="http://ddex.net/xml/ern/411"; for $r in /ernm:NewReleaseMessage for $track_release in $r/ReleaseList/TrackRelease for $party in $r/PartyList/Party for $sound_recording in $r/ResourceList/SoundRecording for $release in $r/ReleaseList/Release where $track_release/ReleaseLabelReference = $party/PartyReference and $track_release/ReleaseResourceReference = $sound_recording/ResourceReference and $track_release/ReleaseResourceReference = $release/ResourceGroup/ResourceGroup/ResourceGroupContentItem/ReleaseResourceReference return <identity> <isrc>{ $track_release/ReleaseId/ISRC/text() }</isrc> <artist>{ fn:string-join($sound_recording/DisplayArtistName, '/') }</artist> <title>{ $sound_recording/DisplayTitleText/text() }</title> <album>{ $release/DisplayTitleText/text() }</album> <icpn>{ $release/ReleaseId/ICPN/text() }</icpn> <sublabel>{ $party/PartyName/FullName/text() }</sublabel> </identity>
Result: - Hit(s): 500000 Items - Updated: 0 Items - Printed: 8192 kB - Read Locking: umg-118061851424 - Write Locking: (none)
Timing: - Parsing: 2.88 ms - Compiling: 4.38 ms - Evaluating: 114935.22 ms - Printing: 114.59 ms - Total Time: 115057.07 ms
Query Plan: <QueryPlan compiled="true" updating="false"> <GFLWOR type="element()*"> <For type="element()" size="1" name="$r" id="0"> <IterPath type="element()*" database="umg-118061851424"> <DBNodeSeq type="document-node()+" size="136938" database="umg-118061851424"> <DBNode pre="0" type="document-node()" size="1" database="umg-118061851424"/> <DBNode pre="59" type="document-node()" size="1" database="umg-118061851424"/> <DBNode pre="118" type="document-node()" size="1" database="umg-118061851424"/> <DBNode pre="177" type="document-node()" size="1" database="umg-118061851424"/> <DBNode pre="236" type="document-node()" size="1" database="umg-118061851424"/> </DBNodeSeq> <IterStep axis="child" test="ernm:NewReleaseMessage" type="element()*"/> </IterPath> </For> <For type="element()" size="1" name="$track_release" id="1"> <IterPath type="element()*" database="umg-118061851424"> <VarRef type="element()" size="1" database="umg-118061851424" name="$r" id="0"/> <IterStep axis="child" test="ReleaseList" type="element()*"/> <IterStep axis="child" test="TrackRelease" type="element()*"/> </IterPath> </For> <For type="element()" size="1" name="$party" id="2"> <IterPath type="element()*" database="umg-118061851424"> <VarRef type="element()" size="1" database="umg-118061851424" name="$r" id="0"/> <IterStep axis="child" test="PartyList" type="element()*"/> <IterStep axis="child" test="Party" type="element()*"> <CmpHashG op="=" type="xs:boolean" size="1"> <SingleIterPath type="element()*" database="umg-118061851424"> <IterStep axis="child" test="PartyReference" type="element()*"/> </SingleIterPath> <IterPath type="element()*" database="umg-118061851424"> <VarRef type="element()" size="1" database="umg-118061851424" name="$track_release" id="1"/> <IterStep axis="child" test="ReleaseLabelReference" type="element()*"/> </IterPath> </CmpHashG> </IterStep> </IterPath> </For> <For type="element()" size="1" name="$sound_recording" id="3"> <IterPath type="element()*" database="umg-118061851424"> <VarRef type="element()" size="1" database="umg-118061851424" name="$r" id="0"/> <IterStep axis="child" test="ResourceList" type="element()*"/> <IterStep axis="child" test="SoundRecording" type="element()*"> <CmpHashG op="=" type="xs:boolean" size="1"> <SingleIterPath type="element()*" database="umg-118061851424"> <IterStep axis="child" test="ResourceReference" type="element()*"/> </SingleIterPath> <IterPath type="element()*" database="umg-118061851424"> <VarRef type="element()" size="1" database="umg-118061851424" name="$track_release" id="1"/> <IterStep axis="child" test="ReleaseResourceReference" type="element()*"/> </IterPath> </CmpHashG> </IterStep> </IterPath> </For> <DualMap type="element()*"> <IterPath type="element()*" database="umg-118061851424"> <VarRef type="element()" size="1" database="umg-118061851424" name="$r" id="0"/> <IterStep axis="child" test="ReleaseList" type="element()*"/> <IterStep axis="child" test="Release" type="element()*"> <CmpHashG op="=" type="xs:boolean" size="1"> <IterPath type="element()*" database="umg-118061851424"> <IterStep axis="child" test="ResourceGroup" type="element()*"/> <IterStep axis="child" test="ResourceGroup" type="element()*"/> <IterStep axis="child" test="ResourceGroupContentItem" type="element()*"/> <IterStep axis="child" test="ReleaseResourceReference" type="element()*"/> </IterPath> <IterPath type="element()*" database="umg-118061851424"> <VarRef type="element()" size="1" database="umg-118061851424" name="$track_release" id="1"/> <IterStep axis="child" test="ReleaseResourceReference" type="element()*"/> </IterPath> </CmpHashG> </IterStep> </IterPath> <CElem type="element()" size="1"> <QNm type="xs:QName" size="1">identity</QNm> <CElem type="element()" size="1"> <QNm type="xs:QName" size="1">isrc</QNm> <IterPath type="text()*" database="umg-118061851424"> <VarRef type="element()" size="1" database="umg-118061851424" name="$track_release" id="1"/> <IterStep axis="child" test="ReleaseId" type="element()*"/> <IterStep axis="child" test="ISRC" type="element()*"/> <IterStep axis="child" test="text()" type="text()*"/> </IterPath> </CElem> <CElem type="element()" size="1"> <QNm type="xs:QName" size="1">artist</QNm> <FnStringJoin name="string-join" type="xs:string" size="1"> <IterPath type="element()*" database="umg-118061851424"> <VarRef type="element()" size="1" database="umg-118061851424" name="$sound_recording" id="3"/> <IterStep axis="child" test="DisplayArtistName" type="element()*"/> </IterPath> <Str type="xs:string" size="1">/</Str> </FnStringJoin> </CElem> <CElem type="element()" size="1"> <QNm type="xs:QName" size="1">title</QNm> <IterPath type="text()*" database="umg-118061851424"> <VarRef type="element()" size="1" database="umg-118061851424" name="$sound_recording" id="3"/> <IterStep axis="child" test="DisplayTitleText" type="element()*"/> <IterStep axis="child" test="text()" type="text()*"/> </IterPath> </CElem> <CElem type="element()" size="1"> <QNm type="xs:QName" size="1">album</QNm> <IterPath type="text()*" database="umg-118061851424"> <IterStep axis="child" test="DisplayTitleText" type="element()*"/> <IterStep axis="child" test="text()" type="text()*"/> </IterPath> </CElem> <CElem type="element()" size="1"> <QNm type="xs:QName" size="1">icpn</QNm> <IterPath type="text()*" database="umg-118061851424"> <IterStep axis="child" test="ReleaseId" type="element()*"/> <IterStep axis="child" test="ICPN" type="element()*"/> <IterStep axis="child" test="text()" type="text()*"/> </IterPath> </CElem> <CElem type="element()" size="1"> <QNm type="xs:QName" size="1">sublabel</QNm> <IterPath type="text()*" database="umg-118061851424"> <VarRef type="element()" size="1" database="umg-118061851424" name="$party" id="2"/> <IterStep axis="child" test="PartyName" type="element()*"/> <IterStep axis="child" test="FullName" type="element()*"/> <IterStep axis="child" test="text()" type="text()*"/> </IterPath> </CElem> </CElem> </DualMap> </GFLWOR> </QueryPlan>
On Fri, Aug 21, 2020 at 1:48 PM Liam R. E. Quin liam@fromoldbooks.org wrote:
On Fri, 2020-08-21 at 12:51 -0700, Bill Osmond wrote:
declare namespace ernm="http://ddex.net/xml/ern/411"; for $r in /ernm:NewReleaseMessage for $track_release in $r/ReleaseList/TrackRelease for $party in $r/PartyList/Party for $sound_recording in $r/ResourceList/SoundRecording for $release in $r/ReleaseList/Release where $track_release/ReleaseLabelReference = $party/PartyReference and $track_release/ReleaseResourceReference =$sound_recording/ResourceReference and $track_release/ReleaseResourceReference = $release/ResourceGroup/ResourceGroup/ResourceGroupContentItem/Release ResourceReference
BaseX is probably smart enough to rewrite this, but check -
for $r in /ernm:NewReleaseMessage for $track_release in $r/ReleaseList/TrackRelease where $track_release/ReleaseLabelReference = $party/PartyReference
return for $party in $r/PartyList/Party for $sound_recording in $r/ResourceList/SoundRecording where $track_release/ReleaseResourceReference = $sound_recording/ResourceReference return for $release in $r/ReleaseList/Release where $track_release/ReleaseResourceReference = $release/ResourceGroup/ResourceGroup/ResourceGroupContentItem/ReleaseRe sourceReference return <identity>...
Am I wrong, and would an additional value index help here? Or is my query just bad?
You're computing every possible combiation of 5 items and then filtering out the ones you want.
Filtering out earlier would probably help. Also, moving the tests least lilely to match to the outside would reduce the number of tests sooner.
A value index might well help, but as Bridger wrote, check in the gUI to see the query plan. BaseX might already be doing the sort of rewrite i suggested.
Liam
-- Liam Quin, https://www.delightfulcomputing.com/ Available for XML/Document/Information Architecture/XSLT/ XSL/XQuery/Web/Text Processing/A11Y training, work & consulting. Barefoot Web-slave, antique illustrations: http://www.fromoldbooks.org