-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 
Author Message
 Post subject: Automatic batch load is slow
PostPosted: Fri Mar 10, 2017 4:29 pm 
Newbie

Joined: Fri Mar 10, 2017 4:17 pm
Posts: 5
I have annotated an entity with @BatchSize. When it is needed a query like the following one is executed:

Code:
Select x, y, z, from BOO where id in (?,  ?, ?, .... ?)


Such queries are always slow. It is as if the configured fetch_size is ignored and each record fetched one by one.
The database is Oracle and other queries can deliver 10k rows in 5 sec with joins over 3-4 tables. On these queries I clearly see a difference for different fetch_sizes - just to say that it looks like the config is ok, just not for "where X in (...)" type of queries.

Is this an Oracle-driver thing?


Top
 Profile  
 
 Post subject: Re: Automatic batch load is slow
PostPosted: Sun Mar 12, 2017 3:03 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
Better than relying on @BatchSize, you are better off replacing that collection with a simple entity query which will perform better anyway.


Top
 Profile  
 
 Post subject: Re: Automatic batch load is slow
PostPosted: Sun Mar 12, 2017 4:42 pm 
Newbie

Joined: Fri Mar 10, 2017 4:17 pm
Posts: 5
Thanks vlad - I've already visited you site many times, but I am still new to this stuff - didn't find much on google about "simple entity query". Can you point me to a more elaborate example?

What I don't understand is how this example can be so slow, like 15 seconds slow for 350 entities, when I know that on other circumstances I can pull 10k of the same entitites in 5 seconds. It is not that the database is slow. The same query is blistering fast in SQL developer.

Scenario:
Load one account where entries are loaded eagerly.
- This goes fine and quick.
Then navigate to the account in the other end of each entry in order to get info about that.
- This triggers the batch-query.

I can battle the problem by pre-requesting the accounts with a specific query instead of relying on batch.
Example
Quick version where I pre-requests all accounts
Code:
Hibernate: -- The request for the account of interest
    /* select
        A
    from
        Account A
    where
        A.accountId = :accountId */ select
            *
        from
            ( select
                account0_.accountId as accountId1_0_,
                account0_.context as context2_0_,
                account0_.currency as currency3_0_,
                account0_.description as description4_0_,
                account0_.name as name5_0_,
                account0_.ownerId as ownerId6_0_
            from
                AUKTIONSMANAGER.tblaccountingaccount account0_
            where
                account0_.accountId=? )
        where
            rownum <= ?

Hibernate: -- The pre-request I do to fill 1st lv cache
    /*
from
    Account
where
    accountId in (
        select
            (case
                when TE.fromAccount.accountId = :accountId then TE.toAccount.accountId
                else TE.fromAccount.accountId
            end)
        from
            TransferEvent TE
        where
            TE.eventId in (
                select
                    distinct E.event.eventId
                from
                    Entry E
                where
                    E.account.accountId= :accountId
            )
        ) */ select
            account0_.accountId as accountId1_0_,
            account0_.context as context2_0_,
            account0_.currency as currency3_0_,
            account0_.description as description4_0_,
            account0_.name as name5_0_,
            account0_.ownerId as ownerId6_0_
        from
            AUKTIONSMANAGER.tblaccountingaccount account0_
        where
            account0_.accountId in (
                select
                    case
                        when transferev1_.fromAccountId=? then transferev1_.toAccountId
                        else transferev1_.fromAccountId
                    end
                from
                    AUKTIONSMANAGER.tblaccountingtransferevent transferev1_
                inner join
                    AUKTIONSMANAGER.tblaccountingevent transferev1_1_
                        on transferev1_.eventId=transferev1_1_.eventId
                where
                    transferev1_.eventId in (
                        select
                            distinct entry2_.eventId
                        from
                            AUKTIONSMANAGER.tblaccountingentry entry2_
                        where
                            entry2_.accountId=?
                    )
                )

Hibernate: --- Hibernate magic when I access the entries on the first account.
    /* load one-to-many com.autorola.fleetmonitor.accounting.domain.model.account.Account.entries */ select
        entries0_.accountId as accountId8_0_4_,
        entries0_.entryId as entryId1_3_4_,
        entries0_.entryId as entryId1_3_3_,
        entries0_.accountId as accountId8_3_3_,
        entries0_.amount as amount2_3_3_,
        entries0_.currency as currency3_3_3_,
        entries0_.deleted as deleted4_3_3_,
        entries0_.eventId as eventId9_3_3_,
        entries0_.text as text5_3_3_,
        entries0_.transactionId as transactionId10_3_3_,
        entries0_.whenBooked as whenBooked6_3_3_,
        entries0_.whenCharged as whenCharged7_3_3_,
        event1_.eventId as eventId1_5_0_,
        event1_.baseEventId as baseEventId6_5_0_,
        event1_.eventType as eventType2_5_0_,
        event1_.origin as origin3_5_0_,
        event1_.whenNoticed as whenNoticed4_5_0_,
        event1_.whenOccurred as whenOccurred5_5_0_,
        event1_1_.toAccountId as toAccountId2_1_0_,
        event1_2_.adjustedEventId as adjustedEventId3_2_0_,
        event1_2_.isProcessed as isProcessed1_2_0_,
        event1_3_.adjustedEventId as adjustedEventId7_8_0_,
        event1_3_.amount as amount1_8_0_,
        event1_3_.currency as currency2_8_0_,
        event1_3_.fromAccountId as fromAccountId8_8_0_,
        event1_3_.fromText as fromText3_8_0_,
        event1_3_.isProcessed as isProcessed4_8_0_,
        event1_3_.replacementEventId as replacementEventId9_8_0_,
        event1_3_.toAccountId as toAccountId10_8_0_,
        event1_3_.toText as toText5_8_0_,
        event1_3_.transactionId as transactionId11_8_0_,
        case
            when event1_1_.eventId is not null then 1
            when event1_2_.eventId is not null then 2
            when event1_3_.eventId is not null then 3
            when event1_.eventId is not null then 0
        end as clazz_0_,
        account2_.accountId as accountId1_0_1_,
        account2_.context as context2_0_1_,
        account2_.currency as currency3_0_1_,
        account2_.description as description4_0_1_,
        account2_.name as name5_0_1_,
        account2_.ownerId as ownerId6_0_1_,
        transferev3_.eventId as eventId1_5_2_,
        transferev3_1_.baseEventId as baseEventId6_5_2_,
        transferev3_1_.eventType as eventType2_5_2_,
        transferev3_1_.origin as origin3_5_2_,
        transferev3_1_.whenNoticed as whenNoticed4_5_2_,
        transferev3_1_.whenOccurred as whenOccurred5_5_2_,
        transferev3_.adjustedEventId as adjustedEventId7_8_2_,
        transferev3_.amount as amount1_8_2_,
        transferev3_.currency as currency2_8_2_,
        transferev3_.fromAccountId as fromAccountId8_8_2_,
        transferev3_.fromText as fromText3_8_2_,
        transferev3_.isProcessed as isProcessed4_8_2_,
        transferev3_.replacementEventId as replacementEventId9_8_2_,
        transferev3_.toAccountId as toAccountId10_8_2_,
        transferev3_.toText as toText5_8_2_,
        transferev3_.transactionId as transactionId11_8_2_
    from
        AUKTIONSMANAGER.tblaccountingentry entries0_
    inner join
        AUKTIONSMANAGER.tblaccountingevent event1_
            on entries0_.eventId=event1_.eventId
    left outer join
        AUKTIONSMANAGER.tblaccountingapplytempltevent event1_1_
            on event1_.eventId=event1_1_.eventId
    left outer join
        AUKTIONSMANAGER.tblaccountingdeleteentryevent event1_2_
            on event1_.eventId=event1_2_.eventId
    left outer join
        AUKTIONSMANAGER.tblaccountingtransferevent event1_3_
            on event1_.eventId=event1_3_.eventId
    left outer join
        AUKTIONSMANAGER.tblaccountingaccount account2_
            on event1_1_.toAccountId=account2_.accountId
    left outer join
        AUKTIONSMANAGER.tblaccountingtransferevent transferev3_
            on event1_2_.adjustedEventId=transferev3_.eventId
    left outer join
        AUKTIONSMANAGER.tblaccountingevent transferev3_1_
            on transferev3_.eventId=transferev3_1_.eventId
    where
        (
            entries0_.deleted = 0
        ) 
        and entries0_.accountId=?

2017-03-12 21:18:04,537 INFO  [http-bio-24080-exec-8] [org.hibernate.engine.internal.StatisticalLoggingSessionEventListener] Session Metrics {
    67764 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    459892 nanoseconds spent preparing 3 JDBC statements;
    488948863 nanoseconds spent executing 3 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    16565 nanoseconds spent executing 2 partial-flushes (flushing a total of 0 entities and 0 collections)
}


Example where I rely on Hibernate Batch.
Code:
Hibernate: -- The request for the account of interest
    /* select
        A
    from
        Account A
    where
        A.accountId = :accountId */ select
            *
        from
            ( select
                account0_.accountId as accountId1_0_,
                account0_.context as context2_0_,
                account0_.currency as currency3_0_,
                account0_.description as description4_0_,
                account0_.name as name5_0_,
                account0_.ownerId as ownerId6_0_
            from
                AUKTIONSMANAGER.tblaccountingaccount account0_
            where
                account0_.accountId=? )
        where
            rownum <= ?

