Hello,
I had to investigate some performance issues related to queries executed over the table WMCUSTOMPROCESSDATA (table for storing the data for logged fields within a process) and found the following:
• the table has only one index defined: IDX_PROCDATA_STEPINSTANCE on the fields INSTANCEID, INSTANCEID, STEPID, STEPITERATION
• the storage model for logged fields is the following: FIELDNAME – name of the logged field, STRINGVALUE – value of the logged field, DOCUMENTNAME – name of the document from which the fields are logged and INSTANCEID – process instance ID
• all the investigated queries are based mostly on FIELDNAME+STRINGVALUE and DOCUMENTNAME+FIELDVALUE
o for this kind of queries no index is defined and in most cases a full scan is done over the table which is not at all efficient
o I have defined locally two indexes as follows :
→ IDX_DOCFIELDNAME on the fields DOCUMENTNAME, FIELDNAME (in this order)
→ IDX_FILEDNAMEVALUE on the fields FIELDNAME, STRINGVALUE (in this order)
o I have found out that with this additional two indexes no more full scan is done over the table
Concerning the additional indexation of the WMCUSTOMPROCESSDATA table I would like to ask your opinion on the following points:
• Would this two additional indexes change the overall performance of other queries done over the WMCUSTOMPROCESSDATA table (webMehods specific queries)?
• If the WMCUSTOMPROCESSDATA table is used for storing logged fields and perform searches upon them wouldn’t it be necessary to index the table on the fields FIELDNAME and STRINGVALUE or on the fields DOCUMENTNAME, FIELDNAME and STRINGVALUE? (for example for searches done via Administrator UI for logged fields within a processinstance)
Thank you for your help 