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

["Solomon Rutzky (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OAK-8908?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17059698#comment-17059698 ] 

Solomon Rutzky commented on OAK-8908:
-------------------------------------

Hi there. If this fix is to _not_ send a Unicode string if the column's datatype is not Unicode, then certainly this is an improvement. However, there are still two better options for improving performance in these scenarios:
 # Ideally, if the data is truly hex / binary data, then it should be stored in a {{BINARY}} / {{VARBINARY}} datatype instead of a string datatype. If the value is a consistent length/size, then the datatype should be {{BINARY(32)}}, else it should be {{VARBINARY(32)}}. This has two benefits:
 ## it cuts the storage size in half (which benefits performance, especially as the number of rows increases)
 ## comparison (i.e. filtering) and sorting operations are much faster (why suffer through culture-specific linguistic rules that will never be needed?)
 # If, for some reason, a binary datatype cannot be used (perhaps not all supported DB options offer a binary datatype), then at the very least a binary collation should be used. This has the following benefit: comparison (i.e. filtering) and sorting operations are much faster (why suffer through culture-specific linguistic rules that will never be needed?). The {{CREATE TABLE}} statements should be updated to include a {{COLLATE collation_name}} clause on the {{ID}} column so that the collation of the database is irrelevant. The particular binary collation to use depends on the minimum version of SQL Server that this project supports:
 ## For SQL Server 2000 and newer, use {{Latin1_General_BIN}} (_since you are only storing hex values, this option is probably fine_)
 ## For SQL Server 2005 and newer, use {{Latin1_General_BIN2}}
 ## For SQL Server 2008 and newer, use {{Latin1_General_100_BIN2}}

 

Take care,
 Solomon...
 [https://SqlQuantumLift.com/|https://sqlquantumlift.com/]
 [https://SqlQuantumLeap.com/|https://sqlquantumleap.com/]
 [https://SQLsharp.com/|https://sqlsharp.com/]

> RDBBlobStore on SQL Server: bad performance when default collation is of type SQL*
> ----------------------------------------------------------------------------------
>
>                 Key: OAK-8908
>                 URL: https://issues.apache.org/jira/browse/OAK-8908
>             Project: Jackrabbit Oak
>          Issue Type: Technical task
>          Components: rdbmk
>            Reporter: Julian Reschke
>            Assignee: Julian Reschke
>            Priority: Major
>             Fix For: 1.28.0
>
>         Attachments: OAK-8908-1.6.diff, OAK-8908.diff
>
>
> 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.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)