ExecutorType.REUSE/BATCH

[Artem Rodin]

Hello MyBatis Team,

I am testing the MyBatis framework to work with our application's Oracle database.

Since I have been for many years working with Oracle database and our application has high performance demands, I am very choosy to the efficiency that framework can give us in regards to retrieving/updating sets of rows in the database.

I was excited when I found the ExecutorType.Reuse/Batch setting hoping that it will allow me to achieve all desired goals and get the best performance possible.

I did several rounds of testing collecting every time SQL Traces (Oracle tool to gather queries execution statistics within session)

Test environment: 

• Oracle instance in my individual use
• Application server running Java code and accessing Oracle database
• Application: in a loop (10 iterations) selecting set of row from the table, number of rows varies from 6 to 112 and updates back same set of the rows
• I used FetchSize = 100 and
• run test either with ExecutorType = BATCH or REUSE

I got following results

1. ExecutoreType : REUSE

1.1 If inside the loop there is only SELECT statement it works fine

• query parsed once ("reused)
• number of execution = number of iterations
• number of fetches - according to fetchsize

1.2 If inside the loop there are SELECT and UPDATE, "reuse" actually does not work 

• every select is parsed everytime it is executed = number of iterations 
• every update is parsed everytime it is executed = number of iterations
• FetchSize works with select (expected)
• updates - updates every row individually - one-by-one (expected)

2. ExecutoreType : BATCH

Gives the best result, since it allows updating databases with "batches", batchsize is controlled programmatically by code. 

• every select is parsed everytime it is executed = number of iterations 
• every update is parsed everytime it is executed = number of iterations
  
• FetchSize works with select (expected)
  
• update is executed only N number of times, depend on "update batch size"

3. Tried to switch ExecutorType within the loop

Actually this is the worst.

• select still is parsed everytime it is executed
• updated is parsed with every row it updates

So in the summary I have following questions

Q1. Why with ExecutorType.REUSE it stops "reuses" if we have a mix of selects/updates?

Q2. Why are REUSE/BATCH mutually exclusive? Other words,  is there any way now, or will it be in the future to combine the best of REUSE/BATCH, that will allow to avoid unnecessary and performance impacting soft parses on the database side, but update the database with "batches"?

I have one more question to be honest, I was trying to use MyBatis with Spring framework as well, and there was a complete disaster. Despite all my efforts (define default-executor-type in application.properties,  set up configuration through JAVA API, passing ExecutorType.BATCH to sqlSessionFactory.openSession, I haven't managed to make it work in "batch" mode. Soe extra question is

Q3. How to make ExecutorType.BATCH work with MyBatis inside Spring Framework 

I will totally understand if you ignore my mail, since I am not sure this is right communication channel, but I will highly appreciate if you comment on my questions/concerns

Thank you in advance

[Guy Rouillier]

Please clarify.  Are you doing a SELECT FOR UPDATE followed by an UPDATE?  Or are your UPDATE statements fully qualified?

Some code would help.  Specifically, I'd like to see the SQL to make sure that the SELECT and UPDATE statements are completely independent.

--

Guy Rouillier

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/2e96a35f-319d-4060-aacd-d51a524ca687n%40googlegroups.com.

[Artem Rodin]

Hi Guy,

Thank you for coming back.

I do SELECT and then UPDATE, they are independent. For your assurance, please, see file attached

Regards

Artem

[Artem Rodin]

Hi,

Did information provided help?

Any comments, updates on my questions?

Thank you in advance

Artem

[Guy Rouillier]

Yes, sorry, I've been busy with other things.

You don't mention setting autocommit.  The default for most databases, including Oracle, is to set autocommit to true.  When commits are executed, all open cursors are closed.  So, when you execute an update with autocommit true, that will cause the prepared statements on that connection to be invalidated, and the cursors for select statements to be closed.  That is probably why you are seeing both your select and update parsed on each iteration.

The solution to this is to set autocommit false, then execute your batch and perform a commit. Then your statements will only be parsed once for each batch.

--

Guy Rouillier

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/90517603-6096-4983-a266-b05e53c4b694n%40googlegroups.com.

[Artem Rodin]

Hi Guy,

Thank you very much indeed for reply. I appreciate it.

You are right - any .commit() and or .flushStatements() within the loop "breaks REUSE".

If I do one single .commit() after the whole loop, all statements: selects, updates - are parsed once.

Okay, there is no much use of it on practice, but conceptually wise it's clear now.

However, I am confused why do you reference on database behavior in general and Oracle in particular?

I was under impression that ExecutorType.REUSE/BATCH is myBatis "feature". Natively JDBC supports

"batching" updates: .addBatch()/.executeBatch. Oracle specific JDBC driver supports "sql statements caching"  

By the way, in case of Oracle we can get both 

• SQL caching (single parse) and
• Batch execution of inserts/updates

So it looks fo me, that "flushing" cache on .commit()/.flushStatements() is myBatis specific/implementation.

I am wondering 

• why was this approach used? 
• what are obstacles to keep SQLs in cache over the session
• are in plans to enhance it in the future

Once again, thank you very much for your help.

Best regards

Artem

[Guy Rouillier]

I mentioned Oracle in particular because each DBMS may do things differently.  For example, PostgreSQL and DB2 both allow a WITH HOLD clause on the declaration of a cursor.  If present, that clause will keep the cursor open when a commit is performed.  Oracle doesn't have this clause, but will keep the cursor open during a commit in certain circumstances.  So, you need to understand how your particular DBMS handles this situation.

-- 

--

Guy Rouillier