[OAK-8908] RDBBlobStore on SQL Server: bad performance when default collation is of type SQL*

• Type: Technical task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: rdbmk
• Assignee: Unassigned
• Reporter: Julian Reschke
• Created: 14/Feb/20 14:35
• Updated: 26/May/21 14:52

Description

RDBBlobStore uses a 64-char primary key (digest in hex).

Unfortunately, this causes performance issues on MS SQL Server, when the collation for that column is of type "SQL*" (see links). These types of collations are deprecated, but still the default for installations on the "EN_US" locale.

The performance loss can be observed by changing the collation on an existing install, and then enable performance logging on RDBBlobStore.

Issue Links

• is related to: OAK-8963 RDBBlobStore: on SQL Server, specify binary collations upon table creation
• is related to: OAK-8966 RDBBlobStore: warn when ID columns are using legacy "SQL Server collation"
• is related to: OAK-8915 RDBDocumentStore: use setNString() for columns known to be of type N*CHAR
• is related to: OAK-8918 RDBBlobStore: warn when legacy (SQLServer) default collation is active
• links to: Microsoft SQL Docs: SQL Server Collation Name
• links to: Sql Quantum Leap: Impact on Indexes When Mixing VARCHAR and NVARCHAR Types