none
Inserting nulls or empty Strings

    Question

  • Our standalone Java application inserts records (in bulk) into a MS SQL Server 2008 R2 using Microsoft JDBC4 Driver for SQL Server as driver.

    There is a huge difference in processing time between using empty strings or null values (as the parameter values set on the PreparedStatement).

    Using null values:
    begin import
    flush finished in 63860 ms
    flush finished in 20378 ms
    finished processing table1 in 85957 ms
    total[6280] added[6131] notValid[149]
    end import [completed in 86340 ms]

    Using empty strings:
    begin import
    flush finished in 15460 ms
    flush finished in 3211 ms
    finished processing table1 in 20334 ms
    total[6280] added[6131] notValid[149]
    end import [completed in 20702 ms]

    When I change thedriver to jTDS JDBC Driver there is (almost) no difference anymore in processing time.

    Using null values:
    begin import
    flush finished in 11719 ms
    flush finished in 2606 ms
    finished processing table1 in 16019 ms
    total[6280] added[6131] notValid[149]
    end import [completed in 16099 ms]

    Using empty strings:
    begin import
    flush finished in 18082 ms
    flush finished in 4051 ms
    finished processing table1 in 23786 ms
    total[6280] added[6131] notValid[149]
    end import [completed in 23868 ms]

    Is this a known issue?

    Kind regards,

    Roel

    Thursday, August 02, 2012 8:50 AM

Answers

  • Hi,

    you may file all bugs/improvements/request on the connect website: http://connect.microsoft.com 

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    • Proposed as answer by Iric WenModerator Sunday, August 05, 2012 11:12 AM
    • Marked as answer by hysterio Sunday, August 05, 2012 12:23 PM
    Friday, August 03, 2012 8:40 AM

All replies

  • After some more fine-grained experiments I discovered calling setNull and/or setObject on the PreparedStatement is causing these performance issues.

    Where can I file this bug?

    Thursday, August 02, 2012 11:23 AM
  • A little more investigation revealed: when you provide the sqlType of the column, you have a performance loss of factor 3-4. So don't use [tt]setNull[/tt]-method (you are required to pass the sqlType). The setObject-method has a variant with sqlType and one without sqlType, use the 2-parameter variant (without sqlType).

    Friday, August 03, 2012 8:30 AM
  • Hi,

    you may file all bugs/improvements/request on the connect website: http://connect.microsoft.com 

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    • Proposed as answer by Iric WenModerator Sunday, August 05, 2012 11:12 AM
    • Marked as answer by hysterio Sunday, August 05, 2012 12:23 PM
    Friday, August 03, 2012 8:40 AM
  • This bug has been filed here.
    Friday, August 03, 2012 10:09 AM