Best method to fetch a lot of objects

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Best method to fetch a lot of objects

Pascal Robert-2
Hi,

I’m still in my FileMaker -> MySQL migration project. This time, I want to migrate a FileMaker table who have 445 244 records in it. If I fetch everything into an object entity for each row, I’m getting a Java heap space problem, which is somewhat expected by the size of the result set.

If I call setFetchLimit() with a 10 000 limit, works fine. FileMaker doesn’t support fetch limits, so I can’t do something on that side.

Any tips?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

Michael Gentry
Hi Pascal,

I suspect you need to utilize an iterated query:

http://cayenne.apache.org/docs/3.1/cayenne-guide/performance-tuning.html#iterated-queries

As you iterate over your entire record set, you can convert the DataRows
into Cayenne objects (see the section in the documentation above the
iterated queries documentation) in a *different* DataContext.  Gather up 50
or 100 or 1000 (whatever number feels good to you) in that second
DataContext and then commit them, throw away that DataContext and create a
new one.  Repeat.  This should keep your memory usage fairly constant and
allow you to process arbitrarily large record sizes.

mrg


On Fri, May 19, 2017 at 9:27 AM, Pascal Robert <[hidden email]> wrote:

> Hi,
>
> I’m still in my FileMaker -> MySQL migration project. This time, I want to
> migrate a FileMaker table who have 445 244 records in it. If I fetch
> everything into an object entity for each row, I’m getting a Java heap
> space problem, which is somewhat expected by the size of the result set.
>
> If I call setFetchLimit() with a 10 000 limit, works fine. FileMaker
> doesn’t support fetch limits, so I can’t do something on that side.
>
> Any tips?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

Andrus Adamchik
I concur with Mike on the suggestion. Though I would recommend using vastly improved 4.0 API:

http://cayenne.apache.org/docs/4.0/cayenne-guide/performance-tuning.html#iterated-queries

> As you iterate over your entire record set, you can convert the DataRows
> into Cayenne objects

In 4.0 you can iterate over objects.

> Gather up 50 or 100 or 1000

In 4.0 you can use batch iterator to receive the stream already split in batches. Docs example actually has a typo. Batch iterator looks like this:

try(ResultBatchIterator<Artist> it =
    ObjectSelect.query(Artist.class).batchIterator(context, batchSize)) {
   
    for(List<Artist> list : it) {
       ...
       context.commitChanges();
    }          
}

Andrus


> On May 19, 2017, at 4:39 PM, Michael Gentry <[hidden email]> wrote:
>
> Hi Pascal,
>
> I suspect you need to utilize an iterated query:
>
> http://cayenne.apache.org/docs/3.1/cayenne-guide/performance-tuning.html#iterated-queries
>
> As you iterate over your entire record set, you can convert the DataRows
> into Cayenne objects (see the section in the documentation above the
> iterated queries documentation) in a *different* DataContext.  Gather up 50
> or 100 or 1000 (whatever number feels good to you) in that second
> DataContext and then commit them, throw away that DataContext and create a
> new one.  Repeat.  This should keep your memory usage fairly constant and
> allow you to process arbitrarily large record sizes.
>
> mrg
>
>
> On Fri, May 19, 2017 at 9:27 AM, Pascal Robert <[hidden email]> wrote:
>
>> Hi,
>>
>> I’m still in my FileMaker -> MySQL migration project. This time, I want to
>> migrate a FileMaker table who have 445 244 records in it. If I fetch
>> everything into an object entity for each row, I’m getting a Java heap
>> space problem, which is somewhat expected by the size of the result set.
>>
>> If I call setFetchLimit() with a 10 000 limit, works fine. FileMaker
>> doesn’t support fetch limits, so I can’t do something on that side.
>>
>> Any tips?

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

John Huss
Andrus, does this handle memory growth too?  If you use the same context
for each batch won't you still run out of memory?

