What is the most efficient way to query for document re-indexing?

[Corey_Gillingha]

Hi,

I have been asked to create a report to show the total number of documents that have been re-indexed from a particular doc type (i.e. count the number of documents that were originally doc type A but has been re-indexed as another doc type). 

I created the following SQL but it takes some time to run:

select xlog.*
from HSI.TRANSACTIONXLOG xlog
where xlog.action = 1036
and xlog.actionnum = 4
and xlog.subactionnum = 4
and xlog.tmessage like '%Old Doc Type%'
and xlog.logdate between '2023-04-01' and '2023-04-30'

I suspect the reason it is long running is due to the size of the transactionxlog table and the fact that I have to use the like clause when trying to identify documents that were re-indexed from the "old" doc type.

Does anyone have suggestions on how to do this in a more efficient manner?

Thank you,

Corey

[Joe_Barnie · Accepted solution]

Hi Corey,

You are correct that the like clause is adding additional time to return results, but the TransactionXLog table is very large and you are not utilizing the indexes. I would suggest that you try to convince Hyland to create a clustered index on the logdate and tmessage columns. I had this done for a client and it decreased the time for the query from well over 2 minutes to under 10 seconds. The downside is that new index will probably require Gb of space on your SQL server depending on how large your DB and TransactionXLog table is.

[Joe_Barnie · Accepted solution]

Hi Corey,

You are correct that the like clause is adding additional time to return results, but the TransactionXLog table is very large and you are not utilizing the indexes. I would suggest that you try to convince Hyland to create a clustered index on the logdate and tmessage columns. I had this done for a client and it decreased the time for the query from well over 2 minutes to under 10 seconds. The downside is that new index will probably require Gb of space on your SQL server depending on how large your DB and TransactionXLog table is.

[Michael_Reindel · Accepted solution]

Hi Corey,

In addition to Joe's suggestion about adding indexes, which is the correct path, I would suggest removing xlog.action from your where clause. That column is a bitwise value so the actual value is a summation of various bit flags, and was only used for backward compatibility around 3.9-5.2. Actionnum and subactionum are INTs and are the columns you want to use. 

You may also want to use the count() function or specific columns like itemnum and tmessage to provide the document count instead of returning all columns in the table.

Mike

[David_Juhlin]

Be careful using the itemnum column.  In my experience, that is NOT indexed.  However, if you use the num column instead, it has the same data as the itemnum column and it IS indexed.

[Michael_Reindel]

itemnum is indexed in the transactionxlog3 index