Nested result mapping taking too long

[maelstrom3]

My nested result mapping is taking too long:
*5 sec more* compared to running the same query directly against the
database.

I strongly suspect I'm not using the ID element right. *Is the ID element
meant to uniquely identify a property from other properties of the same type
or from any other property?* My ID element cannot do the latter.

Notice in the code below that I'm using the property *PRODID *in the ID
elements for the main resultmap and also for its nested resultmaps.

<resultMap type="Product" id="completeProductObject3">
<id property="PRODID" column="PRODID"/>
<association property="productDetails" javaType="productDetails"
columnPrefix="p_">
<id property="PRODID" column="PRODID"/>
</association>
<collection property="buyers" ofType="Buyer" columnPrefix="b_">
<id property="prodid" column="b_prodid"/>
</collection>
</resultMap>

*More Code:*
I'm using a *JUnit* test case to run the above:
public void testGetProductDetailByPackId3() throws Exception {
SqlSession session = sf.openSession();
List<Product> productList;

try {
productList =

session.selectList("com.org.team.proj.persistence.ProductMapper.getProductDetailByPackId3");
} finally {
session.close();
}

assertNotNull(productList);
}

*ProductMapper.xml*
<select id="getProductDetailByPackId3" resultMap="completeProductObject3">
my SQL query here
</select>

*Note*
1. About *500 columns *that are being fetched by the SQL query.
2. I got a log from the mybatis log factory, but I couldn't find the
*mapping logs* in it.




--
View this message in context: http://mybatis-user.963551.n3.nabble.com/Nested-result-mapping-taking-too-long-tp4026407.html
Sent from the mybatis-user mailing list archive at Nabble.com.

[Guy Rouillier]

On 2/4/2013 5:14 PM, maelstrom3 wrote:
> My nested result mapping is taking too long:
> *5 sec more* compared to running the same query directly against the
> database.
>
> I strongly suspect I'm not using the ID element right. *Is the ID element
> meant to uniquely identify a property from other properties of the same type
> or from any other property?* My ID element cannot do the latter.

The id column (or columns) should uniquely identify a resulting object
amongst the collection of all objects.

>
> Notice in the code below that I'm using the property *PRODID *in the ID
> elements for the main resultmap and also for its nested resultmaps.
>
> <resultMap type="Product" id="completeProductObject3">
> <id property="PRODID" column="PRODID"/>
> <association property="productDetails" javaType="productDetails"
> columnPrefix="p_">
> <id property="PRODID" column="PRODID"/>
> </association>
> <collection property="buyers" ofType="Buyer" columnPrefix="b_">
> <id property="prodid" column="b_prodid"/>
> </collection>
> </resultMap>

Are you using MyBatis 3.1.1? If not, you should be. We've had a lot of
discussions about id's and associations. Try this (remove
column="PRODID" from the id outside of the association) :

<resultMap type="Product" id="completeProductObject3">

<id property="PRODID"/>

<association property="productDetails" javaType="productDetails"
columnPrefix="p_">
<id property="PRODID" column="PRODID"/>
</association>
<collection property="buyers" ofType="Buyer" columnPrefix="b_">
<id property="prodid" column="b_prodid"/>
</collection>
</resultMap>

> 2. I got a log from the mybatis log factory, but I couldn't find the
> *mapping logs* in it.

Make sure you enable DEBUG for MyBatis:

log4j.logger.com.ibatis=DEBUG
log4j.logger.org.apache.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG

--
Guy Rouillier

[maelstrom3]

> The id column (or columns) should uniquely identify a resulting object
> amongst the collection of all objects.
>

> Are you using MyBatis 3.1.1? If not, you should be. We've had a lot of
> discussions about id's and associations. Try this (remove
> column="PRODID" from the id outside of the association) :

Yes, I'm using MyBatis 3.1.1. Based on what you've said, having PRODID as
the identifier for the main resultmap and also it's association and
collection types is causing the problem. MyBatis is unable to uniquely
identify objects. The modification you suggested unfortunately didn't work.

