mybatis-user

performance of restulSet to Object graph mapping

Classic

List

Threaded

12 messages Options

Benoît

performance of restulSet to Object graph mapping

Hi Folks,

Using: java 5 + mybatis 3.0.6 with DB2 on iSeries (JDBC driver jt400
v5.1.1).

I am facing some serious performance ussues with certain MyBatis
mapped queries. Initially I thaught it was just due to the abcense of
indexes in the database but that is just part of the explanation.
Thanks to AspectJ I have been able to measure
- the time required execute the query
- the time required to create java objects from the resutlset
(RestulSetHandler interface)

It takes anwhere from 60 up to 80 seconds for the MyBatis mapper to
return the list of queried objects. 20% of that time is used for the
SQL execution, 80% of the time is used to build the resultMap.

I was rather surprised with those values. I agree the database could
do with some indexes but what about the result mapping with MyBatis ?
Can this be improved somehow ?

My resultmap is very hierarchycal. All association and collections
have at least 1 <id> field.
The SQL query has about 5 inner joins and 10 left outer joins.

<resultMap>
<collection></collection>
<collection></collection>
<collection>
<association/>
<collection></collection>
<collection>
<association/>
<collection>
<association/>
<collection>
</collection>
</collection>
</resultMap>

Thanks,
Benoît

Eduardo Macarron

Re: performance of restulSet to Object graph mapping

I would do a pair of things. Ensure you have specified id columns in all your reaultmaps. Arter that, try 3.1, it may speed it up a bit.

El 16/02/2012 13:38, "Benoît" <[hidden email]> escribió:

Hi Folks,

Using: java 5 + mybatis 3.0.6 with DB2 on iSeries (JDBC driver jt400
v5.1.1).

I am facing some serious performance ussues with certain MyBatis
mapped queries. Initially I thaught it was just due to the abcense of
indexes in the database but that is just part of the explanation.
Thanks to AspectJ I have been able to measure
- the time required execute the query
- the time required to create java objects from the resutlset
(RestulSetHandler interface)

It takes anwhere from 60 up to 80 seconds for the MyBatis mapper to
return the list of queried objects. 20% of that time is used for the
SQL execution, 80% of the time is used to build the resultMap.

I was rather surprised with those values. I agree the database could
do with some indexes but what about the result mapping with MyBatis ?
Can this be improved somehow ?

My resultmap is very hierarchycal. All association and collections
have at least 1 <id> field.
The SQL query has about 5 inner joins and 10 left outer joins.

<resultMap>
<collection></collection>
<collection></collection>
<collection>
<association/>
<collection></collection>
<collection>
<association/>
<collection>
<association/>
<collection>
</collection>
</collection>
</resultMap>

Thanks,
Benoît

c c-2

Re: performance of restulSet to Object graph mapping

In reply to this post by Benoît

first，the user required is

Reasonable?

then i think you database table desigh is reasonable?

the last,you had better not use so many collection

you can look the sql return rows

2012/2/16 Benoît <[hidden email]>

Hi Folks,

Using: java 5 + mybatis 3.0.6 with DB2 on iSeries (JDBC driver jt400
v5.1.1).

I am facing some serious performance ussues with certain MyBatis
mapped queries. Initially I thaught it was just due to the abcense of
indexes in the database but that is just part of the explanation.
Thanks to AspectJ I have been able to measure
- the time required execute the query
- the time required to create java objects from the resutlset
(RestulSetHandler interface)

It takes anwhere from 60 up to 80 seconds for the MyBatis mapper to
return the list of queried objects. 20% of that time is used for the
SQL execution, 80% of the time is used to build the resultMap.

I was rather surprised with those values. I agree the database could
do with some indexes but what about the result mapping with MyBatis ?
Can this be improved somehow ?

My resultmap is very hierarchycal. All association and collections
have at least 1 <id> field.
The SQL query has about 5 inner joins and 10 left outer joins.

<resultMap>
<collection></collection>
<collection></collection>
<collection>
<association/>
<collection></collection>
<collection>
<association/>
<collection>
<association/>
<collection>
</collection>
</collection>
</resultMap>