On Fri, May 19, 2017 at 8:49 AM Andrus Adamchik <[hidden email]>
wrote:

> I concur with Mike on the suggestion. Though I would recommend using
> vastly improved 4.0 API:
>
>
> http://cayenne.apache.org/docs/4.0/cayenne-guide/performance-tuning.html#iterated-queries
>
> > As you iterate over your entire record set, you can convert the DataRows
> > into Cayenne objects
>
> In 4.0 you can iterate over objects.
>
> > Gather up 50 or 100 or 1000
>
> In 4.0 you can use batch iterator to receive the stream already split in
> batches. Docs example actually has a typo. Batch iterator looks like this:
>
> try(ResultBatchIterator<Artist> it =
>     ObjectSelect.query(Artist.class).batchIterator(context, batchSize)) {
>
>     for(List<Artist> list : it) {
>        ...
>        context.commitChanges();
>     }
> }
>
> Andrus
>
>
> > On May 19, 2017, at 4:39 PM, Michael Gentry <[hidden email]> wrote:
> >
> > Hi Pascal,
> >
> > I suspect you need to utilize an iterated query:
> >
> >
> http://cayenne.apache.org/docs/3.1/cayenne-guide/performance-tuning.html#iterated-queries
> >
> > As you iterate over your entire record set, you can convert the DataRows
> > into Cayenne objects (see the section in the documentation above the
> > iterated queries documentation) in a *different* DataContext.  Gather up
> 50
> > or 100 or 1000 (whatever number feels good to you) in that second
> > DataContext and then commit them, throw away that DataContext and create
> a
> > new one.  Repeat.  This should keep your memory usage fairly constant and
> > allow you to process arbitrarily large record sizes.
> >
> > mrg
> >
> >
> > On Fri, May 19, 2017 at 9:27 AM, Pascal Robert <[hidden email]>
> wrote:
> >
> >> Hi,
> >>
> >> I’m still in my FileMaker -> MySQL migration project. This time, I want
> to
> >> migrate a FileMaker table who have 445 244 records in it. If I fetch
> >> everything into an object entity for each row, I’m getting a Java heap
> >> space problem, which is somewhat expected by the size of the result set.
> >>
> >> If I call setFetchLimit() with a 10 000 limit, works fine. FileMaker
> >> doesn’t support fetch limits, so I can’t do something on that side.
> >>
> >> Any tips?
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

Michael Gentry
In reply to this post by Andrus Adamchik
Hi Andrus,

In your example, wouldn't "context" continue growing and possibly
exhausting memory as more Artists are brought into it?  (And this would
also slow down the commitChanges() call as it has more evaluation to do as
the context grows.)

mrg


On Fri, May 19, 2017 at 9:49 AM, Andrus Adamchik <[hidden email]>
wrote:

> I concur with Mike on the suggestion. Though I would recommend using
> vastly improved 4.0 API:
>
> http://cayenne.apache.org/docs/4.0/cayenne-guide/performance-tuning.html#
> iterated-queries
>
> > As you iterate over your entire record set, you can convert the DataRows
> > into Cayenne objects
>
> In 4.0 you can iterate over objects.
>
> > Gather up 50 or 100 or 1000
>
> In 4.0 you can use batch iterator to receive the stream already split in
> batches. Docs example actually has a typo. Batch iterator looks like this:
>
> try(ResultBatchIterator<Artist> it =
>     ObjectSelect.query(Artist.class).batchIterator(context, batchSize)) {
>
>     for(List<Artist> list : it) {
>        ...
>        context.commitChanges();
>     }
> }
>
> Andrus
>
>
> > On May 19, 2017, at 4:39 PM, Michael Gentry <[hidden email]> wrote:
> >
> > Hi Pascal,
> >
> > I suspect you need to utilize an iterated query:
> >
> > http://cayenne.apache.org/docs/3.1/cayenne-guide/
> performance-tuning.html#iterated-queries
> >
> > As you iterate over your entire record set, you can convert the DataRows
> > into Cayenne objects (see the section in the documentation above the
> > iterated queries documentation) in a *different* DataContext.  Gather up
> 50
> > or 100 or 1000 (whatever number feels good to you) in that second
> > DataContext and then commit them, throw away that DataContext and create
> a
> > new one.  Repeat.  This should keep your memory usage fairly constant and
> > allow you to process arbitrarily large record sizes.
> >
> > mrg
> >
> >
> > On Fri, May 19, 2017 at 9:27 AM, Pascal Robert <[hidden email]>
> wrote:
> >
> >> Hi,
> >>
> >> I’m still in my FileMaker -> MySQL migration project. This time, I want
> to
> >> migrate a FileMaker table who have 445 244 records in it. If I fetch
> >> everything into an object entity for each row, I’m getting a Java heap
> >> space problem, which is somewhat expected by the size of the result set.
> >>
> >> If I call setFetchLimit() with a 10 000 limit, works fine. FileMaker
> >> doesn’t support fetch limits, so I can’t do something on that side.
> >>
> >> Any tips?
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

