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
-
Expected behaviour:
- Reading Date objects from the database should have low overhead.
-
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
-
Error message/stack trace:
-
Any other details that can be helpful:
- Result Set - Calendar.getInstance https://github.com/microsoft/mssql-jdbc/blob/v7.2.1/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerResultSet.java#L2384-L2398
- DDC.convertTemporalToObject - https://github.com/microsoft/mssql-jdbc/blob/v7.2.1/src/main/java/com/microsoft/sqlserver/jdbc/DDC.java#L755-L1061
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