>> 2. I got a log from the mybatis log factory, but I couldn't find the
>> *mapping logs* in it.
>
> Make sure you enable DEBUG for MyBatis:

I already had this thing in. I guess resultMap logs are not part of what the
internal log factory generates. The following is what I get:

2013-02-06 14:59:18,909 DEBUG [main] - Logging initialized using
'org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
2013-02-06 14:59:19,706 DEBUG [main] - Openning JDBC Connection
2013-02-06 14:59:22,925 DEBUG [main] - ooo Using Connection
[oracle.jdbc.driver.T4CConnection@1a68ef9]
2013-02-06 14:59:22,925 DEBUG [main] - ==> Preparing: <SELECT query>
2013-02-06 14:59:23,128 DEBUG [main] - ==> Parameters:
2013-02-06 14:59:38,394 DEBUG [main] - Resetting autocommit to true on JDBC
Connection [oracle.jdbc.driver.T4CConnection@1a68ef9]
2013-02-06 14:59:38,394 DEBUG [main] - Closing JDBC Connection
[oracle.jdbc.driver.T4CConnection@1a68ef9]

My options

1. Not having buyer (collection) and productDetails (association) properties
as part of Product (main property). Separate queries to fetch these might
help.
2. Having a stored procedure to offset for the loss of time in mapping.
3. Changing my data model to have objects with unique identifiers.

Let me know if you have any suggestions.



--
View this message in context: http://mybatis-user.963551.n3.nabble.com/Nested-result-mapping-taking-too-long-tp4026407p4026410.html

[maelstrom3]

*Problem solved!*

It turns out the developer, who designed the MyBatis config, used an *Oracle
copy *of the database which didn't have *indexes and other optimizations*
set on the database. I was comparing the slow performance of this with the
optimized and indexed *MSSQL database*.

Now the MyBatis performance is as good as running the query directly against
the MSSQL database.



--
View this message in context: http://mybatis-user.963551.n3.nabble.com/Nested-result-mapping-taking-too-long-tp4026407p4026411.html

[Eduardo Macarron]

Hi Guy,

This style of enabling loggers still works for MyBatis 3.1 but will be
definitely removed in 3.2

log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG

Instead you should add your mapper namespace to the logger config file.
http://www.mybatis.org/core/logging.html

In 3.2 a logPrefix setting is provided so all mapper namespaces are
prefixed so as to be able to turn all them on/off at the same time.
http://www.mybatis.org/core/configuration.html

[Guy Rouillier]

On 2/7/2013 2:17 AM, Eduardo Macarron wrote:
> Hi Guy,
>
> This style of enabling loggers still works for MyBatis 3.1 but will be
> definitely removed in 3.2
>
> log4j.logger.java.sql.Connection=DEBUG
> log4j.logger.java.sql.Statement=DEBUG
> log4j.logger.java.sql.PreparedStatement=DEBUG
> log4j.logger.java.sql.ResultSet=DEBUG
>
> Instead you should add your mapper namespace to the logger config file.
> http://www.mybatis.org/core/logging.html

Eduardo, thanks for pointing this out. I'd like to recommend that
selection of a logging implementation be done in the MyBatisConfig.xml
file (perhaps in addition to in the source code as shown in the page you
link to above.) We use Tomcat as our app server and log4j as our
logging implementation. I don't think requiring users to update all
their application source code to select a logging implementation is a
good idea.

Plus, if the preferred implementation can be specified in
MyBatisConfig.xml, then the logging implementation can be easily changed
without having to change source code.

Thanks.

--
Guy Rouillier

[Eduardo Macarron]

Hi Guy,

That makes a lot sense. What about a new setting like this?

<configuration>

<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>

</configuration>


2013/2/7 Guy Rouillier <guy.ro...@gmail.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.
>
>

[Guy Rouillier]

Perfect, thanks!

--
Guy Rouillier