Andrus Adamchik
In reply to this post by John Huss
Context has weak references. So in theory it should release memory.

I usually create batch-scoped context though, as very often the objects I am changing are not the same objects I am reading. (and even if they are, 'localObject' is your friend ... which I guess is an expensive equivalent of DataRows). So an example may look like this:

try(ResultBatchIterator<Artist> it =
   ObjectSelect.query(Artist.class).batchIterator(context, batchSize)) {

   for(List<Artist> list : it) {
      ObjectContext context = runtime.newContext();
      ...
      context.commitChanges();
   }
}

Andrus


> On May 19, 2017, at 5:04 PM, John Huss <[hidden email]> wrote:
>
> Andrus, does this handle memory growth too?  If you use the same context
> for each batch won't you still run out of memory?
>
> On Fri, May 19, 2017 at 8:49 AM Andrus Adamchik <[hidden email]>
> wrote:
>
>> I concur with Mike on the suggestion. Though I would recommend using
>> vastly improved 4.0 API:
>>
>>
>> http://cayenne.apache.org/docs/4.0/cayenne-guide/performance-tuning.html#iterated-queries
>>
>>> As you iterate over your entire record set, you can convert the DataRows
>>> into Cayenne objects
>>
>> In 4.0 you can iterate over objects.
>>
>>> Gather up 50 or 100 or 1000
>>
>> In 4.0 you can use batch iterator to receive the stream already split in
>> batches. Docs example actually has a typo. Batch iterator looks like this:
>>
>> try(ResultBatchIterator<Artist> it =
>>    ObjectSelect.query(Artist.class).batchIterator(context, batchSize)) {
>>
>>    for(List<Artist> list : it) {
>>       ...
>>       context.commitChanges();
>>    }
>> }
>>
>> Andrus
>>
>>
>>> On May 19, 2017, at 4:39 PM, Michael Gentry <[hidden email]> wrote:
>>>
>>> Hi Pascal,
>>>
>>> I suspect you need to utilize an iterated query:
>>>
>>>
>> http://cayenne.apache.org/docs/3.1/cayenne-guide/performance-tuning.html#iterated-queries
>>>
>>> As you iterate over your entire record set, you can convert the DataRows
>>> into Cayenne objects (see the section in the documentation above the
>>> iterated queries documentation) in a *different* DataContext.  Gather up
>> 50
>>> or 100 or 1000 (whatever number feels good to you) in that second
>>> DataContext and then commit them, throw away that DataContext and create
>> a
>>> new one.  Repeat.  This should keep your memory usage fairly constant and
>>> allow you to process arbitrarily large record sizes.
>>>
>>> mrg
>>>
>>>
>>> On Fri, May 19, 2017 at 9:27 AM, Pascal Robert <[hidden email]>
>> wrote:
>>>
>>>> Hi,
>>>>
>>>> I’m still in my FileMaker -> MySQL migration project. This time, I want
>> to
>>>> migrate a FileMaker table who have 445 244 records in it. If I fetch
>>>> everything into an object entity for each row, I’m getting a Java heap
>>>> space problem, which is somewhat expected by the size of the result set.
>>>>
>>>> If I call setFetchLimit() with a 10 000 limit, works fine. FileMaker
>>>> doesn’t support fetch limits, so I can’t do something on that side.
>>>>
>>>> Any tips?
>>
>>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