Hibernate: --- Hibernate magic when I access the entries on the first account.
    /* load one-to-many com.autorola.fleetmonitor.accounting.domain.model.account.Account.entries */ select
        entries0_.accountId as accountId8_0_4_,
        entries0_.entryId as entryId1_3_4_,
        entries0_.entryId as entryId1_3_3_,
        entries0_.accountId as accountId8_3_3_,
        entries0_.amount as amount2_3_3_,
        entries0_.currency as currency3_3_3_,
        entries0_.deleted as deleted4_3_3_,
        entries0_.eventId as eventId9_3_3_,
        entries0_.text as text5_3_3_,
        entries0_.transactionId as transactionId10_3_3_,
        entries0_.whenBooked as whenBooked6_3_3_,
        entries0_.whenCharged as whenCharged7_3_3_,
        event1_.eventId as eventId1_5_0_,
        event1_.baseEventId as baseEventId6_5_0_,
        event1_.eventType as eventType2_5_0_,
        event1_.origin as origin3_5_0_,
        event1_.whenNoticed as whenNoticed4_5_0_,
        event1_.whenOccurred as whenOccurred5_5_0_,
        event1_1_.toAccountId as toAccountId2_1_0_,
        event1_2_.adjustedEventId as adjustedEventId3_2_0_,
        event1_2_.isProcessed as isProcessed1_2_0_,
        event1_3_.adjustedEventId as adjustedEventId7_8_0_,
        event1_3_.amount as amount1_8_0_,
        event1_3_.currency as currency2_8_0_,
        event1_3_.fromAccountId as fromAccountId8_8_0_,
        event1_3_.fromText as fromText3_8_0_,
        event1_3_.isProcessed as isProcessed4_8_0_,
        event1_3_.replacementEventId as replacementEventId9_8_0_,
        event1_3_.toAccountId as toAccountId10_8_0_,
        event1_3_.toText as toText5_8_0_,
        event1_3_.transactionId as transactionId11_8_0_,
        case
            when event1_1_.eventId is not null then 1
            when event1_2_.eventId is not null then 2
            when event1_3_.eventId is not null then 3
            when event1_.eventId is not null then 0
        end as clazz_0_,
        account2_.accountId as accountId1_0_1_,
        account2_.context as context2_0_1_,
        account2_.currency as currency3_0_1_,
        account2_.description as description4_0_1_,
        account2_.name as name5_0_1_,
        account2_.ownerId as ownerId6_0_1_,
        transferev3_.eventId as eventId1_5_2_,
        transferev3_1_.baseEventId as baseEventId6_5_2_,
        transferev3_1_.eventType as eventType2_5_2_,
        transferev3_1_.origin as origin3_5_2_,
        transferev3_1_.whenNoticed as whenNoticed4_5_2_,
        transferev3_1_.whenOccurred as whenOccurred5_5_2_,
        transferev3_.adjustedEventId as adjustedEventId7_8_2_,
        transferev3_.amount as amount1_8_2_,
        transferev3_.currency as currency2_8_2_,
        transferev3_.fromAccountId as fromAccountId8_8_2_,
        transferev3_.fromText as fromText3_8_2_,
        transferev3_.isProcessed as isProcessed4_8_2_,
        transferev3_.replacementEventId as replacementEventId9_8_2_,
        transferev3_.toAccountId as toAccountId10_8_2_,
        transferev3_.toText as toText5_8_2_,
        transferev3_.transactionId as transactionId11_8_2_
    from
        AUKTIONSMANAGER.tblaccountingentry entries0_
    inner join
        AUKTIONSMANAGER.tblaccountingevent event1_
            on entries0_.eventId=event1_.eventId
    left outer join
        AUKTIONSMANAGER.tblaccountingapplytempltevent event1_1_
            on event1_.eventId=event1_1_.eventId
    left outer join
        AUKTIONSMANAGER.tblaccountingdeleteentryevent event1_2_
            on event1_.eventId=event1_2_.eventId
    left outer join
        AUKTIONSMANAGER.tblaccountingtransferevent event1_3_
            on event1_.eventId=event1_3_.eventId
    left outer join
        AUKTIONSMANAGER.tblaccountingaccount account2_
            on event1_1_.toAccountId=account2_.accountId
    left outer join
        AUKTIONSMANAGER.tblaccountingtransferevent transferev3_
            on event1_2_.adjustedEventId=transferev3_.eventId
    left outer join
        AUKTIONSMANAGER.tblaccountingevent transferev3_1_
            on transferev3_.eventId=transferev3_1_.eventId
    where
        (
            entries0_.deleted = 0
        ) 
        and entries0_.accountId=?