Thanks,
Benoît

Richard Yee

Re: performance of restulSet to Object graph mapping

In reply to this post by Benoît

How many rows and columns are being returned?

Sent from my iPhone

On Feb 16, 2012, at 4:38 AM, Benoît <[hidden email]> wrote:

> Hi Folks,
>
> Using: java 5 + mybatis 3.0.6 with DB2 on iSeries (JDBC driver jt400
> v5.1.1).
>
> I am facing some serious performance ussues with certain MyBatis
> mapped queries. Initially I thaught it was just due to the abcense of
> indexes in the database but that is just part of the explanation.
> Thanks to AspectJ I have been able to measure
> - the time required execute the query
> - the time required to create java objects from the resutlset
> (RestulSetHandler interface)
>
> It takes anwhere from 60 up to 80 seconds for the MyBatis mapper to
> return the list of queried objects. 20% of that time is used for the
> SQL execution, 80% of the time is used to build the resultMap.
>
> I was rather surprised with those values. I agree the database could
> do with some indexes but what about the result mapping with MyBatis ?
> Can this be improved somehow ?
>
> My resultmap is very hierarchycal. All association and collections
> have at least 1 <id> field.
> The SQL query has about 5 inner joins and 10 left outer joins.
>
> <resultMap>
> <collection></collection>
> <collection></collection>
> <collection>
> <association/>
> <collection></collection>
> <collection>
> <association/>
> <collection>
> <association/>
> <collection>
> </collection>
> </collection>
> </resultMap>
>
>
> Thanks,
> Benoît

Benoît

Re: performance of restulSet to Object graph mapping

The resultset has about 190 columns and 100K up to 150K records.

Regards,
Benoît

On Feb 16, 6:11 pm, Richard Yee <[hidden email]> wrote:

> How many rows and columns are being returned?
>
> Sent from my iPhone
>
> On Feb 16, 2012, at 4:38 AM, Benoît <[hidden email]> wrote:
>
>
>
>
>
>
>
> > Hi Folks,
>
> > Using: java 5 + mybatis 3.0.6 with DB2 on iSeries (JDBC driver jt400
> > v5.1.1).
>
> > I am facing some serious performance ussues with certain MyBatis
> > mapped queries. Initially I thaught it was just due to the abcense of
> > indexes in the database but that is just part of the explanation.
> > Thanks to AspectJ I have been able to measure
> > - the time required execute the query
> > - the time required to create java objects from the resutlset
> > (RestulSetHandler interface)
>
> > It takes anwhere from 60 up to 80 seconds for the MyBatis mapper to
> > return the list of queried objects. 20% of that time is used for the
> > SQL execution, 80% of the time is used to build the resultMap.
>
> > I was rather surprised with those values. I agree the database could
> > do with some indexes but what about the result mapping with MyBatis ?
> > Can this be improved somehow ?
>
> > My resultmap is very hierarchycal. All association and collections
> > have at least 1 <id> field.
> > The SQL query has about 5 inner joins and 10 left outer joins.
>
> > <resultMap>
> > <collection></collection>
> > <collection></collection>
> > <collection>
> > <association/>
> > <collection></collection>
> > <collection>
> > <association/>
> > <collection>
> > <association/>
> > <collection>
> > </collection>
> > </collection>
> > </resultMap>
>
> > Thanks,
> > Benoît

Eduardo Macarron

Re: performance of restulSet to Object graph mapping

Benoît, sorry to insist. Ensure you use ids in your resulmaps. And go 3.1. :)

2012/2/28 Benoît <[hidden email]>:

