Menu
▾
▴
Re: [Exist-open] xquery nested loops
|
From: Adam R. <ad...@ex...> - 2008-05-09 08:33:31
|
Netbeans 6 has a built in profiler if you work with Netbeans, otherwise there is YourKit Java Profiler which you could probably get a trial of. But I would recommend just trying to understand which are the slowest aspects of your query by a process of elimination first. Once you know you can confirm this by running those aspects standalone and letting us know which bits of your query are slow. We can then make suggestions and perhaps break out the profiler from there... Thanks Adam. 2008/5/8 Max Gaerber <mga...@ar...>: > Hi, > > thanks für your hints. I've already managed to get some better results > but I guess I need a better way to do the profiling. I noticed some > posts which have very detailed information about where the processing > time goes to. What setup do I need to manage this? The source code > distribution and maybe there is an open source profiler? > > Regards, > > Max > > Adam Retter schrieb: >> I would recommend working through your code bottom-up, commenting >> parts out until you find the slow aspect and then letting us know >> which part and we can then discuss that specifically. >> >> >> 2008/4/29 Max Gaerber <mga...@ar...>: >> >>> Hi, >>> >>> I previously had posted all the code but did not get an answer, so I >>> thought to write less in order to make it easier to scan my problem >>> statement. I don't expect others to solve my problem but I'd be thankful >>> for any pointers that lead to a performance improvement of my setup. >>> >>> The complete xquery and xml snippets is below. >>> The connection between the documents is as follows: >>> - CATEGORY.XML: A category tree to classify product data >>> - PRODUCT:XML: The real data containing product information and >>> references to categories (xinclude) >>> - PUBLICATION.XML: An "export definition" of categories and referenced >>> product information (with extra attributes for special ordering etc.) >>> >>> Thanks, Max >>> >>> >>> XQUERY >>> ------------------------------------------------------------------------------ >>> >>> xquery version "1.0"; >>> >>> (: import module namespace request="http://exist-db.org/xquery/request"; >>> import module namespace session="http://exist-db.org/xquery/session"; >>> import module namespace util="http://exist-db.org/xquery/util"; >>> :) >>> >>> declare function local:Tags($el as node()) as node()* >>> { >>> for $tag in $el/Tag >>> return <span class="tag">{$tag/text()}</span> >>> }; >>> >>> declare function local:Categories($pub as node()) as node()? { >>> if (empty($pub/CategoryLink)) then "keine Kategorien enthalten" >>> else >>> <ul class="categories"> { >>> for $catLink in $pub/CategoryLink >>> let $cat := collection(/Syncrovet/Category)//Category[@Guid = >>> $catLink/@Guid] >>> let $publish := ($catLink/@Publish = "true") >>> order by $catLink/@Ordering >>> return >>> <li class="category {if ($publish) then "included" else >>> "excluded"}" id="{$catLink/@Guid}"> >>> <span class="name">{$cat/Name cast as xs:string}</span> >>> { local:Tags($catLink) } >>> { if ($publish) then local:ProductInfos($catLink) else () } >>> </li> >>> }</ul> >>> }; >>> >>> declare function local:ProductInfos($catLink as node()) as node()? { >>> if (empty($catLink/ProductInfoLink)) then () >>> else >>> <ul class="productinfos"> >>> { >>> for $prodLink in $catLink/ProductInfoLink >>> let $prod := collection(/Syncrovet/Product)//ProductInfo[@Guid = >>> $prodLink/@Guid] >>> let $publish := ($prodLink/@Publish = "true") >>> order by $prodLink/@Ordering >>> return >>> <li class="productinfo {if ($publish) then "included" else >>> "excluded"}" id="{$prodLink/@Guid}"> >>> <span class="name">{$prod/ProductName/text()}</span> >>> { local:Tags($prodLink) } >>> { if ($publish) then local:ProductItems($prodLink, $prod) >>> else ()} >>> </li> >>> } >>> { >>> if (false()) then () else >>> (: new productinfos :) >>> let $old := $catLink/ProductInfoLink/@Guid >>> for $new in //ProductInfo[(CategoryLink/@Guid = >>> $catLink/@Guid) and (not (@Guid = $old))] >>> return <li class="productinfo new" id="{$new/@Guid}"><span >>> class="name">{$new/ProductName/text()}</span></li> >>> (: end of new productitems :) >>> } >>> </ul> >>> }; >>> >>> declare function local:ProductItems($prodLink as node(), $prod as >>> node()) as node()? { >>> (: if (empty($prodLink/ProductItemLink)) then () >>> else :) >>> <ul class="productitems"> { >>> for $artLink in $prodLink/ProductItemLink >>> let $art := collection(/Syncrovet/Product)//ProductItem[@Guid = >>> $artLink/@Guid] >>> let $publish := if ($artLink/@Publish = "true") then " included" >>> else " excluded" >>> order by $artLink/@Ordering >>> return >>> <li class="productitem{$publish}" id="{$art/@Guid}"> >>> <span class="name">{$art/Name/text()}</span> >>> {local:Tags($artLink)} >>> </li> >>> } >>> { (: new productitems :) >>> let $old := $prodLink/ProductItemLink/@Guid >>> for $new in $prod/ProductItem[not (@Guid = $old)] >>> return <li class="productitem new" id="{$new/@Guid}"> >>> <span class="name">{$new/Name/text()}</span> >>> </li> >>> (: end of new productitems :) } >>> </ul> >>> }; >>> >>> >>> (: *** main *** :) >>> let $pub := collection(/Syncrovet/Publication)//Publication[@Guid = '{0}'] >>> return >>> if (empty($pub)) then "Publikation {0} nicht gefunden!" >>> else local:Categories($pub) >>> >>> PUBLICATION XML >>> ------------------------------------------------------------ >>> >>> <Publications> >>> <Publication Guid="2ddeabe9-e84c-4a71-87bc-f79f3ddb4011"> >>> <Title>Produkte</Title> >>> <CategoryLink Guid="2ddeabe9-e84c-4a71-87bc-f79f3ddb4098" >>> Publish="true" Ordering="1"> >>> <ProductInfoLink Guid="26ea764d-1ea4-4539-8880-e97401798e35" >>> Publish="true" Ordering="1"> >>> <Tag>Im Preis gesenkt</Tag> >>> <ProductItemLink >>> Guid="b23b1c71-275f-452b-8fb5-ba61bb9fac71" Publish="true" Ordering="1"/> >>> <ProductItemLink >>> Guid="326d8a69-e0ef-4023-a05b-82c388f4e8c3" Publish="true" Ordering="1"/> >>> </ProductInfoLink> >>> <ProductInfoLink Guid="93cd0ddd-d065-406f-936b-c05b9725f2c1" >>> Publish="true" Ordering="1"> >>> <ProductItemLink >>> Guid="2b18bf89-1cf5-4a44-9e64-04c83d79df8f" Publish="true" Ordering="1"/> >>> </ProductInfoLink> >>> <ProductInfoLink Guid="0b467cf9-4456-4b6d-afdf-73079fca8a32" >>> Publish="true" Ordering="1"> >>> <Tag>Neu</Tag> >>> <ProductItemLink >>> Guid="37605e9e-8ab4-432e-a26f-4ee16ef010c9" Publish="true" Ordering="1"/> >>> </ProductInfoLink> >>> </CategoryLink> >>> </Publication> >>> </Publications> >>> >>> CATEGORY XML >>> ---------------------------------------------------------- >>> >>> <Category Key="ALL" Color="" OldId="1" >>> Guid="8a524645-64c1-4053-8420-00a7e6f23e0a"> >>> <Name>Alle</Name> >>> <Category Key="MED" Color="" OldId="2" >>> Guid="c06c7ea1-53f9-42fe-bfef-3fd65f10232c"><Name>Arzneimittel</Name> >>> <Category Key="VET" Color="" OldId="5" >>> Guid="2ddeabe9-e84c-4a71-87bc-f79f3ddb4098"><Name>WDT-Produkte</Name></Category> >>> <Category Key="HUM" Color="" OldId="6" >>> Guid="282a3e30-49ef-4072-b073-f0b2ee8d251f"><Name>Human-Produkte</Name> >>> <Category Key="Analeptika" Color="" OldId="2320" >>> Guid="62cee356-c8fc-439a-94e7-2e688aae1e9a"><Name>Analeptika/Antihypoxämika</Name></Category> >>> >>> >>> PRODUCT XML >>> ---------------------------------------------------------- >>> >>> <ProductInfos xmlns:xsd="http://www.w3.org/2001/XMLSchema" >>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >>> xmlns:syncrovet2="urn:syncrovet2importtransformation-extensions" >>> xmlns:xi="http://www.w3.org/2001/XInclude" >>> xmlns:xhtml="http://www.w3.org/1999/xhtml"> >>> <ProductInfo Guid="26ea764d-1ea4-4539-8880-e97401798e35" >>> OldId="1001" Version="1" WorkflowStatus="Entw." >>> CreatedAt="2005-09-08T11:25:09.046" ModifiedAt="2005-10-23T13:49:23.718" >>> ModifiedBy="112"> >>> <ProductName>Acetatmischung</ProductName> >>> <AtcCode /> >>> <ActiveSubstance /> >>> <Prescription>Nur beim Tierarzt erhältlich</Prescription> >>> <ApprovalNr></ApprovalNr> >>> <RegNr></RegNr> >>> <Description>Pulver zur Anwendung auf der Haut</Description> >>> <ProductClassification>Tierarzneimittel</ProductClassification> >>> <IndexSortName>ACETATMISCHUNG</IndexSortName> >>> <CompanyLink Name="Holder" >>> Guid="91a8c40f-db80-44e5-b093-e8b44ec3844c"> >>> <xi:include href="../Company/Company.xml" >>> xpointer="xpointer(//Company[@Guid='91a8c40f-db80-44e5-b093-e8b44ec3844c']/ShortName)" >>> /> >>> </CompanyLink> >>> <CategoryLink Guid="2ddeabe9-e84c-4a71-87bc-f79f3ddb4098"> >>> <xi:include href="../Category/Category.xml" >>> xpointer="xpointer(//Category[@Guid='2ddeabe9-e84c-4a71-87bc-f79f3ddb4098']/Name)" >>> /> >>> </CategoryLink> >>> <ProductText Key="Tiere" >>> Guid="ede0d147-c0f8-43bf-a349-4d02898ca83e" OldId="1001" Version="1" >>> WorkflowStatus="Entw." CreatedAt="2005-09-08T11:25:09.796" >>> ModifiedAt="2005-11-09T19:37:42.078" ModifiedBy="1" >>> xmlns="http://www.w3.org/1999/xhtml"> >>> <p>Pferde, Rinder, Schweine, Schafe, Ziegen, Hunde und >>> Katzen</p> >>> </ProductText> >>> <ProductItem Guid="b23b1c71-275f-452b-8fb5-ba61bb9fac71" >>> OldId="1001" Version="1" WorkflowStatus="Entw." >>> CreatedAt="2005-09-08T11:25:10.406" ModifiedAt="2007-11-13T10:25:58.631" >>> ModifiedBy="1"> >>> <Name>Acetatmischung</Name> >>> <CompanyLink Name="Supplier" >>> Guid="91a8c40f-db80-44e5-b093-e8b44ec3844c"> >>> <xi:include href="../Company/Company.xml" >>> xpointer="xpointer(//Company[@Guid='91a8c40f-db80-44e5-b093-e8b44ec3844c']/ShortName)" >>> /> >>> </CompanyLink> >>> <SupplierArticleId>04413</SupplierArticleId> >>> <Ean>4030996044135</Ean> >>> <Dispensary>1g</Dispensary> >>> <PriceInfo Guid="ee5c334c-0555-4c0c-9f0b-890981d00172" >>> OldId="6745340" Version="1" WorkflowStatus="Entw." >>> CreatedAt="2007-11-06T00:05:11.808" ModifiedAt="2007-11-06T00:05:11.808" >>> ModifiedBy="1"> >>> <StartDate>2008-01-01T00:00:00</StartDate> >>> <EndDate>3000-01-01T00:00:00</EndDate> >>> <Amount>6.9</Amount> >>> <Currency>EUR</Currency> >>> <Tax>19</Tax> >>> <PriceType>TA-EK</PriceType> >>> <Rebate>0</Rebate> >>> <Country>GER</Country> >>> <OrderQuantity>1</OrderQuantity> >>> <GrossPrice>12.89</GrossPrice> >>> <AmPreisV>10.83</AmPreisV> >>> </PriceInfo> >>> <PriceInfo Guid="11ed25c2-6b34-482d-ae74-97c6110cdbcc" >>> OldId="6745335" Version="1" WorkflowStatus="Entw." >>> CreatedAt="2007-11-06T00:05:11.511" ModifiedAt="2007-11-06T00:05:11.511" >>> ModifiedBy="1"> >>> <StartDate>2007-01-01T00:00:00</StartDate> >>> <EndDate>3000-01-01T00:00:00</EndDate> >>> <Amount>6.7</Amount> >>> <Currency>EUR</Currency> >>> <Tax>19</Tax> >>> <PriceType>TA-EK(alt)</PriceType> >>> <Rebate>0</Rebate> >>> <Country>GER</Country> >>> <OrderQuantity>1</OrderQuantity> >>> <GrossPrice>12.52</GrossPrice> >>> <AmPreisV>10.52</AmPreisV> >>> </PriceInfo> >>> </ProductItem> >>> </ProductInfo> >>> >>> >>> >>> >>> >>> >>> >>> Adam Retter schrieb: >>> >>>> There is a lot you havent told us, these two function are called from >>>> inside your return - local:Tags($catLink) and >>>> local:ProductInfos($catLink), yet you have not included any details of >>>> these functions - these could be the cause? >>>> >>>> 2008/4/28 Max Gaerber <mga...@ar... <mailto:mga...@ar...>>: >>>> >>>> Hi, >>>> >>>> Adam Retter schrieb: >>>> > >>>> > Have you set up appropriate indexes? >>>> Well, I had the fulltext index specified for everything but now added >>>> qnames for Publish and Ordering (see below). There is a small >>>> improvement but still I don't know why such a query should take more >>>> than 1-2 seconds. I have no idea if my xquery is so expensive or >>>> what I >>>> should change to not have a near 100% processor load for more than >>>> 5-10 >>>> seconds... >>>> >>>> Thanks, Max >>>> >>>> <collection xmlns="http://exist-db.org/collection-config/1.0"> >>>> <index> >>>> <fulltext default="all" attributes="yes"/> >>>> <!-- Range indexes --> >>>> <create qname="@Guid" type="xs:string"/> >>>> <create qname="@Publish" type="xs:boolean"/> >>>> <create qname="@Ordering" type="xs:integer"/> >>>> </index> >>>> </collection> >>>> > >>>> > >>>> > 2008/4/25 Max Gaerber <mga...@ar... >>>> <mailto:mga...@ar...> <mailto:mga...@ar... >>>> <mailto:mga...@ar...>>>: >>>> > >>>> > Hi all, >>>> > >>>> > I've got a question about performance tuning and nested >>>> xquery loops. >>>> > The data to be queried has several nesting levels, e.g. >>>> > >>>> > <Publication Guid="a"> >>>> > <CategoryLink Guid="b" Ordering="1" Publish="true"> >>>> > <ProductInfoLink Guid="c" ...> >>>> > <ProductItemLink Guid="d" ...> >>>> > >>>> > A Publication has 5-20 Category elements with each about 50 >>>> > ProductInfo >>>> > elements with each 2-10 ProductItem. Publication is only a >>>> "wrapper" >>>> > declaring some extra information whereas the "real" data of >>>> > ProductInfo >>>> > etc. come from another collection. >>>> > >>>> > There are three functions similar to the one below which >>>> loop through >>>> > the element set and create xhtml for display on a web site. The >>>> > call to >>>> > local:ProductInfos($catLink) processes the next nesteing >>>> level and so >>>> > on. Now, the query is quite slow ( > 10 seconds ) even for small >>>> > Publications. Is there a better way to handle this sort of >>>> nesting? >>>> > >>>> > declare function local:Categories($pub as node()) as node()? { >>>> > <ul class="categories"> { >>>> > for $catLink in $pub/CategoryLink >>>> > let $cat := >>>> collection(/Syncrovet/Category)//Category[@Guid = >>>> > $catLink/@Guid] >>>> > let $publish := ($catLink/@Publish = "true") >>>> > order by $catLink/@Ordering >>>> > return >>>> > <li class="category {if ($publish) then "included" else >>>> > "excluded"}" id="{$catLink/@Guid}"> >>>> > <span class="name">{$cat/Name cast as >>>> xs:string}</span> >>>> > { local:Tags($catLink) } >>>> > { if ($publish) then local:ProductInfos($catLink) >>>> else () } >>>> > </li> >>>> > }</ul> >>>> > }; >>>> > >>>> > Thanks, >>>> > >>>> > Max >>>> > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> ------------------------------------------------------------------------- >>>> > This SF.net email is sponsored by the 2008 JavaOne(SM) >>>> Conference >>>> > Don't miss this year's exciting event. There's still time to >>>> save >>>> > $100. >>>> > Use priority code J8TL2D2. >>>> > >>>> http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone >>>> > _______________________________________________ >>>> > Exist-open mailing list >>>> > Exi...@li... >>>> <mailto:Exi...@li...> >>>> > <mailto:Exi...@li... >>>> <mailto:Exi...@li...>> >>>> > https://lists.sourceforge.net/lists/listinfo/exist-open >>>> > >>>> > >>>> > >>>> > >>>> > -- >>>> > Adam Retter >>>> > >>>> > eXist Developer >>>> > { England } >>>> > ad...@ex... <mailto:ad...@ex...> >>>> <mailto:ad...@ex... <mailto:ad...@ex...>> >>>> > irc://irc.freenode.net/existdb <http://irc.freenode.net/existdb> >>>> <http://irc.freenode.net/existdb> >>>> >>>> >>>> ------------------------------------------------------------------------- >>>> This SF.net email is sponsored by the 2008 JavaOne(SM) Conference >>>> Don't miss this year's exciting event. There's still time to save >>>> $100. >>>> Use priority code J8TL2D2. >>>> http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone >>>> _______________________________________________ >>>> Exist-open mailing list >>>> Exi...@li... >>>> <mailto:Exi...@li...> >>>> https://lists.sourceforge.net/lists/listinfo/exist-open >>>> >>>> >>>> >>>> >>>> -- >>>> Adam Retter >>>> >>>> eXist Developer >>>> { England } >>>> ad...@ex... <mailto:ad...@ex...> >>>> irc://irc.freenode.net/existdb <http://irc.freenode.net/existdb> >>>> >>> ------------------------------------------------------------------------- >>> This SF.net email is sponsored by the 2008 JavaOne(SM) Conference >>> Don't miss this year's exciting event. There's still time to save $100. >>> Use priority code J8TL2D2. >>> http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone >>> _______________________________________________ >>> Exist-open mailing list >>> Exi...@li... >>> https://lists.sourceforge.net/lists/listinfo/exist-open >>> >>> >> >> >> >> > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by the 2008 JavaOne(SM) Conference > Don't miss this year's exciting event. There's still time to save $100. > Use priority code J8TL2D2. > http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone > _______________________________________________ > Exist-open mailing list > Exi...@li... > https://lists.sourceforge.net/lists/listinfo/exist-open > -- Adam Retter eXist Developer { England } ad...@ex... irc://irc.freenode.net/existdb |