MyBatis select query slow

尹文才

unread,

Dec 14, 2017, 10:17:03 PM12/14/17

to mybatis-user

Hi guys, I'm currently using mybatis with Spring boot(I used mybatis-spring 1.3.1) and I found the performance is really bad.

for example, I have a select sql statement which takes about 23 seconds, while I also tried running the sql statement in the following ways:

1. run sql directly inside sql server management studio, this takes about 0.108 seconds.

2. create statement with java jdbc code and it takes about 0.159 seconds.

3. create prepared statement with java jdbc code and it takes about 4.189 seconds.

The database I'm using against is sql server 2012 and below is the code I used:

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

<resultMap id="poolDoOutDetailResult" type="PoolDoOutDetailModel">
    <result column="barcode" property="barcode" jdbcType="VARCHAR"/>
    <result column="equipment_id" property="equipmentId" jdbcType="INTEGER"/>
    <result column="equipment_name" property="equipmentName" jdbcType="VARCHAR"/>
    <result column="op_type" property="opType" jdbcType="INTEGER"/>
    <result column="op_type_name" property="opTypeName" jdbcType="VARCHAR"/>
    <result column="do_code" property="doCode" jdbcType="VARCHAR"/>
    <result column="batch_no" property="batchNo" jdbcType="VARCHAR"/>
    <result column="material_code" property="materialCode" jdbcType="VARCHAR"/>
    <result column="material_name" property="materialName" jdbcType="VARCHAR"/>
    <result column="quantity" property="quantity" jdbcType="NUMERIC"/>
    <result column="quality_type" property="qualityType" jdbcType="INTEGER"/>
    <result column="shift_name" property="shiftName" jdbcType="VARCHAR"/>
    <result column="person_name" property="personName" jdbcType="VARCHAR"/>
    <result column="happen_time" property="happenTime" jdbcType="TIMESTAMP"/>
    <result column="mold_code" property="moldCode" jdbcType="VARCHAR"/>
    <result column="failreason_name" property="failReason" jdbcType="VARCHAR"/>
    <result column="operation_id" property="operationId" jdbcType="VARCHAR"/>
    <result column="dest_snapshot_id" property="destSnapshotId" jdbcType="VARCHAR"/>
</resultMap>

<sql id="outDetailColumnList">
out.barcode,out.equipment_id,out.equipment_name,out.op_type,out.op_type_name,out.do_code,out.batch_no,out.material_code,out.material_name,
out.quantity, out.quality_type,out.shift_name,out.person_name,out.happen_time,out.mold_code,out.failreason_name,out.operation_id,
flow.dest_snapshot_id
</sql>

One more thing to mention is I didn't use spring transaction for this and the 23 seconds is the result I got from the mybatis plugin I wrote
to check the time it takes for each sql query(using spring aop). 

Does anyone know what I should do with mybatis to reduce the query time? Thanks.

/Ben

Guy Rouillier

unread,

Dec 15, 2017, 1:49:44 AM12/15/17

to mybatis-user

You can just turn on logging to get timing for your statements, without incurring the additional overhead of your plugin and Spring AOP. Also, are you sure you are comparing apples to apples? With MyBatis, you are incurring the time to fetch the entire result set, and create objects for each row. With the SQL management studio, you are probably just measuring the time to get back the initial set of rows.

I don't see anything wrong with your code. You'll probably have to explain plan to see what MyBatis is experiencing versus what SQL management studio is doing.

--

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

尹文才

unread,

Dec 15, 2017, 2:19:36 AM12/15/17

to mybatis-user

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", "9D5B40BA-C268-4E88-8777-39D7DAEADD69", "8D09C707-3733-4E15-8AE4-8046AEA4E3D2", "F8217553-9C97-48C3-9135-24331DD005D2", "DE0D59E8-DEB6-4433-A092-DA6A0DE3AF23", "4895EAAF-9467-4F01-8AA1-756C15F45BE4", "2C7B7AED-8728-4F27-858A-7CB4A8BB7E39", "DCF2A5A6-0E5A-45A4-9F89-94C8236822E5", "9C00E14C-2C69-4A57-B220-BB37E0EAD005", "A0B13828-225B-4A65-BB4A-BE80E30F6937", "DB90B0DD-3CCE-48C6-8F9F-9DC7E644F14A", "A77689B5-A504-4E82-9DA3-0156F950B10C", "82AD3624-50E4-413D-A048-7F5D73CA701B", "F4F17233-3B24-4190-9438-CD88683853F7", "6E282529-E59F-453A-866A-AC25895A14EF", "FFD08A43-FDCC-482C-A479-A033E331D013", "B86552B5-0099-4738-A147-4ECCBDC00AA3", "3706AF90-24C9-4F8D-82F1-8054F723BBE0", "9389207B-6E63-4B47-8DF7-13A3543258F6", "CB7F829A-473B-4E31-917C-4BB335E15411", "27455220-5C26-4635-84DC-E5D3E9D3E3B3", "18BDFA51-F294-4E77-80AF-A7F6E4462970", "CBA44EDB-AFDD-4E62-9A05-49A0D96B87FC", "767987C1-ABFA-4E45-9EF9-684055E80B38", "61E514F2-20FA-40E2-B03F-696747A4CAB3", "509E4431-A9F0-449B-97A2-10F45F350082", "42E42B55-0D55-46E8-A51B-A49B7A8CBEE3", "DB4E6C7B-04CD-407D-932B-B0C7AC75D768", "B252988C-580C-4BD6-87E8-1A7748CC1CE1", "79507CE1-8436-4A6B-9E0E-41CF4A648621", "37C004E4-A342-472F-9884-A32D9203389A", "5904C6E2-A329-4CC0-AA44-B6F72BC2641C", "45543ED6-9BC6-4FE2-B534-C3D592917095", "803A3F33-B2B0-4D81-B64F-D22DC4DC9EE5", "4EA0C6B7-CB32-48A1-87AB-F6AC74E7BF0B", "E9D95DD9-9E5A-4204-9A94-7F528DA93CE1", "B97A738A-F5DC-4708-BDFB-BB8242BFBF7B", "E170E616-C9CF-4F03-85EC-0567F831E75E", "C45ADB6D-01FD-4AD4-8722-463AD2D93CDC", "A160885D-CC87-483E-89AD-356E60EE3650", "5D084824-822D-4A7D-B0A6-7BEDFA26DB41", "036FC5C0-5895-4B30-BA7F-F4781A4B7C2B", "4A7301C0-A3F1-4465-BF6A-447681328DB3", "4B1D36F8-EE34-4EDC-A51A-2B62CAE10772", "6CC57E5F-4CE4-41B4-9A23-41B6461E2881", "DAF53A81-6D65-4222-B3EE-81D59694C983", "834C2A4C-BF2B-42ED-9771-AD527577EA3B", "D96D562B-9AB3-4664-A316-E34976DBE117", "0C1885EE-36A1-482C-BBBC-BF343ADC93F8", "63CBFFAB-DF25-4C36-A5BD-E16FFCCA6109");

        //获取配置文件信息
        InputStream resStream = Resources.getResourceAsStream("mybatis-config.xml");
        //通过配置参数构建sqlSessionFactory工厂类
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resStream);
        Stopwatch timer = Stopwatch.createStarted();
        //开启数据库会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<PoolDoOutDetailModel> poolDoOutDetailModels = sqlSession.selectList("DoOutMapper.getOutDetailRecordsByOperationId", params);

        sqlSession.close();

        timer.stop();
        System.out.println("time to execute query with mybatis: " + timer.elapsed(MILLISECONDS));
    } catch (Exception ex){
        System.out.println("myBatisPerfTest exception: " + ex.getMessage());
    }
}