> The resultset has about 190 columns and 100K up to 150K records.
>
> Regards,
> Benoît
>
> On Feb 16, 6:11 pm, Richard Yee <[hidden email]> wrote:
>> How many rows and columns are being returned?
>>
>> Sent from my iPhone
>>
>> On Feb 16, 2012, at 4:38 AM, Benoît <[hidden email]> wrote:
>>
>>
>>
>>
>>
>>
>>
>> > Hi Folks,
>>
>> > Using: java 5 + mybatis 3.0.6 with DB2 on iSeries (JDBC driver jt400
>> > v5.1.1).
>>
>> > I am facing some serious performance ussues with certain MyBatis
>> > mapped queries. Initially I thaught it was just due to the abcense of
>> > indexes in the database but that is just part of the explanation.
>> > Thanks to AspectJ I have been able to measure
>> > - the time required execute the query
>> > - the time required to create java objects from the resutlset
>> > (RestulSetHandler interface)
>>
>> > It takes anwhere from 60 up to 80 seconds for the MyBatis mapper to
>> > return the list of queried objects. 20% of that time is used for the
>> > SQL execution, 80% of the time is used to build the resultMap.
>>
>> > I was rather surprised with those values. I agree the database could
>> > do with some indexes but what about the result mapping with MyBatis ?
>> > Can this be improved somehow ?
>>
>> > My resultmap is very hierarchycal. All association and collections
>> > have at least 1 <id> field.
>> > The SQL query has about 5 inner joins and 10 left outer joins.
>>
>> > <resultMap>
>> > <collection></collection>
>> > <collection></collection>
>> > <collection>
>> > <association/>
>> > <collection></collection>
>> > <collection>
>> > <association/>
>> > <collection>
>> > <association/>
>> > <collection>
>> > </collection>
>> > </collection>
>> > </resultMap>
>>
>> > Thanks,
>> > Benoît

Benoît

Re: performance of restulSet to Object graph mapping

Eduardo,

Yes all collections / associations have at least one id.

For reference, here is a copy of the mapper :
http://dl.dropbox.com/u/1630445/OrderFormMapper.xml
The resultMap I'm talking about is called 'OrderFormResultMap', the
query at hand is 'getByFormNames'

Regards,
Benoît

On Feb 28, 9:57 am, Eduardo Macarron <[hidden email]>
wrote:

> Benoît, sorry to insist. Ensure you use ids in your resulmaps. And go 3.1. :)
>
> 2012/2/28 Benoît <[hidden email]>:
>
>
>
>
>
>
>
> > The resultset has about 190 columns and 100K up to 150K records.
>
> > Regards,
> > Benoît
>
> > On Feb 16, 6:11 pm, Richard Yee <[hidden email]> wrote:
> >> How many rows and columns are being returned?
>
> >> Sent from my iPhone
>
> >> On Feb 16, 2012, at 4:38 AM, Benoît <[hidden email]> wrote:
>
> >> > Hi Folks,
>
> >> > Using: java 5 + mybatis 3.0.6 with DB2 on iSeries (JDBC driver jt400
> >> > v5.1.1).
>
> >> > I am facing some serious performance ussues with certain MyBatis
> >> > mapped queries. Initially I thaught it was just due to the abcense of
> >> > indexes in the database but that is just part of the explanation.
> >> > Thanks to AspectJ I have been able to measure
> >> > - the time required execute the query
> >> > - the time required to create java objects from the resutlset
> >> > (RestulSetHandler interface)
>
> >> > It takes anwhere from 60 up to 80 seconds for the MyBatis mapper to
> >> > return the list of queried objects. 20% of that time is used for the
> >> > SQL execution, 80% of the time is used to build the resultMap.
>
> >> > I was rather surprised with those values. I agree the database could
> >> > do with some indexes but what about the result mapping with MyBatis ?
> >> > Can this be improved somehow ?
>
> >> > My resultmap is very hierarchycal. All association and collections
> >> > have at least 1 <id> field.
> >> > The SQL query has about 5 inner joins and 10 left outer joins.
>
> >> > <resultMap>
> >> > <collection></collection>
> >> > <collection></collection>
> >> > <collection>
> >> > <association/>
> >> > <collection></collection>
> >> > <collection>
> >> > <association/>
> >> > <collection>
> >> > <association/>
> >> > <collection>
> >> > </collection>
> >> > </collection>
> >> > </resultMap>
>
> >> > Thanks,
> >> > Benoît

