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
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg21669275