[Opensource] searchAndRetrieve proposal

[Michael Rimov]

At 04:47 PM 6/30/2002 -0400, you wrote:
>Hi Mike,
>
>We have about 30,000 records in table - let call it A- in our DB.
>There is a table - B that is in a master-detail relationship with Table B.
>Some of the fields in table A are virtual and they got calculated from 
>table B.
>So far so good. When we display the data in table A and B we limit the 
>number if records from table B to 30.
>The problem is that table A calculates the virtual fields of it records 
>literally by going through all the records in table B.
>We get a out if memory exception. The reason this is happening I think is 
>that you guys basically convert the huge Record Set into
>DBObjects - ex. SearchAndRetrieve will return a Vector of all the 
>DBObjects representing certain RecordSet.
>Well this will eat the server's memory. What I am thinking is why not 
>create an alternative to that.
>Instead of converting the whole RecordSet into DBObjects and return them, 
>why not return the RecordSet, wrapped in some Object.
>That object will have its get(i) method that will simply repopulate the 
>"fields" of a single DBObject.
>Of course the user must be aware that he is dealing with one DBObject 
>only, but in many cases one needs to read only the values
>in the DBObject's fields. If one needs to alter and use a DBObject then he 
>will just have to call clone() or the wrapper
>could have a getDBObject method that will return a new DBObject instance. 
>I do not think that the speed will be affected much
>because Expresso is creating the DBObjects from the record set anyway.
>
>Well I am not an expert in Expresso and I do not have much time to go deep 
>in the source code so maybe my assumptions are wrong.
>If so what is the reason to get that out of memory exception that I 
>mentioned? When I deal with an java.sql. I could extract all
>the records that I have without any problems. I noticed that Expresso 
>crushes when dealing with a Record Set of more then 2000-3000 entries.
Milen,

Here's what you can be doing to reduce your overhead significantly:

#1 Use searchAndRetrieveList() instead of searchAndRetrieve().  S&R is 
deprecated and uses double the memory since it copies the results of S&RL 
into a new Vector.

#2 use setMaxRecords() and setOffsetRecord() liberally.  If you get 
Limitation Syntax set up for your particular database you'll be able to 
really crank.  But even if you don't have that worked out for your 
particular database, it makes a huge difference.  [I first dealt with that 
with a 18,000 record table, so I know what you mean memory-wise].

See the code in:

         com.jcorporate.expresso.core.misc.RecordPaginator

for an example of this usage.  The only other thing that will kill your 
performance at this point is DBMaint's forced use of count() for retrieving 
records. [This problem doesn't exist in DBObject itself though].  I noticed 
my original code was commented out by somebody but haven't tracked down why 
yet, so I'm trying not to break anything.

HTH, I think you'll find that the above hints will SIGNIFICANTLY improve 
the speed of your code.  And be sure to check out the JavaDocs in
com.jcorporate.expresso.core.db.DBConnectionPool as they now explain how to 
code your own Limitation Syntax configuration (thanks to Peter P.!)

                                                 -Mike