Eduardo Macarron

Re: performance of restulSet to Object graph mapping

Hi. I am now behind a proxy that does not like that URL :(. I will
have a look at it later.

BTW, in 3.1 we (mostly Iwao) did some improvements that may speed up
queries under certain circumstances. Testing it should be just
swapping the jars. Could you try it?

2012/2/29 Benoît <[hidden email]>:

> Eduardo,
>
> Yes all collections / associations have at least one id.
>
> For reference, here is a copy of the mapper :
> http://dl.dropbox.com/u/1630445/OrderFormMapper.xml
> The resultMap I'm talking about is called 'OrderFormResultMap', the
> query at hand is 'getByFormNames'
>
>
> Regards,
> Benoît
>
> On Feb 28, 9:57 am, Eduardo Macarron <[hidden email]>
> wrote:
>> Benoît, sorry to insist. Ensure you use ids in your resulmaps. And go 3.1. :)
>>
>> 2012/2/28 Benoît <[hidden email]>:
>>
>>
>>
>>
>>
>>
>>
>> > The resultset has about 190 columns and 100K up to 150K records.
>>
>> > Regards,
>> > Benoît
>>
>> > On Feb 16, 6:11 pm, Richard Yee <[hidden email]> wrote:
>> >> How many rows and columns are being returned?
>>
>> >> Sent from my iPhone
>>
>> >> On Feb 16, 2012, at 4:38 AM, Benoît <[hidden email]> wrote:
>>
>> >> > Hi Folks,
>>
>> >> > Using: java 5 + mybatis 3.0.6 with DB2 on iSeries (JDBC driver jt400
>> >> > v5.1.1).
>>
>> >> > I am facing some serious performance ussues with certain MyBatis
>> >> > mapped queries. Initially I thaught it was just due to the abcense of
>> >> > indexes in the database but that is just part of the explanation.
>> >> > Thanks to AspectJ I have been able to measure
>> >> > - the time required execute the query
>> >> > - the time required to create java objects from the resutlset
>> >> > (RestulSetHandler interface)
>>
>> >> > It takes anwhere from 60 up to 80 seconds for the MyBatis mapper to
>> >> > return the list of queried objects. 20% of that time is used for the
>> >> > SQL execution, 80% of the time is used to build the resultMap.
>>
>> >> > I was rather surprised with those values. I agree the database could
>> >> > do with some indexes but what about the result mapping with MyBatis ?
>> >> > Can this be improved somehow ?
>>
>> >> > My resultmap is very hierarchycal. All association and collections
>> >> > have at least 1 <id> field.
>> >> > The SQL query has about 5 inner joins and 10 left outer joins.
>>
>> >> > <resultMap>
>> >> > <collection></collection>
>> >> > <collection></collection>
>> >> > <collection>
>> >> > <association/>
>> >> > <collection></collection>
>> >> > <collection>
>> >> > <association/>
>> >> > <collection>
>> >> > <association/>
>> >> > <collection>
>> >> > </collection>
>> >> > </collection>
>> >> > </resultMap>
>>
>> >> > Thanks,
>> >> > Benoît

Andreas Markitanis

Re: performance of restulSet to Object graph mapping

Does you guys have an idea when a stable release of 3.1 will be out?

Thanks
Andreas
On 29 Feb 2012, at 13:17, Eduardo Macarron wrote:

