Slow JTX performance remedies?

[AndrewRudin]

My JTX users have reported our system becoming slower, particularly when running workflows.  It seems to have gotten slower over time and now we are up to 11,000 jobs after 7 years. Below is a list of our JTX system tables and a record count for each.

I'm curious if there are common things to look for with bigger JTX repositories.  My users have mainly mentioned slow response when running through our workflow.  I have some steps that execute sql and it seems to take a while and my guess is its making a new connection to the database just to populate one field in a table.  Otherwise it seems some of the other steps take some time as well.  We first thought exporting and removing some of the older jobs and job_types would help, but then we found out we can only really export abut 1000 of them, since the other 10,000 are used when we query the system.  However, by removing those 1000 we could delete a lot of old step types and job types.  I would think its the bloat in the JTX_HISTORY, JTX_JOB_STE_XREF, and JTX_JOB_STEP and JTX_STEP_STATUS and JT_STATUS_HIST that could be causing slow response when moving about the workflow.

Thank you for any help you can provide,
Andrew

Table Name Records
JTX_ADMIN.JTX_HISTORY 798894
JTX_ADMIN.JTX_JOB_STEP_XREF 533102
JTX_ADMIN.JTX_JOB_STEP 475710
JTX_ADMIN.JTX_STEP_STATUS 289752
JTX_ADMIN.JTX_STATUS_HIST 113122
JTX_ADMIN.JTXX_AMANDA_ATTACHMENT_COUNT 58931
JTX_ADMIN.JTXX_PLANSTATUS_BYGROUP_VW 15316
JTX_ADMIN.JTXX_STORMWATER_PLAN_DETAIL 14820
JTX_ADMIN.JTXX_PLANSTATUS_BYGROUP_VW_1 14618
JTX_ADMIN.JTXX_SW_MULTIPART_POLY_VW 11989
JTX_ADMIN.JTX_JOBS 11298
JTX_ADMIN.JTX_JOB_BLOB 11288
JTX_ADMIN.JTX_JOBS_DB 11084
JTX_ADMIN.JTXX_JOB_KEYINFO 10157
JTX_ADMIN.JTXX_SW_PLAN_DETAIL_JOBID_VW 9902
JTX_ADMIN.jtxx_sw_plan_page_counts 8754
JTX_ADMIN.sw_plan_page_count 8754
JTX_ADMIN.JTX_JOB_ATTACHMENTS 5101
JTX_ADMIN.JTX_TRANSACTION_SESSIONS 2195
JTX_ADMIN.CONST_PLAN_EXT_PROP 591
JTX_ADMIN.JTX_JOB_TYPE_STEP_XREF 437
JTX_ADMIN.JTX_JOB_TYPE_STEP 394
JTX_ADMIN.JTXX_SW_PLANGROUPID 209
JTX_ADMIN.JTXX_PLANGROUPSTATUS_VW 198
JTX_ADMIN.JTX_AUX_PROPS 186
JTX_ADMIN.JTX_PRIV_XREF 150
JTX_ADMIN.JTXX_SSM_EXTENDED_PROPERTIES 113
JTX_ADMIN.JTX_USER_GROUP_XREF 109
JTX_ADMIN.JTX_STEP_TYPE 87
JTX_ADMIN.JTX_LOGIN_INFO 68
JTX_ADMIN.JTX_LAYERS 63
JTX_ADMIN.JTX_JOB_TYPE_PROPERTIES 58
JTX_ADMIN.JTX_QUERY_CONTAINERS 54
JTX_ADMIN.JTX_USERS 52
JTX_ADMIN.JTX_QUERY_OWNERS 50
JTX_ADMIN.JTX_JOB_DATA 46
JTX_ADMIN.JTXX_PR_VW 41
JTX_ADMIN.JTX_PROPERTIES 39
JTX_ADMIN.JTX_JOB_TYPE_BLOB 30
JTX_ADMIN.JTX_PRIVILEGES 30
JTX_ADMIN.JTX_WORKFLOW_STORE 30
JTX_ADMIN.JTX_ACTIVITY_TYPES 29
JTX_ADMIN.JTX_JOB_TYPES 29
JTX_ADMIN.JTX_JOB_QUERIES 28
JTX_ADMIN.JTX_NOTIFICATIONS 25
JTX_ADMIN.JTX_JOB_TYPE_REL_CLASSES 15
JTX_ADMIN.JTX_STATUS 14
JTX_ADMIN.JTX_PROP_RELATIONSHIPS 10
JTX_ADMIN.JTX_USER_GROUPS 10
JTX_ADMIN.JTXX_DIG_RANDOM_NUMBER 10
JTX_ADMIN.JTX_TOKEN_PARSERS 6
JTX_ADMIN.JTX_USER_GROUP_JOB_FILTERS 6
JTX_ADMIN.JTX_JOB_TYPE_MAP_DOC 4
JTX_ADMIN.JTX_REPORT_QUERIES 4
JTX_ADMIN.JTX_REPORTS 4
JTX_ADMIN.JTX_OBJECT_PERMISSIONS 4
JTX_ADMIN.JTX_PRIORITY 3
JTX_ADMIN.JTXX_EXTRA_PROPERTIES 2
JTX_ADMIN.JTX_DATABASES 2
JTX_ADMIN.JTX_MAP_STORE 2
JTX_ADMIN.JTXX_TV_INSPECTION_WORK_ORDER 2
JTX_ADMIN.JTX_DATABASE_SCHEMA 1
JTX_ADMIN.JTXX_DIG_QC_QUOTE 1
JTX_ADMIN.JTX_HISTORY_SESSIONS 0
JTX_ADMIN.JTX_HOLD_TYPES 0
JTX_ADMIN.JTX_JOB_DEPENDENCIES 0
JTX_ADMIN.JTX_JOB_FILTER_XREF 0
JTX_ADMIN.JTX_JOB_FILTERS 0
JTX_ADMIN.JTX_JOB_HOLDS 0
JTX_ADMIN.JTX_NOTIFICATION_SUBSCRIBERS 0
JTX_ADMIN.JTX_SPAT_NOTIF_LAYERS 0
JTX_ADMIN.JTX_SPAT_NOTIF_MATCHES 0
JTX_ADMIN.JTX_SPAT_NOTIF_RULE_CONDITION 0
JTX_ADMIN.JTX_SPAT_NOTIF_RULES 0
JTX_ADMIN.JTX_STEP_COMMENTS 0
JTX_ADMIN.JTX_TASK_ASSISTANT_WORKFLOWS 0
JTX_ADMIN.JTX_TRANSACTIONS_TEMP 0
JTX_ADMIN.JTX_TRANSACTIONS 0
JTX_ADMIN.JTX_CONN_INFO 0
JTX_ADMIN.JTX_HISTORY_DATASETS 0
JTX_ADMIN.JTX_DELETED_OBJECTS 0
JTX_ADMIN.JTX_REPLICATION_INFO 0
JTX_ADMIN.Prod_ComponentVersion 0
JTX_ADMIN.JTXX_GIS_UPDATE_REQUESTS 0

[BrianDemers]

Hi Andrew,

One thing that caught my eye is your comment about having some steps that run SQL.  It may be worth reviewing these steps to ensure that the fields that they're using for queries have attribute indexes.

I checked one of the Workflow Manager databases that I use, and (for example) in its JTX_HISTORY table, there was an attribute index on "OBJECTID" and another on "JOB_ID", but not on any of the other fields.  You'll probably have comparable indexes set up on each table just from the Workflow Manager post-install.  But if you've developed your own queries against the database using fields that aren't indexed, or set up your own tables, or certainly if one of these indexes is missing for any reason, that could lead to a performance hit over time as the number of records in these tables gets larger.

Another way to investigate/confirm what's happening would be to run through these steps with Workflow Manager logging enabled.  This might provide some more insight as to where the application is spending its time.

Hopefully that helps,

Brian D.