Uploaded image for project: 'Jackrabbit Content Repository'
  1. Jackrabbit Content Repository
  2. JCR-4540

MSSQL: Wrong database column type leads to performance issues in query

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Incomplete
    • 2.18.3
    • None
    • None
    • None

    Description

      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. 

      (@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

      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.

      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:

      createTable=CREATE TABLE ${tablePrefix}${table}(ID VARCHAR(255) PRIMARY KEY, LENGTH BIGINT, LAST_MODIFIED BIGINT, DATA IMAGE)

      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.

      Attachments

        Activity

          People

            Unassigned Unassigned
            jwirsig Jörg Wirsig
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: