IBM Support

Slow Performance During Search

Troubleshooting


Problem

When filtering in the various applications the performance is very poor.

Symptom

Case 1: When I run the first query results returns 25 rows in less than second
but the second query with the order by clause takes over 5 minutes.
What is the best way to improve this?

Case 2: When we filter in the Asset application the performance is very poor,
over a minute to return an asset searched on a single assetid.

Resolving The Problem

select * from workorder
where ((woclass = 'WORKORDER' or woclass ='ACTIVITY')
and historyflag = 0 and persongroup is null and istask = 0
and siteid = 'BEDFORD') order by status asc


For Oracle:
1. Go to SQL Developer (or any preferred SQL tool)
Highlight the query, right click and select Explain > Explain Plan:



For sql server:
Go to Microsoft SQL Server Management Studio (or any preferred SQL tool):


its doing a full table scan


2. Go to the Database Configuration
Bring up the workorder object
Add a new index on fields which have been used in the SQL query
For example, add a new index on woclass, historyflag, persongroup, siteid, status columns

3. From the "Select Action" menu, "Update Statistics":



4. From the "Select Action" menu, "Refresh Index Tables":


This will take a fair amount of time to complete, possibly 30 minutes or more.


Case 2: Slow performance in Asset application
select * from asset
where ((assettag like '%XYZ123%' and siteid ='BEDFORD')) and (plustisconsist=0)
(execution took 49846 milliseconds)

Wildcard search using double wildcard like on %XYZ123% going to be less efficient and slower.

One wildcard search like 'XYZ123%' and exact search assettag = 'XYZ123' would be less expensive

1. Go to a SQL Query tool
Go to SQL Developer (or any preferred SQL tool)
Run EXPLAIN Plan / Execution plan and check to see if any index is being used

2. Go to the Database Configuration
Bring up the asset object
Add a new index on assettag , siteid columns

3. From the "Select Action" menu, "Update Statistics".

4. From the "Select Action" menu, "Refresh Index Tables".

This will take a fair amount of time to complete, possibly 30 minutes or more.

Once the index build is complete, the search will be reduced from 40 seconds down to 1 second

[{"Product":{"code":"SSLKT6","label":"Maximo Asset Management"},"Business Unit":{"code":"BU005","label":"IoT"},"Component":"Work Order Tracking","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"Version Independent","Edition":""},{"Product":{"code":"SS6HJK","label":"Tivoli Service Request Manager"},"Business Unit":{"code":"BU004","label":"Hybrid Cloud"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":""},{"Product":{"code":"SSKTXT","label":"Tivoli Change and Configuration Management Database"},"Business Unit":{"code":"BU004","label":"Hybrid Cloud"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":""},{"Product":{"code":"SSWK4A","label":"Maximo Asset Management Essentials"},"Business Unit":{"code":"BU005","label":"IoT"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":""}]

Document Information

Modified date:
17 June 2018

UID

swg21669275