Bad performance of Transactional metadata queries with big ALF_NODE_PROPERTIES tables

[myamcclure]

[MNT-13897] Bad performance of Transactional metadata queries with big ALF_NODE_PROPERTIES tables. -... 

We have the same issue as the above link.  Any Hotfix or resolution available to above thread?Our database platform is SQL server and SQL have 900 bytes limitation . String_value is nvarchar(1024). Thank you.

[afaust]

TMQs have various potential performance issues depending on data distribution and DB being used. There should generally be no need for an additional CRC column to improve performance. In MS SQL, a filtered index could just as well be used to only index the rows with string values. A CRC column would also not help with any LIKE-based queries.

The biggest problem with MS SQL and TMQ that I have observed at a customer with several million documents is that the query optimizer stupidly reuses query plans for identical SQL queries even if the parameters differ significantly. This can cause extremely slow queries when the statistics / cost assumptions turn out to be incorrect. For my customer it meant that a simple TMQ query that should always only match one single document was essentially doing a table scan because query plan was based on the assumption that several thousand documents may match (determined based on a previous query with different parameters). We ended up having to modify the SQL emitted by Alfresco to include an "OPTION (RECOMPILE)" query hint so MS SQL would never reuse query plans for TMQs.

At BeeCon 2017 I did a talk on proper use / performance of transactional metadata queries that talks about some of the issues, especially noting that the enterprise DBs are generally the worst when it comes to proper performance. (Slides / Video)

[myamcclure]

Thank you Axel for your answer.

I will add a filtered index.  in our environment, alf_node_properties table has 1.5 billion rows and 650 million rows has data in string_value table. The maximum actual data length in String_value column is 290. The index size for qname_id and string_value is 2056 Byes which exceeds SQL server index size limitation. I have tested that the index improves the above query. MS server throws warning for the index Warning! The maximum key length is 900 bytes. The index 'XXX' has maximum length of 2056 bytes. For some combination of large values, the insert/update operation will fail. 

The index is created because current data in the table for the index is within SQL limit. Do you recommend it is ok to add the index with the above warning? Thank you so much!

[afaust]

I cannot claim to be an expert on MS SQL - I only have to deal with it when customers refuse to use better alternatives. For that reason I cannot give a recommendation on whether it would be ok to continue with that warning. Most of my current customers with MS SQL use 2016, so the 900 bytes restriction is no longer a problem for them.

[myamcclure]

Thank you Axel for your answer. I understood . SQL 2016 has a limitation as well but it is 1700 bytes.  What is stored in string_Value column? The column is nvarchar 1024. the max actual data byte in the column is 291 bytes. Most of the rows has white space or null.  Thanks again.

[afaust]

string_value will store any textual property value (d:text / d:mltext) as long as it fits in the column, as well as any non-text data which can be converted into a textual representation, i.e. d:date, d:datetime, d:noderef .... Only properties that are of primitive numeric / boolean types, that contain values which can only be serialized, which have a textual value/representation longer as the column size, or have been explicitly set to NULL will have the column value set to NULL.