From: Max G. <mga...@ar...> - 2008-04-25 17:42:37
|
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 |
From: Adam R. <ad...@ex...> - 2008-04-28 08:15:27
|
Have you set up appropriate indexes? 2008/4/25 Max Gaerber <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... > https://lists.sourceforge.net/lists/listinfo/exist-open > -- Adam Retter eXist Developer { England } ad...@ex... irc://irc.freenode.net/existdb |
From: Max G. <mga...@ar...> - 2008-04-28 17:25:05
|
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...>>: > > 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...> > 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> |
From: Adam R. <ad...@ex...> - 2008-04-29 08:22:33
|
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...>: > 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...>>: > > > > 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...> > > 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 > -- Adam Retter eXist Developer { England } ad...@ex... irc://irc.freenode.net/existdb |
From: Max G. <mga...@ar...> - 2008-04-29 09:11:11
|
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> |
From: Max G. <mga...@ar...> - 2008-05-08 19:57:04
|
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 >> >> > > > > |
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 |