How to enhance performance in mybatis ?

[Krithika Vittal]

 

 

I am using mybatis-3.1.0.jar and mybatis-spring-1.1.0.jar

 

It is taking 93 seconds to map 44000 rows to a resultmap containing collection of three other objects

 

Is there any thing i could do to speed up the process(indexing the table is one option) ?

 

Thanks

Krithika

[Guy Rouillier]

What does your SQL and your mapper look like? Are you using nested
SELECTs for the collections? If so, that may be the source of your
performance issues. See if you can retrieve all the data using a single
SELECT.

--
Guy Rouillier

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[Rick R]

How long does the straight SQL take when you run it against the DB directly? (not using mybatis, run from the command line or in DBVisualizer or something)

Besides making sure you aren't doing subselects as Guy mentioned, why would you need to get back 44K rows? I'd think it's relatively rare that you'd need all 44K objects in memory at one time? 

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Rick R

[Daniel Guggi]

@Is there any thing i could do to speed up the process

as rick mentioned: selecting 44k records rarely makes sense?

@indexing the table is one option

it's not just an option - it's a "must have".

[Artem Pronchakov]

Have you tried to map this collection by using plain JDBC?

How many seconds is it takes?

I'm just curious, if this issue is from mybatis or from your query...

—
Sent from Mailbox for iPad

--

[Dridi Boukelmoune]

On Mon, Jan 6, 2014 at 8:47 PM, Artem Pronchakov
<artem.pr...@gmail.com> wrote:
> Have you tried to map this collection by using plain JDBC?
> How many seconds is it takes?
>
> I'm just curious, if this issue is from mybatis or from your query...

Hi,

mybatis is not the issue to me, the use-case is.

I don't see 44000 rows as a problem in itself, as long as it's used in
a streaming fashion. Not fully mapped in-memory in one go (unless
maybe if you're populating some sort of cache).

My 2 cents,
Dridi

> —

[Ikchan Sim]

See :

>> report : compare mybatis default cache with third part cache...


1. test environment

====================================================
    os : windows 7
    jdk : 1.7
    ide : eclipse 4.2

    database: oracle 11g

    mybatis : 3.2

    third part cache type :

         ehcache-core-2.5.1.jar

         log4j-1.2.17.jar

         mybatis-ehcache-1.0.1.jar

         slf4j-api-1.6.1.jar
 

         hazelcast-2.0.2.jar

         mybatis-hazelcast-1.0.1.jar

         log4j-1.2.17.jar

 

        mybatis-memcached-1.0.0-beta1.jar

        spymemcached-2.8.1.jar

        log4j-1.2.17.jar

          mybatis-oscache-1.0.1.jar

         oscache-2.4.jar

         commons-logging-1.1.jar

         log4j-1.2.17.jar

 

2. mapper xml & properties file
====================================================

<!------------------------------------------------------------------->

<!-- ehcache mapper configuration xml file -->

<cache type="org.mybatis.caches.ehcache.EhcacheCache" /> <cache type="org.mybatis.caches.ehcache.LoggingEhcache" /> <!-- ehcache configuration xml file --> <?xml version="1.0" encoding="UTF-8" ?> <ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xsi:noNamespaceSchemaLocation="ehcache.xsd" updateCheck="false"     monitoring="autodetect" dynamicConfig="true">       <defaultCache timeToIdleSeconds="120" timeToLiveSeconds="120" maxEntriesLocalHeap="10000"         maxEntriesLocalDisk="10000000" memoryStoreEvictionPolicy="LRU" /> </ehcache> <!-------------------------------------------------------------------> <!-------------------------------------------------------------------> <!-- hazelcast mapper configuration xml file --> <cache type="org.mybatis.caches.hazelcast.HazelcastCache " /> <cache type="org.mybatis.caches.hazelcast.LoggingHazelcastCache" /> <!-- hazelcast configuration xml file --> <?xml version="1.0" encoding="UTF-8"?>  <hazelcast xsi:schemaLocation="http://www.hazelcast.com/schema/config hazelcast-config-2.5.xsd"         xmlns="http://www.hazelcast.com/schema/config" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">     <!-- cofiguration reference site -> http://www.hazelcast.com/docs.jsp --> </hazelcast> <!-------------------------------------------------------------------> <!-------------------------------------------------------------------> <!-- memcached mapper configuration xml file --> <cache type="org.mybatis.caches.memcached.MemcachedCache" /> <cache type="org.mybatis.caches.memcached.LoggingMemcachedCache" /> <!-- memcached configuration properties file --> org.mybatis.caches.memcached.servers=127.0.0.1:12000 configuration reference site -> https://code.google.com/p/spymemcached) <!-------------------------------------------------------------------> <!------------------------------------------------------------------->  <!-- oscache mapper configuration xml file --> <cache type="org.mybatis.caches.oscache.OSCache" /> <cache type="org.mybatis.caches.oscache.LoggingOSCache" /> <!-- oscache configuration properties file --> cache.capacity=1000 cache.memory=true cache.algorithm=com.opensymphony.oscache.base.algorithm.LRUCache configuration reference site -> https://code.google.com/p/spymemcached <!------------------------------------------------------------------->

====================================================

3. execute java file
====================================================

import java.io.Reader;

import java.util.Calendar;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.apache.log4j.Logger;

 

public class RunCache {

    private static Logger logger = Logger.getLogger(RunCache.class);

     private static SqlSessionFactory sqlSessionFactory;

 

    static {

        try {

            String resource = "chapter14/resources/mybatis/config-mybatis.xml";

            Reader reader = Resources.getResourceAsReader(resource);

            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

 

    public static void main(String args[]) {

        long intervalTime = 0;

        for (int count = 0; count < 100000; count++) {

            SqlSession sqlSession = sqlSessionFactory.openSession();

            try {

                long startIntervalTime = Calendar.getInstance().getTimeInMillis();

                sqlSession.selectList("chapter14.org.mybatis.persistence.ShopMapper.selectListShop");

                long endIntervalTime = Calendar.getInstance().getTimeInMillis();

 

                intervalTime = intervalTime + (endIntervalTime - startIntervalTime);

            } catch (Exception e) {

                e.printStackTrace();

            } finally {

                sqlSession.close();

            }

        }

 

        logger.debug("Total interval time is : " + intervalTime);

    }

}

====================================================

4. result 
====================================================
# mybatis default cache - 7841ms
                     Ehcache - 1564ms

                    Hzelcast - 1990ms

                   OSCache - 1775ms

2014/1/6 Artem Pronchakov <artem.pr...@gmail.com>

--

 

==================================

IkChan SIM

Software Architeture.
E-mail:plusp...@gmail.com
H.P : 010 - 8242 - 2727
Nothing in the world can take the place of persistence.
==================================

[Jose María Zaragoza]

2014/1/10 Ikchan Sim <plusp...@gmail.com>

See :

>> report : compare mybatis default cache with third part cache...

4. result 
====================================================
# mybatis default cache - 7841ms
                     Ehcache - 1564ms

                    Hzelcast - 1990ms

                   OSCache - 1775ms

Oh, its looks there is a big difference between default cache and 3rd part caches

Are these results normal ? 

One question, does default cache serialize/deserialize objects into/from cache ? 

Regards

 

[Krithika Vittal]

Below is my approach,

 

1)I am properly defining the id element in all of the resultmaps

2)It is a join query combining 7 tables.(Please note that I am not making use of nested selects).

 

Thanks.

Krithika

[Rick R]

On Fri, Jan 10, 2014 at 11:51 AM, Krithika Vittal <kri6d...@gmail.com> wrote:

 

2)It is a join query combining 7 tables.(Please note that I am not making use of nested selects).

 

