[
https://issues.apache.org/jira/browse/JCR-4540?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Reschke closed JCR-4540.
-------------------------------
> MSSQL: Wrong database column type leads to performance issues in query
> ----------------------------------------------------------------------
>
> Key: JCR-4540
> URL: https://issues.apache.org/jira/browse/JCR-4540
> Project: Jackrabbit Content Repository
> Issue Type: Bug
> Affects Versions: 2.18.3
> Reporter: Jörg Wirsig
> Priority: Major
>
> We are using AzureSQL aka MSSQL as database to store Magnolia repositories
> which uses Jackrabbit as JCR Library to store data.
> After facing some performance issues on Magnolia Author an analysis of time
> consumption on queries showed two main affeted queries.
> {code:java}
> (@P0 nvarchar(4000))SELECT LENGTH, LAST_MODIFIED FROM ds_DATASTORE WHERE
> ID=@P0
> ((@P0 nvarchar(4000))SELECT ID, DATA FROM ds_DATASTORE WHERE ID=@P0{code}
> From another database in this project we are using separetly from Magnolia we
> know, that on JDBC Layer there is an auto conversion to nvarchar because
> unicode compatibility.
> We were facing the same performance issues on that database, having a column
> that was of type varchar, as well as the index on that column, while the
> query was converted to nvarchar, which resulted in a full table scan, because
> the index was not taken into account because of the difference in datatype of
> column.
> In our application beside from Magnolia we converted the columns that needed
> to be indexed to type nvarchar to solve the problem.
> We assume the same issue is present on magnolias table _ds_DATASTORE_ where
> the column _ID_ is of type varchar, but should be a nvarchar, to get the
> index working again for that queries.
>
> We created an issue in Magnolia issue tracker recieving this information by
> magnolia support.
> {quote}
> thanks for reporting your findings in this ticket, they sound really
> interesting. However, I'm afraid that database data types are managed by
> jackrabbit implementation in
> {{org.apache.jackrabbit.core.data.db.DbDataStore}} class. Take a look at
> org.apache.jackrabbit.core.data.db.DbDataStore.createTableSQL property which
> is meant to create the DB tables, then if you take a look at the
> corresponding properties file ({{azure.properties}} or {{mysql.properties}})
> you will see that VARCHAR is set for ID column:
> {code:java}
> createTable=CREATE TABLE ${tablePrefix}${table}(ID VARCHAR(255) PRIMARY KEY,
> LENGTH BIGINT, LAST_MODIFIED BIGINT, DATA IMAGE){code}
> This means that Magnolia is not involved in this process and I'm not sure
> whether something can be done from our side or not. For that reason I would
> like to check it internally and I will come back when I have something.
> {quote}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
