Main Page » Mailing List » August 2014

optimize BaseX performance

Thursday, 21 August 2014 · 12 messages · last reply 25 Aug 2014
KC
Kunal Chauhan21 Aug 2014 15:40 #
Hello, I have a 1GB sized xml file. which contains 94,50,001 records. while I fired query to retrieve all the records it will take approx 30 secs to execute a query. *I fired below mentioned query using Java API (QueryProcessor). * "transaction/* except (/transaction/traInfo)" QueryProcessor proc = new QueryProcessor(query, context);
        Result result = proc.execute();
        System.out.println(result.size());
Iter itr = proc.iter(); it took *40 Secs* on execution of the above code. is there a scope of improvement without changing the query ? *My XML file is look like this.*
<transaction>

<traInfo id="ti1">
<date>01-01-2014</date>
<source>bank1</source>
</traInfo>

<traInfo id="ti2">
<date>01-01-2014</date>
<source>bank2</source>
</traInfo>

<traInfo id="ti3">
<date>01-01-2014</date>
<source>bank3</source>
</traInfo>

<income transInfoRef="ti1">1000</income>
<assets transInfoRef="ti1">1000</assets>
<liablity transInfoRef="ti1">1000</liablity>
<grossprofit transInfoRef="ti1">1000</grossprofit>

<income transInfoRef="ti2">1000</income>
<assets transInfoRef="ti2">1000</assets>
<liablity transInfoRef="ti2">1000</liablity>
<grossprofit transInfoRef="ti2">1000</grossprofit>