Hibernate: -- Hibernate batch - Yes, this takes a looong time.
    /* load com.autorola.fleetmonitor.accounting.domain.model.account.Account */ select
        account0_.accountId as accountId1_0_0_,
        account0_.context as context2_0_0_,
        account0_.currency as currency3_0_0_,
        account0_.description as description4_0_0_,
        account0_.name as name5_0_0_,
        account0_.ownerId as ownerId6_0_0_
    from
        AUKTIONSMANAGER.tblaccountingaccount account0_
    where
        account0_.accountId in (
            ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
        )

2017-03-12 21:29:36,699 INFO  [http-bio-24080-exec-10] [org.hibernate.engine.internal.StatisticalLoggingSessionEventListener] Session Metrics {
    53910 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    591809 nanoseconds spent preparing 3 JDBC statements;
    19238488744 nanoseconds spent executing 3 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    4819 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}


Looking at the system with a profiler - it looks like a lot of network activity - or waiting - which indicates to me that batch-size is ignored.
oracle.net.ns.Packet.receive() 23.024458 19197 ms (23%) 19197 ms

I mean, I can certainly write custom SQL, and tweak annotations for days on end, but Hibernate quickly loses its eligibility when I constantly have to do workarounds and write custom SQL - even if it is in HQL syntax which have learning curve too when you are used to writing and thinking SQL and database models.


Top
 Profile  
 
 Post subject: Re: Automatic batch load is slow
PostPosted: Sun Mar 12, 2017 4:48 pm 
Newbie

Joined: Fri Mar 10, 2017 4:17 pm
Posts: 5
The previous post shows how Hibernate translates to SQL.
Here is my Hibernate/HQL query to pre-request all accounts related to entries on one specific account.

Code:
  @Override
  public Collection<Account> getTransferAccounts(String _accountId)
  {
    Query query = currentSession().createQuery("from Account where accountId in (" +
            "select (case when TE.fromAccount.accountId = :accountId then TE.toAccount.accountId " +
            "else TE.fromAccount.accountId end) " +
            "from TransferEvent TE where TE.eventId in (" +
            "select distinct E.event.eventId from Entry E where E.account.accountId= :accountId))")
            .setString(ACCOUNT_ID, _accountId);
    //noinspection unchecked
    return query.list();
  }


If I had to do this old-fashion SQL I would probably de-normalise my model a bit. However, in most circumstances Hibernate is doing a marvelous job of getting the data quickly - just not when the query is like "... where X in (...)"


Top
 Profile  
 
 Post subject: Re: Automatic batch load is slow
PostPosted: Fri Mar 17, 2017 4:45 am 
Newbie

Joined: Fri Mar 10, 2017 4:17 pm
Posts: 5
@vlad, any tips on why automatic batch does not perform?

There is just no query of 300 small objects that takes 15 seconds under normal circumstances - especially not when id's are included in the where-clause.

Or a tip on how I can debug this?
Is there a config setting I should try?


Top
 Profile  
 
 Post subject: Re: Automatic batch load is slow
PostPosted: Fri Mar 17, 2017 5:04 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
You should use a profiler and see where the time is spent. Check also the execution plan on the DB too. Otherwise, just run a native SQL query and map the results to a DTO.


Top
 Profile  
 
 Post subject: Re: Automatic batch load is slow
PostPosted: Fri Mar 17, 2017 5:34 am 
Newbie

Joined: Fri Mar 10, 2017 4:17 pm
Posts: 5
I did look with a profiler:
Looking at the system with a profiler - it looks like a lot of network activity - or waiting - which indicates to me that batch-size is ignored.
oracle.net.ns.Packet.receive() 23.024458 19197 ms (23%) 19197 ms

I also looked at the hibernate stats:

Code:
2017-03-12 21:29:36,699 INFO  [http-bio-24080-exec-10] [org.hibernate.engine.internal.StatisticalLoggingSessionEventListener] Session Metrics {
    53910 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    591809 nanoseconds spent preparing 3 JDBC statements;
    19238488744 nanoseconds spent executing 3 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    4819 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}


Could you give me a pointer to what it means to map results to DTO? Who and when should this SQL be executed? Can it be automated or are you proposing a solution like what I have implemented with pre-load of all accounts (See earlier posts)?


Top
 Profile  
 
 Post subject: Re: Automatic batch load is slow
PostPosted: Fri Mar 17, 2017 10:31 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
If you read the [Hibernate User Guide](http://docs.jboss.org/hibernate/orm/5.2 ... Guide.html), you'll find an answer to how to use DTOs.

As for when to use them, check out this article.

Related to your DB-related issue, check the DB logs as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.