MyBatis very slow

[maelstrom3]

I'm wondering why MyBatis is slow in my application.

For a SELECT COUNT(*), the time taken is:

20 secs - first request
2-3 secs - subsequent requests

Caching, most likely, is making the subsequent requests faster.


Configuration


- 3-tier (WPF UI - Java Backend - Oracle Database)
- JBoss Server exposes the Java Backed as a Web-Service for the WPF UI
- Request time == time taken between when the WPF UI sends and receives
the result

Approaches tried


1. *Disabled logging*

I don't know if disabling both the logging subsystem and log4j makes a
difference; but, the best I got was 15 secs for the SELECT COUNT(*).

2. *Disabled Caching and Lazy loading*

This too probably made a 5 sec difference at most.

Do the following help?

1. Using explicit* result mapping*, by turning off auto-mapping. (See
Result Maps here).
2. Using *Pooling*. (See environments here).

I gathered these techniques from: here.
<http://mybatis-user.963551.n3.nabble.com/myBatis-is-slow-td2236249.html>

Another example


For a nested SQL statement with 2 joins and 1 sub-query, the time taken is:

60-90 secs - first request
2-3 secs - subsequent requests




--
View this message in context: http://mybatis-user.963551.n3.nabble.com/MyBatis-very-slow-tp4025893.html
Sent from the mybatis-user mailing list archive at Nabble.com.

[Larry Meadors]

Is this a joke?

There is nowhere near enough context for anyone to help you in a
material way here.

You have a TON of moving parts here and any one of them could be the bottleneck:
- UI
- who knows what
- web service
- who knows what
- mybatis
- who knows what
- database

Isolate this to a unit test that demonstrates that the issue is
mybatis and try again.

Larry

[Daniel Lyons]

I strongly suspect all your issues are in the database layer, not myBatis. The database itself is probably making subsequent requests faster with its own caching; the OS also caches requests for disk blocks, which can speed up subsequent queries. Complaints about SELECT COUNT(*) are pretty common among people switching to Postgres from MySQL--if that's your use-case, there are specific things you can do to ameliorate the problem, such as tracking the count yourself somewhere, better indexing or using the query planner's guess instead of the actual count.

[maelstrom3]

I got the problems solved! /MyBatis/ now takes the same time to query as
running directly against the database.

It was the *N+1 selects problem* (nicely described here
<http://www.mybatis.org/core/sqlmap-xml.html#Result_Maps> ).

Solution


*Nested Results* (as opposed to *Nested Select*), which is also described on
the same page mentioned above.

The difference it made to my SQL query with 4 joins was enormous:

*Before:* 38 s
*After:* 3 s

I tracked the problem down by isolating it to a *JUnit test-case* around
/MyBatis-Spring/ (removing the /JBoss/ part).

I had inherited the code-base from someone and was totally new to MyBatis,
Spring and JBoss. Took me a lot of time to solve it. Thanks Larry for that
JUnit tip!




--
View this message in context: http://mybatis-user.963551.n3.nabble.com/MyBatis-very-slow-tp4025893p4026043.html