Andrus Adamchik
In reply to this post by Pascal Robert-2
Oh, an BTW, link-move can be used for data migration. It already has the iterator encapsulated and can transfer large data sets.

Andrus

> On May 19, 2017, at 4:27 PM, Pascal Robert <[hidden email]> wrote:
>
> Hi,
>
> I’m still in my FileMaker -> MySQL migration project. This time, I want to migrate a FileMaker table who have 445 244 records in it. If I fetch everything into an object entity for each row, I’m getting a Java heap space problem, which is somewhat expected by the size of the result set.
>
> If I call setFetchLimit() with a 10 000 limit, works fine. FileMaker doesn’t support fetch limits, so I can’t do something on that side.
>
> Any tips?

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

Andrus Adamchik
In reply to this post by Michael Gentry
See my reply to John. The context is using weak references for committed objects, so it is self-cleaning. Though of course having a second (long- or short- lived context) is probably safer in a general case.

Andrus

> On May 19, 2017, at 5:06 PM, Michael Gentry <[hidden email]> wrote:
>
> Hi Andrus,
>
> In your example, wouldn't "context" continue growing and possibly
> exhausting memory as more Artists are brought into it?  (And this would
> also slow down the commitChanges() call as it has more evaluation to do as
> the context grows.)
>
> mrg
>
>
> On Fri, May 19, 2017 at 9:49 AM, Andrus Adamchik <[hidden email]>
> wrote:
>
>> I concur with Mike on the suggestion. Though I would recommend using
>> vastly improved 4.0 API:
>>
>> http://cayenne.apache.org/docs/4.0/cayenne-guide/performance-tuning.html#
>> iterated-queries
>>
>>> As you iterate over your entire record set, you can convert the DataRows
>>> into Cayenne objects
>>
>> In 4.0 you can iterate over objects.
>>
>>> Gather up 50 or 100 or 1000
>>
>> In 4.0 you can use batch iterator to receive the stream already split in
>> batches. Docs example actually has a typo. Batch iterator looks like this:
>>
>> try(ResultBatchIterator<Artist> it =
>>    ObjectSelect.query(Artist.class).batchIterator(context, batchSize)) {
>>
>>    for(List<Artist> list : it) {
>>       ...
>>       context.commitChanges();
>>    }
>> }
>>
>> Andrus
>>
>>
>>> On May 19, 2017, at 4:39 PM, Michael Gentry <[hidden email]> wrote:
>>>
>>> Hi Pascal,
>>>
>>> I suspect you need to utilize an iterated query:
>>>
>>> http://cayenne.apache.org/docs/3.1/cayenne-guide/
>> performance-tuning.html#iterated-queries
>>>
>>> As you iterate over your entire record set, you can convert the DataRows
>>> into Cayenne objects (see the section in the documentation above the
>>> iterated queries documentation) in a *different* DataContext.  Gather up
>> 50
>>> or 100 or 1000 (whatever number feels good to you) in that second
>>> DataContext and then commit them, throw away that DataContext and create
>> a
>>> new one.  Repeat.  This should keep your memory usage fairly constant and
>>> allow you to process arbitrarily large record sizes.
>>>
>>> mrg
>>>
>>>
>>> On Fri, May 19, 2017 at 9:27 AM, Pascal Robert <[hidden email]>
>> wrote:
>>>
>>>> Hi,
>>>>
>>>> I’m still in my FileMaker -> MySQL migration project. This time, I want
>> to
>>>> migrate a FileMaker table who have 445 244 records in it. If I fetch
>>>> everything into an object entity for each row, I’m getting a Java heap
>>>> space problem, which is somewhat expected by the size of the result set.
>>>>
>>>> If I call setFetchLimit() with a 10 000 limit, works fine. FileMaker
>>>> doesn’t support fetch limits, so I can’t do something on that side.
>>>>
>>>> Any tips?
>>
>>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

