MyBatis select query slow

classic Classic list List threaded Threaded
28 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Re: MyBatis select query slow

尹文才
Hi Iwao, below is the log output after running your program:

26412307879318 : preparing statement
26412362758293 : setting parameters
26412393464955 : executing query
26416625251343 : handling results
T69AD733534226730
T69AD733539886730
T69AD733541626730
T69AD733534136730
T69AD733534316730
T69AD733534016730
T69AD733534086730
T69AD733534046730
T69AD733539476730
T69AD733534276730
T69AD733541726730
T69AD733539516730
T69AD733534466730
T69AD733634566730
T69AD733539616730
T69AD733539486730
T69AD733539806730
T69AD733533896730
T69AD733534106730
T69AD733539466730
T69AD733533916730
T69AD733541766730
T69AD733539536730
T69AD733634526730
T69AD733541786730
T69AD733534396730
T69AD733539416730
T69AD733541676730
T69AD733533996730
T69AD733539936730
T69AD733534006730
T69AD733539626730
T69AD733631286730
T69AD733539456730
T69AD733534216730
T69AD733539506730
T69AD733534186730
T69AD733534436730
T69AD733539796730
T69AD733534386730
T69AD733534096730
T69AD733533966730
T69AD733534376730
T69AD733539946730
T69AD733534146730
T69AD733534476730
T69AD733539606730
T69AD733534166730
T69AD733534416730
T69AD733539726730
T69AD733631246730
T69AD733541636730
T69AD733534236730
T69AD733539766730
T69AD733534296730
T69AD733539666730
T69AD733539676730
T69AD733533946730
T69AD733534486730
T69AD733534036730
T69AD733634776730
T69AD733534456730
T69AD733539526730
T69AD733539916730
T69AD733534256730
T69AD733539576730
T69AD733539746730
T69AD733539706730
T69AD733534336730
T69AD733534176730
T69AD733539786730
T69AD733539596730
T69AD733534126730
T69AD733533976730
T69AD733539566730
T69AD733539846730
T69AD733539966730
T69AD733539756730
T69AD733541806730
T69AD733539426730
T69AD733634506730
T69AD733634496730
T69AD733539696730
T69AD733539656730
T69AD733539866730
T69AD733534366730
T69AD733539826730
T69AD733534446730
T69AD733539816730
T69AD733539646730
T69AD733534066730
T69AD733541646730
T69AD733533926730
T69AD733534076730
T69AD733534266730
T69AD733541746730
T69AD733539446730
T69AD733634426730
T69AD733541756730
T69AD733534056730
T69AD733541736730
T69AD733634576730
T69AD733533956730
T69AD733631296730
T69AD733539436730
T69AD733533986730
T69AD733539776730
T69AD733539556730
T69AD733541776730
26435330436800 : closing connection

Regards,
Ben

On Tuesday, December 19, 2017 at 3:34:19 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

> I also tried your test against sql server it indeed finished within 1 second,

I actually expected that.
The number of rows in the tables should not affect MyBatis performance.
If it does affect performance of the query, it may be caused by the driver and/or the DB.

MyBatis just passes the query string and parameter objects to the driver.
Then SQL Server builds an execution plan to parameterize the query.
The performance of this process may be affected by the number of rows in the tables.

Here is a standalone Java app executing the same query using plain JDBC.
<a href="https://gist.github.com/harawata/e4748263c89550dd92df90ece925042b" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgist.github.com%2Fharawata%2Fe4748263c89550dd92df90ece925042b\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNH65WmrAfmpMKfj0hmcQazgdyGH_g&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgist.github.com%2Fharawata%2Fe4748263c89550dd92df90ece925042b\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNH65WmrAfmpMKfj0hmcQazgdyGH_g&#39;;return true;">https://gist.github.com/harawata/e4748263c89550dd92df90ece925042b
This is essentially what MyBatis does.
Please run it against the database containing many rows and let us know how it performs.

Regards,
Iwao



2017-12-19 10:12 GMT+09:00 尹文才 <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="v7qghw1JBAAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">batm...@...>:
Hi Iwao, thanks for updating the test project accordingly for my case, but I was not using the original project to try to find out the time cost bottleneck.
Instead I created a test project specifically for this case and had only used mybatis alone(exlcuding spring related stuff). One more thing to mention,
I also tried your test against sql server it indeed finished within 1 second, but the problem is your sql query is different, you created a table from the
returned resultset and it's only 109 rows. But my query is against 2 tables, one of them has 1875150 rows and the other one has  4178815 rows.(it takes
almost the same time even if I removed the join part with the second table in the query) I have uploaded my test project onto my google drive, please check
if has any problems. the link is <a href="https://drive.google.com/open?id=1tqvw2rM-29A3LCQlJ-QvTUbR01EqoUVC" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://drive.google.com/open?id\x3d1tqvw2rM-29A3LCQlJ-QvTUbR01EqoUVC&#39;;return true;" onclick="this.href=&#39;https://drive.google.com/open?id\x3d1tqvw2rM-29A3LCQlJ-QvTUbR01EqoUVC&#39;;return true;">https://drive.google.com/open?id=1tqvw2rM-29A3LCQlJ-QvTUbR01EqoUVC
 I think it would be better that I shared my data of both tables, would it be convenient that I also upload my data onto google drive and share it to you?

Regards,
Ben

On Monday, December 18, 2017 at 8:29:56 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

As your 'expanded' query does not perform parameter bindings, you cannot blame foreach just by comparing these results.

I added the foreach element to the demo app and it still runs within a second.
<a href="https://github.com/harawata/mybatis-issues/tree/master/ml-20171215T031703" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Ftree%2Fmaster%2Fml-20171215T031703\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFFJS9o7Gn2y1lTIBk45qps1uW9yQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Ftree%2Fmaster%2Fml-20171215T031703\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFFJS9o7Gn2y1lTIBk45qps1uW9yQ&#39;;return true;">https://github.com/harawata/mybatis-issues/tree/master/ml-20171215T031703
Please run the demo app with your SQL Server and let us know how long it takes.

What I am trying to do is to eliminate the other possible causes like Spring, AOP, plugins, cache, etc..

Regards,
Iwao

2017-12-18 13:40 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, I finally had a clue as to why my query took so long(>22 secs) but I don't know why.
I tried to find out the problem using my original select query as below:

<select id="getOutDetailRecordsByOperationId" resultMap="poolDoOutDetailResult">
select
<include refid="outDetailColumnList"/>
from fact_pool_do_out out inner join fact_material_flow_record flow
on out.operation_id = flow.op_id
<where>
<if test="operationIdList != null">
AND out.operation_id in
<foreach item="op_id" index="index" collection="operationIdList" open="(" separator="," close=")">
#{op_id}
</foreach>
</if>
</where>
</select>

please notice that my table fact_pool_do_out has about 1875150 rows and table fact_material_flow_record has 4178815 rows.
I tried to change my query to find out if any part is the bottleneck for the problem and finally I found when I changed the
foreach part into expanded parameter list, it only took around 780ms, the part to replace foreach is like this:
<where>
out.operation_id in ('002E58E7-FE39-47DF-9CAD-32EDE1D9454C' ,'036FC5C0-5895-4B30-BA7F-F4781A4B7C2B'
,'09BB8B7E-F971-4F13-A770-ED7CE9E01555'
,'0C1885EE-36A1-482C-BBBC-BF343ADC93F8' ,'164CE2AD-8DAE-425C-9F73-AE74DD728B04'
,'18BDFA51-F294-4E77-80AF-A7F6E4462970'
,'1B7DD22D-0A0D-491C-8383-16B7026907BA' ,'23C08BF8-F954-4061-A86B-C225046A72AA'
,'27455220-5C26-4635-84DC-E5D3E9D3E3B3'
,'2AD014DC-45BA-4B1F-AF5C-07A4E1039CD9' ,'2B71AF6C-A505-414F-BA5B-63CD7A0556FB'
,'2C7B7AED-8728-4F27-858A-7CB4A8BB7E39'
,'2DB06DF1-AEFF-43AE-B1B1-6BD599DB08FD' ,'313D32C6-180B-4094-B12A-692E1AEEAABB'
,'31DD5B66-EB1D-439F-841F-EFC4E86CFB23'
,'31F07BD0-76F8-435C-BA97-5DC6CBE9468D' ,'3414019F-2A50-4BE4-AFBD-111303C0FD17'
,'3464259F-2C53-494D-A7B0-2DB8C0D0AD84'
,'34D4B261-BF91-4A71-ABB3-1BF15B8A9FCD' ,'3706AF90-24C9-4F8D-82F1-8054F723BBE0'
,'37C004E4-A342-472F-9884-A32D9203389A'
,'389BDA99-7676-4419-980F-FBEE44F71007' ,'42874649-D07C-4F43-B053-4756CA399389'
,'42E42B55-0D55-46E8-A51B-A49B7A8CBEE3'
,'45543ED6-9BC6-4FE2-B534-C3D592917095' ,'4895EAAF-9467-4F01-8AA1-756C15F45BE4'
,'49043691-6C7A-4317-9231-1994F485C5D4'
,'4A7301C0-A3F1-4465-BF6A-447681328DB3' ,'4B1D36F8-EE34-4EDC-A51A-2B62CAE10772'
,'4EA0C6B7-CB32-48A1-87AB-F6AC74E7BF0B'
,'509E4431-A9F0-449B-97A2-10F45F350082' ,'5387B831-FC43-407A-A41C-E0A0A5A9759A'
,'5609EA14-1489-4780-831B-E83AE1C7DD22'
,'5904C6E2-A329-4CC0-AA44-B6F72BC2641C' ,'5AF4490A-228B-4328-B9FB-CBB48D6A5898'
,'5D084824-822D-4A7D-B0A6-7BEDFA26DB41'
,'61E514F2-20FA-40E2-B03F-696747A4CAB3' ,'63CBFFAB-DF25-4C36-A5BD-E16FFCCA6109'
,'647A3916-C466-47B9-9571-6A583A21480B'
,'66AB13D5-09B4-4535-AEDE-DFC801753021' ,'6CC57E5F-4CE4-41B4-9A23-41B6461E2881'
,'6E282529-E59F-453A-866A-AC25895A14EF'
,'6EEB39E9-F2CE-441F-AC84-6DFBAC5E904B' ,'6F54BE14-1AA0-448B-BFBD-7D6EB2388C7F'
,'71206AAC-EEBF-4B09-8E5C-C4397D48CD74'
,'767987C1-ABFA-4E45-9EF9-684055E80B38' ,'78E7C0F7-1B86-4A74-9682-79019AC288E7'
,'79507CE1-8436-4A6B-9E0E-41CF4A648621'
,'7B0D12C4-9F79-4FB7-B149-BEC40F949B30' ,'7CD5101D-CCA4-4A4F-B134-E005F9F6DB97'
,'7EC2F968-6952-48AC-B628-B179C2D7E20A'
,'803A3F33-B2B0-4D81-B64F-D22DC4DC9EE5' ,'81DD571F-4768-4557-A77D-EC79DE600741'
,'82AD3624-50E4-413D-A048-7F5D73CA701B'
,'834C2A4C-BF2B-42ED-9771-AD527577EA3B' ,'883DDA21-C6E5-4492-99AB-7F36CE216B92'
,'889CA877-2EAC-4948-A341-D1890B1B3EFF'
,'89A891E3-829F-4CCC-B643-47E53F441613' ,'89B779CD-CC08-40EC-B369-9CDB774765B0'
,'8D09C707-3733-4E15-8AE4-8046AEA4E3D2'
,'9152D7B3-E601-40B8-8387-0E1A01746579' ,'92D00E83-ECF3-4794-87DA-4CF4E5E0280D'
,'9389207B-6E63-4B47-8DF7-13A3543258F6'
,'96A19C4F-AF0C-4052-9C42-9AD829BAD171' ,'96D9154A-C3F9-46F4-9240-CA097FE67033'
,'98F4B7DA-369B-4BC7-82F7-0FA45516E536'
,'9C00E14C-2C69-4A57-B220-BB37E0EAD005' ,'9D5B40BA-C268-4E88-8777-39D7DAEADD69'
,'A017DED3-FF52-41C6-BA81-29E42AF6011A'
,'A0B13828-225B-4A65-BB4A-BE80E30F6937' ,'A160885D-CC87-483E-89AD-356E60EE3650'
,'A190EC79-4365-4493-AEB5-E0BBFC3F360E'
,'A5EAF9BE-44E8-49DA-ACD2-271728720F35' ,'A6CAD892-8F00-41F5-B5DD-0D9577A8CA59'
,'A77689B5-A504-4E82-9DA3-0156F950B10C'
,'B1562267-2075-456A-A985-B704499E3732' ,'B252988C-580C-4BD6-87E8-1A7748CC1CE1'
,'B86552B5-0099-4738-A147-4ECCBDC00AA3'
,'B97A738A-F5DC-4708-BDFB-BB8242BFBF7B' ,'BDFF93B9-8ECA-4F1A-B428-AD3C3A243369'
,'BEAE319D-6588-4E10-BCC7-B3D84EB79433'
,'C24F43F4-FF32-473F-BEC6-9EDF56A9778F' ,'C356C9E2-8FD9-46D5-B627-5D14D59340A5'
,'C45ADB6D-01FD-4AD4-8722-463AD2D93CDC'
,'C46E63D5-C622-4471-8738-F6D8A3CBECE4' ,'CB7F829A-473B-4E31-917C-4BB335E15411'
,'CBA44EDB-AFDD-4E62-9A05-49A0D96B87FC'
,'CF48C192-8F91-4238-BE45-046471B48F09' ,'D674F88F-6BA6-46F3-9A9D-0B8BBDC5FD64'
,'D8680DF0-3DBA-47FC-9E77-B40022A74333'
,'D96D562B-9AB3-4664-A316-E34976DBE117' ,'DAF53A81-6D65-4222-B3EE-81D59694C983'
,'DB4E6C7B-04CD-407D-932B-B0C7AC75D768'
,'DB77210F-7A93-44B6-83F2-220AAEFE730E' ,'DB90B0DD-3CCE-48C6-8F9F-9DC7E644F14A'
,'DC505D8B-957B-497A-A55C-09111C654881'
,'DCF2A5A6-0E5A-45A4-9F89-94C8236822E5' ,'DE0D59E8-DEB6-4433-A092-DA6A0DE3AF23'
,'E170E616-C9CF-4F03-85EC-0567F831E75E'
,'E1D7098C-ED21-4CBE-8139-1C53886A2A8B' ,'E40BC73A-2A70-4DC3-98A0-0A04551ADB16'
,'E7F81BF5-178D-4B93-B38F-9BB526E16FD5'
,'E9D95DD9-9E5A-4204-9A94-7F528DA93CE1' ,'ECB86354-F8F1-4C3C-94F4-A354DE1D1F4F'
,'F291F389-E0FC-4A05-86B3-FEAFE0EEB949'
,'F4F17233-3B24-4190-9438-CD88683853F7' ,'F8217553-9C97-48C3-9135-24331DD005D2'
,'F86EC61B-4BC6-48A5-BE69-E89407481625'
,'FFD08A43-FDCC-482C-A479-A033E331D013')
</where>

