i2b2 Sponsored Project/s:
Currently when prepared statements are used to pass in string based variables, the parameters are sent in the SQL statement as Unicode (nvarchar) instead of varchar. The SQL Server then has to explicitly convert the nvarchar to varchar in the query. The effect is that the SQL Server optimizer may have to use an index scan instead of an index seek, which is the faster of the two.
The solution is to add a new connection parameter to the datasource definition. The value defined for this new connection parameter will determine whether the parameters with string based variables are sent as nvarchar or varchar.
Parameter: <connection-property name="sendStringParametersAsUnicode">
Located in:
Behavior:
Value = ‘false’ the parameter will be sent as varchar
Value = ‘true’ the parameter will continue to be sent as Unicode (‘nvarchar’)
If the parameter is missing the system will continue with the current logic.
IMPORTANT: This change only affects SQL Server databases.
{"report":{"fcp":729.3999999761581,"ttfb":71,"pageVisibility":"visible","entityId":10868,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":16,"apdex":1,"journeyId":"9e128933-0d01-4507-94be-10110c728a85","navigationType":0,"readyForUser":780,"redirectCount":0,"resourceLoadedEnd":988,"resourceLoadedStart":76.19999998807907,"resourceTiming":[{"duration":334,"initiatorType":"link","name":"https://community.i2b2.org/jira/s/7fd763ecdf5ed1f47fc4d22fa8382e97-CDN/4x9nqn/820011/16zrvj4/49fa3aa3d35a2cc689cbf274e66cc41a/_/download/contextbatch/css/_super/batch.css","startTime":76.19999998807907,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":76.19999998807907,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":410.19999998807907,"responseStart":0,"secureConnectionStart":0},{"duration":334.7999999523163,"initiatorType":"link","name":"https://community.i2b2.org/jira/s/898b8076c5e82f53cd816ca393e45a56-CDN/4x9nqn/820011/16zrvj4/f614b50eeb842ebd1ea7ab2903699907/_/download/contextbatch/css/project.issue.navigator,jira.view.issue,jira.global,atl.general,-_super/batch.css?jira.create.linked.issue=true&richediton=true","startTime":76.60000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":76.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":411.39999997615814,"responseStart":0,"secureConnectionStart":0},{"duration":377.30000001192093,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/4c9c503fe98f210445831b0d7b0cdc33-CDN/4x9nqn/820011/16zrvj4/49fa3aa3d35a2cc689cbf274e66cc41a/_/download/contextbatch/js/_super/batch.js?locale=en-US","startTime":76.69999998807907,"connectEnd":76.69999998807907,"connectStart":76.69999998807907,"domainLookupEnd":76.69999998807907,"domainLookupStart":76.69999998807907,"fetchStart":76.69999998807907,"redirectEnd":0,"redirectStart":0,"requestStart":76.69999998807907,"responseEnd":454,"responseStart":454,"secureConnectionStart":76.69999998807907},{"duration":390.0999999642372,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/694e598c1ae48b0f96655173631cd247-CDN/4x9nqn/820011/16zrvj4/f614b50eeb842ebd1ea7ab2903699907/_/download/contextbatch/js/project.issue.navigator,jira.view.issue,jira.global,atl.general,-_super/batch.js?jira.create.linked.issue=true&locale=en-US&richediton=true","startTime":76.80000001192093,"connectEnd":76.80000001192093,"connectStart":76.80000001192093,"domainLookupEnd":76.80000001192093,"domainLookupStart":76.80000001192093,"fetchStart":76.80000001192093,"redirectEnd":0,"redirectStart":0,"requestStart":76.80000001192093,"responseEnd":466.89999997615814,"responseStart":466.89999997615814,"secureConnectionStart":76.80000001192093},{"duration":391.80000001192093,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/53f54e0ac3f00bb56b136b4d2fff2853-CDN/4x9nqn/820011/16zrvj4/aae1242f5fc81cc6a5bb8bc963ccda29/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en-US","startTime":76.89999997615814,"connectEnd":76.89999997615814,"connectStart":76.89999997615814,"domainLookupEnd":76.89999997615814,"domainLookupStart":76.89999997615814,"fetchStart":76.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":76.89999997615814,"responseEnd":468.69999998807907,"responseStart":468.69999998807907,"secureConnectionStart":76.89999997615814},{"duration":392.2000000476837,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/d41d8cd98f00b204e9800998ecf8427e-CDN/4x9nqn/820011/16zrvj4/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":76.89999997615814,"connectEnd":76.89999997615814,"connectStart":76.89999997615814,"domainLookupEnd":76.89999997615814,"domainLookupStart":76.89999997615814,"fetchStart":76.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":76.89999997615814,"responseEnd":469.10000002384186,"responseStart":469.10000002384186,"secureConnectionStart":76.89999997615814},{"duration":393,"initiatorType":"link","name":"https://community.i2b2.org/jira/s/30748292e4ca68be6947d7969829384b-CDN/4x9nqn/820011/16zrvj4/4f66da484ef7d95a2a604d3ab014374c/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":77,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":77,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":470,"responseStart":0,"secureConnectionStart":0},{"duration":392.5,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/d41d8cd98f00b204e9800998ecf8427e-CDN/4x9nqn/820011/16zrvj4/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":77,"connectEnd":77,"connectStart":77,"domainLookupEnd":77,"domainLookupStart":77,"fetchStart":77,"redirectEnd":0,"redirectStart":0,"requestStart":77,"responseEnd":469.5,"responseStart":469.5,"secureConnectionStart":77},{"duration":392.89999997615814,"initiatorType":"script","name":"https://community.i2b2.org/jira/rest/api/1.0/shortcuts/820011/5c69e534a30b62def7b98bda0f001346/shortcuts.js?context=issuenavigation&context=issueaction","startTime":77.10000002384186,"connectEnd":77.10000002384186,"connectStart":77.10000002384186,"domainLookupEnd":77.10000002384186,"domainLookupStart":77.10000002384186,"fetchStart":77.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":77.10000002384186,"responseEnd":470,"responseStart":470,"secureConnectionStart":77.10000002384186},{"duration":369,"initiatorType":"link","name":"https://community.i2b2.org/jira/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/4x9nqn/820011/16zrvj4/efa42a25652b26dfd802540c024826b3/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-jira.view.issue,-project.issue.navigator/batch.css?jira.create.linked.issue=true&richediton=true","startTime":101.39999997615814,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":101.39999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":470.39999997615814,"responseStart":0,"secureConnectionStart":0},{"duration":368.89999997615814,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/8087506fefd02b4096991c90836b49f6-CDN/4x9nqn/820011/16zrvj4/efa42a25652b26dfd802540c024826b3/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-jira.view.issue,-project.issue.navigator/batch.js?jira.create.linked.issue=true&locale=en-US&richediton=true","startTime":101.60000002384186,"connectEnd":101.60000002384186,"connectStart":101.60000002384186,"domainLookupEnd":101.60000002384186,"domainLookupStart":101.60000002384186,"fetchStart":101.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":101.60000002384186,"responseEnd":470.5,"responseStart":470.5,"secureConnectionStart":101.60000002384186},{"duration":595.1000000238419,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/d41d8cd98f00b204e9800998ecf8427e-CDN/4x9nqn/820011/16zrvj4/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":108,"connectEnd":108,"connectStart":108,"domainLookupEnd":108,"domainLookupStart":108,"fetchStart":108,"redirectEnd":0,"redirectStart":0,"requestStart":108,"responseEnd":703.1000000238419,"responseStart":703.1000000238419,"secureConnectionStart":108},{"duration":874,"initiatorType":"script","name":"https://community.i2b2.org/jira/s/d41d8cd98f00b204e9800998ecf8427e-CDN/4x9nqn/820011/16zrvj4/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":114,"connectEnd":114,"connectStart":114,"domainLookupEnd":114,"domainLookupStart":114,"fetchStart":114,"redirectEnd":0,"redirectStart":0,"requestStart":114,"responseEnd":988,"responseStart":988,"secureConnectionStart":114},{"duration":368.5,"initiatorType":"xmlhttprequest","name":"https://community.i2b2.org/jira/rest/webResources/1.0/resources","startTime":652.6999999880791,"connectEnd":1001.8999999761581,"connectStart":988.8000000119209,"domainLookupEnd":652.6999999880791,"domainLookupStart":652.6999999880791,"fetchStart":652.6999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":1001.8999999761581,"responseEnd":1021.1999999880791,"responseStart":1020.5,"secureConnectionStart":988.8000000119209}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":16,"connectEnd":44,"secureConnectionStart":30,"requestStart":44,"responseStart":71,"responseEnd":114,"domLoading":75,"domInteractive":1022,"domContentLoadedEventStart":1022,"domContentLoadedEventEnd":1061,"domComplete":1585,"loadEventStart":1585,"loadEventEnd":1585,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":991.6000000238419},{"name":"bigPipe.sidebar-id.end","time":992.3999999761581},{"name":"bigPipe.activity-panel-pipe-id.start","time":992.6000000238419},{"name":"bigPipe.activity-panel-pipe-id.end","time":993.3999999761581},{"name":"activityTabFullyLoaded","time":1079.6000000238419}],"measures":[],"correlationId":"3b7b6d38116e49","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":55,"dbReadsTimeInMs":7,"dbConnsTimeInMs":9,"applicationHash":"0629dd8d260e3954ece49053e565d01dabe11609","experiments":[]}}