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....
Sent from the mybatis-user mailing list archive at Nabble.com.

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

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.

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-tp4025893p...
Sent from the mybatis-user mailing list archive at Nabble.com.