Skip to content

[BUG] Performance Issues reading LocalDate,LocalDateTime, and LocalTime #1070

Closed

Description

Driver version

7.2.1.jre8

SQL Server version

Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
Sep 7 2018 01:37:51
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: )

Client Operating System

Ubuntu 16.04

JAVA/JVM version

openjdk version "12" 2019-03-19
OpenJDK Runtime Environment (build 12+33)
OpenJDK 64-Bit Server VM (build 12+33, mixed mode, sharing)

Table schema

CREATE TABLE test.DatePerformance (
	someDate DATE
)

-- Populate Table with Random Dates
INSERT INTO test.DatePerformance
SELECT DATEADD(DAY, -RAND(12313456) * 1000, GETDATE())

WHILE((SELECT COUNT(*) FROM test.DatePerformance) < 2500)
INSERT INTO test.DatePerformance
SELECT DATEADD(DAY, -RAND() * 1000, GETDATE())

Problem description

  1. Expected behaviour:

    • Reading Date objects from the database should have low overhead.
  2. Actual behaviour:

    • There is substantial overhead creating GregorianCalendars and converting them to the LocalDate, LocalDateTime, LocalTime classes
    • Calling Calendar.getInstance(TimeZone.getTimeZone("UTC")) is 30% of the getObject call for LocalDate, LocalDateTime, LocalTime classes
  3. Error message/stack trace:

  4. Any other details that can be helpful:

Sample Profiling Snapshots

	com.microsoft.sqlserver.jdbc.SQLServerResultSet.getObject () 15.094s
>	com.microsoft.sqlserver.jdbc.DDC.convertTemporalToObject () 9.206s
>>	java.util.GregorianCalendar.<init> () 6.01s
>>	java.util.Calendar.getTimeInMillis () 3.005s
>	java.util.Calendar.getInstance () 5.088s

	com.microsoft.sqlserver.jdbc.SQLServerResultSet.getObject () 7.601s
>	com.microsoft.sqlserver.jdbc.DDC.convertTemporalToObject () 3.906s
>>	java.util.GregorianCalendar.<init> () 2.498s
>>	java.util.Calendar.getTimeInMillis () 1.407s
>	java.util.Calendar.getInstance () 3.302s

Reproduction code

    /**
    Using JMH (LocalDate)

    Result: 338.352 ±(99.9%) 27.177 ops/s [Average]
    Statistics: (min, avg, max) = (5.716, 338.352, 435.926), stdev = 115.069
    Confidence interval (99.9%): [311.175, 365.529]


    # Run complete. Total time: 00:08:10

    Benchmark                        Mode  Samples    Score  Score error  Units
    c.c.m.MyBenchmark.testMethod    thrpt      200  338.352       27.177  ops/s
    */

    @Benchmark
    public void testMethod()
    {
        List<LocalDate> dates = new ArrayList<>();

        try (
                Connection conn = dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement("SELECT someDate from test.DatePerformance");
                ResultSet rs = ps.executeQuery()
        )
        {
            while(rs.next())
                dates.add(rs.getObject("someDate", LocalDate.class));

        } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Assignees

Labels

EnhancementAn enhancement to the driver. Lower priority than bugs.

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions