I want to add cache for in-parameter to avoid getting in-parameter repeatedly. Now there are at least twp places to handle the in-paramter. It is unnecessary to handle them repeatedly.

Comment From: harawata

Thank you @cyflhn !

This PR is difficult to review because you made many unrelated changes like line wrapping or indentation. Please remove these unrelated changes and update this PR.

Comment From: cyflhn

I am sorry for those unuseful changes. I have already updated the PR.

Comment From: cyflhn

As there are some problems with Date typed fields in Oracle or DATETIME typed fields in mysql, the indexes built on those fields may not be utilized fully in current jdbc mechanism. e.g, in Oracle, if the type of a field is Date and there is a index built on it, when we execute a query with conditions on that field through Jdbc, the Jdbc program will execute the method of "setTimestamp" when setting the parameters. The execution plain shows that the execution lead to table full scan or index full scan as oracle made a internal conversion from timestemp type to date type. If we use the function of "to_date", there will be no such a problem. So we want to add more flexibility on parsing parameter mapping.

Comment From: harawata

Thank you for the update, @cyflhn !

So, there are two enhancements in this PR.

  1. Adding cache for in-parameter to avoid handle in-parameter repeatedly.
  2. Add more flexibility on parsing parameter mapping.

Adding cache for in-parameter to avoid handle in-parameter repeatedly

If I understand correctly, this change just reduces the number of parameter value evaluation process from two to one. Assuming you are experiencing some kind of performance issue, could you show me how to reproduce it? I would like to see if there is another option to solve your problem.

Add more flexibility on parsing parameter mapping

Modifying SQL when setting parameter does not seem to be a good idea. You should write the actual SQL using to_date function...

<select id="getUsers" resultMap="userResult"><![CDATA[
  select * from users
  where birthday >= to_date(#{start,jdbcType=VARCHAR}, 'yyyy-mm-dd hh24:mi:ss')
]]></select>

...and register a custom type handler for Date -> VARCHAR mapping.

<typeHandlers>
  <typeHandler
    javaType="java.util.Date"
    jdbcType="VARCHAR"
    handler="xxx.OracleDateHandler"/>
</typeHandlers>

Comment From: cyflhn

@harawata, Thank you for your reply. I am waiting for a long time. For the first issure of "Adding cache for in-parameter to avoid handle in-parameter repeatedly", you can reproduce the performance issure easily. you can write a sql with many in-parameters and execute it concurrently with many threads. you can make a contrast between orignal version and my PR.

For the second issue of "Add more flexibility on parsing parameter mapping", I do think your proposal is a good solution. 1. We should not force programer to write oracle functions in sqlmap. As users of Mybatis, They just want to pass in parameters of Date-typed. They should not pay more attention to the details that how database deal with date-typed paramters. 2. For the custom type handler you mentioned, it is not a general solution. In some situations, user do want to pass in date-typed parameter, as the type of field in datatable is "DATE". If you add such a customer handler, it will hamper other functions.

Comment From: harawata

Hi @cyflhn ,

you can write a sql with many in-parameters and execute it concurrently with many threads.

This process is not that slow in normal use cases. It's hard to say without seeing a repro, but my guess is that there is not enough information for MyBatis to determine type handler during parse phase and it forces MyBatis to perform type handler resolution on each statement execution. If it's difficult to provide a repro, could you show us the definition of parameter objects and the mapper statement that are causing the performance issue?

For the second issue of "Add more flexibility on parsing parameter mapping", I do think your proposal is a good solution.

I assumed that you meant "I do not think".

We should not force programer to write oracle functions in sqlmap. As users of Mybatis, They just want to pass in parameters of Date-typed. They should not pay more attention to the details that how database deal with date-typed paramters.

Your view is totally valid and there may be tools that support it, but MyBatis takes different approch and encourages users to write the actual SQL including vendor specific function for transparency. See how MyBatis supports SQL dialects.

For the custom type handler you mentioned, it is not a general solution. In some situations, user do want to pass in date-typed parameter, as the type of field in datatable is "DATE". If you add such a customer handler, it will hamper other functions.

Type handler is selected based on the combination of javaType and jdbcType, basically. In my example, the type handler is registered to java.util.Date:VARCHAR, so the default DateTypeHandler will be used when jdbcType is DATE.

Comment From: cyflhn

Thank you very much for your explaination @harawata .

If it's difficult to provide a repro, could you show us the definition of parameter objects and the mapper statement that are causing the performance issue?

We do really experienced slight performance issure if parameters were parsed twice. I can take an example in my project. It is an insert statement. The in-parameter of sql statement is a java object which has many properties. Some fields are also java objects and have their own propeties. The insert sql statement has more than 50 parameters as the table has too many fields. I just want to mention that beside performance issue, maybe it is a trivial deficiency in code designing if a function is executed repeatdly.

Your view is totally valid and there may be tools that support it, but MyBatis takes different approch and encourages users to write the actual SQL including vendor specific function for transparency.

I do know mybatis provides many features that support many DB provides. But I still think it is not a friendly method for mybatis users(java programmer) to write DB-specific functions in sqlmap in this situation. For most users of mybatis(java programmer), they do not necessarily know the index issues in oracle DB, so they do not have the idea that using the function of "to_date" instead of passing the date-typed java object directly can resolve the index issure of date-typed field in oracle DB. I think this job should be done by middleware not by users of middleware.

Thank you.

Comment From: harawata

That is weird. It seems that BaseExecutor#createCacheKey() is not invoked on INSERT, so cached objects are never used and there should be no performance improvement. I must be missing something here, but am not sure what.

I just want to mention that beside performance issue, maybe it is a trivial deficiency in code designing if a function is executed repeatdly.

It should not be a problem if it's fast enough. Caching is a good strategy, but it's not free. If each cached object is used only once (or none), it usually is not the best option.

Please create a small example that demonstrates what you are doing. If there is a performance issue, I need to verify the effect of the cache and would like to look for alternative solutions.

I do know mybatis provides many features that support many DB provides. But I still think it is not a friendly method for mybatis users(java programmer) to write DB-specific functions in sqlmap in this situation. For most users of mybatis(java programmer), they do not necessarily know the index issues in oracle DB, so they do not have the idea that using the function of "to_date" instead of passing the date-typed java object directly can resolve the index issure of date-typed field in oracle DB. I think this job should be done by middleware not by users of middleware.

Again, there are tools that hide SQL from a developer as you expect, but MyBatis is "SQL Mapper" and hiding SQL is not in its objective [1]. You just need to choose the right tool for you. :)

[1] In some organizations, DBAs manage SQL and Java developers use mapper interfaces. This model might meet your requirement.

Comment From: harawata

Couldn't reproduce. Closing.