Pascal Robert-2
In reply to this post by Andrus Adamchik
So far, so good. But I’m getting this:

...
10:14:31.507 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - +++ transaction committed.
10:14:31.508 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - LOCK TABLES AUTO_PK_SUPPORT WRITE
10:14:31.516 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - UNLOCK TABLES
10:14:31.518 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - === returned 4294 rows. - took 254959 ms.
10:14:31.522 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - +++ transaction committed.
org.apache.cayenne.CayenneRuntimeException: [v.4.0.M5 Feb 24 2017 07:47:55] Commit Exception
        at org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:774)
        at org.apache.cayenne.access.DataContext.commitChanges(DataContext.java:691)
        at com.druide.filemaker.migrations.Migration.faitMigrationUtilisateurs(Migration.java:202)
        at com.druide.filemaker.migrations.Migration.main(Migration.java:36)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'filemaker.auto_pk_support' doesn't exist

The PK is copied from the FileMaker entity, so I don’t know why it’s trying to find a filemaker.auto_pk_support table.

> Le 19 mai 2017 à 09:49, Andrus Adamchik <[hidden email]> a écrit :
>
> I concur with Mike on the suggestion. Though I would recommend using vastly improved 4.0 API:
>
> http://cayenne.apache.org/docs/4.0/cayenne-guide/performance-tuning.html#iterated-queries
>
>> As you iterate over your entire record set, you can convert the DataRows
>> into Cayenne objects
>
> In 4.0 you can iterate over objects.
>
>> Gather up 50 or 100 or 1000
>
> In 4.0 you can use batch iterator to receive the stream already split in batches. Docs example actually has a typo. Batch iterator looks like this:
>
> try(ResultBatchIterator<Artist> it =
>    ObjectSelect.query(Artist.class).batchIterator(context, batchSize)) {
>
>    for(List<Artist> list : it) {
>       ...
>       context.commitChanges();
>    }          
> }
>
> Andrus
>
>
>> On May 19, 2017, at 4:39 PM, Michael Gentry <[hidden email]> wrote:
>>
>> Hi Pascal,
>>
>> I suspect you need to utilize an iterated query:
>>
>> http://cayenne.apache.org/docs/3.1/cayenne-guide/performance-tuning.html#iterated-queries
>>
>> As you iterate over your entire record set, you can convert the DataRows
>> into Cayenne objects (see the section in the documentation above the
>> iterated queries documentation) in a *different* DataContext.  Gather up 50
>> or 100 or 1000 (whatever number feels good to you) in that second
>> DataContext and then commit them, throw away that DataContext and create a
>> new one.  Repeat.  This should keep your memory usage fairly constant and
>> allow you to process arbitrarily large record sizes.
>>
>> mrg
>>
>>
>> On Fri, May 19, 2017 at 9:27 AM, Pascal Robert <[hidden email]> wrote:
>>
>>> Hi,
>>>
>>> I’m still in my FileMaker -> MySQL migration project. This time, I want to
>>> migrate a FileMaker table who have 445 244 records in it. If I fetch
>>> everything into an object entity for each row, I’m getting a Java heap
>>> space problem, which is somewhat expected by the size of the result set.
>>>
>>> If I call setFetchLimit() with a 10 000 limit, works fine. FileMaker
>>> doesn’t support fetch limits, so I can’t do something on that side.
>>>
>>> Any tips?
>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

Andrus Adamchik
How do you copy the PK?

Do all your MySQL entities contain an ObjAttribute for their PK?

