A database driver sits on the critical path of many applications. When a frequently executed path performs unnecessary work, even a small amount of overhead can compound across millions of operations.<\/p>\n
Over the past several releases, we have systematically profiled frequently executed paths across the Microsoft JDBC Driver for SQL Server, identifying opportunities to improve efficiency without changing application behavior. What began with ResultSet processing expanded into bulk copy operations, parameter metadata, Always Encrypted, and authentication.<\/p>\n
The work followed a consistent principle: measure where the cost is, understand why it exists, and remove unnecessary work while preserving correctness and compatibility.<\/strong><\/p>\n We focused on several critical execution paths:<\/p>\n The investigation began with customer workloads returning large, sparse result sets containing a mix of SQL Server data types. Profiling showed that network I\/O was often not the primary bottleneck. Instead, significant cost came from allocations, temporary objects, and repeated work while materializing values from the TDS stream.<\/p>\n The JDBC driver uses lazy decoding, keeping values in their wire representation until applications request them through getter methods. This model supports adaptive buffering, streaming access, Always Encrypted, server cursors, and updatable result sets. Rather than redesigning this architecture, we focused on shortening the existing hot paths and eliminating work that was not necessary to produce the requested value.<\/p>\n Together, these changes shorten common ResultSet execution paths by reducing unnecessary decoding, intermediate representations, and transient allocations, while preserving the existing lazy-decoding model and application-visible behavior.<\/p>\n Customer workloads and community contributions highlighted similar opportunities in bulk-copy-based batch inserts. The focus here was broader than reducing conversion overhead: we wanted to give applications more control over bulk execution, keep eligible operations on the bulk-copy path, and avoid unnecessary transformations before values reach SQL Server.<\/p>\n For eligibility requirements, known limitations, and supported data types, see Using bulk copy API for batch insert operation<\/a>.<\/li>\n Together, these changes make bulk-copy-based batch inserts more configurable, keep more eligible workloads on the bulk execution path, and reduce unnecessary conversion work while preserving the <\/i>For Spark workloads:<\/strong><\/p>Applications focused on high-throughput ingestion can evaluate increasing the JDBC Parameter metadata can influence SQL Server query optimization. When applications do not provide parameter sizing information, the driver may generate parameter declarations that are broader than the application\u2019s actual data model, which can affect plan selection and execution efficiency.<\/p>\n Explicit parameter sizing:<\/strong> Applications can provide expected parameter metadata through Define parameter metadata, then set the value:<\/strong><\/p>\n Set the value with an expected-length hint:<\/strong><\/p>\n These APIs give applications finer control over parameter declarations while preserving the existing A community investigation identified repeated key-provider work in enclave-enabled Always Encrypted scenarios. Profiling showed that, unlike non-enclave encrypted operations, enclave-enabled paths could perform additional provider lookups even when reusable key material was already available.<\/p>\n Profiling of highly concurrent workloads also identified contention during service-principal token acquisition. Requests that only needed a valid cached token could still encounter synchronization associated with token refresh coordination.<\/p>\n An upcoming release will optimize this path by checking the token cache before entering refresh synchronization.<\/strong> When a valid cached token is available, the request can return it without waiting for refresh coordination. The update will also narrow the scope of refresh coordination so that unrelated authentication operations are less likely to block one another.<\/p>\n This change is intended to reduce unnecessary synchronization in highly concurrent authentication workloads while preserving existing token acquisition and refresh semantics.<\/p>\n Across the driver, these improvements follow a consistent engineering pattern: profile real workloads, identify where unnecessary work occurs, and remove it without changing application-visible behavior.<\/p>\n The same approach appears at different layers of the driver: detecting NULL values before decoder initialization, avoiding unnecessary intermediate representations, keeping eligible operations on the bulk-copy path, reusing cached key material, and avoiding synchronization when a valid authentication token is already available.<\/p>\n The broader lesson is that driver performance does not always require a new algorithm or architectural redesign. Significant gains can come from understanding existing execution paths deeply enough to ensure that each path performs only the work required to produce the correct result.<\/p>\n The latest Microsoft JDBC Driver for SQL Server is available through Maven Central as Customer workloads, profiling traces, and community contributions shaped many of these optimizations and continue to guide future work. The effort does not end here; we are continuing to explore additional opportunities, including memory-segment APIs and further bulk-copy improvements.<\/p>\n The following pull requests provide additional implementation details for the optimizations discussed in this article.<\/p>\n A database driver sits on the critical path of many applications. When a frequently executed path performs unnecessary work, even a small amount of overhead can compound across millions of operations. Over the past several releases, we have systematically profiled frequently executed paths across the Microsoft JDBC Driver for SQL Server, identifying opportunities to improve […]<\/p>\n","protected":false},"author":195730,"featured_media":227205,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[844,1],"tags":[248,856,855,857,860],"class_list":["post-233210","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure","category-java","tag-java","tag-jdbc","tag-mssql-jdbc-driver","tag-performance-engineering","tag-sql-server"],"acf":[],"blog_post_summary":" A database driver sits on the critical path of many applications. When a frequently executed path performs unnecessary work, even a small amount of overhead can compound across millions of operations. Over the past several releases, we have systematically profiled frequently executed paths across the Microsoft JDBC Driver for SQL Server, identifying opportunities to improve […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/java\/wp-json\/wp\/v2\/posts\/233210","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/java\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/java\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/java\/wp-json\/wp\/v2\/users\/195730"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/java\/wp-json\/wp\/v2\/comments?post=233210"}],"version-history":[{"count":2,"href":"https:\/\/devblogs.microsoft.com\/java\/wp-json\/wp\/v2\/posts\/233210\/revisions"}],"predecessor-version":[{"id":233213,"href":"https:\/\/devblogs.microsoft.com\/java\/wp-json\/wp\/v2\/posts\/233210\/revisions\/233213"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/java\/wp-json\/wp\/v2\/media\/227205"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/java\/wp-json\/wp\/v2\/media?parent=233210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/java\/wp-json\/wp\/v2\/categories?post=233210"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/java\/wp-json\/wp\/v2\/tags?post=233210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n
Faster ResultSet Processing<\/strong><\/h3>\n
\n
getString()<\/code> path through reusable buffers and more direct value construction while preserving existing behavior for large objects, streaming access, and adaptive buffering.<\/li>\nMore Efficient Bulk Copy<\/strong><\/h3>\n
\n
useBulkCopyForBatchInsert=true<\/code>. We extended this capability with connection properties that expose relevant Bulk Copy API options while allowing applications to continue using the standard PreparedStatement<\/code> programming model. This gives applications control over behaviors such as constraint checking, triggers, identity handling, NULL handling, table locking, encrypted-value modifications, and batch size without requiring changes to the application-level insert API.\nbulkCopyForBatchInsertAllowEncryptedValueModifications=true\r\nbulkCopyForBatchInsertCheckConstraints=true\r\nbulkCopyForBatchInsertFireTriggers=true\r\nbulkCopyForBatchInsertKeepIdentity=true\r\nbulkCopyForBatchInsertKeepNulls=true\r\nbulkCopyForBatchInsertTableLock=true\r\nbulkCopyForBatchInsertBatchSize=<application-defined size>\r\n<\/code><\/span><\/pre>\nsendStringParametersAsUnicode=false<\/code>, including character-encoding issues with accented characters. The updated implementation keeps values in their string representation until the final destination-encoding stage, avoiding redundant transformations while preserving the expected encoding behavior.<\/li>\n<\/ul>\nPreparedStatement<\/code> programming model.<\/p>\npacketSize<\/code> to 32767<\/code> to reduce network-transfer overhead. Validate the setting with representative workloads, as the impact depends on network conditions, batch size, partitioning, and the overall deployment environment. <\/div><\/span><\/p>\nBetter Parameter Metadata<\/strong><\/h3>\n
defineParameterType()<\/code> or the length-aware setObject()<\/code> overload. Both APIs allow applications to communicate domain-specific sizing information without requiring additional metadata round trips.<\/p>\npstmt.defineParameterType(parameterIndex, Types.VARCHAR, expectedLength);\r\npstmt.setString(parameterIndex, value);\r\n<\/code><\/span><\/pre>\npstmt.setObject(parameterIndex, value, Types.VARCHAR, expectedLength);\r\n<\/code><\/span><\/pre>\ndefineParameterType()<\/code> associates the SQL type and expected length with the prepared-statement parameter, allowing the metadata to be reused across subsequent executions and batch operations. The scaleOrLength<\/code> argument of setObject()<\/code> provides the expected-length hint at the point where the value is bound. For character and binary parameters, this hint influences the generated parameter declaration rather than limiting the value itself.<\/p>\nPreparedStatement<\/code> programming model. The metadata also remains compatible with the driver’s existing Always Encrypted requirements, allowing parameter sizing improvements without introducing additional metadata lookups or changing application behavior. For the API details, see the <\/span>setObject method documentation<\/span><\/a> on Microsoft Learn.<\/span>\u00a0<\/span><\/p>\nImproving Always Encrypted Performance<\/strong><\/h3>\n
\n
Reducing Authentication Contention<\/strong><\/h3>\n
A Common Engineering Pattern<\/strong><\/h3>\n
Availability and Further Work<\/strong><\/h3>\n
com.microsoft.sqlserver:mssql-jdbc<\/code>. Review the driver documentation for workload-specific configuration options, and report reproducible issues or performance findings through the <\/span>Microsoft JDBC Driver for SQL Server GitHub repository<\/span><\/a>.<\/span><\/p>\nFurther Reading<\/strong><\/h3>\n
\n\n
\n \nArea<\/th>\n Pull request<\/th>\n Description<\/th>\n<\/tr>\n<\/thead>\n \n ResultSet processing<\/strong><\/td>\n PR #2600<\/a><\/td>\n Early return for NULL values<\/td>\n<\/tr>\n \n <\/td>\n PR #2974<\/a><\/td>\n ResultSet read-path optimizations<\/td>\n<\/tr>\n \n <\/td>\n PR #2975<\/a><\/td>\n Decimal and numeric decoder optimization<\/td>\n<\/tr>\n \n <\/td>\n PR #2991<\/a><\/td>\n Additional decoder and buffer optimizations<\/td>\n<\/tr>\n \n <\/td>\n PR #2955<\/a><\/td>\n Fine-grained logging optimization<\/td>\n<\/tr>\n \n Bulk copy<\/strong><\/td>\n PR #2555<\/a><\/td>\n Expose bulk-copy batch properties<\/td>\n<\/tr>\n \n <\/td>\n PR #2670<\/a><\/td>\n Keep supported values on the bulk path<\/td>\n<\/tr>\n \n <\/td>\n PR #2735<\/a><\/td>\n Preserve string representation through bulk copy<\/td>\n<\/tr>\n \n Parameter metadata<\/strong><\/td>\n PR #2960<\/a><\/td>\n Add defineParameterType()<\/code> support<\/td>\n<\/tr>\n\n <\/td>\n PR #3026<\/a><\/td>\n Support parameter-length hints through setObject()<\/code><\/td>\n<\/tr>\n\n Always Encrypted<\/strong><\/td>\n PR #2964<\/a><\/td>\n Route enclave key lookup through the symmetric-key cache<\/td>\n<\/tr>\n \n Authentication<\/strong><\/td>\n PR #2982<\/a><\/td>\n Check token cache before synchronization<\/td>\n<\/tr>\n \n <\/td>\n PR #2983<\/a><\/td>\n Partition authentication synchronization<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"