> Hi. I am now behind a proxy that does not like that URL :(. I will
> have a look at it later.
>
> BTW, in 3.1 we (mostly Iwao) did some improvements that may speed up
> queries under certain circumstances. Testing it should be just
> swapping the jars. Could you try it?
>
> 2012/2/29 Benoît <[hidden email]>:
>> Eduardo,
>>
>> Yes all collections / associations have at least one id.
>>
>> For reference, here is a copy of the mapper :
>> http://dl.dropbox.com/u/1630445/OrderFormMapper.xml
>> The resultMap I'm talking about is called 'OrderFormResultMap', the
>> query at hand is 'getByFormNames'
>>
>>
>> Regards,
>> Benoît
>>
>> On Feb 28, 9:57 am, Eduardo Macarron <[hidden email]>
>> wrote:
>>> Benoît, sorry to insist. Ensure you use ids in your resulmaps. And go 3.1. :)
>>>
>>> 2012/2/28 Benoît <[hidden email]>:
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>> The resultset has about 190 columns and 100K up to 150K records.
>>>
>>>> Regards,
>>>> Benoît
>>>
>>>> On Feb 16, 6:11 pm, Richard Yee <[hidden email]> wrote:
>>>>> How many rows and columns are being returned?
>>>
>>>>> Sent from my iPhone
>>>
>>>>> On Feb 16, 2012, at 4:38 AM, Benoît <[hidden email]> wrote:
>>>
>>>>>> Hi Folks,
>>>
>>>>>> Using: java 5 + mybatis 3.0.6 with DB2 on iSeries (JDBC driver jt400
>>>>>> v5.1.1).
>>>
>>>>>> I am facing some serious performance ussues with certain MyBatis
>>>>>> mapped queries. Initially I thaught it was just due to the abcense of
>>>>>> indexes in the database but that is just part of the explanation.
>>>>>> Thanks to AspectJ I have been able to measure
>>>>>> - the time required execute the query
>>>>>> - the time required to create java objects from the resutlset
>>>>>> (RestulSetHandler interface)
>>>
>>>>>> It takes anwhere from 60 up to 80 seconds for the MyBatis mapper to
>>>>>> return the list of queried objects. 20% of that time is used for the
>>>>>> SQL execution, 80% of the time is used to build the resultMap.
>>>
>>>>>> I was rather surprised with those values. I agree the database could
>>>>>> do with some indexes but what about the result mapping with MyBatis ?
>>>>>> Can this be improved somehow ?
>>>
>>>>>> My resultmap is very hierarchycal. All association and collections
>>>>>> have at least 1 <id> field.
>>>>>> The SQL query has about 5 inner joins and 10 left outer joins.
>>>
>>>>>> <resultMap>
>>>>>> <collection></collection>
>>>>>> <collection></collection>
>>>>>> <collection>
>>>>>> <association/>
>>>>>> <collection></collection>
>>>>>> <collection>
>>>>>> <association/>
>>>>>> <collection>
>>>>>> <association/>
>>>>>> <collection>
>>>>>> </collection>
>>>>>> </collection>
>>>>>> </resultMap>
>>>
>>>>>> Thanks,
>>>>>> Benoît

Eduardo Macarron

Re: performance of restulSet to Object graph mapping

It is closed now. Current snapshot is the final version unless a
serious bug is found (very unlikely).

The release depends on de availability of some key team members :) but
will try to do it the weekend of 10th March.

Richard Yee

Re: performance of restulSet to Object graph mapping

In reply to this post by Benoît

100-150k rows and 190 columns ~= 24 million object creations (if the columns are mostly strings or wrapper objects)

What is your expectation on the time it should take? I'm not that surprised that it is taking so long
Have you tried limiting the # of rows returned?.

-Richard

On Tue, Feb 28, 2012 at 12:32 AM, Benoît <[hidden email]> wrote:

The resultset has about 190 columns and 100K up to 150K records.

Regards,
Benoît

On Feb 16, 6:11 pm, Richard Yee <[hidden email]> wrote:
> How many rows and columns are being returned?
>
> Sent from my iPhone
>