Andrus

> On May 19, 2017, at 5:25 PM, Pascal Robert <[hidden email]> wrote:
>
> So far, so good. But I’m getting this:
>
> ...
> 10:14:31.507 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - +++ transaction committed.
> 10:14:31.508 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - LOCK TABLES AUTO_PK_SUPPORT WRITE
> 10:14:31.516 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - UNLOCK TABLES
> 10:14:31.518 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - === returned 4294 rows. - took 254959 ms.
> 10:14:31.522 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - +++ transaction committed.
> org.apache.cayenne.CayenneRuntimeException: [v.4.0.M5 Feb 24 2017 07:47:55] Commit Exception
> at org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:774)
> at org.apache.cayenne.access.DataContext.commitChanges(DataContext.java:691)
> at com.druide.filemaker.migrations.Migration.faitMigrationUtilisateurs(Migration.java:202)
> at com.druide.filemaker.migrations.Migration.main(Migration.java:36)
> Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'filemaker.auto_pk_support' doesn't exist
>
> The PK is copied from the FileMaker entity, so I don’t know why it’s trying to find a filemaker.auto_pk_support table.
>
>> Le 19 mai 2017 à 09:49, Andrus Adamchik <[hidden email]> a écrit :
>>
>> I concur with Mike on the suggestion. Though I would recommend using vastly improved 4.0 API:
>>
>> http://cayenne.apache.org/docs/4.0/cayenne-guide/performance-tuning.html#iterated-queries
>>
>>> As you iterate over your entire record set, you can convert the DataRows
>>> into Cayenne objects
>>
>> In 4.0 you can iterate over objects.
>>
>>> Gather up 50 or 100 or 1000
>>
>> In 4.0 you can use batch iterator to receive the stream already split in batches. Docs example actually has a typo. Batch iterator looks like this:
>>
>> try(ResultBatchIterator<Artist> it =
>>   ObjectSelect.query(Artist.class).batchIterator(context, batchSize)) {
>>
>>   for(List<Artist> list : it) {
>>      ...
>>      context.commitChanges();
>>   }          
>> }
>>
>> Andrus
>>
>>
>>> On May 19, 2017, at 4:39 PM, Michael Gentry <[hidden email]> wrote:
>>>
>>> Hi Pascal,
>>>
>>> I suspect you need to utilize an iterated query:
>>>
>>> http://cayenne.apache.org/docs/3.1/cayenne-guide/performance-tuning.html#iterated-queries
>>>
>>> As you iterate over your entire record set, you can convert the DataRows
>>> into Cayenne objects (see the section in the documentation above the
>>> iterated queries documentation) in a *different* DataContext.  Gather up 50
>>> or 100 or 1000 (whatever number feels good to you) in that second
>>> DataContext and then commit them, throw away that DataContext and create a
>>> new one.  Repeat.  This should keep your memory usage fairly constant and
>>> allow you to process arbitrarily large record sizes.
>>>
>>> mrg
>>>
>>>
>>> On Fri, May 19, 2017 at 9:27 AM, Pascal Robert <[hidden email]> wrote:
>>>
>>>> Hi,
>>>>
>>>> I’m still in my FileMaker -> MySQL migration project. This time, I want to
>>>> migrate a FileMaker table who have 445 244 records in it. If I fetch
>>>> everything into an object entity for each row, I’m getting a Java heap
>>>> space problem, which is somewhat expected by the size of the result set.
>>>>
>>>> If I call setFetchLimit() with a 10 000 limit, works fine. FileMaker
>>>> doesn’t support fetch limits, so I can’t do something on that side.
>>>>
>>>> Any tips?
>>
>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

Pascal Robert-2

> Le 20 mai 2017 à 02:40, Andrus Adamchik <[hidden email]> a écrit :
>
> How do you copy the PK?

By exposing it in the entity:

          contactPrincipalMySQL.setKpCodeContact(contactPrincipalFileMaker.getKpCodeContact().intValue());


