Jörg Wirsig created JCR-4540:
--------------------------------
Summary: 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
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.3.4#803005)