below is mybatis related files:

mybatis-config.xml:

<configuration>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING" />
    </settings>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
                <property name="url" value="jdbc:sqlserver://192.168.227.139:1433;databaseName=ADMIOT_BI_1207"/>
                <property name="username" value="sa"/>
                <property name="password" value="admin"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/TestMapper.xml"/>
    </mappers>
</configuration>

TestMapper.xml:

<mapper namespace="DoOutMapper">
    <resultMap id="poolDoOutDetailResult" type="com.realtech.PoolDoOutDetailModel">


        <result column="barcode" property="barcode" jdbcType="VARCHAR"/>
        <result column="equipment_id" property="equipmentId" jdbcType="INTEGER"/>
        <result column="equipment_name" property="equipmentName" jdbcType="VARCHAR"/>
        <result column="op_type" property="opType" jdbcType="INTEGER"/>
        <result column="op_type_name" property="opTypeName" jdbcType="VARCHAR"/>
        <result column="do_code" property="doCode" jdbcType="VARCHAR"/>
        <result column="batch_no" property="batchNo" jdbcType="VARCHAR"/>
        <result column="material_code" property="materialCode" jdbcType="VARCHAR"/>
        <result column="material_name" property="materialName" jdbcType="VARCHAR"/>
        <result column="quantity" property="quantity" jdbcType="NUMERIC"/>
        <result column="quality_type" property="qualityType" jdbcType="INTEGER"/>
        <result column="shift_name" property="shiftName" jdbcType="VARCHAR"/>
        <result column="person_name" property="personName" jdbcType="VARCHAR"/>
        <result column="happen_time" property="happenTime" jdbcType="TIMESTAMP"/>
        <result column="mold_code" property="moldCode" jdbcType="VARCHAR"/>
        <result column="failreason_name" property="failReason" jdbcType="VARCHAR"/>
        <result column="operation_id" property="operationId" jdbcType="VARCHAR"/>
        <result column="dest_snapshot_id" property="destSnapshotId" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="outDetailColumnList">
        out.barcode,out.equipment_id,out.equipment_name,out.op_type,out.op_type_name,out.do_code,out.batch_no,out.material_code,out.material_name,
        out.quantity, out.quality_type,out.shift_name,out.person_name,out.happen_time,out.mold_code,out.failreason_name,out.operation_id,
        flow.dest_snapshot_id
    </sql>

    <select id="getOutDetailRecordsByOperationId" fetchSize="1000" resultMap="poolDoOutDetailResult" parameterType="list">


        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>

            AND out.operation_id in
            <foreach item="op_id" index="index" collection="list" open="(" separator="," close=")">
                #{op_id}
            </foreach>
        </where>
    </select>
</mapper>