I also tried another similar approach to verify my concern, which is to use or intead of the in keyword, like this:
<foreach item="op_id" index="index" collection="list" open="(" separator=" or " close=")">
out.operation_id = #{op_id}
</foreach>

this takes almost the same time as with in keyword together with foreach(around 23 secs), but when I expanded all 109
parameters without using the foreach operator(like the example above but with the or operator), it again took less than 1 second.

I tried to search on google the possible performance impact of foreach operator(they call it mybatis dynamic sql) but
unfortunately found no clue about select query(I only found one guy mentioned about this performance problem when doing
bulk insert in stackoverflow: <a href="https://stackoverflow.com/questions/36407980/mybatis-performance-of-bulk-operator" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F36407980%2Fmybatis-performance-of-bulk-operator\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHNDt6MdQy1GnB9S8V5MeQ_V_IReg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F36407980%2Fmybatis-performance-of-bulk-operator\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHNDt6MdQy1GnB9S8V5MeQ_V_IReg&#39;;return true;">https://stackoverflow.com/questions/36407980/mybatis-performance-of-bulk-operator, not sure
if it's the same problem as mine)

Regards,
Ben


On Saturday, December 16, 2017 at 10:57:06 PM UTC+8, Iwao AVE! wrote:
Hi,

If network is the cause, there is nothing MyBatis can do.
Why don't you modify the datasource setting of my demo app and run it against your SQL Server instance?
You may also need to rewrite the insert statement in Create.sql, but it shouldn't be too difficult.
 
Regards,
iwao

2017-12-16 21:59 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, thanks for taking the time to write such a test program to test the performance for me. I took a look into your test project and also has ran it, indeed it is very fast and took less than 1 second.
However, the difference between your test project and mine is you're using hsqldb and using it in memory mode, while my database is sql server 2012 in a OpenStack virtual machine.  According to
my knowledge, the process of mybatis storing the retrieved resultset data into the final object lists is to iterate over the resultset and put the data into the mapped objects. So I'm wondering if the time
difference could be caused by the time of network communication with sql server when reading data from ResultSet? Thanks.

Regards,
Ben

On Saturday, December 16, 2017 at 3:11:52 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

It looks pretty standard, yes.
Based on the information you provided, I created a test project using HSQLDB.

<a href="https://github.com/harawata/mybatis-issues/tree/master/ml-20171215T031703" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Ftree%2Fmaster%2Fml-20171215T031703\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFFJS9o7Gn2y1lTIBk45qps1uW9yQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Ftree%2Fmaster%2Fml-20171215T031703\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFFJS9o7Gn2y1lTIBk45qps1uW9yQ&#39;;return true;">https://github.com/harawata/mybatis-issues/tree/master/ml-20171215T031703

In terms of result mapping process, it does the same thing as your example and the test finishes within a second.
So, there may be another factor in your application that slows down the process.

Regards,
Iwao

2017-12-16 8:24 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, below is my definitiion of PoolDoOutDetailModel:

@Data
public class PoolDoOutDetailModel {
private String barcode;
private Integer equipmentId;
private String equipmentName;
private Integer opType;
private String opTypeName;
private String doCode;
private String batchNo;
private String materialCode;
private String materialName;
private BigDecimal quantity = BigDecimal.ZERO;
private Integer qualityType;
private String qualityTypeName;
private String shiftName;
private String personName;
private Date happenTime;
private String moldCode;
private String failReason;
String operationId;
String destSnapshotId;
}

I'm using lombok's @Data annotation to automatically generate getters and setters for all the fields. As you could see
there seems no complex type(Object type as you mentioned) inside the class denifition. 

Regards,
Ben

On Friday, December 15, 2017 at 11:34:49 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

Could you show us the definition of PoolDoOutDetailModel class?

Mapping can be slow when MyBatis cannot resolve property types using reflection (e.g. the declared property type is Object).
In this case, you can help MyBatis by specifying 'javaType' in <result />.

Regards,
Iwao

2017-12-15 21:52 GMT+09:00 尹文才 <[hidden email]>:
Hi Guy, I debugged my test project and also into mybatis code. I found that the first time the query took about 25 seconds, the first 6-7 seconds are used to get a connection, then the prepared statement execution took 
about 5 seconds(which is close to the time it took when I used prepared statement directly), and finally it took around 12 seconds to store the data from retrieved ResultSet into the returning object list. When I added a
second call with a different parameter, it took about 17 seconds(because the connection is already available in the connection pool). So I think my only concern here is about why would it took so long to put the retrieved
data into the mapped object list, is there anything I could do to reduce this time? Thanks.

/Ben


On Friday, December 15, 2017 at 3:25:55 PM UTC+8, 尹文才 wrote:
not sure why the latter part of my post is truncated, I will attach my detail information in the attached file. Please refer to the txt file for details.
As you could see inside the log, it still took around 24 seconds.

/Ben

On Friday, December 15, 2017 at 3:19:36 PM UTC+8, 尹文才 wrote:
Hi Guy, I went to create a small project just to test how long it would take my query to run with mybatis. I didn't use spring this time and I use google's Guava stopwatch to get the time the query takes.

Below is the method I used:

private static void myBatisPerfTest(){
try {
List<String> params = Arrays.asList("F86EC61B-4BC6-48A5-BE69-E89407481625", "A017DED3-FF52-41C6-BA81-29E42AF6011A", "883DDA21-C6E5-4492-99AB-7F36CE216B92", "DB77210F-7A93-44B6-83F2-220AAEFE730E", "CF48C192-8F91-4238-BE45-046471B48F09", "313D32C6-180B-4094-B12A-692E1AEEAABB", "164CE2AD-8DAE-425C-9F73-AE74DD728B04", "71206AAC-EEBF-4B09-8E5C-C4397D48CD74", "78E7C0F7-1B86-4A74-9682-79019AC288E7", "ECB86354-F8F1-4C3C-94F4-A354DE1D1F4F", "89A891E3-829F-4CCC-B643-47E53F441613", "66AB13D5-09B4-4535-AEDE-DFC801753021", "49043691-6C7A-4317-9231-1994F485C5D4", "E40BC73A-2A70-4DC3-98A0-0A04551ADB16", "6EEB39E9-F2CE-441F-AC84-6DFBAC5E904B", "6F54BE14-1AA0-448B-BFBD-7D6EB2388C7F", "96A19C4F-AF0C-4052-9C42-9AD829BAD171", "31F07BD0-76F8-435C-BA97-5DC6CBE9468D", "2AD014DC-45BA-4B1F-AF5C-07A4E1039CD9", "A6CAD892-8F00-41F5-B5DD-0D9577A8CA59", "5609EA14-1489-4780-831B-E83AE1C7DD22", "2DB06DF1-AEFF-43AE-B1B1-6BD599DB08FD", "C356C9E2-8FD9-46D5-B627-5D14D59340A5", "C24F43F4-FF32-473F-BEC6-9EDF56A9778F", "647A3916-C466-47B9-9571-6A583A21480B", "7CD5101D-CCA4-4A4F-B134-E005F9F6DB97", "34D4B261-BF91-4A71-ABB3-1BF15B8A9FCD", "E7F81BF5-178D-4B93-B38F-9BB526E16FD5", "A190EC79-4365-4493-AEB5-E0BBFC3F360E", "3464259F-2C53-494D-A7B0-2DB8C0D0AD84", "09BB8B7E-F971-4F13-A770-ED7CE9E01555", "C46E63D5-C622-4471-8738-F6D8A3CBECE4", "89B779CD-CC08-40EC-B369-9CDB774765B0", "E1D7098C-ED21-4CBE-8139-1C53886A2A8B", "9152D7B3-E601-40B8-8387-0E1A01746579", "2B71AF6C-A505-414F-BA5B-63CD7A0556FB", "A5EAF9BE-44E8-49DA-ACD2-271728720F35", "31DD5B66-EB1D-439F-841F-EFC4E86CFB23", "002E58E7-FE39-47DF-9CAD-32EDE1D9454C", "98F4B7DA-369B-4BC7-82F7-0FA45516E536", "BEAE319D-6588-4E10-BCC7-B3D84EB79433", "5AF4490A-228B-4328-B9FB-CBB48D6A5898", "96D9154A-C3F9-46F4-9240-CA097FE67033", "3414019F-2A50-4BE4-AFBD-111303C0FD17", "92D00E83-ECF3-4794-87DA-4CF4E5E0280D", "389BDA99-7676-4419-980F-FBEE44F71007", "23C08BF8-F954-4061-A86B-C225046A72AA", "1B7DD22D-0A0D-491C-8383-16B7026907BA", "7EC2F968-6952-48AC-B628-B179C2D7E20A", "F291F389-E0FC-4A05-86B3-FEAFE0EEB949", "D674F88F-6BA6-46F3-9A9D-0B8BBDC5FD64", "5387B831-FC43-407A-A41C-E0A0A5A9759A", "B1562267-2075-456A-A985-B704499E3732", "BDFF93B9-8ECA-4F1A-B428-AD3C3A243369", "D8680DF0-3DBA-47FC-9E77-B40022A74333", "81DD571F-4768-4557-A77D-EC79DE600741", "7B0D12C4-9F79-4FB7-B149-BEC40F949B30", "42874649-D07C-4F43-B053-4756CA399389", "DC505D8B-957B-497A-A55C-09111C654881", "889CA877-2EAC-4948-A341-D1890B1B3EFF", <span style="color:rgb(0,128,0);font-weight

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: MyBatis select query slow

尹文才
Hi Iwao, I tried your program both against sql server 2012 and 2016 and I'm using the sql server jdbc driver version 6.2.2.jre8(using maven).
Do you mean this program works in the same way as the foreach in mybatis? I didn't expect the program to took so long and I still couldn't get what is the problem.
One more thing that puzzles me is when I don't use the foreach operator in mybatis, it always took less than 1 second. 

Regards,
Ben

On Tuesday, December 19, 2017 at 3:51:56 PM UTC+8, 尹文才 wrote:
Hi Iwao, below is the log output after running your program:

26412307879318 : preparing statement
26412362758293 : setting parameters
26412393464955 : executing query
26416625251343 : handling results
T69AD733534226730
T69AD733539886730
T69AD733541626730
T69AD733534136730
T69AD733534316730
T69AD733534016730
T69AD733534086730
T69AD733534046730
T69AD733539476730
T69AD733534276730
T69AD733541726730
T69AD733539516730
T69AD733534466730
T69AD733634566730
T69AD733539616730
T69AD733539486730
T69AD733539806730
T69AD733533896730
T69AD733534106730
T69AD733539466730
T69AD733533916730
T69AD733541766730
T69AD733539536730
T69AD733634526730
T69AD733541786730
T69AD733534396730
T69AD733539416730
T69AD733541676730
T69AD733533996730
T69AD733539936730
T69AD733534006730
T69AD733539626730
T69AD733631286730
T69AD733539456730
T69AD733534216730
T69AD733539506730
T69AD733534186730
T69AD733534436730
T69AD733539796730
T69AD733534386730
T69AD733534096730
T69AD733533966730
T69AD733534376730
T69AD733539946730
T69AD733534146730
T69AD733534476730
T69AD733539606730
T69AD733534166730
T69AD733534416730
T69AD733539726730
T69AD733631246730
T69AD733541636730
T69AD733534236730
T69AD733539766730
T69AD733534296730
T69AD733539666730
T69AD733539676730
T69AD733533946730
T69AD733534486730
T69AD733534036730
T69AD733634776730
T69AD733534456730
T69AD733539526730
T69AD733539916730
T69AD733534256730
T69AD733539576730
T69AD733539746730
T69AD733539706730
T69AD733534336730
T69AD733534176730
T69AD733539786730
T69AD733539596730
T69AD733534126730
T69AD733533976730
T69AD733539566730
T69AD733539846730
T69AD733539966730
T69AD733539756730
T69AD733541806730
T69AD733539426730
T69AD733634506730
T69AD733634496730
T69AD733539696730
T69AD733539656730
T69AD733539866730
T69AD733534366730
T69AD733539826730
T69AD733534446730
T69AD733539816730
T69AD733539646730
T69AD733534066730
T69AD733541646730
T69AD733533926730
T69AD733534076730
T69AD733534266730
T69AD733541746730
T69AD733539446730
T69AD733634426730
T69AD733541756730
T69AD733534056730
T69AD733541736730
T69AD733634576730
T69AD733533956730
T69AD733631296730
T69AD733539436730
T69AD733533986730
T69AD733539776730
T69AD733539556730
T69AD733541776730
26435330436800 : closing connection

Regards,
Ben

On Tuesday, December 19, 2017 at 3:34:19 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

> I also tried your test against sql server it indeed finished within 1 second,

I actually expected that.
The number of rows in the tables should not affect MyBatis performance.
If it does affect performance of the query, it may be caused by the driver and/or the DB.

MyBatis just passes the query string and parameter objects to the driver.
Then SQL Server builds an execution plan to parameterize the query.
The performance of this process may be affected by the number of rows in the tables.

Here is a standalone Java app executing the same query using plain JDBC.
<a href="https://gist.github.com/harawata/e4748263c89550dd92df90ece925042b" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgist.github.com%2Fharawata%2Fe4748263c89550dd92df90ece925042b\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNH65WmrAfmpMKfj0hmcQazgdyGH_g&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgist.github.com%2Fharawata%2Fe4748263c89550dd92df90ece925042b\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNH65WmrAfmpMKfj0hmcQazgdyGH_g&#39;;return true;">https://gist.github.com/harawata/e4748263c89550dd92df90ece925042b
This is essentially what MyBatis does.
Please run it against the database containing many rows and let us know how it performs.

Regards,
Iwao



2017-12-19 10:12 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, thanks for updating the test project accordingly for my case, but I was not using the original project to try to find out the time cost bottleneck.
Instead I created a test project specifically for this case and had only used mybatis alone(exlcuding spring related stuff). One more thing to mention,
I also tried your test against sql server it indeed finished within 1 second, but the problem is your sql query is different, you created a table from the
returned resultset and it's only 109 rows. But my query is against 2 tables, one of them has 1875150 rows and the other one has  4178815 rows.(it takes
almost the same time even if I removed the join part with the second table in the query) I have uploaded my test project onto my google drive, please check
if has any problems. the link is <a href="https://drive.google.com/open?id=1tqvw2rM-29A3LCQlJ-QvTUbR01EqoUVC" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://drive.google.com/open?id\x3d1tqvw2rM-29A3LCQlJ-QvTUbR01EqoUVC&#39;;return true;" onclick="this.href=&#39;https://drive.google.com/open?id\x3d1tqvw2rM-29A3LCQlJ-QvTUbR01EqoUVC&#39;;return true;">https://drive.google.com/open?id=1tqvw2rM-29A3LCQlJ-QvTUbR01EqoUVC
 I think it would be better that I shared my data of both tables, would it be convenient that I also upload my data onto google drive and share it to you?

Regards,
Ben

On Monday, December 18, 2017 at 8:29:56 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

As your 'expanded' query does not perform parameter bindings, you cannot blame foreach just by comparing these results.

I added the foreach element to the demo app and it still runs within a second.
<a href="https://github.com/harawata/mybatis-issues/tree/master/ml-20171215T031703" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Ftree%2Fmaster%2Fml-20171215T031703\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFFJS9o7Gn2y1lTIBk45qps1uW9yQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Ftree%2Fmaster%2Fml-20171215T031703\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFFJS9o7Gn2y1lTIBk45qps1uW9yQ&#39;;return true;">https://github.com/harawata/mybatis-issues/tree/master/ml-20171215T031703
Please run the demo app with your SQL Server and let us know how long it takes.

What I am trying to do is to eliminate the other possible causes like Spring, AOP, plugins, cache, etc..

Regards,
Iwao

2017-12-18 13:40 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, I finally had a clue as to why my query took so long(>22 secs) but I don't know why.
I tried to find out the problem using my original select query as below:

<select id="getOutDetailRecordsByOperationId" resultMap="poolDoOutDetailResult">
select
<include refid="outDetailColumnList"/>
from fact_pool_do_out out inner join fact_material_flow_record flow
on out.operation_id = flow.op_id
<where>
<if test="operationIdList != null">
AND out.operation_id in
<foreach item="op_id" index="index" collection="operationIdList" open="(" separator="," close=")">
#{op_id}
</foreach>
</if>
</where>
</select>

please notice that my table fact_pool_do_out has about 1875150 rows and table fact_material_flow_record has 4178815 rows.
I tried to change my query to find out if any part is the bottleneck for the problem and finally I found when I changed the
foreach part into expanded parameter list, it only took around 780ms, the part to replace foreach is like this:
<where>
out.operation_id in ('002E58E7-FE39-47DF-9CAD-32EDE1D9454C' ,'036FC5C0-5895-4B30-BA7F-F4781A4B7C2B'
,'09BB8B7E-F971-4F13-A770-ED7CE9E01555'
,'0C1885EE-36A1-482C-BBBC-BF343ADC93F8' ,'164CE2AD-8DAE-425C-9F73-AE74DD728B04'
,'18BDFA51-F294-4E77-80AF-A7F6E4462970'
,'1B7DD22D-0A0D-491C-8383-16B7026907BA' ,'23C08BF8-F954-4061-A86B-C225046A72AA'
,'27455220-5C26-4635-84DC-E5D3E9D3E3B3'
,'2AD014DC-45BA-4B1F-AF5C-07A4E1039CD9' ,'2B71AF6C-A505-414F-BA5B-63CD7A0556FB'
,'2C7B7AED-8728-4F27-858A-7CB4A8BB7E39'
,'2DB06DF1-AEFF-43AE-B1B1-6BD599DB08FD' ,'313D32C6-180B-4094-B12A-692E1AEEAABB'
,'31DD5B66-EB1D-439F-841F-EFC4E86CFB23'
,'31F07BD0-76F8-435C-BA97-5DC6CBE9468D' ,'3414019F-2A50-4BE4-AFBD-111303C0FD17'
,'3464259F-2C53-494D-A7B0-2DB8C0D0AD84'
,'34D4B261-BF91-4A71-ABB3-1BF15B8A9FCD' ,'3706AF90-24C9-4F8D-82F1-8054F723BBE0'
,'37C004E4-A342-472F-9884-A32D9203389A'
,'389BDA99-7676-4419-980F-FBEE44F71007' ,'42874649-D07C-4F43-B053-4756CA399389'
,'42E42B55-0D55-46E8-A51B-A49B7A8CBEE3'
,'45543ED6-9BC6-4FE2-B534-C3D592917095' ,'4895EAAF-9467-4F01-8AA1-756C15F45BE4'
,'49043691-6C7A-4317-9231-1994F485C5D4'
,'4A7301C0-A3F1-4465-BF6A-447681328DB3' ,'4B1D36F8-EE34-4EDC-A51A-2B62CAE10772'
,'4EA0C6B7-CB32-48A1-87AB-F6AC74E7BF0B'
,'509E4431-A9F0-449B-97A2-10F45F350082' ,'5387B831-FC43-407A-A41C-E0A0A5A9759A'
,'5609EA14-1489-4780-831B-E83AE1C7DD22'
,'5904C6E2-A329-4CC0-AA44-B6F72BC2641C' ,'5AF4490A-228B-4328-B9FB-CBB48D6A5898'
,'5D084824-822D-4A7D-B0A6-7BEDFA26DB41'
,'61E514F2-20FA-40E2-B03F-696747A4CAB3' ,'63CBFFAB-DF25-4C36-A5BD-E16FFCCA6109'
,'647A3916-C466-47B9-9571-6A583A21480B'
,'66AB13D5-09B4-4535-AEDE-DFC801753021' ,'6CC57E5F-4CE4-41B4-9A23-41B6461E2881'
,'6E282529-E59F-453A-866A-AC25895A14EF'
,'6EEB39E9-F2CE-441F-AC84-6DFBAC5E904B' ,'6F54BE14-1AA0-448B-BFBD-7D6EB2388C7F'
,'71206AAC-EEBF-4B09-8E5C-C4397D48CD74'
,'767987C1-ABFA-4E45-9EF9-684055E80B38' ,'78E7C0F7-1B86-4A74-9682-79019AC288E7'
,'79507CE1-8436-4A6B-9E0E-41CF4A648621'
,'7B0D12C4-9F79-4FB7-B149-BEC40F949B30' ,'7CD5101D-CCA4-4A4F-B134-E005F9F6DB97'
,'7EC2F968-6952-48AC-B628-B179C2D7E20A'
,'803A3F33-B2B0-4D81-B64F-D22DC4DC9EE5' ,'81DD571F-4768-4557-A77D-EC79DE600741'
,'82AD3624-50E4-413D-A048-7F5D73CA701B'
,'834C2A4C-BF2B-42ED-9771-AD527577EA3B' ,'883DDA21-C6E5-4492-99AB-7F36CE216B92'
,'889CA877-2EAC-4948-A341-D1890B1B3EFF'
,'89A891E3-829F-4CCC-B643-47E53F441613' ,'89B779CD-CC08-40EC-B369-9CDB774765B0'
,'8D09C707-3733-4E15-8AE4-8046AEA4E3D2'
,'9152D7B3-E601-40B8-8387-0E1A01746579' ,'92D00E83-ECF3-4794-87DA-4CF4E5E0280D'
,'9389207B-6E63-4B47-8DF7-13A3543258F6'
,'96A19C4F-AF0C-4052-9C42-9AD829BAD171' ,'96D9154A-C3F9-46F4-9240-CA097FE67033'
,'98F4B7DA-369B-4BC7-82F7-0FA45516E536'
,'9C00E14C-2C69-4A57-B220-BB37E0EAD005' ,'9D5B40BA-C268-4E88-8777-39D7DAEADD69'
,'A017DED3-FF52-41C6-BA81-29E42AF6011A'
,'A0B13828-225B-4A65-BB4A-BE80E30F6937' ,'A160885D-CC87-483E-89AD-356E60EE3650'
,'A190EC79-4365-4493-AEB5-E0BBFC3F360E'
,'A5EAF9BE-44E8-49DA-ACD2-271728720F35' ,'A6CAD892-8F00-41F5-B5DD-0D9577A8CA59'
,'A77689B5-A504-4E82-9DA3-0156F950B10C'
,'B1562267-2075-456A-A985-B704499E3732' ,'B252988C-580C-4BD6-87E8-1A7748CC1CE1'
,'B86552B5-0099-4738-A147-4ECCBDC00AA3'
,'B97A738A-F5DC-4708-BDFB-BB8242BFBF7B' ,'BDFF93B9-8ECA-4F1A-B428-AD3C3A243369'
,'BEAE319D-6588-4E10-BCC7-B3D84EB79433'
,'C24F43F4-FF32-473F-BEC6-9EDF56A9778F' ,'C356C9E2-8FD9-46D5-B627-5D14D59340A5'
,'C45ADB6D-01FD-4AD4-8722-463AD2D93CDC'
,'C46E63D5-C622-4471-8738-F6D8A3CBECE4' ,'CB7F829A-473B-4E31-917C-4BB335E15411'
,'CBA44EDB-AFDD-4E62-9A05-49A0D96B87FC'
,'CF48C192-8F91-4238-BE45-046471B48F09' ,'D674F88F-6BA6-46F3-9A9D-0B8BBDC5FD64'
,'D8680DF0-3DBA-47FC-9E77-B40022A74333'
,'D96D562B-9AB3-4664-A316-E34976DBE117' ,'DAF53A81-6D65-4222-B3EE-81D59694C983'
,'DB4E6C7B-04CD-407D-932B-B0C7AC75D768'
,'DB77210F-7A93-44B6-83F2-220AAEFE730E' ,'DB90B0DD-3CCE-48C6-8F9F-9DC7E644F14A'
,'DC505D8B-957B-497A-A55C-09111C654881'
,'DCF2A5A6-0E5A-45A4-9F89-94C8236822E5' ,'DE0D59E8-DEB6-4433-A092-DA6A0DE3AF23'
,'E170E616-C9CF-4F03-85EC-0567F831E75E'
,'E1D7098C-ED21-4CBE-8139-1C53886A2A8B' ,'E40BC73A-2A70-4DC3-98A0-0A04551ADB16'
,'E7F81BF5-178D-4B93-B38F-9BB526E16FD5'
,'E9D95DD9-9E5A-4204-9A94-7F528DA93CE1' ,'ECB86354-F8F1-4C3C-94F4-A354DE1D1F4F'
,'F291F389-E0FC-4A05-86B3-FEAFE0EEB949'
,'F4F17233-3B24-4190-9438-CD88683853F7' ,'F8217553-9C97-48C3-9135-24331DD005D2'
,'F86EC61B-4BC6-48A5-BE69-E89407481625'
,'FFD08A43-FDCC-482C-A479-A033E331D013')
</where>

I also tried another similar approach to verify my concern, which is to use or intead of the in keyword, like this:
<foreach item="op_id" index="index" collection="list" open="(" separator=" or " close=")">
out.operation_id = #{op_id}
</foreach>

this takes almost the same time as with in keyword together with foreach(around 23 secs), but when I expanded all 109
parameters without using the foreach operator(like the example above but with the or operator), it again took less than 1 second.

I tried to search on google the possible performance impact of foreach operator(they call it mybatis dynamic sql) but
unfortunately found no clue about select query(I only found one guy mentioned about this performance problem when doing
bulk insert in stackoverflow: <a href="https://stackoverflow.com/questions/36407980/mybatis-performance-of-bulk-operator" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F36407980%2Fmybatis-performance-of-bulk-operator\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHNDt6MdQy1GnB9S8V5MeQ_V_IReg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F36407980%2Fmybatis-performance-of-bulk-operator\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHNDt6MdQy1GnB9S8V5MeQ_V_IReg&#39;;return true;">https://stackoverflow.com/questions/36407980/mybatis-performance-of-bulk-operator, not sure
if it's the same problem as mine)

Regards,
Ben


On Saturday, December 16, 2017 at 10:57:06 PM UTC+8, Iwao AVE! wrote:
Hi,

If network is the cause, there is nothing MyBatis can do.
Why don't you modify the datasource setting of my demo app and run it against your SQL Server instance?
You may also need to rewrite the insert statement in Create.sql, but it shouldn't be too difficult.
 
Regards,
iwao

2017-12-16 21:59 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, thanks for taking the time to write such a test program to test the performance for me. I took a look into your test project and also has ran it, indeed it is very fast and took less than 1 second.
However, the difference between your test project and mine is you're using hsqldb and using it in memory mode, while my database is sql server 2012 in a OpenStack virtual machine.  According to
my knowledge, the process of mybatis storing the retrieved resultset data into the final object lists is to iterate over the resultset and put the data into the mapped objects. So I'm wondering if the time
difference could be caused by the time of network communication with sql server when reading data from ResultSet? Thanks.

Regards,
Ben

On Saturday, December 16, 2017 at 3:11:52 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

It looks pretty standard, yes.
Based on the information you provided, I created a test project using HSQLDB.

<a href="https://github.com/harawata/mybatis-issues/tree/master/ml-20171215T031703" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Ftree%2Fmaster%2Fml-20171215T031703\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFFJS9o7Gn2y1lTIBk45qps1uW9yQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Ftree%2Fmaster%2Fml-20171215T031703\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFFJS9o7Gn2y1lTIBk45qps1uW9yQ&#39;;return true;">https://github.com/harawata/mybatis-issues/tree/master/ml-20171215T031703

In terms of result mapping process, it does the same thing as your example and the test finishes within a second.
So, there may be another factor in your application that slows down the process.

Regards,
Iwao

2017-12-16 8:24 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, below is my definitiion of PoolDoOutDetailModel:

@Data
public class PoolDoOutDetailModel {
private String barcode;
private Integer equipmentId;
private String equipmentName;
private Integer opType;
private String opTypeName;
private String doCode;
private String batchNo;
private String materialCode;
private String materialName;
private BigDecimal quantity = BigDecimal.ZERO;
private Integer qualityType;
private String qualityTypeName;
private String shiftName;
private String personName;
private Date happenTime;
private String moldCode;
private String failReason;
String operationId;
String destSnapshotId;
}

I'm using lombok's @Data annotation to automatically generate getters and setters for all the fields. As you could see
there seems no complex type(Object type as you mentioned) inside the class denifition. 

Regards,
Ben

On Friday, December 15, 2017 at 11:34:49 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

Could you show us the definition of PoolDoOutDetailModel class?

Mapping can be slow when MyBatis cannot resolve property types using reflection (e.g. the declared property type is Object).
In this case, you can help MyBatis by specifying 'javaType' in <result />.

Regards,
Iwao

2017-12-15 21:52 GMT+09:00 尹文才 <[hidden email]>:
Hi Guy, I debugged my test project and also into mybatis code. I found that the first time the query took about 25 seconds, the first 6-7 seconds are used to get a connection, then the prepared statement execution took 
about 5 seconds(which is close to the time it took when I used prepared statement directly), and finally it took around 12 seconds to store the data from retrieved ResultSet into the returning object list. When I added a
second call with a different parameter, it took about 17 seconds(because the connection is already available in the connection pool). So I think my only concern here is about why would it took so long to put the retrieved
data into the mapped object list, is there anything I could do to reduce this time? Thanks.

/Ben


On Friday, December 15, 2017 at 3:25:55 PM UTC+8, 尹文才 wrote:
not sure why the latter part of my post is truncated, I will attach my detail information in the attached file. Please refer to the txt file for details.
As you could see inside the log, it still took around 24 seconds.

/Ben

On Friday, December 15, 2017 at 3:19:36 PM UTC+8, 尹文才 wrote:
Hi Guy, I went to create a small project just to test how long it would take my query to run with mybatis. I didn't use spring this time and I use google's Guava stopwatch to get the time the query takes.

Below is the method I used:

private static void myBatisPerfTest(){
try {
List<String> params = Arrays.asList("F86EC61B-4BC6-48A5-BE69-E89407481625", "A017DED3-FF52-41C6-BA81-29E42AF6011A", "883DDA21-C6E5-4492-99AB-7F36CE216B92", "DB77210F-7A93-44B6-83F2-220AAEFE730E", "CF48C192-8F91-4238-BE45-046471B48F09", "313D32C6-180B-4094-B12A-692E1AEEAABB", "164CE2AD-8DAE-425C-9F73-AE74DD728B04", "71206AAC-EEBF-4B09-8E5C-C4397D48CD74", <span style="color:rgb(0,128,

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: MyBatis select query slow

Iwao AVE!
Here is the result in human readable form:

0.054878975 secs to prepare
0.030706662 secs to set parameters
4.231786388 secs to query
18.70518546 secs to retrieve results

Total: 23.02255748 secs

It's not exactly what I expected, but the total time is the same as your first post.
So, it proves MyBatis is not the cause.

> Do you mean this program works in the same way as the foreach in mybatis?

Yes.

> when I don't use the foreach operator in mybatis, it always took less than 1 second.

You mean the 'expanded' version, right?
It uses literal parameters and that makes the difference, it seems (try rewriting my JDBC app to use the 'expanded' query to verify).

Even without foreach, it will get slow if you write the MyBatis query as follows.

... IN (#{operationIdList[0]}, #{operationIdList[1]}, ...

> I didn't expect the program to took so long and I still couldn't get what is the problem.

If we describe what we observe : Retrieving results takes longer when using parameterized query.
It is weird indeed.

If it's reproducible with a fresh database, it may be worth reporting.

Regards,
Iwao

2017-12-19 17:48 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, I tried your program both against sql server 2012 and 2016 and I'm using the sql server jdbc driver version 6.2.2.jre8(using maven).
Do you mean this program works in the same way as the foreach in mybatis? I didn't expect the program to took so long and I still couldn't get what is the problem.
One more thing that puzzles me is when I don't use the foreach operator in mybatis, it always took less than 1 second. 

Regards,
Ben


On Tuesday, December 19, 2017 at 3:51:56 PM UTC+8, 尹文才 wrote:
Hi Iwao, below is the log output after running your program:

26412307879318 : preparing statement
26412362758293 : setting parameters
26412393464955 : executing query
26416625251343 : handling results
T69AD733534226730
T69AD733539886730
T69AD733541626730
T69AD733534136730
T69AD733534316730
T69AD733534016730
T69AD733534086730
T69AD733534046730
T69AD733539476730
T69AD733534276730
T69AD733541726730
T69AD733539516730
T69AD733534466730
T69AD733634566730
T69AD733539616730
T69AD733539486730
T69AD733539806730
T69AD733533896730
T69AD733534106730
T69AD733539466730
T69AD733533916730
T69AD733541766730
T69AD733539536730
T69AD733634526730
T69AD733541786730
T69AD733534396730
T69AD733539416730
T69AD733541676730
T69AD733533996730
T69AD733539936730
T69AD733534006730
T69AD733539626730
T69AD733631286730
T69AD733539456730
T69AD733534216730
T69AD733539506730
T69AD733534186730
T69AD733534436730
T69AD733539796730
T69AD733534386730
T69AD733534096730
T69AD733533966730
T69AD733534376730
T69AD733539946730
T69AD733534146730
T69AD733534476730
T69AD733539606730
T69AD733534166730
T69AD733534416730
T69AD733539726730
T69AD733631246730
T69AD733541636730
T69AD733534236730
T69AD733539766730
T69AD733534296730
T69AD733539666730
T69AD733539676730
T69AD733533946730
T69AD733534486730
T69AD733534036730
T69AD733634776730
T69AD733534456730
T69AD733539526730
T69AD733539916730
T69AD733534256730
T69AD733539576730
T69AD733539746730
T69AD733539706730
T69AD733534336730
T69AD733534176730
T69AD733539786730
T69AD733539596730
T69AD733534126730
T69AD733533976730
T69AD733539566730
T69AD733539846730
T69AD733539966730
T69AD733539756730
T69AD733541806730
T69AD733539426730
T69AD733634506730
T69AD733634496730
T69AD733539696730
T69AD733539656730
T69AD733539866730
T69AD733534366730
T69AD733539826730
T69AD733534446730
T69AD733539816730
T69AD733539646730
T69AD733534066730
T69AD733541646730
T69AD733533926730
T69AD733534076730
T69AD733534266730
T69AD733541746730
T69AD733539446730
T69AD733634426730
T69AD733541756730
T69AD733534056730
T69AD733541736730
T69AD733634576730
T69AD733533956730
T69AD733631296730
T69AD733539436730
T69AD733533986730
T69AD733539776730
T69AD733539556730
T69AD733541776730
26435330436800 : closing connection

Regards,
Ben

On Tuesday, December 19, 2017 at 3:34:19 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

> I also tried your test against sql server it indeed finished within 1 second,

I actually expected that.
The number of rows in the tables should not affect MyBatis performance.
If it does affect performance of the query, it may be caused by the driver and/or the DB.

MyBatis just passes the query string and parameter objects to the driver.
Then SQL Server builds an execution plan to parameterize the query.
The performance of this process may be affected by the number of rows in the tables.

Here is a standalone Java app executing the same query using plain JDBC.
This is essentially what MyBatis does.
Please run it against the database containing many rows and let us know how it performs.

Regards,
Iwao



2017-12-19 10:12 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, thanks for updating the test project accordingly for my case, but I was not using the original project to try to find out the time cost bottleneck.
Instead I created a test project specifically for this case and had only used mybatis alone(exlcuding spring related stuff). One more thing to mention,
I also tried your test against sql server it indeed finished within 1 second, but the problem is your sql query is different, you created a table from the
returned resultset and it's only 109 rows. But my query is against 2 tables, one of them has 1875150 rows and the other one has  4178815 rows.(it takes
almost the same time even if I removed the join part with the second table in the query) I have uploaded my test project onto my google drive, please check
 I think it would be better that I shared my data of both tables, would it be convenient that I also upload my data onto google drive and share it to you?

Regards,
Ben

On Monday, December 18, 2017 at 8:29:56 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

As your 'expanded' query does not perform parameter bindings, you cannot blame foreach just by comparing these results.

I added the foreach element to the demo app and it still runs within a second.
Please run the demo app with your SQL Server and let us know how long it takes.

What I am trying to do is to eliminate the other possible causes like Spring, AOP, plugins, cache, etc..

Regards,
Iwao

2017-12-18 13:40 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, I finally had a clue as to why my query took so long(>22 secs) but I don't know why.
I tried to find out the problem using my original select query as below:

<select id="getOutDetailRecordsByOperationId" resultMap="poolDoOutDetailResult">
select
<include refid="outDetailColumnList"/>
from fact_pool_do_out out inner join fact_material_flow_record flow
on out.operation_id = flow.op_id
<where>
<if test="operationIdList != null">
AND out.operation_id in
<foreach item="op_id" index="index" collection="operationIdList" open="(" separator="," close=")">
#{op_id}
</foreach>
</if>
</where>
</select>

please notice that my table fact_pool_do_out has about 1875150 rows and table fact_material_flow_record has 4178815 rows.
I tried to change my query to find out if any part is the bottleneck for the problem and finally I found when I changed the
foreach part into expanded parameter list, it only took around 780ms, the part to replace foreach is like this:
<where>
out.operation_id in ('002E58E7-FE39-47DF-9CAD-32EDE1D9454C' ,'036FC5C0-5895-4B30-BA7F-F4781A4B7C2B'
,'09BB8B7E-F971-4F13-A770-ED7CE9E01555'
,'0C1885EE-36A1-482C-BBBC-BF343ADC93F8' ,'164CE2AD-8DAE-425C-9F73-AE74DD728B04'
,'18BDFA51-F294-4E77-80AF-A7F6E4462970'
,'1B7DD22D-0A0D-491C-8383-16B7026907BA' ,'23C08BF8-F954-4061-A86B-C225046A72AA'
,'27455220-5C26-4635-84DC-E5D3E9D3E3B3'
,'2AD014DC-45BA-4B1F-AF5C-07A4E1039CD9' ,'2B71AF6C-A505-414F-BA5B-63CD7A0556FB'
,'2C7B7AED-8728-4F27-858A-7CB4A8BB7E39'
,'2DB06DF1-AEFF-43AE-B1B1-6BD599DB08FD' ,'313D32C6-180B-4094-B12A-692E1AEEAABB'
,'31DD5B66-EB1D-439F-841F-EFC4E86CFB23'
,'31F07BD0-76F8-435C-BA97-5DC6CBE9468D' ,'3414019F-2A50-4BE4-AFBD-111303C0FD17'
,'3464259F-2C53-494D-A7B0-2DB8C0D0AD84'
,'34D4B261-BF91-4A71-ABB3-1BF15B8A9FCD' ,'3706AF90-24C9-4F8D-82F1-8054F723BBE0'
,'37C004E4-A342-472F-9884-A32D9203389A'
,'389BDA99-7676-4419-980F-FBEE44F71007' ,'42874649-D07C-4F43-B053-4756CA399389'
,'42E42B55-0D55-46E8-A51B-A49B7A8CBEE3'
,'45543ED6-9BC6-4FE2-B534-C3D592917095' ,'4895EAAF-9467-4F01-8AA1-756C15F45BE4'
,'49043691-6C7A-4317-9231-1994F485C5D4'
,'4A7301C0-A3F1-4465-BF6A-447681328DB3' ,'4B1D36F8-EE34-4EDC-A51A-2B62CAE10772'
,'4EA0C6B7-CB32-48A1-87AB-F6AC74E7BF0B'
,'509E4431-A9F0-449B-97A2-10F45F350082' ,'5387B831-FC43-407A-A41C-E0A0A5A9759A'
,'5609EA14-1489-4780-831B-E83AE1C7DD22'
,'5904C6E2-A329-4CC0-AA44-B6F72BC2641C' ,'5AF4490A-228B-4328-B9FB-CBB48D6A5898'
,'5D084824-822D-4A7D-B0A6-7BEDFA26DB41'
,'61E514F2-20FA-40E2-B03F-696747A4CAB3' ,'63CBFFAB-DF25-4C36-A5BD-E16FFCCA6109'
,'647A3916-C466-47B9-9571-6A583A21480B'
,'66AB13D5-09B4-4535-AEDE-DFC801753021' ,'6CC57E5F-4CE4-41B4-9A23-41B6461E2881'
,'6E282529-E59F-453A-866A-AC25895A14EF'
,'6EEB39E9-F2CE-441F-AC84-6DFBAC5E904B' ,'6F54BE14-1AA0-448B-BFBD-7D6EB2388C7F'
,'71206AAC-EEBF-4B09-8E5C-C4397D48CD74'
,'767987C1-ABFA-4E45-9EF9-684055E80B38' ,'78E7C0F7-1B86-4A74-9682-79019AC288E7'
,'79507CE1-8436-4A6B-9E0E-41CF4A648621'
,'7B0D12C4-9F79-4FB7-B149-BEC40F949B30' ,'7CD5101D-CCA4-4A4F-B134-E005F9F6DB97'
,'7EC2F968-6952-48AC-B628-B179C2D7E20A'
,'803A3F33-B2B0-4D81-B64F-D22DC4DC9EE5' ,'81DD571F-4768-4557-A77D-EC79DE600741'
,'82AD3624-50E4-413D-A048-7F5D73CA701B'
,'834C2A4C-BF2B-42ED-9771-AD527577EA3B' ,'883DDA21-C6E5-4492-99AB-7F36CE216B92'
,'889CA877-2EAC-4948-A341-D1890B1B3EFF'
,'89A891E3-829F-4CCC-B643-47E53F441613' ,'89B779CD-CC08-40EC-B369-9CDB774765B0'
,'8D09C707-3733-4E15-8AE4-8046AEA4E3D2'
,'9152D7B3-E601-40B8-8387-0E1A01746579' ,'92D00E83-ECF3-4794-87DA-4CF4E5E0280D'
,'9389207B-6E63-4B47-8DF7-13A3543258F6'
,'96A19C4F-AF0C-4052-9C42-9AD829BAD171' ,'96D9154A-C3F9-46F4-9240-CA097FE67033'
,'98F4B7DA-369B-4BC7-82F7-0FA45516E536'
,'9C00E14C-2C69-4A57-B220-BB37E0EAD005' ,'9D5B40BA-C268-4E88-8777-39D7DAEADD69'
,'A017DED3-FF52-41C6-BA81-29E42AF6011A'
,'A0B13828-225B-4A65-BB4A-BE80E30F6937' ,'A160885D-CC87-483E-89AD-356E60EE3650'
,'A190EC79-4365-4493-AEB5-E0BBFC3F360E'
,'A5EAF9BE-44E8-49DA-ACD2-271728720F35' ,'A6CAD892-8F00-41F5-B5DD-0D9577A8CA59'
,'A77689B5-A504-4E82-9DA3-0156F950B10C'
,'B1562267-2075-456A-A985-B704499E3732' ,'B252988C-580C-4BD6-87E8-1A7748CC1CE1'
,'B86552B5-0099-4738-A147-4ECCBDC00AA3'
,'B97A738A-F5DC-4708-BDFB-BB8242BFBF7B' ,'BDFF93B9-8ECA-4F1A-B428-AD3C3A243369'
,'BEAE319D-6588-4E10-BCC7-B3D84EB79433'
,'C24F43F4-FF32-473F-BEC6-9EDF56A9778F' ,'C356C9E2-8FD9-46D5-B627-5D14D59340A5'
,'C45ADB6D-01FD-4AD4-8722-463AD2D93CDC'
,'C46E63D5-C622-4471-8738-F6D8A3CBECE4' ,'CB7F829A-473B-4E31-917C-4BB335E15411'
,'CBA44EDB-AFDD-4E62-9A05-49A0D96B87FC'
,'CF48C192-8F91-4238-BE45-046471B48F09' ,'D674F88F-6BA6-46F3-9A9D-0B8BBDC5FD64'
,'D8680DF0-3DBA-47FC-9E77-B40022A74333'
,'D96D562B-9AB3-4664-A316-E34976DBE117' ,'DAF53A81-6D65-4222-B3EE-81D59694C983'
,'DB4E6C7B-04CD-407D-932B-B0C7AC75D768'
,'DB77210F-7A93-44B6-83F2-220AAEFE730E' ,'DB90B0DD-3CCE-48C6-8F9F-9DC7E644F14A'
,'DC505D8B-957B-497A-A55C-09111C654881'
,'DCF2A5A6-0E5A-45A4-9F89-94C8236822E5' ,'DE0D59E8-DEB6-4433-A092-DA6A0DE3AF23'
,'E170E616-C9CF-4F03-85EC-0567F831E75E'
,'E1D7098C-ED21-4CBE-8139-1C53886A2A8B' ,'E40BC73A-2A70-4DC3-98A0-0A04551ADB16'
,'E7F81BF5-178D-4B93-B38F-9BB526E16FD5'
,'E9D95DD9-9E5A-4204-9A94-7F528DA93CE1' ,'ECB86354-F8F1-4C3C-94F4-A354DE1D1F4F'
,'F291F389-E0FC-4A05-86B3-FEAFE0EEB949'
,'F4F17233-3B24-4190-9438-CD88683853F7' ,'F8217553-9C97-48C3-9135-24331DD005D2'
,'F86EC61B-4BC6-48A5-BE69-E89407481625'
,'FFD08A43-FDCC-482C-A479-A033E331D013')
</where>

I also tried another similar approach to verify my concern, which is to use or intead of the in keyword, like this:
<foreach item="op_id" index="index" collection="list" open="(" separator=" or " close=")">
out.operation_id = #{op_id}
</foreach>

this takes almost the same time as with in keyword together with foreach(around 23 secs), but when I expanded all 109
parameters without using the foreach operator(like the example above but with the or operator), it again took less than 1 second.

I tried to search on google the possible performance impact of foreach operator(they call it mybatis dynamic sql) but
unfortunately found no clue about select query(I only found one guy mentioned about this performance problem when doing
bulk insert in stackoverflow: https://stackoverflow.com/questions/36407980/mybatis-performance-of-bulk-operator, not sure
if it's the same problem as mine)

Regards,
Ben


On Saturday, December 16, 2017 at 10:57:06 PM UTC+8, Iwao AVE! wrote:
Hi,

If network is the cause, there is nothing MyBatis can do.
Why don't you modify the datasource setting of my demo app and run it against your SQL Server instance?
You may also need to rewrite the insert statement in Create.sql, but it shouldn't be too difficult.
 
Regards,
iwao

2017-12-16 21:59 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, thanks for taking the time to write such a test program to test the performance for me. I took a look into your test project and also has ran it, indeed it is very fast and took less than 1 second.
However, the difference between your test project and mine is you're using hsqldb and using it in memory mode, while my database is sql server 2012 in a OpenStack virtual machine.  According to
my knowledge, the process of mybatis storing the retrieved resultset data into the final object lists is to iterate over the resultset and put the data into the mapped objects. So I'm wondering if the time
difference could be caused by the time of network communication with sql server when reading data from ResultSet? Thanks.

Regards,
Ben

On Saturday, December 16, 2017 at 3:11:52 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

It looks pretty standard, yes.
Based on the information you provided, I created a test project using HSQLDB.


In terms of result mapping process, it does the same thing as your example and the test finishes within a second.
So, there may be another factor in your application that slows down the process.

Regards,
Iwao

2017-12-16 8:24 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, below is my definitiion of PoolDoOutDetailModel:

@Data
public class PoolDoOutDetailModel {
private String barcode;
private Integer equipmentId;
private String equipmentName;
private Integer opType;
private String opTypeName;
private String doCode;
private String batchNo;
private String materialCode;
private String materialName;
private BigDecimal quantity = BigDecimal.ZERO;
private Integer qualityType;
private String qualityTypeName;
private String shiftName;
private String personName;
private Date happenTime;
private String moldCode;
private String failReason;
String operationId;
String destSnapshotId;
}

I'm using lombok's @Data annotation to automatically generate getters and setters for all the fields. As you could see
there seems no complex type(Object type as you mentioned) inside the class denifition. 

Regards,
Ben

On Friday, December 15, 2017 at 11:34:49 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

Could you show us the definition of PoolDoOutDetailModel class?

Mapping can be slow when MyBatis cannot resolve property types using reflection (e.g. the declared property type is Object).
In this case, you can help MyBatis by specifying 'javaType' in <result />.

Regards,
Iwao

2017-12-15 21:52 GMT+09:00 尹文才 <[hidden email]>:
Hi Guy, I debugged my test project and also into mybatis code. I found that the first time the query took about 25 seconds, the first 6-7 seconds are used to get a connection, then the prepared statement execution took 
about 5 seconds(which is close to the time it took when I used prepared statement directly), and finally it took around 12 seconds to store the data from retrieved ResultSet into the returning object list. When I added a
second call with a different parameter, it took about 17 seconds(because the connection is already available in the connection pool). So I think my only concern here is about why would it took so long to put the retrieved
data into the mapped object list, is there anything I could do to reduce this time? Thanks.

/Ben


On Friday, December 15, 2017 at 3:25:55 PM UTC+8, 尹文才 wrote:
not sure why the latter part of my post is truncated, I will attach my detail information in the attached file. Please refer to the txt file for details.
As you could see inside the log, it still took around 24 seconds.

/Ben

On Friday, December 15, 2017 at 3:19:36 PM UTC+8, 尹文才 wrote:
Hi Guy, I went to create a small project just to test how long it would take my query to run with mybatis. I didn't use spring this time and I use google's Guava stopwatch to get the time the query takes.

Below is the method I used:

private static void myBatisPerfTest(){
try {
List<String> params = Arrays.asList("F86EC61B-4BC6-48A5-BE69-E89407481625", "A017DED3-FF52-41C6-BA81-29E42AF6011A", "883DDA21-C6E5-4492-99AB-7F36CE216B92", "DB77210F-7A93-44B6-83F2-220AAEFE730E", "CF48C192-8F91-4238-BE45-046471B48F09", "313D32C6-180B-4094-B12A-692E1AEEAABB", "164CE2AD-8DAE-425C-9F73-AE74DD728B04", "71206AAC-EEBF-4B09-8E5C-C4397D48CD74", <span style="color:rgb(0,128,

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: MyBatis select query slow

尹文才
Hi Iwao & guy, thanks very much for all your patient explanation and your help, I really appreciate it.
I tried the test program with only pure jdbc code, it indeed is slow when using the parameterized way.(I tried sql server 2012/2014/2016)
It seems the problem lies in sql server/ms jdbc driver side, I will report this problem to the mssql-jdbc mailing list.

Regards,
Ben

On Tuesday, December 19, 2017 at 7:32:47 PM UTC+8, Iwao AVE! wrote:
Here is the result in human readable form:

0.054878975 secs to prepare
0.030706662 secs to set parameters
4.231786388 secs to query
18.70518546 secs to retrieve results

Total: 23.02255748 secs

It's not exactly what I expected, but the total time is the same as your first post.
So, it proves MyBatis is not the cause.

> Do you mean this program works in the same way as the foreach in mybatis?

Yes.

> when I don't use the foreach operator in mybatis, it always took less than 1 second.

You mean the 'expanded' version, right?
It uses literal parameters and that makes the difference, it seems (try rewriting my JDBC app to use the 'expanded' query to verify).

Even without foreach, it will get slow if you write the MyBatis query as follows.

... IN (#{operationIdList[0]}, #{operationIdList[1]}, ...

> I didn't expect the program to took so long and I still couldn't get what is the problem.

If we describe what we observe : Retrieving results takes longer when using parameterized query.
It is weird indeed.

If it's reproducible with a fresh database, it may be worth reporting.
<a href="https://github.com/Microsoft/mssql-jdbc" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FMicrosoft%2Fmssql-jdbc\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGVig6JNW_4Q7rztn2-4zeP1e8FfQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2FMicrosoft%2Fmssql-jdbc\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGVig6JNW_4Q7rztn2-4zeP1e8FfQ&#39;;return true;">https://github.com/Microsoft/mssql-jdbc

Regards,
Iwao

2017-12-19 17:48 GMT+09:00 尹文才 <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="Sjc7yxBWBAAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">batm...@...>:
Hi Iwao, I tried your program both against sql server 2012 and 2016 and I'm using the sql server jdbc driver version 6.2.2.jre8(using maven).
Do you mean this program works in the same way as the foreach in mybatis? I didn't expect the program to took so long and I still couldn't get what is the problem.
One more thing that puzzles me is when I don't use the foreach operator in mybatis, it always took less than 1 second. 

Regards,
Ben


On Tuesday, December 19, 2017 at 3:51:56 PM UTC+8, 尹文才 wrote:
Hi Iwao, below is the log output after running your program:

26412307879318 : preparing statement
26412362758293 : setting parameters
26412393464955 : executing query
26416625251343 : handling results
T69AD733534226730
T69AD733539886730
T69AD733541626730
T69AD733534136730
T69AD733534316730
T69AD733534016730
T69AD733534086730
T69AD733534046730
T69AD733539476730
T69AD733534276730
T69AD733541726730
T69AD733539516730
T69AD733534466730
T69AD733634566730
T69AD733539616730
T69AD733539486730
T69AD733539806730
T69AD733533896730
T69AD733534106730
T69AD733539466730
T69AD733533916730
T69AD733541766730
T69AD733539536730
T69AD733634526730
T69AD733541786730
T69AD733534396730
T69AD733539416730
T69AD733541676730
T69AD733533996730
T69AD733539936730
T69AD733534006730
T69AD733539626730
T69AD733631286730
T69AD733539456730
T69AD733534216730
T69AD733539506730
T69AD733534186730
T69AD733534436730
T69AD733539796730
T69AD733534386730
T69AD733534096730
T69AD733533966730
T69AD733534376730
T69AD733539946730
T69AD733534146730
T69AD733534476730
T69AD733539606730
T69AD733534166730
T69AD733534416730
T69AD733539726730
T69AD733631246730
T69AD733541636730
T69AD733534236730
T69AD733539766730
T69AD733534296730
T69AD733539666730
T69AD733539676730
T69AD733533946730
T69AD733534486730
T69AD733534036730
T69AD733634776730
T69AD733534456730
T69AD733539526730
T69AD733539916730
T69AD733534256730
T69AD733539576730
T69AD733539746730
T69AD733539706730
T69AD733534336730
T69AD733534176730
T69AD733539786730
T69AD733539596730
T69AD733534126730
T69AD733533976730
T69AD733539566730
T69AD733539846730
T69AD733539966730
T69AD733539756730
T69AD733541806730
T69AD733539426730
T69AD733634506730
T69AD733634496730
T69AD733539696730
T69AD733539656730
T69AD733539866730
T69AD733534366730
T69AD733539826730
T69AD733534446730
T69AD733539816730
T69AD733539646730
T69AD733534066730
T69AD733541646730
T69AD733533926730
T69AD733534076730
T69AD733534266730
T69AD733541746730
T69AD733539446730
T69AD733634426730
T69AD733541756730
T69AD733534056730
T69AD733541736730
T69AD733634576730
T69AD733533956730
T69AD733631296730
T69AD733539436730
T69AD733533986730
T69AD733539776730
T69AD733539556730
T69AD733541776730
26435330436800 : closing connection

Regards,
Ben

On Tuesday, December 19, 2017 at 3:34:19 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

> I also tried your test against sql server it indeed finished within 1 second,

I actually expected that.
The number of rows in the tables should not affect MyBatis performance.
If it does affect performance of the query, it may be caused by the driver and/or the DB.

MyBatis just passes the query string and parameter objects to the driver.
Then SQL Server builds an execution plan to parameterize the query.
The performance of this process may be affected by the number of rows in the tables.

Here is a standalone Java app executing the same query using plain JDBC.
<a href="https://gist.github.com/harawata/e4748263c89550dd92df90ece925042b" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgist.github.com%2Fharawata%2Fe4748263c89550dd92df90ece925042b\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNH65WmrAfmpMKfj0hmcQazgdyGH_g&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgist.github.com%2Fharawata%2Fe4748263c89550dd92df90ece925042b\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNH65WmrAfmpMKfj0hmcQazgdyGH_g&#39;;return true;">https://gist.github.com/harawata/e4748263c89550dd92df90ece925042b
This is essentially what MyBatis does.
Please run it against the database containing many rows and let us know how it performs.

Regards,
Iwao



2017-12-19 10:12 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, thanks for updating the test project accordingly for my case, but I was not using the original project to try to find out the time cost bottleneck.
Instead I created a test project specifically for this case and had only used mybatis alone(exlcuding spring related stuff). One more thing to mention,
I also tried your test against sql server it indeed finished within 1 second, but the problem is your sql query is different, you created a table from the
returned resultset and it's only 109 rows. But my query is against 2 tables, one of them has 1875150 rows and the other one has  4178815 rows.(it takes
almost the same time even if I removed the join part with the second table in the query) I have uploaded my test project onto my google drive, please check
if has any problems. the link is <a href="https://drive.google.com/open?id=1tqvw2rM-29A3LCQlJ-QvTUbR01EqoUVC" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://drive.google.com/open?id\x3d1tqvw2rM-29A3LCQlJ-QvTUbR01EqoUVC&#39;;return true;" onclick="this.href=&#39;https://drive.google.com/open?id\x3d1tqvw2rM-29A3LCQlJ-QvTUbR01EqoUVC&#39;;return true;">https://drive.google.com/open?id=1tqvw2rM-29A3LCQlJ-QvTUbR01EqoUVC
 I think it would be better that I shared my data of both tables, would it be convenient that I also upload my data onto google drive and share it to you?

Regards,
Ben

On Monday, December 18, 2017 at 8:29:56 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

As your 'expanded' query does not perform parameter bindings, you cannot blame foreach just by comparing these results.

I added the foreach element to the demo app and it still runs within a second.
<a href="https://github.com/harawata/mybatis-issues/tree/master/ml-20171215T031703" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Ftree%2Fmaster%2Fml-20171215T031703\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFFJS9o7Gn2y1lTIBk45qps1uW9yQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Ftree%2Fmaster%2Fml-20171215T031703\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFFJS9o7Gn2y1lTIBk45qps1uW9yQ&#39;;return true;">https://github.com/harawata/mybatis-issues/tree/master/ml-20171215T031703
Please run the demo app with your SQL Server and let us know how long it takes.

What I am trying to do is to eliminate the other possible causes like Spring, AOP, plugins, cache, etc..

Regards,
Iwao

2017-12-18 13:40 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, I finally had a clue as to why my query took so long(>22 secs) but I don't know why.
I tried to find out the problem using my original select query as below:

<select id="getOutDetailRecordsByOperationId" resultMap="poolDoOutDetailResult">
select
<include refid="outDetailColumnList"/>
from fact_pool_do_out out inner join fact_material_flow_record flow
on out.operation_id = flow.op_id
<where>
<if test="operationIdList != null">
AND out.operation_id in
<foreach item="op_id" index="index" collection="operationIdList" open="(" separator="," close=")">
#{op_id}
</foreach>
</if>
</where>
</select>

please notice that my table fact_pool_do_out has about 1875150 rows and table fact_material_flow_record has 4178815 rows.
I tried to change my query to find out if any part is the bottleneck for the problem and finally I found when I changed the
foreach part into expanded parameter list, it only took around 780ms, the part to replace foreach is like this:
<where>
out.operation_id in ('002E58E7-FE39-47DF-9CAD-32EDE1D9454C' ,'036FC5C0-5895-4B30-BA7F-F4781A4B7C2B'
,'09BB8B7E-F971-4F13-A770-ED7CE9E01555'
,'0C1885EE-36A1-482C-BBBC-BF343ADC93F8' ,'164CE2AD-8DAE-425C-9F73-AE74DD728B04'
,'18BDFA51-F294-4E77-80AF-A7F6E4462970'
,'1B7DD22D-0A0D-491C-8383-16B7026907BA' ,'23C08BF8-F954-4061-A86B-C225046A72AA'
,'27455220-5C26-4635-84DC-E5D3E9D3E3B3'
,'2AD014DC-45BA-4B1F-AF5C-07A4E1039CD9' ,'2B71AF6C-A505-414F-BA5B-63CD7A0556FB'
,'2C7B7AED-8728-4F27-858A-7CB4A8BB7E39'
,'2DB06DF1-AEFF-43AE-B1B1-6BD599DB08FD' ,'313D32C6-180B-4094-B12A-692E1AEEAABB'
,'31DD5B66-EB1D-439F-841F-EFC4E86CFB23'
,'31F07BD0-76F8-435C-BA97-5DC6CBE9468D' ,'3414019F-2A50-4BE4-AFBD-111303C0FD17'
,'3464259F-2C53-494D-A7B0-2DB8C0D0AD84'
,'34D4B261-BF91-4A71-ABB3-1BF15B8A9FCD' ,'3706AF90-24C9-4F8D-82F1-8054F723BBE0'
,'37C004E4-A342-472F-9884-A32D9203389A'
,'389BDA99-7676-4419-980F-FBEE44F71007' ,'42874649-D07C-4F43-B053-4756CA399389'
,'42E42B55-0D55-46E8-A51B-A49B7A8CBEE3'
,'45543ED6-9BC6-4FE2-B534-C3D592917095' ,'4895EAAF-9467-4F01-8AA1-756C15F45BE4'
,'49043691-6C7A-4317-9231-1994F485C5D4'
,'4A7301C0-A3F1-4465-BF6A-447681328DB3' ,'4B1D36F8-EE34-4EDC-A51A-2B62CAE10772'
,'4EA0C6B7-CB32-48A1-87AB-F6AC74E7BF0B'
,'509E4431-A9F0-449B-97A2-10F45F350082' ,'5387B831-FC43-407A-A41C-E0A0A5A9759A'
,'5609EA14-1489-4780-831B-E83AE1C7DD22'
,'5904C6E2-A329-4CC0-AA44-B6F72BC2641C' ,'5AF4490A-228B-4328-B9FB-CBB48D6A5898'
,'5D084824-822D-4A7D-B0A6-7BEDFA26DB41'
,'61E514F2-20FA-40E2-B03F-696747A4CAB3' ,'63CBFFAB-DF25-4C36-A5BD-E16FFCCA6109'
,'647A3916-C466-47B9-9571-6A583A21480B'
,'66AB13D5-09B4-4535-AEDE-DFC801753021' ,'6CC57E5F-4CE4-41B4-9A23-41B6461E2881'
,'6E282529-E59F-453A-866A-AC25895A14EF'
,'6EEB39E9-F2CE-441F-AC84-6DFBAC5E904B' ,'6F54BE14-1AA0-448B-BFBD-7D6EB2388C7F'
,'71206AAC-EEBF-4B09-8E5C-C4397D48CD74'
,'767987C1-ABFA-4E45-9EF9-684055E80B38' ,'78E7C0F7-1B86-4A74-9682-79019AC288E7'
,'79507CE1-8436-4A6B-9E0E-41CF4A648621'
,'7B0D12C4-9F79-4FB7-B149-BEC40F949B30' ,'7CD5101D-CCA4-4A4F-B134-E005F9F6DB97'
,'7EC2F968-6952-48AC-B628-B179C2D7E20A'
,'803A3F33-B2B0-4D81-B64F-D22DC4DC9EE5' ,'81DD571F-4768-4557-A77D-EC79DE600741'
,'82AD3624-50E4-413D-A048-7F5D73CA701B'
,'834C2A4C-BF2B-42ED-9771-AD527577EA3B' ,'883DDA21-C6E5-4492-99AB-7F36CE216B92'
,'889CA877-2EAC-4948-A341-D1890B1B3EFF'
,'89A891E3-829F-4CCC-B643-47E53F441613' ,'89B779CD-CC08-40EC-B369-9CDB774765B0'
,'8D09C707-3733-4E15-8AE4-8046AEA4E3D2'
,'9152D7B3-E601-40B8-8387-0E1A01746579' ,'92D00E83-ECF3-4794-87DA-4CF4E5E0280D'
,'9389207B-6E63-4B47-8DF7-13A3543258F6'
,'96A19C4F-AF0C-4052-9C42-9AD829BAD171' ,'96D9154A-C3F9-46F4-9240-CA097FE67033'
,'98F4B7DA-369B-4BC7-82F7-0FA45516E536'
,'9C00E14C-2C69-4A57-B220-BB37E0EAD005' ,'9D5B40BA-C268-4E88-8777-39D7DAEADD69'
,'A017DED3-FF52-41C6-BA81-29E42AF6011A'
,'A0B13828-225B-4A65-BB4A-BE80E30F6937' ,'A160885D-CC87-483E-89AD-356E60EE3650'
,'A190EC79-4365-4493-AEB5-E0BBFC3F360E'
,'A5EAF9BE-44E8-49DA-ACD2-271728720F35' ,'A6CAD892-8F00-41F5-B5DD-0D9577A8CA59'
,'A77689B5-A504-4E82-9DA3-0156F950B10C'
,'B1562267-2075-456A-A985-B704499E3732' ,'B252988C-580C-4BD6-87E8-1A7748CC1CE1'
,'B86552B5-0099-4738-A147-4ECCBDC00AA3'
,'B97A738A-F5DC-4708-BDFB-BB8242BFBF7B' ,'BDFF93B9-8ECA-4F1A-B428-AD3C3A243369'
,'BEAE319D-6588-4E10-BCC7-B3D84EB79433'
,'C24F43F4-FF32-473F-BEC6-9EDF56A9778F' ,'C356C9E2-8FD9-46D5-B627-5D14D59340A5'
,'C45ADB6D-01FD-4AD4-8722-463AD2D93CDC'
,'C46E63D5-C622-4471-8738-F6D8A3CBECE4' ,'CB7F829A-473B-4E31-917C-4BB335E15411'
,'CBA44EDB-AFDD-4E62-9A05-49A0D96B87FC'
,'CF48C192-8F91-4238-BE45-046471B48F09' ,'D674F88F-6BA6-46F3-9A9D-0B8BBDC5FD64'
,'D8680DF0-3DBA-47FC-9E77-B40022A74333'
,'D96D562B-9AB3-4664-A316-E34976DBE117' ,'DAF53A81-6D65-4222-B3EE-81D59694C983'
,'DB4E6C7B-04CD-407D-932B-B0C7AC75D768'
,'DB77210F-7A93-44B6-83F2-220AAEFE730E' ,'DB90B0DD-3CCE-48C6-8F9F-9DC7E644F14A'
,'DC505D8B-957B-497A-A55C-09111C654881'
,'DCF2A5A6-0E5A-45A4-9F89-94C8236822E5' ,'DE0D59E8-DEB6-4433-A092-DA6A0DE3AF23'
,'E170E616-C9CF-4F03-85EC-0567F831E75E'
,'E1D7098C-ED21-4CBE-8139-1C53886A2A8B' ,'E40BC73A-2A70-4DC3-98A0-0A04551ADB16'
,'E7F81BF5-178D-4B93-B38F-9BB526E16FD5'
,'E9D95DD9-9E5A-4204-9A94-7F528DA93CE1' ,'ECB86354-F8F1-4C3C-94F4-A354DE1D1F4F'
,'F291F389-E0FC-4A05-86B3-FEAFE0EEB949'
,'F4F17233-3B24-4190-9438-CD88683853F7' ,'F8217553-9C97-48C3-9135-24331DD005D2'
,'F86EC61B-4BC6-48A5-BE69-E89407481625'
,'FFD08A43-FDCC-482C-A479-A033E331D013')
</where>

I also tried another similar approach to verify my concern, which is to use or intead of the in keyword, like this:
<foreach item="op_id" index="index" collection="list" open="(" separator=" or " close=")">
out.operation_id = #{op_id}
</foreach>

this takes almost the same time as with in keyword together with foreach(around 23 secs), but when I expanded all 109
parameters without using the foreach operator(like the example above but with the or operator), it again took less than 1 second.

I tried to search on google the possible performance impact of foreach operator(they call it mybatis dynamic sql) but
unfortunately found no clue about select query(I only found one guy mentioned about this performance problem when doing
bulk insert in stackoverflow: <a href="https://stackoverflow.com/questions/36407980/mybatis-performance-of-bulk-operator" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F36407980%2Fmybatis-performance-of-bulk-operator\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHNDt6MdQy1GnB9S8V5MeQ_V_IReg&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F36407980%2Fmybatis-performance-of-bulk-operator\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHNDt6MdQy1GnB9S8V5MeQ_V_IReg&#39;;return true;">https://stackoverflow.com/questions/36407980/mybatis-performance-of-bulk-operator, not sure
if it's the same problem as mine)

Regards,
Ben


On Saturday, December 16, 2017 at 10:57:06 PM UTC+8, Iwao AVE! wrote:
Hi,

If network is the cause, there is nothing MyBatis can do.
Why don't you modify the datasource setting of my demo app and run it against your SQL Server instance?
You may also need to rewrite the insert statement in Create.sql, but it shouldn't be too difficult.
 
Regards,
iwao

2017-12-16 21:59 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, thanks for taking the time to write such a test program to test the performance for me. I took a look into your test project and also has ran it, indeed it is very fast and took less than 1 second.
However, the difference between your test project and mine is you're using hsqldb and using it in memory mode, while my database is sql server 2012 in a OpenStack virtual machine.  According to
my knowledge, the process of mybatis storing the retrieved resultset data into the final object lists is to iterate over the resultset and put the data into the mapped objects. So I'm wondering if the time
difference could be caused by the time of network communication with sql server when reading data from ResultSet? Thanks.

Regards,
Ben

On Saturday, December 16, 2017 at 3:11:52 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

It looks pretty standard, yes.
Based on the information you provided, I created a test project using HSQLDB.

<a href="https://github.com/harawata/mybatis-issues/tree/master/ml-20171215T031703" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Ftree%2Fmaster%2Fml-20171215T031703\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFFJS9o7Gn2y1lTIBk45qps1uW9yQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Ftree%2Fmaster%2Fml-20171215T031703\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFFJS9o7Gn2y1lTIBk45qps1uW9yQ&#39;;return true;">https://github.com/harawata/mybatis-issues/tree/master/ml-20171215T031703

In terms of result mapping process, it does the same thing as your example and the test finishes within a second.
So, there may be another factor in your application that slows down the process.

Regards,
Iwao

2017-12-16 8:24 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao, below is my definitiion of PoolDoOutDetailModel:

@Data
public class PoolDoOutDetailModel {
private String barcode;
private Integer equipmentId;
private String equipmentName;
private Integer opType;
private String opTypeName;
private String doCode;
private String batchNo;
private String materialCode;
private String materialName;
private BigDecimal quantity = BigDecimal.ZERO;
private Integer qualityType;
private String qualityTypeName;
private String shiftName;
private String personName;
private Date happenTime;
private String moldCode;
private String failReason;
String operationId;
String destSnapshotId;
}

I'm using lombok's @Data annotation to automatically generate getters and setters for all the fields. As you could see
there seems no complex type(Object type as you mentioned) inside the class denifition. 

Regards,
Ben

On Friday, December 15, 2017 at 11:34:49 PM UTC+8, Iwao AVE! wrote:
Hi Ben,

Could you show us the definition of PoolDoOutDetailModel class?

Mapping can be slow when MyBatis cannot resolve property types using reflection (e.g. the declared property type is Object).
In this case, you can help MyBatis by specifying 'javaType' in <result />.

Regards,
Iwao

2017-12-15 21:52 GMT+09:00 尹文才 <[hidden email]>:
Hi Guy, I debugged my test project and also into mybatis code. I found that the first time the query took about 25 seconds, the first 6-7 seconds are used to get a connection, then the prepared statement execution took 
about 5 seconds(which is close to the time it took when I used prepared statement directly), and finally it took around 12 seconds to store the data from retrieved ResultSet into the returning object list. When I added a
second call with a different parameter, it took about 17 seconds(because the connection is already available in the connection pool). So I think my only concern here is about why would it took so long to put the retrieved
data into the mapped object list, is there anything I could do to reduce this time? Thanks.

/Ben


On Friday, December 15, 2017 at 3:25:55 PM UTC+8, 尹文才 wrote:
not sure why the latter part of my post is truncated, I will attach my detail information in the attached file. Please refer to the txt file for details.
As you could see inside the log, it still took around 24 seconds.

/Ben

On Friday, December 15, 2017 at 3:19:36 PM UTC+8, 尹文才 wrote:
<blockquote class="gmail_quote" style="margin:0px 0

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: MyBatis select query slow

Iwao AVE!
You are very welcome! Thank you for not giving up. :)
Hope the issue is resolved soon.

Regards,
Iwao

2017-12-19 21:47 GMT+09:00 尹文才 <[hidden email]>:
Hi Iwao & guy, thanks very much for all your patient explanation and your help, I really appreciate it.
I tried the test program with only pure jdbc code, it indeed is slow when using the parameterized way.(I tried sql server 2012/2014/2016)
It seems the problem lies in sql server/ms jdbc driver side, I will report this problem to the mssql-jdbc mailing list.

Regards,
Ben

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: MyBatis select query slow

nclemeur

I haven't read the details in its full details, but the symptoms makes me think that you might want to add ';sendStringParametersAsUnicode=false'  in the url that you use to connect to MS SQL. From memory, if you don't have this set, MS SQL does not use the indexes when a parameter is a string.

Give it ago, ant let us know

Cheers
Nicolas

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: MyBatis select query slow

尹文才
Thanks very much nclemeur, you saved my day. I added the parameter ';sendStringParametersAsUnicode=false' to my connection string and
all of a sudden it always took less than 1 second. 

Regards,
Ben

On Wednesday, December 20, 2017 at 6:27:12 AM UTC+8, nclemeur wrote:

I haven't read the details in its full details, but the symptoms makes me think that you might want to add ';sendStringParametersAsUnicode=false'  in the url that you use to connect to MS SQL. From memory, if you don't have this set, MS SQL does not use the indexes when a parameter is a string.

Give it ago, ant let us know

Cheers
Nicolas

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: MyBatis select query slow

wenxing zheng
so the issue only exists in the SQLServer, but not on MySQL?

Thanks, wenxing

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
12