> On Feb 16, 2012, at 4:38 AM, Benoît <[hidden email]> wrote:
>
>
>
>
>
>
>
> > Hi Folks,
>
> > Using: java 5 + mybatis 3.0.6 with DB2 on iSeries (JDBC driver jt400
> > v5.1.1).
>
> > I am facing some serious performance ussues with certain MyBatis
> > mapped queries. Initially I thaught it was just due to the abcense of
> > indexes in the database but that is just part of the explanation.
> > Thanks to AspectJ I have been able to measure
> > - the time required execute the query
> > - the time required to create java objects from the resutlset
> > (RestulSetHandler interface)
>
> > It takes anwhere from 60 up to 80 seconds for the MyBatis mapper to
> > return the list of queried objects. 20% of that time is used for the
> > SQL execution, 80% of the time is used to build the resultMap.
>
> > I was rather surprised with those values. I agree the database could
> > do with some indexes but what about the result mapping with MyBatis ?
> > Can this be improved somehow ?
>
> > My resultmap is very hierarchycal. All association and collections
> > have at least 1 <id> field.
> > The SQL query has about 5 inner joins and 10 left outer joins.
>
> > <resultMap>
> > <collection></collection>
> > <collection></collection>
> > <collection>
> > <association/>
> > <collection></collection>
> > <collection>
> > <association/>
> > <collection>
> > <association/>
> > <collection>
> > </collection>
> > </collection>
> > </resultMap>
>
> > Thanks,
> > Benoît

Benoît

Re: performance of restulSet to Object graph mapping

Hi Richard,

Initialialy I blaimed the database (DB2 on iSeries) for being crap:
not-so-good design, lack of indexes and foreign keys etc...
But that accounts only for 20% of the slowness.

Currently I have acceptable response times by using AspectJ + pro-
active data caching:
At application startup I load reference data from db into memory. Most
of my reference tables have less than 100 records so it fits nicely in
memory.
After returning from a Batis query, I use AspectJ to enrich the object
graph with the cached data.
Doing so seriously reduces the number of left outer joins in my query,
and the number of returned records (+/- 6K records).

Thanks,
Benoît

On Feb 29, 6:37 pm, Richard Yee <[hidden email]> wrote:

> 100-150k rows and 190 columns ~= 24 million object creations (if the
> columns are mostly strings or wrapper objects)
>
> What is your expectation on the time it should take? I'm not that surprised
> that it is taking so long
> Have you tried limiting the # of rows returned?.
>
> -Richard
>
>
>
>
>
>
>
> On Tue, Feb 28, 2012 at 12:32 AM, Benoît <[hidden email]> wrote:
> > The resultset has about 190 columns and 100K up to 150K records.
>
> > Regards,
> > Benoît
>
> > On Feb 16, 6:11 pm, Richard Yee <[hidden email]> wrote:
> > > How many rows and columns are being returned?
>
> > > Sent from my iPhone
>
> > > On Feb 16, 2012, at 4:38 AM, Benoît <[hidden email]> wrote:
>
> > > > Hi Folks,
>
> > > > Using: java 5 + mybatis 3.0.6 with DB2 on iSeries (JDBC driver jt400
> > > > v5.1.1).
>
> > > > I am facing some serious performance ussues with certain MyBatis
> > > > mapped queries. Initially I thaught it was just due to the abcense of
> > > > indexes in the database but that is just part of the explanation.
> > > > Thanks to AspectJ I have been able to measure
> > > > - the time required execute the query
> > > > - the time required to create java objects from the resutlset
> > > > (RestulSetHandler interface)
>
> > > > It takes anwhere from 60 up to 80 seconds for the MyBatis mapper to
> > > > return the list of queried objects. 20% of that time is used for the
> > > > SQL execution, 80% of the time is used to build the resultMap.
>
> > > > I was rather surprised with those values. I agree the database could
> > > > do with some indexes but what about the result mapping with MyBatis ?
> > > > Can this be improved somehow ?
>
> > > > My resultmap is very hierarchycal. All association and collections
> > > > have at least 1 <id> field.
> > > > The SQL query has about 5 inner joins and 10 left outer joins.
>
> > > > <resultMap>
> > > > <collection></collection>
> > > > <collection></collection>
> > > > <collection>
> > > > <association/>
> > > > <collection></collection>
> > > > <collection>
> > > > <association/>
> > > > <collection>
> > > > <association/>
> > > > <collection>
> > > > </collection>
> > > > </collection>
> > > > </resultMap>
>
> > > > Thanks,
> > > > Benoît