>
> Do all your MySQL entities contain an ObjAttribute for their PK?

In fact, the source is FileMaker, and even if the field is listed as a number with autofill, that stupid database still allows NULL or non-number values...

>
> Andrus
>
>> On May 19, 2017, at 5:25 PM, Pascal Robert <[hidden email]> wrote:
>>
>> So far, so good. But I’m getting this:
>>
>> ...
>> 10:14:31.507 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - +++ transaction committed.
>> 10:14:31.508 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - LOCK TABLES AUTO_PK_SUPPORT WRITE
>> 10:14:31.516 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - UNLOCK TABLES
>> 10:14:31.518 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - === returned 4294 rows. - took 254959 ms.
>> 10:14:31.522 [main] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - +++ transaction committed.
>> org.apache.cayenne.CayenneRuntimeException: [v.4.0.M5 Feb 24 2017 07:47:55] Commit Exception
>> at org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:774)
>> at org.apache.cayenne.access.DataContext.commitChanges(DataContext.java:691)
>> at com.druide.filemaker.migrations.Migration.faitMigrationUtilisateurs(Migration.java:202)
>> at com.druide.filemaker.migrations.Migration.main(Migration.java:36)
>> Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'filemaker.auto_pk_support' doesn't exist
>>
>> The PK is copied from the FileMaker entity, so I don’t know why it’s trying to find a filemaker.auto_pk_support table.
>>
>>> Le 19 mai 2017 à 09:49, Andrus Adamchik <[hidden email]> a écrit :
>>>
>>> I concur with Mike on the suggestion. Though I would recommend using vastly improved 4.0 API:
>>>
>>> http://cayenne.apache.org/docs/4.0/cayenne-guide/performance-tuning.html#iterated-queries
>>>
>>>> As you iterate over your entire record set, you can convert the DataRows
>>>> into Cayenne objects
>>>
>>> In 4.0 you can iterate over objects.
>>>
>>>> Gather up 50 or 100 or 1000
>>>
>>> In 4.0 you can use batch iterator to receive the stream already split in batches. Docs example actually has a typo. Batch iterator looks like this:
>>>
>>> try(ResultBatchIterator<Artist> it =
>>>  ObjectSelect.query(Artist.class).batchIterator(context, batchSize)) {
>>>
>>>  for(List<Artist> list : it) {
>>>     ...
>>>     context.commitChanges();
>>>  }          
>>> }
>>>
>>> Andrus
>>>
>>>
>>>> On May 19, 2017, at 4:39 PM, Michael Gentry <[hidden email]> wrote:
>>>>
>>>> Hi Pascal,
>>>>
>>>> I suspect you need to utilize an iterated query:
>>>>
>>>> http://cayenne.apache.org/docs/3.1/cayenne-guide/performance-tuning.html#iterated-queries
>>>>
>>>> As you iterate over your entire record set, you can convert the DataRows
>>>> into Cayenne objects (see the section in the documentation above the
>>>> iterated queries documentation) in a *different* DataContext.  Gather up 50
>>>> or 100 or 1000 (whatever number feels good to you) in that second
>>>> DataContext and then commit them, throw away that DataContext and create a
>>>> new one.  Repeat.  This should keep your memory usage fairly constant and
>>>> allow you to process arbitrarily large record sizes.
>>>>
>>>> mrg
>>>>
>>>>
>>>> On Fri, May 19, 2017 at 9:27 AM, Pascal Robert <[hidden email]> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> I’m still in my FileMaker -> MySQL migration project. This time, I want to
>>>>> migrate a FileMaker table who have 445 244 records in it. If I fetch
>>>>> everything into an object entity for each row, I’m getting a Java heap
>>>>> space problem, which is somewhat expected by the size of the result set.
>>>>>
>>>>> If I call setFetchLimit() with a 10 000 limit, works fine. FileMaker
>>>>> doesn’t support fetch limits, so I can’t do something on that side.
>>>>>
>>>>> Any tips?
>>>
>>
>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