How long does it take to run stand-alone (not using MyBatis) ?

 

Thanks.

Krithika

 

On Monday, 6 January 2014 13:59:13 UTC-5, Guy Rouillier wrote:

On 1/6/2014 12:58 PM, Krithika Vittal wrote:
> I am using mybatis-3.1.0.jar and mybatis-spring-1.1.0.jar
> It is taking 93 seconds to map 44000 rows to a resultmap containing
> collection of three other objects
> Is there any thing i could do to speed up the process(indexing the table
> is one option) ?

What does your SQL and your mapper look like?  Are you using nested
SELECTs for the collections?  If so, that may be the source of your
performance issues.  See if you can retrieve all the data using a single
SELECT.

--
Guy Rouillier

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Rick R

[Krithika Vittal]

It takes 32 seconds ..

[Krithika Vittal]

Ok Guys..

 

I set the fetchsize as 1000 in  the select query.

 

Now it is 16s .. Is that the way to then ?

 

Thanks.

Krithika

[Krithika Vittal]

Any suggestions on the solution ?

 

Setting fetch size as 1000 ?

[Akash Singla]

Please post your mapper and Objects.
I've pulled more than 1,00,000 in under 60 sec from mybatis through nested queries.

I think, if we optimze query, time should be reduced.

[Krithika Vittal]

Akash Singla  , Now I am able to pull around 3000 objects in 18 seconds.

 

1)id column in resultmaps

2)No nested selects

3)fetchsize set as 1000  

 

So can you suggest any other thing to be followed to achieve good performance.

[Krithika Vittal]

Also ,

 

4) When used the mybatis-3.2.3.jar(the latest) , I am getting the results in 8 seconds.

 

Please  suggest for more options to reduce the time.

 

Thanks.