Improve Task- and CaseQuery performance on LTS 10 by adding indices

Alexander Suter

For LTS-12 we have introduced some new indices in the system database to improve the performance for task and case queries. If you like, you can add them on LTS 10. When migrating to LTS 12, there are no migration problems, because we check if the index already exists:

Postgresql

CREATE INDEX IF NOT EXISTS IWA_CaseLocalized_CaseIdLanguageIdNameIndex ON IWA_CaseLocalized (CaseId, LanguageId, Name varchar_pattern_ops);
CREATE INDEX IF NOT EXISTS IWA_CaseLocalized_LanguageIdNameCaseIdIndex ON IWA_CaseLocalized (LanguageId, Name varchar_pattern_ops, CaseId);
CREATE INDEX IF NOT EXISTS IWA_Task_TaskIdCaseIdIndex ON IWA_Task (TaskId, CaseId);
CREATE INDEX IF NOT EXISTS IWA_Task_CaseIdTaskIdIndex ON IWA_Task (CaseId, TaskId);
CREATE INDEX IF NOT EXISTS IWA_Task_TaskIdBusinessCaseIdIndex ON IWA_Task (TaskId, BusinessCaseId);
CREATE INDEX IF NOT EXISTS IWA_Task_BusinessCaseIdTaskIdIndex ON IWA_Task (BusinessCaseId, TaskId);
CREATE INDEX IF NOT EXISTS IWA_TaskLocalized_TaskIdLanguageIdNameIndex ON IWA_TaskLocalized (TaskId, LanguageId, Name varchar_pattern_ops);
CREATE INDEX IF NOT EXISTS IWA_TaskLocalized_LanguageIdNameTaskIdIndex ON IWA_TaskLocalized (LanguageId, Name varchar_pattern_ops, TaskId);
CREATE INDEX IF NOT EXISTS IWA_TaskCstmStringFld_TaskIdNameValueIndex ON IWA_TaskCustomStringField (TaskId, Name varchar_pattern_ops, "Value" varchar_pattern_ops);
CREATE INDEX IF NOT EXISTS IWA_TaskCstmStringFld_NameValueTaskIdIndex ON IWA_TaskCustomStringField (Name varchar_pattern_ops, "Value" varchar_pattern_ops, TaskId);
CREATE INDEX IF NOT EXISTS IWA_TaskCstmNumberFld_TaskIdNameValueIndex ON IWA_TaskCustomNumberField (TaskId, Name varchar_pattern_ops, "Value");
CREATE INDEX IF NOT EXISTS IWA_TaskCstmNumberFld_NameValueTaskIdIndex ON IWA_TaskCustomNumberField (Name varchar_pattern_ops, "Value", TaskId);
CREATE INDEX IF NOT EXISTS IWA_TaskCstmTimestampFld_TaskIdNameValueIndex ON IWA_TaskCustomTimestampField (TaskId, Name varchar_pattern_ops, "Value");
CREATE INDEX IF NOT EXISTS IWA_TaskCstmTimestampFld_NameValueTaskIdIndex ON IWA_TaskCustomTimestampField (Name varchar_pattern_ops, "Value", TaskId);
CREATE INDEX IF NOT EXISTS IWA_CaseCstmStringFld_CaseIdNameValueIndex ON IWA_CaseCustomStringField (CaseId, Name varchar_pattern_ops, "Value" varchar_pattern_ops);
CREATE INDEX IF NOT EXISTS IWA_CaseCstmStringFld_NameValueCaseIdIndex ON IWA_CaseCustomStringField (Name varchar_pattern_ops, "Value" varchar_pattern_ops, CaseId);
CREATE INDEX IF NOT EXISTS IWA_CaseCstmNumberFld_CaseIdNameValueIndex ON IWA_CaseCustomNumberField (CaseId, Name varchar_pattern_ops, "Value");
CREATE INDEX IF NOT EXISTS IWA_CaseCstmNumberFld_NameValueCaseIdIndex ON IWA_CaseCustomNumberField (Name varchar_pattern_ops, "Value", CaseId);
CREATE INDEX IF NOT EXISTS IWA_CaseCstmTmstmpFld_CaseIdNameValueIndex ON IWA_CaseCustomTimestampField (CaseId, Name varchar_pattern_ops, "Value");
CREATE INDEX IF NOT EXISTS IWA_CaseCstmTmstmpFld_NameValueCaseIdIndex ON IWA_CaseCustomTimestampField (Name varchar_pattern_ops, "Value", CaseId);
CREATE INDEX IF NOT EXISTS IWA_CaseLocalized_CaseIdLanguageIdLowerNameIndex ON IWA_CaseLocalized (CaseId, LanguageId, lower(Name) varchar_pattern_ops);
CREATE INDEX IF NOT EXISTS IWA_CaseLocalized_LanguageIdLowerNameCaseIdIndex ON IWA_CaseLocalized (LanguageId, lower(Name) varchar_pattern_ops, CaseId);
CREATE INDEX IF NOT EXISTS IWA_TaskLocalized_TaskIdLanguageIdLowerNameIndex ON IWA_TaskLocalized (TaskId, LanguageId, lower(Name) varchar_pattern_ops);
CREATE INDEX IF NOT EXISTS IWA_TaskLocalized_LanguageIdLowerNameTaskIdIndex ON IWA_TaskLocalized (LanguageId, lower(Name) varchar_pattern_ops, TaskId);
CREATE INDEX IF NOT EXISTS IWA_TaskCstmStringFld_TaskIdNameLowerValueIndex ON IWA_TaskCustomStringField (TaskId, Name varchar_pattern_ops, lower("Value") varchar_pattern_ops);
CREATE INDEX IF NOT EXISTS IWA_TaskCstmStringFld_NameLowerValueTaskIdIndex ON IWA_TaskCustomStringField (Name varchar_pattern_ops, lower("Value") varchar_pattern_ops, TaskId);
CREATE INDEX IF NOT EXISTS IWA_CaseCstmStringFld_CaseIdNameLowerValueIndex ON IWA_CaseCustomStringField (CaseId, Name varchar_pattern_ops, lower("Value") varchar_pattern_ops);
CREATE INDEX IF NOT EXISTS IWA_CaseCstmStringFld_NameLowerValueCaseIdIndex ON IWA_CaseCustomStringField (Name varchar_pattern_ops, lower("Value") varchar_pattern_ops, CaseId);