And finally the log is as below:

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1436901839.
Setting autocommit to false on JDBC Connection [ConnectionID:1 ClientConnectionId: 2b56fbbe-a671-445f-8cc9-219adad10206]
==>  Preparing: select out.barcode,out.equipment_id,out.equipment_name,out.op_type,out.op_type_name,out.do_code,out.batch_no,out.material_code,out.material_name, out.quantity, out.quality_type,out.shift_name,out.person_name,out.happen_time,out.mold_code,out.failreason_name,out.operation_id, flow.dest_snapshot_id from fact_pool_do_out out inner join fact_material_flow_record flow on out.operation_id = flow.op_id WHERE out.operation_id in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) 
==> Parameters: F86EC61B-4BC6-48A5-BE69-E89407481625(String), A017DED3-FF52-41C6-BA81-29E42AF6011A(String), 883DDA21-C6E5-4492-99AB-7F36CE216B92(String), DB77210F-7A93-44B6-83F2-220AAEFE730E(String), CF48C192-8F91-4238-BE45-046471B48F09(String), 313D32C6-180B-4094-B12A-692E1AEEAABB(String), 164CE2AD-8DAE-425C-9F73-AE74DD728B04(String), 71206AAC-EEBF-4B09-8E5C-C4397D48CD74(String), 78E7C0F7-1B86-4A74-9682-79019AC288E7(String), ECB86354-F8F1-4C3C-94F4-A354DE1D1F4F(String), 89A891E3-829F-4CCC-B643-47E53F441613(String), 66AB13D5-09B4-4535-AEDE-DFC801753021(String), 49043691-6C7A-4317-9231-1994F485C5D4(String), E40BC73A-2A70-4DC3-98A0-0A04551ADB16(String), 6EEB39E9-F2CE-441F-AC84-6DFBAC5E904B(String), 6F54BE14-1AA0-448B-BFBD-7D6EB2388C7F(String), 96A19C4F-AF0C-4052-9C42-9AD829BAD171(String), 31F07BD0-76F8-435C-BA97-5DC6CBE9468D(String), 2AD014DC-45BA-4B1F-AF5C-07A4E1039CD9(String), A6CAD892-8F00-41F5-B5DD-0D9577A8CA59(String), 5609EA14-1489-4780-831B-E83AE1C7DD22(String), 2DB06DF1-AEFF-43AE-B1B1-6BD599DB08FD(String), C356C9E2-8FD9-46D5-B627-5D14D59340A5(String), C24F43F4-FF32-473F-BEC6-9EDF56A9778F(String), 647A3916-C466-47B9-9571-6A583A21480B(String), 7CD5101D-CCA4-4A4F-B134-E005F9F6DB97(String), 34D4B261-BF91-4A71-ABB3-1BF15B8A9FCD(String), E7F81BF5-178D-4B93-B38F-9BB526E16FD5(String), A190EC79-4365-4493-AEB5-E0BBFC3F360E(String), 3464259F-2C53-494D-A7B0-2DB8C0D0AD84(String), 09BB8B7E-F971-4F13-A770-ED7CE9E01555(String), C46E63D5-C622-4471-8738-F6D8A3CBECE4(String), 89B779CD-CC08-40EC-B369-9CDB774765B0(String), E1D7098C-ED21-4CBE-8139-1C53886A2A8B(String), 9152D7B3-E601-40B8-8387-0E1A01746579(String), 2B71AF6C-A505-414F-BA5B-63CD7A0556FB(String), A5EAF9BE-44E8-49DA-ACD2-271728720F35(String), 31DD5B66-EB1D-439F-841F-EFC4E86CFB23(String), 002E58E7-FE39-47DF-9CAD-32EDE1D9454C(String), 98F4B7DA-369B-4BC7-82F7-0FA45516E536(String), BEAE319D-6588-4E10-BCC7-B3D84EB79433(String), 5AF4490A-228B-4328-B9FB-CBB48D6A5898(String), 96D9154A-C3F9-46F4-9240-CA097FE67033(String), 3414019F-2A50-4BE4-AFBD-111303C0FD17(String), 92D00E83-ECF3-4794-87DA-4CF4E5E0280D(String), 389BDA99-7676-4419-980F-FBEE44F71007(String), 23C08BF8-F954-4061-A86B-C225046A72AA(String), 1B7DD22D-0A0D-491C-8383-16B7026907BA(String), 7EC2F968-6952-48AC-B628-B179C2D7E20A(String), F291F389-E0FC-4A05-86B3-FEAFE0EEB949(String), D674F88F-6BA6-46F3-9A9D-0B8BBDC5FD64(String), 5387B831-FC43-407A-A41C-E0A0A5A9759A(String), B1562267-2075-456A-A985-B704499E3732(String), BDFF93B9-8ECA-4F1A-B428-AD3C3A243369(String), D8680DF0-3DBA-47FC-9E77-B40022A74333(String), 81DD571F-4768-4557-A77D-EC79DE600741(String), 7B0D12C4-9F79-4FB7-B149-BEC40F949B30(String), 42874649-D07C-4F43-B053-4756CA399389(String), DC505D8B-957B-497A-A55C-09111C654881(String), 889CA877-2EAC-4948-A341-D1890B1B3EFF(String), 9D5B40BA-C268-4E88-8777-39D7DAEADD69(String), 8D09C707-3733-4E15-8AE4-8046AEA4E3D2(String), F8217553-9C97-48C3-9135-24331DD005D2(String), DE0D59E8-DEB6-4433-A092-DA6A0DE3AF23(String), 4895EAAF-9467-4F01-8AA1-756C15F45BE4(String), 2C7B7AED-8728-4F27-858A-7CB4A8BB7E39(String), DCF2A5A6-0E5A-45A4-9F89-94C8236822E5(String), 9C00E14C-2C69-4A57-B220-BB37E0EAD005(String), A0B13828-225B-4A65-BB4A-BE80E30F6937(String), DB90B0DD-3CCE-48C6-8F9F-9DC7E644F14A(String), A77689B5-A504-4E82-9DA3-0156F950B10C(String), 82AD3624-50E4-413D-A048-7F5D73CA701B(String), F4F17233-3B24-4190-9438-CD88683853F7(String), 6E282529-E59F-453A-866A-AC25895A14EF(String), FFD08A43-FDCC-482C-A479-A033E331D013(String), B86552B5-0099-4738-A147-4ECCBDC00AA3(String), 3706AF90-24C9-4F8D-82F1-8054F723BBE0(String), 9389207B-6E63-4B47-8DF7-13A3543258F6(String), CB7F829A-473B-4E31-917C-4BB335E15411(String), 27455220-5C26-4635-84DC-E5D3E9D3E3B3(String), 18BDFA51-F294-4E77-80AF-A7F6E4462970(String), CBA44EDB-AFDD-4E62-9A05-49A0D96B87FC(String), 767987C1-ABFA-4E45-9EF9-684055E80B38(String), 61E514F2-20FA-40E2-B03F-696747A4CAB3(String), 509E4431-A9F0-449B-97A2-10F45F350082(String), 42E42B55-0D55-46E8-A51B-A49B7A8CBEE3(String), DB4E6C7B-04CD-407D-932B-B0C7AC75D768(String), B252988C-580C-4BD6-87E8-1A7748CC1CE1(String), 79507CE1-8436-4A6B-9E0E-41CF4A648621(String), 37C004E4-A342-472F-9884-A32D9203389A(String), 5904C6E2-A329-4CC0-AA44-B6F72BC2641C(String), 45543ED6-9BC6-4FE2-B534-C3D592917095(String), 803A3F33-B2B0-4D81-B64F-D22DC4DC9EE5(String), 4EA0C6B7-CB32-48A1-87AB-F6AC74E7BF0B(String), E9D95DD9-9E5A-4204-9A94-7F528DA93CE1(String), B97A738A-F5DC-4708-BDFB-BB8242BFBF7B(String), E170E616-C9CF-4F03-85EC-0567F831E75E(String), C45ADB6D-01FD-4AD4-8722-463AD2D93CDC(String), A160885D-CC87-483E-89AD-356E60EE3650(String), 5D084824-822D-4A7D-B0A6-7BEDFA26DB41(String), 036FC5C0-5895-4B30-BA7F-F4781A4B7C2B(String), 4A7301C0-A3F1-4465-BF6A-447681328DB3(String), 4B1D36F8-EE34-4EDC-A51A-2B62CAE10772(String), 6CC57E5F-4CE4-41B4-9A23-41B6461E2881(String), DAF53A81-6D65-4222-B3EE-81D59694C983(String), 834C2A4C-BF2B-42ED-9771-AD527577EA3B(String), D96D562B-9AB3-4664-A316-E34976DBE117(String), 0C1885EE-36A1-482C-BBBC-BF343ADC93F8(String), 63CBFFAB-DF25-4C36-A5BD-E16FFCCA6109(String)
<==    Columns: barcode, equipment_id, equipment_name, op_type, op_type_name, do_code, batch_no, material_code, material_name, quantity, quality_type, shift_name, person_name, happen_time, mold_code, failreason_name, operation_id, dest_snapshot_id
<==        Row: T69AD733534226730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:10:55.0, , null, 002E58E7-FE39-47DF-9CAD-32EDE1D9454C, 002E58E7-FE39-47DF-9CAD-32EDE1D9454C
<==        Row: T69AD733539886730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:40:48.0, , null, 036FC5C0-5895-4B30-BA7F-F4781A4B7C2B, 036FC5C0-5895-4B30-BA7F-F4781A4B7C2B
<==        Row: T69AD733541626730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 3, 20171203早班, 吴云焕, 2017-12-03 08:44:11.0, , null, 09BB8B7E-F971-4F13-A770-ED7CE9E01555, 09BB8B7E-F971-4F13-A770-ED7CE9E01555
<==        Row: T69AD733534136730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:36:10.0, , null, 0C1885EE-36A1-482C-BBBC-BF343ADC93F8, 0C1885EE-36A1-482C-BBBC-BF343ADC93F8
<==        Row: T69AD733534316730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:50:29.0, , null, 164CE2AD-8DAE-425C-9F73-AE74DD728B04, 164CE2AD-8DAE-425C-9F73-AE74DD728B04
<==        Row: T69AD733534016730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:59:40.0, , null, 18BDFA51-F294-4E77-80AF-A7F6E4462970, 18BDFA51-F294-4E77-80AF-A7F6E4462970
<==        Row: T69AD733534086730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:02:49.0, , null, 1B7DD22D-0A0D-491C-8383-16B7026907BA, 1B7DD22D-0A0D-491C-8383-16B7026907BA
<==        Row: T69AD733534046730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:25:49.0, , null, 23C08BF8-F954-4061-A86B-C225046A72AA, 23C08BF8-F954-4061-A86B-C225046A72AA
<==        Row: T69AD733539476730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:46:54.0, , null, 27455220-5C26-4635-84DC-E5D3E9D3E3B3, 27455220-5C26-4635-84DC-E5D3E9D3E3B3
<==        Row: T69AD733534276730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:10:51.0, , null, 2AD014DC-45BA-4B1F-AF5C-07A4E1039CD9, 2AD014DC-45BA-4B1F-AF5C-07A4E1039CD9
<==        Row: T69AD733541726730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:27:42.0, , null, 2B71AF6C-A505-414F-BA5B-63CD7A0556FB, 2B71AF6C-A505-414F-BA5B-63CD7A0556FB
<==        Row: T69AD733539516730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:42:19.0, , null, 2C7B7AED-8728-4F27-858A-7CB4A8BB7E39, 2C7B7AED-8728-4F27-858A-7CB4A8BB7E39
<==        Row: T69AD733534466730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:16:53.0, , null, 2DB06DF1-AEFF-43AE-B1B1-6BD599DB08FD, 2DB06DF1-AEFF-43AE-B1B1-6BD599DB08FD
<==        Row: T69AD733634566730, 116, C10T_油封压装, 6, 产出, D201712030035, P171202A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:27:56.0, , null, 313D32C6-180B-4094-B12A-692E1AEEAABB, 313D32C6-180B-4094-B12A-692E1AEEAABB
<==        Row: T69AD733539616730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:53:01.0, , null, 31DD5B66-EB1D-439F-841F-EFC4E86CFB23, 31DD5B66-EB1D-439F-841F-EFC4E86CFB23
<==        Row: T69AD733539486730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:08:22.0, , null, 31F07BD0-76F8-435C-BA97-5DC6CBE9468D, 31F07BD0-76F8-435C-BA97-5DC6CBE9468D
<==        Row: T69AD733539806730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:52:46.0, , null, 3414019F-2A50-4BE4-AFBD-111303C0FD17, 3414019F-2A50-4BE4-AFBD-111303C0FD17
<==        Row: T69AD733533896730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:29:02.0, , null, 3464259F-2C53-494D-A7B0-2DB8C0D0AD84, 3464259F-2C53-494D-A7B0-2DB8C0D0AD84
<==        Row: T69AD733534106730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:08:12.0, , null, 34D4B261-BF91-4A71-ABB3-1BF15B8A9FCD, 34D4B261-BF91-4A71-ABB3-1BF15B8A9FCD
<==        Row: T69AD733539466730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:13:49.0, , null, 3706AF90-24C9-4F8D-82F1-8054F723BBE0, 3706AF90-24C9-4F8D-82F1-8054F723BBE0
<==        Row: T69AD733533916730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:38:05.0, , null, 37C004E4-A342-472F-9884-A32D9203389A, 37C004E4-A342-472F-9884-A32D9203389A
<==        Row: T69AD733541766730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:51:10.0, , null, 389BDA99-7676-4419-980F-FBEE44F71007, 389BDA99-7676-4419-980F-FBEE44F71007
<==        Row: T69AD733539536730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:49:26.0, , null, 42874649-D07C-4F43-B053-4756CA399389, 42874649-D07C-4F43-B053-4756CA399389
<==        Row: T69AD733634526730, 116, C10T_油封压装, 6, 产出, D201712030035, P171202A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:08:17.0, , null, 42E42B55-0D55-46E8-A51B-A49B7A8CBEE3, 42E42B55-0D55-46E8-A51B-A49B7A8CBEE3
<==        Row: T69AD733541786730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:43:24.0, , null, 45543ED6-9BC6-4FE2-B534-C3D592917095, 45543ED6-9BC6-4FE2-B534-C3D592917095
<==        Row: T69AD733534396730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:31:06.0, , null, 4895EAAF-9467-4F01-8AA1-756C15F45BE4, 4895EAAF-9467-4F01-8AA1-756C15F45BE4
<==        Row: T69AD733539416730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:34:17.0, , null, 49043691-6C7A-4317-9231-1994F485C5D4, 49043691-6C7A-4317-9231-1994F485C5D4
<==        Row: T69AD733541676730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 08:52:08.0, , null, 4A7301C0-A3F1-4465-BF6A-447681328DB3, 4A7301C0-A3F1-4465-BF6A-447681328DB3
<==        Row: T69AD733533996730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:06:51.0, , null, 4B1D36F8-EE34-4EDC-A51A-2B62CAE10772, 4B1D36F8-EE34-4EDC-A51A-2B62CAE10772
<==        Row: T69AD733539936730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:20:30.0, , null, 4EA0C6B7-CB32-48A1-87AB-F6AC74E7BF0B, 4EA0C6B7-CB32-48A1-87AB-F6AC74E7BF0B
<==        Row: T69AD733534006730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:38:42.0, , null, 509E4431-A9F0-449B-97A2-10F45F350082, 509E4431-A9F0-449B-97A2-10F45F350082
<==        Row: T69AD733539626730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:48:47.0, , null, 5387B831-FC43-407A-A41C-E0A0A5A9759A, 5387B831-FC43-407A-A41C-E0A0A5A9759A
<==        Row: T69AD733631286730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:24:35.0, , null, 5609EA14-1489-4780-831B-E83AE1C7DD22, 5609EA14-1489-4780-831B-E83AE1C7DD22
<==        Row: T69AD733539456730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:20:55.0, , null, 5904C6E2-A329-4CC0-AA44-B6F72BC2641C, 5904C6E2-A329-4CC0-AA44-B6F72BC2641C
<==        Row: T69AD733534216730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:10:06.0, , null, 5AF4490A-228B-4328-B9FB-CBB48D6A5898, 5AF4490A-228B-4328-B9FB-CBB48D6A5898
<==        Row: T69AD733539506730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:16:26.0, , null, 5D084824-822D-4A7D-B0A6-7BEDFA26DB41, 5D084824-822D-4A7D-B0A6-7BEDFA26DB41
<==        Row: T69AD733534186730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:55:45.0, , null, 61E514F2-20FA-40E2-B03F-696747A4CAB3, 61E514F2-20FA-40E2-B03F-696747A4CAB3
<==        Row: T69AD733534436730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:52:16.0, , null, 63CBFFAB-DF25-4C36-A5BD-E16FFCCA6109, 63CBFFAB-DF25-4C36-A5BD-E16FFCCA6109
<==        Row: T69AD733539796730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:00:36.0, , null, 647A3916-C466-47B9-9571-6A583A21480B, 647A3916-C466-47B9-9571-6A583A21480B
<==        Row: T69AD733534386730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:36:26.0, , null, 66AB13D5-09B4-4535-AEDE-DFC801753021, 66AB13D5-09B4-4535-AEDE-DFC801753021
<==        Row: T69AD733534096730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:18:24.0, , null, 6CC57E5F-4CE4-41B4-9A23-41B6461E2881, 6CC57E5F-4CE4-41B4-9A23-41B6461E2881
<==        Row: T69AD733533966730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:46:36.0, , null, 6E282529-E59F-453A-866A-AC25895A14EF, 6E282529-E59F-453A-866A-AC25895A14EF
<==        Row: T69AD733534376730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:21:11.0, , null, 6EEB39E9-F2CE-441F-AC84-6DFBAC5E904B, 6EEB39E9-F2CE-441F-AC84-6DFBAC5E904B
<==        Row: T69AD733539946730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:33:58.0, , null, 6F54BE14-1AA0-448B-BFBD-7D6EB2388C7F, 6F54BE14-1AA0-448B-BFBD-7D6EB2388C7F
<==        Row: T69AD733534146730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:37:10.0, , null, 71206AAC-EEBF-4B09-8E5C-C4397D48CD74, 71206AAC-EEBF-4B09-8E5C-C4397D48CD74
<==        Row: T69AD733534476730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:39:21.0, , null, 767987C1-ABFA-4E45-9EF9-684055E80B38, 767987C1-ABFA-4E45-9EF9-684055E80B38
<==        Row: T69AD733539606730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:13:27.0, , null, 78E7C0F7-1B86-4A74-9682-79019AC288E7, 78E7C0F7-1B86-4A74-9682-79019AC288E7
<==        Row: T69AD733534166730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:27:01.0, , null, 79507CE1-8436-4A6B-9E0E-41CF4A648621, 79507CE1-8436-4A6B-9E0E-41CF4A648621
<==        Row: T69AD733534416730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:48:49.0, , null, 7B0D12C4-9F79-4FB7-B149-BEC40F949B30, 7B0D12C4-9F79-4FB7-B149-BEC40F949B30
<==        Row: T69AD733539726730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:56:19.0, , null, 7CD5101D-CCA4-4A4F-B134-E005F9F6DB97, 7CD5101D-CCA4-4A4F-B134-E005F9F6DB97
<==        Row: T69AD733631246730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:16:16.0, , null, 7EC2F968-6952-48AC-B628-B179C2D7E20A, 7EC2F968-6952-48AC-B628-B179C2D7E20A
<==        Row: T69AD733541636730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 08:59:48.0, , null, 803A3F33-B2B0-4D81-B64F-D22DC4DC9EE5, 803A3F33-B2B0-4D81-B64F-D22DC4DC9EE5
<==        Row: T69AD733534236730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:03:33.0, , null, 81DD571F-4768-4557-A77D-EC79DE600741, 81DD571F-4768-4557-A77D-EC79DE600741
<==        Row: T69AD733539766730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:11:03.0, , null, 82AD3624-50E4-413D-A048-7F5D73CA701B, 82AD3624-50E4-413D-A048-7F5D73CA701B
<==        Row: T69AD733534296730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:23:50.0, , null, 834C2A4C-BF2B-42ED-9771-AD527577EA3B, 834C2A4C-BF2B-42ED-9771-AD527577EA3B
<==        Row: T69AD733539666730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:58:41.0, , null, 883DDA21-C6E5-4492-99AB-7F36CE216B92, 883DDA21-C6E5-4492-99AB-7F36CE216B92
<==        Row: T69AD733539676730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:47:34.0, , null, 889CA877-2EAC-4948-A341-D1890B1B3EFF, 889CA877-2EAC-4948-A341-D1890B1B3EFF
<==        Row: T69AD733533946730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:34:53.0, , null, 89A891E3-829F-4CCC-B643-47E53F441613, 89A891E3-829F-4CCC-B643-47E53F441613
<==        Row: T69AD733534486730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:09:25.0, , null, 89B779CD-CC08-40EC-B369-9CDB774765B0, 89B779CD-CC08-40EC-B369-9CDB774765B0
<==        Row: T69AD733534036730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:44:40.0, , null, 8D09C707-3733-4E15-8AE4-8046AEA4E3D2, 8D09C707-3733-4E15-8AE4-8046AEA4E3D2
<==        Row: T69AD733634776730, 116, C10T_油封压装, 6, 产出, D201712030035, P171202A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:23:08.0, , null, 9152D7B3-E601-40B8-8387-0E1A01746579, 9152D7B3-E601-40B8-8387-0E1A01746579
<==        Row: T69AD733534456730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:05:25.0, , null, 92D00E83-ECF3-4794-87DA-4CF4E5E0280D, 92D00E83-ECF3-4794-87DA-4CF4E5E0280D
<==        Row: T69AD733539526730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:23:53.0, , null, 9389207B-6E63-4B47-8DF7-13A3543258F6, 9389207B-6E63-4B47-8DF7-13A3543258F6
<==        Row: T69AD733539916730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:59:44.0, , null, 96A19C4F-AF0C-4052-9C42-9AD829BAD171, 96A19C4F-AF0C-4052-9C42-9AD829BAD171
<==        Row: T69AD733534256730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:11:38.0, , null, 96D9154A-C3F9-46F4-9240-CA097FE67033, 96D9154A-C3F9-46F4-9240-CA097FE67033
<==        Row: T69AD733539576730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:50:12.0, , null, 98F4B7DA-369B-4BC7-82F7-0FA45516E536, 98F4B7DA-369B-4BC7-82F7-0FA45516E536
<==        Row: T69AD733539746730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:11:49.0, , null, 9C00E14C-2C69-4A57-B220-BB37E0EAD005, 9C00E14C-2C69-4A57-B220-BB37E0EAD005
<==        Row: T69AD733539706730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:23:08.0, , null, 9D5B40BA-C268-4E88-8777-39D7DAEADD69, 9D5B40BA-C268-4E88-8777-39D7DAEADD69
<==        Row: T69AD733534336730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:20:34.0, , null, A017DED3-FF52-41C6-BA81-29E42AF6011A, A017DED3-FF52-41C6-BA81-29E42AF6011A
<==        Row: T69AD733534176730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:21:51.0, , null, A0B13828-225B-4A65-BB4A-BE80E30F6937, A0B13828-225B-4A65-BB4A-BE80E30F6937
<==        Row: T69AD733539786730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:41:34.0, , null, A160885D-CC87-483E-89AD-356E60EE3650, A160885D-CC87-483E-89AD-356E60EE3650
<==        Row: T69AD733539596730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:51:50.0, , null, A190EC79-4365-4493-AEB5-E0BBFC3F360E, A190EC79-4365-4493-AEB5-E0BBFC3F360E
<==        Row: T69AD733534126730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:08:47.0, , null, A5EAF9BE-44E8-49DA-ACD2-271728720F35, A5EAF9BE-44E8-49DA-ACD2-271728720F35
<==        Row: T69AD733533976730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:29:38.0, , null, A6CAD892-8F00-41F5-B5DD-0D9577A8CA59, A6CAD892-8F00-41F5-B5DD-0D9577A8CA59
<==        Row: T69AD733539566730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:21:11.0, , null, A77689B5-A504-4E82-9DA3-0156F950B10C, A77689B5-A504-4E82-9DA3-0156F950B10C
<==        Row: T69AD733539846730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:26:05.0, , null, B1562267-2075-456A-A985-B704499E3732, B1562267-2075-456A-A985-B704499E3732
<==        Row: T69AD733539966730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:42:24.0, , null, B252988C-580C-4BD6-87E8-1A7748CC1CE1, B252988C-580C-4BD6-87E8-1A7748CC1CE1
<==        Row: T69AD733539756730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:24:59.0, , null, B86552B5-0099-4738-A147-4ECCBDC00AA3, B86552B5-0099-4738-A147-4ECCBDC00AA3
<==        Row: T69AD733541806730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:19:44.0, , null, B97A738A-F5DC-4708-BDFB-BB8242BFBF7B, B97A738A-F5DC-4708-BDFB-BB8242BFBF7B
<==        Row: T69AD733539426730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:06:02.0, , null, BDFF93B9-8ECA-4F1A-B428-AD3C3A243369, BDFF93B9-8ECA-4F1A-B428-AD3C3A243369
<==        Row: T69AD733634506730, 116, C10T_油封压装, 6, 产出, D201712030035, P171202A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:18:51.0, , null, BEAE319D-6588-4E10-BCC7-B3D84EB79433, BEAE319D-6588-4E10-BCC7-B3D84EB79433
<==        Row: T69AD733634496730, 116, C10T_油封压装, 6, 产出, D201712030035, P171202A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:26:32.0, , null, C24F43F4-FF32-473F-BEC6-9EDF56A9778F, C24F43F4-FF32-473F-BEC6-9EDF56A9778F
<==        Row: T69AD733539696730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:58:23.0, , null, C356C9E2-8FD9-46D5-B627-5D14D59340A5, C356C9E2-8FD9-46D5-B627-5D14D59340A5
<==        Row: T69AD733539656730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:43:40.0, , null, C45ADB6D-01FD-4AD4-8722-463AD2D93CDC, C45ADB6D-01FD-4AD4-8722-463AD2D93CDC
<==        Row: T69AD733539866730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:23:45.0, , null, C46E63D5-C622-4471-8738-F6D8A3CBECE4, C46E63D5-C622-4471-8738-F6D8A3CBECE4
<==        Row: T69AD733534366730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 3, 20171203早班, 吴云焕, 2017-12-03 09:31:48.0, , null, CB7F829A-473B-4E31-917C-4BB335E15411, CB7F829A-473B-4E31-917C-4BB335E15411
<==        Row: T69AD733539826730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:23:46.0, , null, CBA44EDB-AFDD-4E62-9A05-49A0D96B87FC, CBA44EDB-AFDD-4E62-9A05-49A0D96B87FC
<==        Row: T69AD733534446730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:19:50.0, , null, CF48C192-8F91-4238-BE45-046471B48F09, CF48C192-8F91-4238-BE45-046471B48F09
<==        Row: T69AD733539816730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:26:49.0, , null, D674F88F-6BA6-46F3-9A9D-0B8BBDC5FD64, D674F88F-6BA6-46F3-9A9D-0B8BBDC5FD64
<==        Row: T69AD733539646730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:48:10.0, , null, D8680DF0-3DBA-47FC-9E77-B40022A74333, D8680DF0-3DBA-47FC-9E77-B40022A74333
<==        Row: T69AD733534066730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:17:45.0, , null, D96D562B-9AB3-4664-A316-E34976DBE117, D96D562B-9AB3-4664-A316-E34976DBE117
<==        Row: T69AD733541646730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 08:55:41.0, , null, DAF53A81-6D65-4222-B3EE-81D59694C983, DAF53A81-6D65-4222-B3EE-81D59694C983
<==        Row: T69AD733533926730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:55:09.0, , null, DB4E6C7B-04CD-407D-932B-B0C7AC75D768, DB4E6C7B-04CD-407D-932B-B0C7AC75D768
<==        Row: T69AD733534076730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:32:24.0, , null, DB77210F-7A93-44B6-83F2-220AAEFE730E, DB77210F-7A93-44B6-83F2-220AAEFE730E
<==        Row: T69AD733534266730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:24:32.0, , null, DB90B0DD-3CCE-48C6-8F9F-9DC7E644F14A, DB90B0DD-3CCE-48C6-8F9F-9DC7E644F14A
<==        Row: T69AD733541746730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:08:03.0, , null, DC505D8B-957B-497A-A55C-09111C654881, DC505D8B-957B-497A-A55C-09111C654881
<==        Row: T69AD733539446730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:01:11.0, , null, DCF2A5A6-0E5A-45A4-9F89-94C8236822E5, DCF2A5A6-0E5A-45A4-9F89-94C8236822E5
<==        Row: T69AD733634426730, 116, C10T_油封压装, 6, 产出, D201712030035, P171202A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:12:30.0, , null, DE0D59E8-DEB6-4433-A092-DA6A0DE3AF23, DE0D59E8-DEB6-4433-A092-DA6A0DE3AF23
<==        Row: T69AD733541756730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:39:58.0, , null, E170E616-C9CF-4F03-85EC-0567F831E75E, E170E616-C9CF-4F03-85EC-0567F831E75E
<==        Row: T69AD733534056730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:28:19.0, , null, E1D7098C-ED21-4CBE-8139-1C53886A2A8B, E1D7098C-ED21-4CBE-8139-1C53886A2A8B
<==        Row: T69AD733541736730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:12:30.0, , null, E40BC73A-2A70-4DC3-98A0-0A04551ADB16, E40BC73A-2A70-4DC3-98A0-0A04551ADB16
<==        Row: T69AD733634576730, 116, C10T_油封压装, 6, 产出, D201712030035, P171202A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 13:21:08.0, , null, E7F81BF5-178D-4B93-B38F-9BB526E16FD5, E7F81BF5-178D-4B93-B38F-9BB526E16FD5
<==        Row: T69AD733533956730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:57:09.0, , null, E9D95DD9-9E5A-4204-9A94-7F528DA93CE1, E9D95DD9-9E5A-4204-9A94-7F528DA93CE1
<==        Row: T69AD733631296730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:39:53.0, , null, ECB86354-F8F1-4C3C-94F4-A354DE1D1F4F, ECB86354-F8F1-4C3C-94F4-A354DE1D1F4F
<==        Row: T69AD733539436730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 09:07:22.0, , null, F291F389-E0FC-4A05-86B3-FEAFE0EEB949, F291F389-E0FC-4A05-86B3-FEAFE0EEB949
<==        Row: T69AD733533986730, 116, C10T_油封压装, 6, 产出, D201712030035, P171130B-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:00:51.0, , null, F4F17233-3B24-4190-9438-CD88683853F7, F4F17233-3B24-4190-9438-CD88683853F7
<==        Row: T69AD733539776730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 12:22:29.0, , null, F8217553-9C97-48C3-9135-24331DD005D2, F8217553-9C97-48C3-9135-24331DD005D2
<==        Row: T69AD733539556730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:59:17.0, , null, F86EC61B-4BC6-48A5-BE69-E89407481625, F86EC61B-4BC6-48A5-BE69-E89407481625
<==        Row: T69AD733541776730, 116, C10T_油封压装, 6, 产出, D201712030035, P171201A-6730, 10.999.01097, C10T罩盖总成|24106730, 1.000000, 1, 20171203早班, 吴云焕, 2017-12-03 10:44:46.0, , null, FFD08A43-FDCC-482C-A479-A033E331D013, FFD08A43-FDCC-482C-A479-A033E331D013
<==      Total: 109
Resetting autocommit to true on JDBC Connection [ConnectionID:1 ClientConnectionId: 2b56fbbe-a671-445f-8cc9-219adad10206]
Closing JDBC Connection [ConnectionID:1 ClientConnectionId: 2b56fbbe-a671-445f-8cc9-219adad10206]
Returned connection 1436901839 to pool.
time to execute query with mybatis: 24293