Andrus Adamchik

> On May 23, 2017, at 3:02 PM, Pascal Robert <[hidden email]> wrote:
>
>>
>> Do all your MySQL entities contain an ObjAttribute for their PK?
>
> In fact, the source is FileMaker, and even if the field is listed as a number with autofill, that stupid database still allows NULL or non-number values...

So maybe null PKs are the culprit? Can you check for null, and assign a PK yourself?

Andrus
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

Pascal Robert-2

> Le 24 mai 2017 à 03:34, Andrus Adamchik <[hidden email]> a écrit :
>
>
>> On May 23, 2017, at 3:02 PM, Pascal Robert <[hidden email]> wrote:
>>
>>>
>>> Do all your MySQL entities contain an ObjAttribute for their PK?
>>
>> In fact, the source is FileMaker, and even if the field is listed as a number with autofill, that stupid database still allows NULL or non-number values...
>
> So maybe null PKs are the culprit? Can you check for null, and assign a PK yourself?

This is what I do, but not only the PK is null, but the fetched object is null.

    try(ResultIterator<ContactsPrincipaux> contactsPrincipaux = ObjectSelect.query(ContactsPrincipaux.class).iterator(context)) {
      try(BufferedWriter writer = Files.newBufferedWriter(file.toPath(), charset, StandardOpenOption.CREATE)) {
        for (ContactsPrincipaux contact: contactsPrincipaux) {
          if (contact != null && contact.getKpCodeContact() != null) {
            String s = contact.getKpCodeContact() + ";" + contact.getInfoPrenom() + ";" + contact.getInfoNom() + ";" + contact.getInfoCourriel1() + ";" + contact.getAdressePrincipaleCodepostal() + "\n";
            writer.write(s, 0, s.length());
          } else {
            System.out.println(contact);
          }
        }
      } catch (IOException ex) {

      }
    }

When the FileMaker record does not have a value in kp_code_contact, contact is null.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Best method to fetch a lot of objects

Andrus Adamchik
If PK is null, I guess Cayenne just can't create an object. To deal with such data, you may have to fetch DataRows (ObjectSelect.dataRowQuery(..)).

Andrus

> On May 24, 2017, at 4:42 PM, Pascal Robert <[hidden email]> wrote:
>
>
>> Le 24 mai 2017 à 03:34, Andrus Adamchik <[hidden email]> a écrit :
>>
>>
>>> On May 23, 2017, at 3:02 PM, Pascal Robert <[hidden email]> wrote:
>>>
>>>>
>>>> Do all your MySQL entities contain an ObjAttribute for their PK?
>>>
>>> In fact, the source is FileMaker, and even if the field is listed as a number with autofill, that stupid database still allows NULL or non-number values...
>>
>> So maybe null PKs are the culprit? Can you check for null, and assign a PK yourself?
>
> This is what I do, but not only the PK is null, but the fetched object is null.
>
>    try(ResultIterator<ContactsPrincipaux> contactsPrincipaux = ObjectSelect.query(ContactsPrincipaux.class).iterator(context)) {
>      try(BufferedWriter writer = Files.newBufferedWriter(file.toPath(), charset, StandardOpenOption.CREATE)) {
>        for (ContactsPrincipaux contact: contactsPrincipaux) {
>          if (contact != null && contact.getKpCodeContact() != null) {
>            String s = contact.getKpCodeContact() + ";" + contact.getInfoPrenom() + ";" + contact.getInfoNom() + ";" + contact.getInfoCourriel1() + ";" + contact.getAdressePrincipaleCodepostal() + "\n";
>            writer.write(s, 0, s.length());
>          } else {
>            System.out.println(contact);
>          }
>        }
>      } catch (IOException ex) {
>
>      }
>    }
>
> When the FileMaker record does not have a value in kp_code_contact, contact is null.

Loading...