Oracle

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_CaseLocalized_CaseIdLanguageIdNameIndex ON IWA_CaseLocalized (CaseId, LanguageId, Name)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_CaseLocalized_LanguageIdNameCaseIdIndex ON IWA_CaseLocalized (LanguageId, Name, CaseId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_Task_TaskIdCaseIdIndex ON IWA_Task (TaskId, CaseId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_Task_CaseIdTaskIdIndex ON IWA_Task (CaseId, TaskId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_Task_TaskIdBusinessCaseIdIndex ON IWA_Task (TaskId, BusinessCaseId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_Task_BusinessCaseIdTaskIdIndex ON IWA_Task (BusinessCaseId, TaskId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_TaskLocalized_TaskIdLanguageIdNameIndex ON IWA_TaskLocalized (TaskId, LanguageId, Name)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_TaskLocalized_LanguageIdNameTaskIdIndex ON IWA_TaskLocalized (LanguageId, Name, TaskId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_TaskCstmStringFld_TaskIdNameValueIndex ON IWA_TaskCustomStringField (TaskId, Name, "VALUE")';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_TaskCstmStringFld_NameValueTaskIdIndex ON IWA_TaskCustomStringField (Name, "VALUE", TaskId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_TaskCstmNumberFld_TaskIdNameValueIndex ON IWA_TaskCustomNumberField (TaskId, Name, "VALUE")';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_TaskCstmNumberFld_NameValueTaskIdIndex ON IWA_TaskCustomNumberField (Name, "VALUE", TaskId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_TaskCstmTimestampFld_TaskIdNameValueIndex ON IWA_TaskCustomTimestampField (TaskId, Name, "VALUE")';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_TaskCstmTimestampFld_NameValueTaskIdIndex ON IWA_TaskCustomTimestampField (Name, "VALUE", TaskId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_CaseCstmStringFld_CaseIdNameValueIndex ON IWA_CaseCustomStringField (CaseId, Name, "VALUE")';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_CaseCstmStringFld_NameValueCaseIdIndex ON IWA_CaseCustomStringField (Name, "VALUE", CaseId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_CaseCstmNumberFld_CaseIdNameValueIndex ON IWA_CaseCustomNumberField (CaseId, Name, "VALUE")';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_CaseCstmNumberFld_NameValueCaseIdIndex ON IWA_CaseCustomNumberField (Name, "VALUE", CaseId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_CaseCstmTmstmpFld_CaseIdNameValueIndex ON IWA_CaseCustomTimestampField (CaseId, Name, "VALUE")';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_CaseCstmTmstmpFld_NameValueCaseIdIndex ON IWA_CaseCustomTimestampField (Name, "VALUE", CaseId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_CaseLocalized_CaseIdLanguageIdLowerNameIndex ON IWA_CaseLocalized (CaseId, LanguageId, lower(Name))';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_CaseLocalized_LanguageIdLowerNameCaseIdIndex ON IWA_CaseLocalized (LanguageId, lower(Name), CaseId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_TaskLocalized_TaskIdLanguageIdLowerNameIndex ON IWA_TaskLocalized (TaskId, LanguageId, lower(Name))';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_TaskLocalized_LanguageIdLowerNameTaskIdIndex ON IWA_TaskLocalized (LanguageId, lower(Name), TaskId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_TaskCstmStringFld_TaskIdNameLowerValueIndex ON IWA_TaskCustomStringField (TaskId, Name, lower("VALUE"))';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_TaskCstmStringFld_NameLowerValueTaskIdIndex ON IWA_TaskCustomStringField (Name, lower("VALUE"), TaskId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_CaseCstmStringFld_CaseIdNameLowerValueIndex ON IWA_CaseCustomStringField (CaseId, Name, lower("VALUE"))';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

DECLARE
  ALREADY_EXISTS EXCEPTION;
  COLUMNS_INDEXED EXCEPTION;
  PRAGMA EXCEPTION_INIT(ALREADY_EXISTS, -955);
  PRAGMA EXCEPTION_INIT(COLUMNS_INDEXED, -1408);
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX IWA_CaseCstmStringFld_NameLowerValueCaseIdIndex ON IWA_CaseCustomStringField (Name, lower("VALUE"), CaseId)';
EXCEPTION
  WHEN ALREADY_EXISTS OR COLUMNS_INDEXED THEN
  NULL;
END;
;

MySQL / MariaDb

CREATE PROCEDURE create_index_if_not_exists(sqlstatement VARCHAR(256))
BEGIN
  DECLARE already_exists CONDITION FOR SQLSTATE '23000';
  DECLARE columns_indexed CONDITION FOR SQLSTATE '42000';
  DECLARE EXIT HANDLER FOR already_exists, columns_indexed
  BEGIN
    -- ignore exceptions for index already exists or columns already indexed
  END;
  SET @sqlstmt = sqlstatement;
  PREPARE stmt FROM @sqlstmt;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;
;

CALL create_index_if_not_exists('CREATE INDEX IWA_CaseLocalized_CaseIdLanguageIdNameIndex ON IWA_CaseLocalized (CaseId, LanguageId, Name)');
CALL create_index_if_not_exists('CREATE INDEX IWA_CaseLocalized_LanguageIdNameCaseIdIndex ON IWA_CaseLocalized (LanguageId, Name, CaseId)');

CALL create_index_if_not_exists('CREATE INDEX IWA_Task_TaskIdCaseIdIndex ON IWA_Task (TaskId, CaseId)');
CALL create_index_if_not_exists('CREATE INDEX IWA_Task_CaseIdTaskIdIndex ON IWA_Task (CaseId, TaskId)');
CALL create_index_if_not_exists('CREATE INDEX IWA_Task_TaskIdBusinessCaseIdIndex ON IWA_Task (TaskId, BusinessCaseId)');
CALL create_index_if_not_exists('CREATE INDEX IWA_Task_BusinessCaseIdTaskIdIndex ON IWA_Task (BusinessCaseId, TaskId)');

CALL create_index_if_not_exists('CREATE INDEX IWA_TaskLocalized_TaskIdLanguageIdNameIndex ON IWA_TaskLocalized (TaskId, LanguageId, Name)');
CALL create_index_if_not_exists('CREATE INDEX IWA_TaskLocalized_LanguageIdNameTaskIdIndex ON IWA_TaskLocalized (LanguageId, Name, TaskId)');

CALL create_index_if_not_exists('CREATE INDEX IWA_TaskCstmStringFld_TaskIdNameValueIndex ON IWA_TaskCustomStringField (TaskId, Name, `Value`(200))');
CALL create_index_if_not_exists('CREATE INDEX IWA_TaskCstmStringFld_NameValueTaskIdIndex ON IWA_TaskCustomStringField (Name, `Value`(200), TaskId)');

CALL create_index_if_not_exists('CREATE INDEX IWA_TaskCstmNumberFld_TaskIdNameValueIndex ON IWA_TaskCustomNumberField (TaskId, Name, `Value`)');
CALL create_index_if_not_exists('CREATE INDEX IWA_TaskCstmNumberFld_NameValueTaskIdIndex ON IWA_TaskCustomNumberField (Name, `Value`, TaskId)');

CALL create_index_if_not_exists('CREATE INDEX IWA_TaskCstmTimestampFld_TaskIdNameValueIndex ON IWA_TaskCustomTimestampField (TaskId, Name, `Value`)');
CALL create_index_if_not_exists('CREATE INDEX IWA_TaskCstmTimestampFld_NameValueTaskIdIndex ON IWA_TaskCustomTimestampField (Name, `Value`, TaskId)');

CALL create_index_if_not_exists('CREATE INDEX IWA_CaseCstmStringFld_CaseIdNameValueIndex ON IWA_CaseCustomStringField (CaseId, Name, `Value`(200))');
CALL create_index_if_not_exists('CREATE INDEX IWA_CaseCstmStringFld_NameValueCaseIdIndex ON IWA_CaseCustomStringField (Name, `Value`(200), CaseId)');

CALL create_index_if_not_exists('CREATE INDEX IWA_CaseCstmNumberFld_CaseIdNameValueIndex ON IWA_CaseCustomNumberField (CaseId, Name, `Value`)');
CALL create_index_if_not_exists('CREATE INDEX IWA_CaseCstmNumberFld_NameValueCaseIdIndex ON IWA_CaseCustomNumberField (Name, `Value`, CaseId)');

CALL create_index_if_not_exists('CREATE INDEX IWA_CaseCstmTmstmpFld_CaseIdNameValueIndex ON IWA_CaseCustomTimestampField (CaseId, Name, `Value`)');
CALL create_index_if_not_exists('CREATE INDEX IWA_CaseCstmTmstmpFld_NameValueCaseIdIndex ON IWA_CaseCustomTimestampField (Name, `Value`, CaseId)');

DROP PROCEDURE create_index_if_not_exists;

MSSQL

IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_CaseLocalized') AND name='IWA_CaseLocalized_CaseIdLanguageIdNameIndex')
  CREATE INDEX IWA_CaseLocalized_CaseIdLanguageIdNameIndex ON IWA_CaseLocalized (CaseId, LanguageId, Name)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_CaseLocalized') AND name='IWA_CaseLocalized_LanguageIdNameCaseIdIndex')
  CREATE INDEX IWA_CaseLocalized_LanguageIdNameCaseIdIndex ON IWA_CaseLocalized (LanguageId, Name, CaseId)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_Task') AND name='IWA_Task_TaskIdCaseIdIndex')
  CREATE INDEX IWA_Task_TaskIdCaseIdIndex ON IWA_Task (TaskId, CaseId)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_Task') AND name='IWA_Task_CaseIdTaskIdIndex')
  CREATE INDEX IWA_Task_CaseIdTaskIdIndex ON IWA_Task (CaseId, TaskId)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_Task') AND name='IWA_Task_TaskIdBusinessCaseIdIndex')
  CREATE INDEX IWA_Task_TaskIdBusinessCaseIdIndex ON IWA_Task (TaskId, BusinessCaseId)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_Task') AND name='IWA_Task_BusinessCaseIdTaskIdIndex')
  CREATE INDEX IWA_Task_BusinessCaseIdTaskIdIndex ON IWA_Task (BusinessCaseId, TaskId)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_TaskLocalized') AND name='IWA_TaskLocalized_TaskIdLanguageIdNameIndex')
  CREATE INDEX IWA_TaskLocalized_TaskIdLanguageIdNameIndex ON IWA_TaskLocalized (TaskId, LanguageId, Name)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_TaskLocalized') AND name='IWA_TaskLocalized_LanguageIdNameTaskIdIndex')
  CREATE INDEX IWA_TaskLocalized_LanguageIdNameTaskIdIndex ON IWA_TaskLocalized (LanguageId, Name, TaskId)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_TaskCustomStringField') AND name='IWA_TaskCstmStringFld_TaskIdNameValueIndex')
  CREATE INDEX IWA_TaskCstmStringFld_TaskIdNameValueIndex ON IWA_TaskCustomStringField (TaskId, Name, "Value")
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_TaskCustomStringField') AND name='IWA_TaskCstmStringFld_NameValueTaskIdIndex')
  CREATE INDEX IWA_TaskCstmStringFld_NameValueTaskIdIndex ON IWA_TaskCustomStringField (Name, "Value", TaskId)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_TaskCustomNumberField') AND name='IWA_TaskCstmNumberFld_TaskIdNameValueIndex')
  CREATE INDEX IWA_TaskCstmNumberFld_TaskIdNameValueIndex ON IWA_TaskCustomNumberField (TaskId, Name, "Value")
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_TaskCustomNumberField') AND name='IWA_TaskCstmNumberFld_NameValueTaskIdIndex')
  CREATE INDEX IWA_TaskCstmNumberFld_NameValueTaskIdIndex ON IWA_TaskCustomNumberField (Name, "Value", TaskId)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_TaskCustomTimestampField') AND name='IWA_TaskCstmTimestampFld_TaskIdNameValueIndex')
  CREATE INDEX IWA_TaskCstmTimestampFld_TaskIdNameValueIndex ON IWA_TaskCustomTimestampField (TaskId, Name, "Value")
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_TaskCustomTimestampField') AND name='IWA_TaskCstmTimestampFld_NameValueTaskIdIndex')
  CREATE INDEX IWA_TaskCstmTimestampFld_NameValueTaskIdIndex ON IWA_TaskCustomTimestampField (Name, "Value", TaskId)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_CaseCustomStringField') AND name='IWA_CaseCstmStringFld_CaseIdNameValueIndex')
  CREATE INDEX IWA_CaseCstmStringFld_CaseIdNameValueIndex ON IWA_CaseCustomStringField (CaseId, Name, "Value")
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_CaseCustomStringField') AND name='IWA_CaseCstmStringFld_NameValueCaseIdIndex')
  CREATE INDEX IWA_CaseCstmStringFld_NameValueCaseIdIndex ON IWA_CaseCustomStringField (Name, "Value", CaseId)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_CaseCustomNumberField') AND name='IWA_CaseCstmNumberFld_CaseIdNameValueIndex')
  CREATE INDEX IWA_CaseCstmNumberFld_CaseIdNameValueIndex ON IWA_CaseCustomNumberField (CaseId, Name, "Value")
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_CaseCustomNumberField') AND name='IWA_CaseCstmNumberFld_NameValueCaseIdIndex')
  CREATE INDEX IWA_CaseCstmNumberFld_NameValueCaseIdIndex ON IWA_CaseCustomNumberField (Name, "Value", CaseId)
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_CaseCustomTimestampField') AND name='IWA_CaseCstmTmstmpFld_CaseIdNameValueIndex')
  CREATE INDEX IWA_CaseCstmTmstmpFld_CaseIdNameValueIndex ON IWA_CaseCustomTimestampField (CaseId, Name, "Value")
GO
IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=object_id('IWA_CaseCustomTimestampField') AND name='IWA_CaseCstmTmstmpFld_NameValueCaseIdIndex')
  CREATE INDEX IWA_CaseCstmTmstmpFld_NameValueCaseIdIndex ON IWA_CaseCustomTimestampField (Name, "Value", CaseId)
GO

Timo Rupp

Hi @Alexander Suter

When adding the indexes for MS SQL (Axon Ivy 10.0.23), I get these warning messages:

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IWA_TaskCstmStringFld_TaskIdNameValueIndex' has maximum length of 8808 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IWA_TaskCstmStringFld_NameValueTaskIdIndex' has maximum length of 8808 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IWA_CaseCstmStringFld_CaseIdNameValueIndex' has maximum length of 8808 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IWA_CaseCstmStringFld_NameValueCaseIdIndex' has maximum length of 8808 bytes. For some combination of large values, the insert/update operation will fail.

I think it means that allowed length of custom field string value will be limited after the indexes are added. Is this correct and expected?

Alexander Suter

Timo Rupp I didn't know that. I guess it depends on the given charset/collation. Which one do you use?

Timo Rupp

@Alexander Suter I just tried it on a new/plain/default Axon Ivy 11 database (schema version 157) and same issue happened. It's using collation Latin1_General_CI_AS.

I think with current sizes of the fields, this will happen for all collations. Even a collation using only one byte per char will hit this limit of 1,700 bytes, as the value field in IWA_CaseCustomStringField already has 8000 chars.

The limit is specified here (search for "bytes per index key"): https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver16&viewFallbackFrom=sql-server-ver22

mhoffmann

For SQL Server, we're currently trying out the following index:

CREATE NONCLUSTERED INDEX IX_CaseString_NameCase
    ON dbo.IWA_CaseCustomStringField (Name, CaseId)
    INCLUDE (Value);

The include (doc) ensures that we don't run into the issue with the maximum length of 1700 bytes, however, I think that the database design here can be improved by a lot.

I honestly don't think that someone would need 800 chars for a key let alone 8000 chars for a string while text fields also exist, this wasn't really designed with the dashboards in mind which query these tables and therefore can lead to performance problems, especially when SQL Server has to perform expensive lookups.

The index is used pretty well by the SQL Server:

And currently it seems like this was the key to solve this issue for us, at least the engine seems to run a lot faster and the statements to the IWA_Task and IWA_Case table with joins on the CustomField tables aren't reported as very expensive queries anymore.

Hopefully this will persist, but I would argue that the length of the fields is not really practical. Whoever needs a 800 char long key for a variable (in the end, custom fields are just that) should probably consider his naming conventions and 8000 for a String field, while text exists, is just too much and not keeping in mind the limitations of the database itself. Were the value field limited to a more realistic length, a clustered index would be possible without any possible side effects. I would really urge you to refactor the structure here. If you need long text, use the text custom field - if that's slow, that's a known side effect of it then. But for the rest, it shouldn't be like that.

drag0p

When talking about lengths of fields, the problem is compatibility with previous versions, vast amount of different approaches at each customer project, etc...
But the INCLUDE value is a nice approach which should help in MS SQL, i will push this further for newer versions...

mhoffmann

drag0p
I really appreciate your effort! 🙂

While I can understand that you want to keep compatibility with previous versions, I would argue that the introduction of the dashboards converted this compatibility to a technical debt and it feels a bit like an oversight.

I think the distinction between String and Text is important to know and should be kept in mind by implementing developers. I have probably made the mistake myself and stored something which should have been a TextCustomField on a StringCustomField just because the String field was sufficient in length. If I had to change this on a major update, where you need to modify and update your processes anyway, I'd do it with a smile if it leads to a better performance for the end users for who we develop to begin with.

But I can understand that this would be almost a show stopper for someone who almost exhausted either limitation (key / value length) and probably very hard or at least annoying to migrate, especially if the key length is the issue.

While it makes no difference in terms of storage that we have a definition of varchar(800) and varchar(8000) it might cause memory allocations which are too big, especially if an ORDER BY is used. Also the issue with the index still persists, although the inclusion of the value column made it a lot better.

Knowing that you usually improve things in Axon Ivy, I am confident that we might see another approach in the future. For now, I guess we can leave it like that 🙂

Alexander Suter

If you have added these indices on your LTS 10 Axon Ivy Engine in your system database and you want to migrate to LTS 12, then you may get errors if you have MSSQL or Postgresql. Then you need first removing the following indices:

IWA_CaseCstmTmstmpFld_NameValueCaseIdIndex
IWA_CaseCstmTmstmpFld_CaseIdNameValueIndex
IWA_TaskCstmTimestampFld_NameValueTaskIdIndex
IWA_TaskCstmTimestampFld_TaskIdNameValueIndex