<income transInfoRef="ti3">1000</income>
<assets transInfoRef="ti3">1000</assets>
<liablity transInfoRef="ti3">1000</liablity>
<grossprofit transInfoRef="ti3">1000</grossprofit>
...
</transaction>
-- *Kunal Chauhan* mail4ck at gmail.com [+918655517141]
CG
Christian Grün21 Aug 2014 15:59 #
Hi Kunal, we need more information to help you here. How does your query look like? Does it benefit from index structures (please check out the output in the QueryInfo panel)? Are your index structures up-to-date?
Quoted text · Kunal Chauhan · 3 lines
> while I fired query to retrieve all the records it will take approx 30 secs > to execute a query. > System.out.println(result.size());
Just in case: printing something to stdout takes often much more time than evaluating the query. Christian
KC
Kunal Chauhan21 Aug 2014 17:07 #
Hi Christian, how to print QueryInfo through Java API ? I checked query in BaseX GUI and over there it shows Total time : 21 secs. What else do I need to check in QueryInfo panel ? apart from this following is my DB info. *Indexes* Up-to-date: true TEXTINDEX: true ATTRINDEX: true FTINDEX: true LANGUAGE: English STEMMING: false CASESENS: false DIACRITICS: false STOPWORDS: UPDINDEX: false MAXCATS: 100 MAXLEN: 96 and also I observed several timestamp in my code. which are I am sharing with you. QueryProcessor proc = new QueryProcessor(query, context); Result result = proc.execute(); // * took time : 21259 mili secs* System.out.println(result.size()); Iter itr = proc.iter();
               while((item = itr.next()) != null){
     if(count >= start)
      System.out.println(/*item.serialize()*/);
     count++;
     if(count > end)
     break;
} // * loop took 18 More secs to complete.* total time taken is 40 secs for following query. *"transaction/* except (/transaction/traInfo)"* - Kunal
Quoted text · Christian Grün · 19 lines
On Thu, Aug 21, 2014 at 7:29 PM, Christian Grün <christian.gruen at gmail.com> wrote: > Hi Kunal, > > we need more information to help you here. How does your query look > like? Does it benefit from index structures (please check out the > output in the QueryInfo panel)? Are your index structures up-to-date? > > > while I fired query to retrieve all the records it will take approx 30 > secs > > to execute a query. > > > System.out.println(result.size()); > > Just in case: printing something to stdout takes often much more time > than evaluating the query. > > Christian >
-- *Kunal Chauhan* mail4ck at gmail.com [+918655517141]
CG
Christian Grün21 Aug 2014 17:39 #
Quoted text · Kunal Chauhan · 1 line
> how to print QueryInfo through Java API ?
The -V flag or the QUERYINFO option are alternatives; please find more information in the documentation.
Quoted text · Kunal Chauhan · 1 line
> "transaction/* except (/transaction/traInfo)"
You could try to rewrite this to... transaction/* except traInfo ...or...
  let $traInfo := /transaction/traInfo
  return transaction/* except $traInfo
...or... transaction/*[name() ne 'traInfo']
CG
Christian Grün21 Aug 2014 17:41 #
Quoted text · Christian Grün · 1 line
> transaction/* except traInfo
Sorry, this one was nonsense.
KC
Kunal Chauhan22 Aug 2014 12:06 #
Hi Christian, Thanks for your suggestion. It gave me considerable improvement. Now, I am able to get query result in *13 secs* and loop will be completed within half a sec. It's defiantly a remarkable achievement. Still, is there any way to optimize this time ? It would be great if you can explain me why the query and loop took so much time earlier and now it gets completed quickly. Apart from this I have one concern that in my application XQueries will be provided by end users. So, every time I wouldn't be able to change or optimize the query. does BaseX use any query optimizer or can you suggest me any external tool / lib for the same ? Regards, Kunal Chauhan
Quoted text · Christian Grün · 6 lines
On Thu, Aug 21, 2014 at 9:11 PM, Christian Grün <christian.gruen at gmail.com> wrote: > > transaction/* except traInfo > > Sorry, this one was nonsense. >
-- *Kunal Chauhan* mail4ck at gmail.com [+918655517141]
CG
Christian Grün22 Aug 2014 13:31 #
Quoted text · Kunal Chauhan · 2 lines
> It would be great if you can explain me why the query and loop took so much > time earlier and now it gets completed quickly.
In your original query... transaction/* except (/transaction/traInfo) ...the second path expression was evaluated for each result of the first expression. While it would theoretically be possible for the query processor to cache the results of the second expression, it is difficult in practice to decide when this is reasonable. Beside that, you have a potentially large number of sets that need to be compared every time, resulting in max. n*n comparisons (or O(n²)). The following query (which is probably the one you chose?) will always be linear: transaction/*[name() ne 'traInfo']
Quoted text · Kunal Chauhan · 3 lines
> Apart from this I have one concern that in my application XQueries will be > provided by end users. > So, every time I wouldn't be able to change or optimize the query.
It is hardly possible to limit queries of end users to those that are fast enough to be processed in a given time. As an example, the following query will take hours or even days to compute, even if it looks that simple: (1 to 10000000000)[. = 0] However, you can limit evaluation time and memory consumption, as described here: http://docs.basex.org/wiki/XQuery_Module#xquery:eval
Quoted text · Kunal Chauhan · 2 lines
> does BaseX use any query optimizer or can you suggest me any external tool > / lib for the same ?
BaseX would be pretty much worthless without query optimizer, so I don't quite get what you mean by that? Christian
KC
Kunal Chauhan22 Aug 2014 17:07 #
Thanks Christian, I changed the query from *"transaction/* except (/transaction/traInfo)" *to* "**transaction/*[name() ne 'traInfo']**" *as suggested by you. later one takes *13 secs* to complete while former was taking* 21 secs.*
*LOOP *
while((item = itr.next()) != null){
     if(count >= start)
      System.out.println(/*item.serialize()*/);
     count++;
     if(count > end)
     break;
} *Former Query : "transaction/* except (/transaction/traInfo)"* *Loop (executed with java api) ** takes 18-19 seconds* *QueryInfo : * Timing: - Parsing: 1.02 ms - Compiling: 3.53 ms - Evaluating: 21157.98 ms - Printing: 144.41 ms - Total Time: 21306.94 ms *Later Query (suggested by you) **"**transaction/*[name() ne 'traInfo']* *"* *Loop (executed with java api) takes 0.5 secs* *Query Info :* Timing: - Parsing: 1.0 ms - Compiling: 3.46 ms - Evaluating: 15469.8 ms - Printing: 56.87 ms - Total Time: 15531.14 ms *Query will return around 94 lacks items.* So, I was wondering apart from query change, is there any baseX tuning or configuration changes should I do to further improve time from 13 secs. meanwhile I tried to run the same query on a high end machine ( 64 GB RAM, 8 cores Linux machine). BaseX was started with -Xmx32g. I didn't find any improvement in execution time. I am also attaching -Xrunhprof:cpu for former query = java.hprof_former_query.txt later query = java.hprof_later_query.txt Hope above information suffices you. Thanks
Quoted text · Christian Grün · 47 lines
On Fri, Aug 22, 2014 at 5:01 PM, Christian Grün <christian.gruen at gmail.com> wrote: > > It would be great if you can explain me why the query and loop took so > much > > time earlier and now it gets completed quickly. > > In your original query... > > transaction/* except (/transaction/traInfo) > > ...the second path expression was evaluated for each result of the > first expression. While it would theoretically be possible for the > query processor to cache the results of the second expression, it is > difficult in practice to decide when this is reasonable. Beside that, > you have a potentially large number of sets that need to be compared > every time, resulting in max. n*n comparisons (or O(n²)). The > following query (which is probably the one you chose?) will always be > linear: > > transaction/*[name() ne 'traInfo'] > > > Apart from this I have one concern that in my application XQueries will > be > > provided by end users. > > So, every time I wouldn't be able to change or optimize the query. > > It is hardly possible to limit queries of end users to those that are > fast enough to be processed in a given time. As an example, the > following query will take hours or even days to compute, even if it > looks that simple: > > (1 to 10000000000)[. = 0] > > However, you can limit evaluation time and memory consumption, as > described here: > > http://docs.basex.org/wiki/XQuery_Module#xquery:eval > > > does BaseX use any query optimizer or can you suggest me any external > tool > > / lib for the same ? > > BaseX would be pretty much worthless without query optimizer, so I > don't quite get what you mean by that? > > Christian >
-- *Kunal Chauhan* mail4ck at gmail.com [+918655517141]
CG
Christian Grün24 Aug 2014 22:04 #
Hi Kunal, thanks for giving more details.
Quoted text · Kunal Chauhan · 5 lines
> later one takes 13 secs to complete while former was taking 21 secs. > Former Query : "transaction/* except (/transaction/traInfo)" > Loop (executed with java api) takes 18-19 seconds > Later Query (suggested by you) "transaction/*[name() ne 'traInfo']" > Loop (executed with java api) takes 0.5 secs
This is still sth. I don't quite get: does the second query take 0.5 or 13 seconds?
Quoted text · Kunal Chauhan · 1 line
> Query will return around 94 lacks items.
So there are 94 child element that are not named "traInfo", but "lacks"? Two more questions.. What's the total number of child nodes (try count(/transaction/*)?
Quoted text · Kunal Chauhan · 2 lines
> So, I was wondering apart from query change, is there any baseX tuning or > configuration changes should I do to further improve time from 13 secs.
The query profiling results suggest that some additional time is spent for checking namespaces. Maybe the following query is slightly faster: transaction/*[local-name() ne 'traInfo'] The remaining information indicates that most time is spent for sequentially parsing the document. If you work with the client/server architecture, you might experience beneficial caching effects. Hope this helps, Christian
KC
Kunal Chauhan25 Aug 2014 08:59 #
Hi Christian,
Quoted text · Christian Grün · 4 lines
> > This is still sth. I don't quite get: does the second query take 0.5 > or 13 seconds? >
Actually we are doing two things. 1) We are firing the query through Java API QueryProcessor. 2) we are iterating the results to get serialize items. while firing the query ("transaction/*[local-name() ne 'traInfo']"). it took 13 seconds while iterating over the resultset it will takes 0.5 secs.
Quoted text · Christian Grün · 9 lines
> > > Query will return around 94 lacks items. > > So there are 94 child element that are not named "traInfo", but > "lacks"? Two more questions.. What's the total number of child nodes > > (try count(/transaction/*)? > > There are 94 lacks child element that are not names "traInfo" as result
of *"transaction/*[local-name() ne 'traInfo']"* query. Total child elements are around 1.01 crores. result of (try count(/transaction/*).
Quoted text · Christian Grün · 8 lines
> > So, I was wondering apart from query change, is there any baseX tuning or > > configuration changes should I do to further improve time from 13 secs. > > The query profiling results suggest that some additional time is spent > for checking namespaces. Maybe the following query is slightly faster: > > transaction/*[local-name() ne 'traInfo'] >
I tried the above suggested query, It takes the same time to execute as well as loop iteration. Regards,
-- *Kunal Chauhan* mail4ck at gmail.com [+918655517141]
CG
Christian Grün25 Aug 2014 10:52 #
Hi Kunal,
Quoted text · Kunal Chauhan · 3 lines
> Actually we are doing two things. > 1) We are firing the query through Java API QueryProcessor. > 2) we are iterating the results to get serialize items.
Thanks, that was helpful. I had a look at your original Java code again: There is no need to call proc.execute() (which will compute the full result). Instead, it is sufficient to call proc.iter().
Quoted text · Kunal Chauhan · 2 lines
> Total child elements are around 1.01 crores. result of > (try count(/transaction/*).
Does 1.01 mean 1.01 million elements? Hope this helps, Christian
KC
Kunal Chauhan25 Aug 2014 16:06 #
Hi Christian, Thanks for your great help !!! in a previous mail trail you asked, Does 1.01 mean 1.01 million elements? It means *10.01 million *elements (First child of transaction (root element). "count(/transaction/*)"). Thanks & Regards, Kunal Chauhan
Quoted text · Christian Grün · 20 lines
On Mon, Aug 25, 2014 at 2:22 PM, Christian Grün <christian.gruen at gmail.com> wrote: > Hi Kunal, > > > Actually we are doing two things. > > 1) We are firing the query through Java API QueryProcessor. > > 2) we are iterating the results to get serialize items. > > Thanks, that was helpful. I had a look at your original Java code > again: There is no need to call proc.execute() (which will compute the > full result). Instead, it is sufficient to call proc.iter(). > > > Total child elements are around 1.01 crores. result of > > (try count(/transaction/*). > > Does 1.01 mean 1.01 million elements? > > Hope this helps, > Christian >
-- *Kunal Chauhan* mail4ck at gmail.com [+918655517141]

Written for BaseX 7.9. BaseX may have changed since: please check the current documentation.

« Downloading filesfn:trace improvement »
⚡Generated with XQuery