as you could see it still took around 24 seconds.

/Ben

尹文才

unread,

Dec 15, 2017, 2:25:55 AM12/15/17

to mybatis-user

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

Mybatis_perf_test.txt

尹文才

unread,

Dec 15, 2017, 7:52:21 AM12/15/17

to mybatis-user

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

Iwao AVE!

unread,

Dec 15, 2017, 10:34:49 AM12/15/17

to mybatis-user

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

--

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+unsubscribe@googlegroups.com.

尹文才

unread,

Dec 15, 2017, 6:24:23 PM12/15/17

to mybatis-user

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

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.

Iwao AVE!

unread,

Dec 16, 2017, 2:11:52 AM12/16/17

to mybatis-user

Hi Ben,

It looks pretty standard, yes.

Based on the information you provided, I created a test project using HSQLDB.

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

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

尹文才

unread,

Dec 16, 2017, 7:59:49 AM12/16/17

to mybatis-user

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

Iwao AVE!

unread,

Dec 16, 2017, 9:57:06 AM12/16/17

to mybatis-user

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

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

尹文才

unread,

Dec 17, 2017, 11:40:42 PM12/17/17

to mybatis-user

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

Guy Rouillier

unread,

Dec 18, 2017, 12:43:46 AM12/18/17

to mybatis-user

