Slow Hibernate flush

Question

I try to save my data to MySql(local DB) throw HibernateTemplate -

getHibernateTemplate().execute(new HibernateCallback<Void>() {
            @Override
            public Void doInHibernate(Session session) throws HibernateException, SQLException {
                for (TimeInvocationStatistics stat : statistics) {
                    session.persist(stat);
                }
                session.persist(workloadProcessDescriptiveStatistics);
                session.flush();
                return null;
            }
        });

The size of data is not so big, but this operation takes over 60 sec.

I tried to profile it -

enter image description here

(good picture resolution - picture )

As I can see session.flush() (second row in stacktrace) works slow, how I can improve it ? Could it be MySql server problems ?

UPD: find interesting thread - hibernate forum, try to do in this way

Please show us SQL Explaination plan. It helps to figure out how long database works — ADS, Jan 15 '14 at 12:32

score 5 · Answer 1 · answered Jan 15 '14 at 12:38

Without knowing how many TimeInvocationStatistics you have, nor how your tables are setup or how much data is in those tables., my best guess would be that you are inserting individual rows where you should be inserting as a batch. We recently had an app that tried to insert 18k rows of some fairly simple data (I didn't write it originally), and just by moving to batch inserts we cut the time down from about 18min to about 2sec.

The reason for such a drastic improvement was because of indexing and table size. Each insert causes the DB to recalculate the index. The larger the data set, the longer it takes to recalculate the indexes. This is the trade-off to indexing a DB table: Speed of look-up vs. slowness of insert. By batching the inserts, the DB was only required to calculate the indexes once for the whole table. Much more efficient.

The number of entities is between 200-500. The information about indexes is interesting, I will check it. — Mary Ryllo, Jan 15 '14 at 12:47

score 2 · Accepted Answer · edited May 23 '17 at 11:54

2

The problem was, that batching processing didn't work, because I used bad generation strategy. Read about this problem here -

edited May 23 '17 at 11:54

Community

1
1

answered Jan 20 '14 at 07:48

Mary Ryllo

2,321
7
34
53

Slow Hibernate flush

2 Answers2