Return-Path: Delivered-To: apmail-cayenne-user-archive@www.apache.org Received: (qmail 32282 invoked from network); 5 Jul 2007 12:22:04 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 5 Jul 2007 12:22:04 -0000 Received: (qmail 50920 invoked by uid 500); 5 Jul 2007 12:22:06 -0000 Delivered-To: apmail-cayenne-user-archive@cayenne.apache.org Received: (qmail 50905 invoked by uid 500); 5 Jul 2007 12:22:06 -0000 Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cayenne.apache.org Delivered-To: mailing list user@cayenne.apache.org Received: (qmail 50895 invoked by uid 99); 5 Jul 2007 12:22:06 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 05 Jul 2007 05:22:05 -0700 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: local policy) Received: from [208.78.103.231] (HELO vorsha.objectstyle.org) (208.78.103.231) by apache.org (qpsmtpd/0.29) with SMTP; Thu, 05 Jul 2007 05:22:00 -0700 Received: (qmail 2475 invoked from network); 5 Jul 2007 12:21:39 -0000 Received: from unknown (HELO ?JB??o??IPv6:::1?) (127.0.0.1) by localhost with SMTP; 5 Jul 2007 12:21:39 -0000 Mime-Version: 1.0 (Apple Message framework v752.3) In-Reply-To: <468CDEDD.4000202@najdi.si> References: <64ACF5E2-8CD9-4C97-9270-7DA0C446AF51@maniatis.org> <5DA3C484-E55B-4599-95D1-F58C75F2F9B1@objectstyle.org> <614314C2-62BB-4B7B-BD5E-7F3E3E57084B@ish.com.au> <97511D1B-39F8-43BB-9CBB-B5E8C70C11B4@objectstyle.org> <31793559-8BBA-42C7-B17B-9AB3731063A2@objectstyle.org> <468CDEDD.4000202@najdi.si> Content-Type: text/plain; charset=UTF-8; delsp=yes; format=flowed Message-Id: Content-Transfer-Encoding: quoted-printable From: Andrus Adamchik Subject: Re: paged query slow when fetching big lists Date: Thu, 5 Jul 2007 15:21:39 +0300 To: user@cayenne.apache.org X-Mailer: Apple Mail (2.752.3) X-Virus-Checked: Checked by ClamAV on apache.org 3.0 (SVN trunk), which will hopefully be released fairly soon as 3.0M1 Andrus On Jul 5, 2007, at 3:06 PM, Borut Bol=C4=8Dina wrote: > Hello, > > which version includes this corrections? 2.0.3 or 3.0? > > Thanks, > Borut > > On 26.6.2007 20:32, Andrus Adamchik wrote: >> Hi Marcin, >> >> I have good news (I think). Fetching just id columns inside the =20 >> IncrementalFaultList indeed speeds things up significantly. I just =20= >> committed the change to optimize SelectQueries to do just that. =20 >> Please let me know how does it work for you. >> >> Now the profiling details... >> >> * I profiled on Derby and MySQL. In both cases fetching a table =20 >> with 25 columns and 100000 rows took between 3-4 seconds (not as =20 >> long as in your case, but what's important is relative times I guess) >> * YourKit clearly showed the bottleneck: ~95% of the 'fillIn' =20 >> method is spent in the driver code, rewinding the result set (i.e. =20= >> brining the data from db to the client). >> * After my change the query time went down to 0.2-0.5 second (0.8 =20 >> if you consider the second query needed to fault the first page). =20 >> Not bad! >> * ResultSet reading still remained a bottleneck, but it became =20 >> faster in absolute terms. And now finally Cayenne-related code =20 >> (such as DataRow creation) started to show up on the radar (e.g. =20 >> DataRow constructor taking 3% of the 'fillIn' method time). >> >> Andrus >> >> >> >> On Jun 26, 2007, at 10:55 AM, Marcin Skladaniec wrote: >> >>> Hi Andrus ! >>> Many thanks for that ! >>> Marcin >>> >>> On 26/06/2007, at 5:39 PM, Andrus Adamchik wrote: >>> >>>> Hi Marcin, >>>> >>>>> 1) SelectQuery(Student.class) with page size 10 takes 30-50 =20 >>>>> seconds. >>>>> 2) SQLTemplate(Student.class, "SELECT #result('id' 'int') from =20 >>>>> STUDENT") without paging takes 100 sec. >>>>> 3) SQLTemplate(Student.class, "SELECT #result('id' 'int') from =20 >>>>> STUDENT") with page size 10 takes 5 sec. >>>>> 4) SQLTemplate(Student.class, "SELECT #result('id' 'int') from =20 >>>>> STUDENT") with page size 10 and fetching row data rows takes =20 >>>>> 4.5 sec. >>>> >>>> I see you still didn't use profiler, but the data you provided =20 >>>> seems to finally confirm that at least on Derby not fetching all =20= >>>> columns does result in significant speedup (#1 vs. #3). So now =20 >>>> it is a question of implementing the right algorithm for the =20 >>>> IncrementalFaultList. >>>> >>>>> Andrus, you mentioned using addCustomDbAttribute to fetch only =20 >>>>> part of the data. I tried to use addCustomDbAttribute("id") on =20 >>>>> client, it resulted in returning the raw dataRows, is there =20 >>>>> something I can do to fetch faulted objects ? >>>> >>>> We should encapsulate this logic inside IncrementalFaultList on =20 >>>> the server. >>>> >>>>> Our application was designed to use the SelectQuery. If we have =20= >>>>> to change that and use the SQLTemplate instead, there is a lot =20 >>>>> of work for us, including: >>>> >>>> Same thing - the right thing to do is to fix it on the server. >>>> >>>> Let me try to find a spare minute later tonight and implement id-=20= >>>> only fetch. I have some large tables in a MySQL5 so I can test =20 >>>> the performance in a slightly different environment. >>>> >>>> Andrus >>>> >>>> >>>> >>>> On Jun 26, 2007, at 9:26 AM, Marcin Skladaniec wrote: >>>>> Hi >>>>> >>>>> I have done some more profiling and testing. >>>>> >>>>> executing queries on table with >100000 records, directly on =20 >>>>> server (not on client) gave results as listed below: >>>>> >>>>> 1) SelectQuery(Student.class) with page size 10 takes 30-50 =20 >>>>> seconds. >>>>> 2) SQLTemplate(Student.class, "SELECT #result('id' 'int') from =20 >>>>> STUDENT") without paging takes 100 sec. >>>>> 3) SQLTemplate(Student.class, "SELECT #result('id' 'int') from =20 >>>>> STUDENT") with page size 10 takes 5 sec. >>>>> 4) SQLTemplate(Student.class, "SELECT #result('id' 'int') from =20 >>>>> STUDENT") with page size 10 and fetching row data rows takes =20 >>>>> 4.5 sec. >>>>> >>>>> what more, I found that executing the SQLTemplate does allow to =20= >>>>> fault the objects (I sometimes discover the simplest things =20 >>>>> last), so I did try to check how long it takes for the objects =20 >>>>> to be faulted: >>>>> 1) first object on every page (except first) 30-200ms, rest =3D = 0ms >>>>> 2) objects is faulted in 20ms (average) >>>>> 3) on first page first object faulted in 200ms, rest ~20 ms, >>>>> on any following page first object faulted in 30-200ms, =20 >>>>> rest 0ms (interesting that the first page does not seem to be =20 >>>>> faulted at all) >>>>> 4) no point testing. >>>>> >>>>> Also I did check if the resizing of the ArrayList which is =20 >>>>> keeping the results does affect the speed, and it does not. =20 >>>>> (Tried to make the ArrayList initial size =3D 150,000). >>>>> >>>>> My conclusion is that SelectQuery with paging is usable only =20 >>>>> for fetching less than, say 10,000 records, otherwise the =20 >>>>> performance is to low. With SQLTemplate the performance is much =20= >>>>> greater. It applies to both ROP and 'normal' cayenne, since I =20 >>>>> made those tests on server. >>>>> >>>>> Andrus, you mentioned using addCustomDbAttribute to fetch only =20 >>>>> part of the data. I tried to use addCustomDbAttribute("id") on =20 >>>>> client, it resulted in returning the raw dataRows, is there =20 >>>>> something I can do to fetch faulted objects ? >>>>> >>>>> Our application was designed to use the SelectQuery. If we have =20= >>>>> to change that and use the SQLTemplate instead, there is a lot =20 >>>>> of work for us, including: >>>>> - dealing with adding and concatenating Expressions to the =20 >>>>> SQLTemplate (is there an easy way ?) >>>>> - dealing with declared qualifier (the one set in modeller) >>>>> - possibly more... >>>>> i would really like to avoid all of that, so if you have any =20 >>>>> ideas on how to improve the performance without too much hassle =20= >>>>> I would really appreciate. >>>>> >>>>> Marcin >>>>> >>>>> On 25/06/2007, at 8:31 PM, Marcin Skladaniec wrote: >>>>> >>>>>> Hi Andrus >>>>>> I had not much time to check, but with the fix the 100k =20 >>>>>> records load in 30 instead of 50 seconds. It is some =20 >>>>>> improvement, but not enough. I'll do some more profiling =20 >>>>>> tomorrow and let you know. >>>>>> >>>>>> By the way, we are using netbeans for profiling, the =20 >>>>>> benefit : it is free. I will evaluate the yourkit as we are =20 >>>>>> moving away from netbeans as a development platform. >>>>>> >>>>>> Marcin >>>>>> >>>>>> On 23/06/2007, at 5:38 PM, Andrus Adamchik wrote: >>>>>> >>>>>>> Ari, Marcin -- >>>>>>> >>>>>>> going through the code I noticed one inefficiency - the =20 >>>>>>> elements array access is synchronized in 'fillIn' method. =20 >>>>>>> Since 'fillIn' is called from constructor, such =20 >>>>>>> synchronization is unneeded and only slows things down. I =20 >>>>>>> just checked a fixed version to trunk. Could you try it out? >>>>>>> >>>>>>> Andrus >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Jun 23, 2007, at 12:33 AM, Aristedes Maniatis wrote: >>>>>>>> On 22/06/2007, at 11:10 PM, Michael Gentry wrote: >>>>>>>> >>>>>>>>> Marcin, this thread might be of interest to you ... >>>>>>>>> >>>>>>>>> http://mail-archives.apache.org/mod_mbox/cayenne-dev/=20 >>>>>>>>> 200705.mbox/browser >>>>>>>>> >>>>>>>>> Look at the "Paging and SQL queries" thread on May 25. >>>>>>>> >>>>>>>> >>>>>>>> Yes, this is the same project we are working on. I started =20 >>>>>>>> some performance profiling and Marcin has been able now to =20 >>>>>>>> take it much further. What is it about: >>>>>>>> >>>>>>>>> elements.add(it.nextObjectId(entity)); >>>>>>>> >>>>>>>> which is so slow? The code gets a little complex at that =20 >>>>>>>> point and we are having difficulty tracing it through to the =20= >>>>>>>> exact performance problem in the underlying code. Is it the =20 >>>>>>>> speed of adding the object id to the Collection or the speed =20= >>>>>>>> of creating an object id itself? 0.5ms doesn't sound slow, =20 >>>>>>>> but it doesn't scale well. >>>>>>>> >>>>>>>> Andrus, I got the impression from the previous thread that =20 >>>>>>>> you suspected something slightly different. That the =20 >>>>>>>> performance problem might be in the fat query itself, but =20 >>>>>>>> from our tests this isn't the case. If I've got this right, =20 >>>>>>>> the way it works is: >>>>>>>> >>>>>>>> 1. perform regular query to get all columns but return =20 >>>>>>>> result in iterator >>>>>>>> 2. iterate through first page and build full objects >>>>>>>> 3. iterate through other pages and build just objectids =20 >>>>>>>> (this is the slow part for us) >>>>>>>> 4. when another page is fetched perform another query and =20 >>>>>>>> fetch those objects from the DB >>>>>>>> >>>>>>>> So, getting just primary keys from the DB may not be any =20 >>>>>>>> faster if the performance problem is simply in the =20 >>>>>>>> construction of objectIds. If the performance problem is in =20 >>>>>>>> the numerous resizings of the Collection (each time it runs =20 >>>>>>>> out of space, then it is increased by 50% or 100% in size), =20 >>>>>>>> then the solution could be as simple as figuring out the =20 >>>>>>>> size of the iterator and sizing the collection appropriately =20= >>>>>>>> from the beginning. >>>>>>>> >>>>>>>> Any ideas on how to discover the exact cause of the =20 >>>>>>>> performance hit? >>>>>>>> >>>>>>>> >>>>>>>> Ari Maniatis >>>>>>>> >>>>>>>> >>>>> >>>>> >>>>> >>>> >>> >>> >>> >>> >> > > --=20 >