I'm wondering if you are encountering slowness in simple string concatenation. I remember reading that doing something like

new String = old String + new token

in a loop is very inefficient, but using a StringBuffer is much faster. I just looked at ForEachSqlNode.java, which invokes DynamicContext.java, which uses StringBuilder, which has the same approach as StringBuffer. So, the code *should* be doing the right thing. But your testing would seem to indicate otherwise. Unfortunately, given the busy holiday season, I don't know if I'll be able to profile this in the next couple days.

Good detective work!

--

Guy Rouillier

------ Original Message ------

From: "尹文才" <batm...@gmail.com>

To: "mybatis-user" <mybati...@googlegroups.com>

尹文才

unread,

Dec 18, 2017, 3:07:57 AM12/18/17

to mybatis-user

Hi Guy, do you mean the apply and appendSql method inside class ForEachSqlNode? I checked the time during debugging my program and found the apply and appendSql method is very fast, so I don't think

the problem is due to the string concatenation problem you mentioned. One thing I noticed while I was watching the mybatis output log is the following lines do not appear all at once but in the latter part it paused

for a while and then some more row log lines were shown, this kind of pause during the rows line logging happened about 2-3 times:

But if I didn't use the foreach operator and instead use the all expanded condition values, these row log lines appear very fast.

It appeared to me that it was still getting data from database(I'm actually not sure what it was really doing in the background)

Regards,

Ben

Iwao AVE!

unread,

Dec 18, 2017, 7:29:56 AM12/18/17

to mybatis-user

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.

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

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

Guy Rouillier

unread,

Dec 18, 2017, 6:47:24 PM12/18/17

to mybatis-user

Ben, yes, I was wondering about the time spent in appendSql. Glad you already checked this out. Sorry, i'm baffled. If you can share some data, I can replicate under PostgreSQL to see if the same thing happens there. Otherwise, I'm out of ideas.

The type of pause you describe is typically attributable to the JDBC driver returning to the server to obtain more data. Data is sent in batches, e.g, maybe 30 rows at a time. So, you'll see those 30 rows processed very fast, then a pause while the drive waits to get the next set of data from the server. Perhaps that is what you are observing. Though, that doesn't explain why this only occurs using foreach, and not if you hardcode the values yourself.

Message has been deleted

尹文才

unread,

Dec 18, 2017, 8:12:58 PM12/18/17

to mybatis-user

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

尹文才

unread,

Dec 18, 2017, 8:14:16 PM12/18/17

to mybatis-user

Thanks guy, would it be convenient to you if I upload my data of both tables onto my google drive and share it to you?

Regards,

Ben

...

Guy Rouillier

unread,

Dec 18, 2017, 10:36:18 PM12/18/17

to mybati...@googlegroups.com

Yes, Ben, that would be very helpful, thanks.

--

尹文才

unread,

Dec 18, 2017, 11:41:30 PM12/18/17

to mybatis-user

Hi Guy & Iwao, I finally decided to use java code to populate some amount of dummy data into the table and only insert the 109 rows with real data that will be the

returned resultset.(Originally I was trying to simply dump all the data and share it, but it's about 1.65 GB and it's not easy to download in a short time, the point in

my problem is in table size, so I think it doesn't matter if the data is real data or some dummy data)

I have uploaded my latest test project onto my google drive, here is the link to get it:

https://drive.google.com/open?id=1xSzT0Nk_D-UyQDziqhmJN_PkvKrJK5_9

I've tried to first insert 200000 dummy rows into the table, then insert the 109 rows real data and finally in the end insert another 200000 rows of dummy data.

During the testing, I found as the size of the table grew, the time it cost also increased. When the table had 400109 rows, it would take around 5-6 secs while

using the expanded parameter list would only require less than 1 second.

I actually found that someone had the same problem as mine in another forum(Sorry, the thread is in Chinese so I skipped the link here), he was currently doing

the same thing as me to work around the problem, which is to concatenate the parameter list in advance and only pass the final parameter list as string to mybatis select

query with the “$” string substitution operator. With this method, it would always took less than 1 second. I hope you guys could find out where the problem lies.

(using the workaround I mentioned is not convenient). Thanks.

Regards,

Ben

...

Iwao AVE!

unread,

Dec 19, 2017, 2:34:19 AM12/19/17

to mybatis-user

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.

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

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

尹文才

unread,

Dec 19, 2017, 2:51:56 AM12/19/17

to mybatis-user

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

Regards,
iwao

/Ben

        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

尹文才

unread,

Dec 19, 2017, 3:48:35 AM12/19/17

to mybatis-user

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

Regards,
iwao

/Ben

        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,

Iwao AVE!

unread,

Dec 19, 2017, 6:32:47 AM12/19/17

to mybatis-user

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.

https://github.com/Microsoft/mssql-jdbc

Regards,

Iwao

--

尹文才

unread,

Dec 19, 2017, 7:47:35 AM12/19/17

to mybatis-user

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

Regards,
Iwao

Regards,
iwao

/Ben

<blockquote class="gmail_quote" style="margin:0px 0

Iwao AVE!

unread,

Dec 19, 2017, 10:26:58 AM12/19/17

to mybatis-user

You are very welcome! Thank you for not giving up. :)

Hope the issue is resolved soon.

Regards,

Iwao

nclemeur

unread,

Dec 19, 2017, 5:27:12 PM12/19/17

to mybatis-user

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

尹文才

unread,

Dec 19, 2017, 5:47:50 PM12/19/17

to mybatis-user

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

wenxing zheng

unread,

Oct 19, 2018, 7:52:43 AM10/19/18

to mybatis-user

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

Thanks, wenxing

Reply all

Reply to author

Forward