MyBatis very slow

Question

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

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

1. 20 secs - first request
2. 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
• Spring Framework being used

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).
3. Do transactions help speed up SQL statements with sub-queries?

The above techniques are listed here:

1. MyBatis forums
2. JBoss Best Practices (Page 9)

Another example

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

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

Answer 1

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).

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).