Hello everyone

Our maximo is getting slower and slower. It varies, but most of the users have noticed it getting slower over the last few months or so. Sometimes it takes 1-2 minutes until all of the portlets are finished loading.
I am only admin for our site and far from a maximo expert, but not all start centers are slow so maybe it has to do with some of my queries. I make changes to the queries once in a while, but I can't say if it has gotten slower after a particular change I made.

I would be thankful if anyone with maximo and SQL experience would take a look at the queries and give some feedback. Maybe you got a suggestion how to make them perform quicker.

Here are the main queries from our maintenance start center, which is the slowest:

Portlet Type: Result SetApplication: WOTRACKDisplay Name: Åbne arbejdsordrer (rød skrift = venter på ny ejer)Query name: DKNORHS åbne arbejdsordrerQuery description: Åbne arbejdsordrer L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') OR WORKTYPE IS NULL) AND STATUS NOT IN ('COMP','CLOSE','CAN') AND ISTASK = 0ORDER BY REPORTDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Afmeldte arbejdsordrerQuery name: DKNORHS afmeldte arbejdsorderQuery description: Afmeldte arbejdsordrer L32Query:SITEID = 'DKNORHS' AND WORKTYPE != 'PMCAL' AND STATUS = 'COMP' AND ISTASK = 0ORDER BY ACTFINISH DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: ProjekterQuery name: DKNORHS udviklingsprojekterQuery description: Udviklingsprojekter L32Query:SITEID = 'DKNORHS' AND WORKTYPE IN ('PRSML','EC','TPM') AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0ORDER BY STATUSDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligehold i dagQuery name: DKNORHS planlagt vedligehold i dagQuery description: Planlagt vedligehold i dag L32Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND CAST(SCHEDSTART AS DATE) = CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligeholdQuery name: DKNORHS alt vedligeholdQuery description: Alt vedligehold L32 (undtagen fremtidig)Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0 AND CAST(SCHEDSTART AS DATE) <= CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Arbejdsordre-historikQuery name: DKNORHS arbejdsordre-historikQuery description: Arbejdsordre-historik L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN ('PMCAL','PM') ORWORKTYPE IS NULL) AND STATUS IN ('COMP','CLOSE') AND ISTASK = 0ORDER BY ACTFINISH DESC

Kind regards
Julian

Edit: The last query returns about 16,000 results, I don't know if the amount of results is a factor regarding performance. But I tried removing that portlet in my start center and it did not get any quicker.

------------------------------
julian meyer
------------------------------

Hi Julian,

I can see one of the major performance degradation contributer in all your queries are sorting based on date fields.

One best approach will be to reduce the result set by just fetching the records modified in last 1 week or using rowstamp to fetch top 50 records.Fetching all the records in the start center itself will not be a usal use case I guess.

You can try adding indexes for these fields and if possible composite indexes using a combination of the attributes used in each individual queries. 

I would also recommend to try out these queries in the database and see if the time taken in frustating.Try out the indexes and you should be able to see a performance improvement. Also ensure that the we are gathering the statistics for huge tables like workorder, recommended every week. You can check that with your DBA team.

Also if there are huge amount of historical workorders which are not required to be fetched more frequently, then you can try out partitioning the table, DBA Team can help you out there as well.

Hello everyone

Our maximo is getting slower and slower. It varies, but most of the users have noticed it getting slower over the last few months or so. Sometimes it takes 1-2 minutes until all of the portlets are finished loading.
I am only admin for our site and far from a maximo expert, but not all start centers are slow so maybe it has to do with some of my queries. I make changes to the queries once in a while, but I can't say if it has gotten slower after a particular change I made.

I would be thankful if anyone with maximo and SQL experience would take a look at the queries and give some feedback. Maybe you got a suggestion how to make them perform quicker.

Here are the main queries from our maintenance start center, which is the slowest:

Portlet Type: Result SetApplication: WOTRACKDisplay Name: Åbne arbejdsordrer (rød skrift = venter på ny ejer)Query name: DKNORHS åbne arbejdsordrerQuery description: Åbne arbejdsordrer L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') OR WORKTYPE IS NULL) AND STATUS NOT IN ('COMP','CLOSE','CAN') AND ISTASK = 0ORDER BY REPORTDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Afmeldte arbejdsordrerQuery name: DKNORHS afmeldte arbejdsorderQuery description: Afmeldte arbejdsordrer L32Query:SITEID = 'DKNORHS' AND WORKTYPE != 'PMCAL' AND STATUS = 'COMP' AND ISTASK = 0ORDER BY ACTFINISH DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: ProjekterQuery name: DKNORHS udviklingsprojekterQuery description: Udviklingsprojekter L32Query:SITEID = 'DKNORHS' AND WORKTYPE IN ('PRSML','EC','TPM') AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0ORDER BY STATUSDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligehold i dagQuery name: DKNORHS planlagt vedligehold i dagQuery description: Planlagt vedligehold i dag L32Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND CAST(SCHEDSTART AS DATE) = CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligeholdQuery name: DKNORHS alt vedligeholdQuery description: Alt vedligehold L32 (undtagen fremtidig)Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0 AND CAST(SCHEDSTART AS DATE) <= CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Arbejdsordre-historikQuery name: DKNORHS arbejdsordre-historikQuery description: Arbejdsordre-historik L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN ('PMCAL','PM') ORWORKTYPE IS NULL) AND STATUS IN ('COMP','CLOSE') AND ISTASK = 0ORDER BY ACTFINISH DESC

Kind regards
Julian

Edit: The last query returns about 16,000 results, I don't know if the amount of results is a factor regarding performance. But I tried removing that portlet in my start center and it did not get any quicker.

------------------------------
julian meyer
------------------------------

Hello everyone

Our maximo is getting slower and slower. It varies, but most of the users have noticed it getting slower over the last few months or so. Sometimes it takes 1-2 minutes until all of the portlets are finished loading.
I am only admin for our site and far from a maximo expert, but not all start centers are slow so maybe it has to do with some of my queries. I make changes to the queries once in a while, but I can't say if it has gotten slower after a particular change I made.

I would be thankful if anyone with maximo and SQL experience would take a look at the queries and give some feedback. Maybe you got a suggestion how to make them perform quicker.

Here are the main queries from our maintenance start center, which is the slowest:

Portlet Type: Result SetApplication: WOTRACKDisplay Name: Åbne arbejdsordrer (rød skrift = venter på ny ejer)Query name: DKNORHS åbne arbejdsordrerQuery description: Åbne arbejdsordrer L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') OR WORKTYPE IS NULL) AND STATUS NOT IN ('COMP','CLOSE','CAN') AND ISTASK = 0ORDER BY REPORTDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Afmeldte arbejdsordrerQuery name: DKNORHS afmeldte arbejdsorderQuery description: Afmeldte arbejdsordrer L32Query:SITEID = 'DKNORHS' AND WORKTYPE != 'PMCAL' AND STATUS = 'COMP' AND ISTASK = 0ORDER BY ACTFINISH DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: ProjekterQuery name: DKNORHS udviklingsprojekterQuery description: Udviklingsprojekter L32Query:SITEID = 'DKNORHS' AND WORKTYPE IN ('PRSML','EC','TPM') AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0ORDER BY STATUSDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligehold i dagQuery name: DKNORHS planlagt vedligehold i dagQuery description: Planlagt vedligehold i dag L32Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND CAST(SCHEDSTART AS DATE) = CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligeholdQuery name: DKNORHS alt vedligeholdQuery description: Alt vedligehold L32 (undtagen fremtidig)Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0 AND CAST(SCHEDSTART AS DATE) <= CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Arbejdsordre-historikQuery name: DKNORHS arbejdsordre-historikQuery description: Arbejdsordre-historik L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN ('PMCAL','PM') ORWORKTYPE IS NULL) AND STATUS IN ('COMP','CLOSE') AND ISTASK = 0ORDER BY ACTFINISH DESC

Kind regards
Julian

Edit: The last query returns about 16,000 results, I don't know if the amount of results is a factor regarding performance. But I tried removing that portlet in my start center and it did not get any quicker.

------------------------------
julian meyer
------------------------------

Hi Julian,

I had a look at your first query - DKNORHS åbne arbejdsordrer  

May I suggest this version below instead. Being that you are looking for workorders that are not equal to COMP CLOSE or CAN, I added in the history flag and just set status not equal to COMP.

This query should be very fast for you

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')
and status != 'COMP'
and (   worktype NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') 
        OR 
        worktype IS NULL
    )
  

Hello everyone

Our maximo is getting slower and slower. It varies, but most of the users have noticed it getting slower over the last few months or so. Sometimes it takes 1-2 minutes until all of the portlets are finished loading.
I am only admin for our site and far from a maximo expert, but not all start centers are slow so maybe it has to do with some of my queries. I make changes to the queries once in a while, but I can't say if it has gotten slower after a particular change I made.

I would be thankful if anyone with maximo and SQL experience would take a look at the queries and give some feedback. Maybe you got a suggestion how to make them perform quicker.

Here are the main queries from our maintenance start center, which is the slowest:

Portlet Type: Result SetApplication: WOTRACKDisplay Name: Åbne arbejdsordrer (rød skrift = venter på ny ejer)Query name: DKNORHS åbne arbejdsordrerQuery description: Åbne arbejdsordrer L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') OR WORKTYPE IS NULL) AND STATUS NOT IN ('COMP','CLOSE','CAN') AND ISTASK = 0ORDER BY REPORTDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Afmeldte arbejdsordrerQuery name: DKNORHS afmeldte arbejdsorderQuery description: Afmeldte arbejdsordrer L32Query:SITEID = 'DKNORHS' AND WORKTYPE != 'PMCAL' AND STATUS = 'COMP' AND ISTASK = 0ORDER BY ACTFINISH DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: ProjekterQuery name: DKNORHS udviklingsprojekterQuery description: Udviklingsprojekter L32Query:SITEID = 'DKNORHS' AND WORKTYPE IN ('PRSML','EC','TPM') AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0ORDER BY STATUSDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligehold i dagQuery name: DKNORHS planlagt vedligehold i dagQuery description: Planlagt vedligehold i dag L32Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND CAST(SCHEDSTART AS DATE) = CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligeholdQuery name: DKNORHS alt vedligeholdQuery description: Alt vedligehold L32 (undtagen fremtidig)Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0 AND CAST(SCHEDSTART AS DATE) <= CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Arbejdsordre-historikQuery name: DKNORHS arbejdsordre-historikQuery description: Arbejdsordre-historik L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN ('PMCAL','PM') ORWORKTYPE IS NULL) AND STATUS IN ('COMP','CLOSE') AND ISTASK = 0ORDER BY ACTFINISH DESC

Kind regards
Julian

Edit: The last query returns about 16,000 results, I don't know if the amount of results is a factor regarding performance. But I tried removing that portlet in my start center and it did not get any quicker.

------------------------------
julian meyer
------------------------------

Jist a small correction, that have better reading and perfomance:

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')and status != 'COMP'and coalesce(worktype, '$NULL$') NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') )

Hi Julian,

I had a look at your first query - DKNORHS åbne arbejdsordrer  

May I suggest this version below instead. Being that you are looking for workorders that are not equal to COMP CLOSE or CAN, I added in the history flag and just set status not equal to COMP.

This query should be very fast for you

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')
and status != 'COMP'
and (   worktype NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') 
        OR 
        worktype IS NULL
    )
  

Hello everyone

Our maximo is getting slower and slower. It varies, but most of the users have noticed it getting slower over the last few months or so. Sometimes it takes 1-2 minutes until all of the portlets are finished loading.
I am only admin for our site and far from a maximo expert, but not all start centers are slow so maybe it has to do with some of my queries. I make changes to the queries once in a while, but I can't say if it has gotten slower after a particular change I made.

I would be thankful if anyone with maximo and SQL experience would take a look at the queries and give some feedback. Maybe you got a suggestion how to make them perform quicker.

Here are the main queries from our maintenance start center, which is the slowest:

Portlet Type: Result SetApplication: WOTRACKDisplay Name: Åbne arbejdsordrer (rød skrift = venter på ny ejer)Query name: DKNORHS åbne arbejdsordrerQuery description: Åbne arbejdsordrer L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') OR WORKTYPE IS NULL) AND STATUS NOT IN ('COMP','CLOSE','CAN') AND ISTASK = 0ORDER BY REPORTDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Afmeldte arbejdsordrerQuery name: DKNORHS afmeldte arbejdsorderQuery description: Afmeldte arbejdsordrer L32Query:SITEID = 'DKNORHS' AND WORKTYPE != 'PMCAL' AND STATUS = 'COMP' AND ISTASK = 0ORDER BY ACTFINISH DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: ProjekterQuery name: DKNORHS udviklingsprojekterQuery description: Udviklingsprojekter L32Query:SITEID = 'DKNORHS' AND WORKTYPE IN ('PRSML','EC','TPM') AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0ORDER BY STATUSDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligehold i dagQuery name: DKNORHS planlagt vedligehold i dagQuery description: Planlagt vedligehold i dag L32Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND CAST(SCHEDSTART AS DATE) = CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligeholdQuery name: DKNORHS alt vedligeholdQuery description: Alt vedligehold L32 (undtagen fremtidig)Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0 AND CAST(SCHEDSTART AS DATE) <= CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Arbejdsordre-historikQuery name: DKNORHS arbejdsordre-historikQuery description: Arbejdsordre-historik L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN ('PMCAL','PM') ORWORKTYPE IS NULL) AND STATUS IN ('COMP','CLOSE') AND ISTASK = 0ORDER BY ACTFINISH DESC

Kind regards
Julian

Edit: The last query returns about 16,000 results, I don't know if the amount of results is a factor regarding performance. But I tried removing that portlet in my start center and it did not get any quicker.

------------------------------
julian meyer
------------------------------

Hi Julian,

I had a look at your first query - DKNORHS åbne arbejdsordrer  

May I suggest this version below instead. Being that you are looking for workorders that are not equal to COMP CLOSE or CAN, I added in the history flag and just set status not equal to COMP.

This query should be very fast for you

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')
and status != 'COMP'
and (   worktype NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') 
        OR 
        worktype IS NULL
    )
  

Hello everyone

Our maximo is getting slower and slower. It varies, but most of the users have noticed it getting slower over the last few months or so. Sometimes it takes 1-2 minutes until all of the portlets are finished loading.
I am only admin for our site and far from a maximo expert, but not all start centers are slow so maybe it has to do with some of my queries. I make changes to the queries once in a while, but I can't say if it has gotten slower after a particular change I made.

I would be thankful if anyone with maximo and SQL experience would take a look at the queries and give some feedback. Maybe you got a suggestion how to make them perform quicker.

Here are the main queries from our maintenance start center, which is the slowest:

Portlet Type: Result SetApplication: WOTRACKDisplay Name: Åbne arbejdsordrer (rød skrift = venter på ny ejer)Query name: DKNORHS åbne arbejdsordrerQuery description: Åbne arbejdsordrer L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') OR WORKTYPE IS NULL) AND STATUS NOT IN ('COMP','CLOSE','CAN') AND ISTASK = 0ORDER BY REPORTDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Afmeldte arbejdsordrerQuery name: DKNORHS afmeldte arbejdsorderQuery description: Afmeldte arbejdsordrer L32Query:SITEID = 'DKNORHS' AND WORKTYPE != 'PMCAL' AND STATUS = 'COMP' AND ISTASK = 0ORDER BY ACTFINISH DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: ProjekterQuery name: DKNORHS udviklingsprojekterQuery description: Udviklingsprojekter L32Query:SITEID = 'DKNORHS' AND WORKTYPE IN ('PRSML','EC','TPM') AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0ORDER BY STATUSDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligehold i dagQuery name: DKNORHS planlagt vedligehold i dagQuery description: Planlagt vedligehold i dag L32Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND CAST(SCHEDSTART AS DATE) = CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligeholdQuery name: DKNORHS alt vedligeholdQuery description: Alt vedligehold L32 (undtagen fremtidig)Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0 AND CAST(SCHEDSTART AS DATE) <= CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Arbejdsordre-historikQuery name: DKNORHS arbejdsordre-historikQuery description: Arbejdsordre-historik L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN ('PMCAL','PM') ORWORKTYPE IS NULL) AND STATUS IN ('COMP','CLOSE') AND ISTASK = 0ORDER BY ACTFINISH DESC

Kind regards
Julian

Edit: The last query returns about 16,000 results, I don't know if the amount of results is a factor regarding performance. But I tried removing that portlet in my start center and it did not get any quicker.

------------------------------
julian meyer
------------------------------

Hi @Darren Hornidge

Thanks for the suggestion. It really performs notably faster. But I don't understand why. I have two questions about your version of the query:

(woclass = 'WORKORDER' or woclass = 'ACTIVITY')

That part is always added in the automatically generated queries, and yours contains it too, just like the ones of the other guys. But I don't see why. Because the results are the same when I leave it out and the performance does not seem to be affected either. As you can see, I have not used it in any of my queries. I want to keep my queries as clean as possible, so if that "woclass" part doesn't add any value I don't want to use it.

Also, why is there an extra set of paranthesis around the first line ?

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')

I tried the query with and without it, it doesn't seem to make a difference.

I am just curious and trying to understand all of it :) The part with the history flag instead of sorting by status did make it quicker, so thank you very much for that.

Best regards
Julian

Hi Julian,

I had a look at your first query - DKNORHS åbne arbejdsordrer  

May I suggest this version below instead. Being that you are looking for workorders that are not equal to COMP CLOSE or CAN, I added in the history flag and just set status not equal to COMP.

This query should be very fast for you

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')
and status != 'COMP'
and (   worktype NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') 
        OR 
        worktype IS NULL
    )
  

Hello everyone

Our maximo is getting slower and slower. It varies, but most of the users have noticed it getting slower over the last few months or so. Sometimes it takes 1-2 minutes until all of the portlets are finished loading.
I am only admin for our site and far from a maximo expert, but not all start centers are slow so maybe it has to do with some of my queries. I make changes to the queries once in a while, but I can't say if it has gotten slower after a particular change I made.

I would be thankful if anyone with maximo and SQL experience would take a look at the queries and give some feedback. Maybe you got a suggestion how to make them perform quicker.

Here are the main queries from our maintenance start center, which is the slowest:

Portlet Type: Result SetApplication: WOTRACKDisplay Name: Åbne arbejdsordrer (rød skrift = venter på ny ejer)Query name: DKNORHS åbne arbejdsordrerQuery description: Åbne arbejdsordrer L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') OR WORKTYPE IS NULL) AND STATUS NOT IN ('COMP','CLOSE','CAN') AND ISTASK = 0ORDER BY REPORTDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Afmeldte arbejdsordrerQuery name: DKNORHS afmeldte arbejdsorderQuery description: Afmeldte arbejdsordrer L32Query:SITEID = 'DKNORHS' AND WORKTYPE != 'PMCAL' AND STATUS = 'COMP' AND ISTASK = 0ORDER BY ACTFINISH DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: ProjekterQuery name: DKNORHS udviklingsprojekterQuery description: Udviklingsprojekter L32Query:SITEID = 'DKNORHS' AND WORKTYPE IN ('PRSML','EC','TPM') AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0ORDER BY STATUSDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligehold i dagQuery name: DKNORHS planlagt vedligehold i dagQuery description: Planlagt vedligehold i dag L32Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND CAST(SCHEDSTART AS DATE) = CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligeholdQuery name: DKNORHS alt vedligeholdQuery description: Alt vedligehold L32 (undtagen fremtidig)Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0 AND CAST(SCHEDSTART AS DATE) <= CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Arbejdsordre-historikQuery name: DKNORHS arbejdsordre-historikQuery description: Arbejdsordre-historik L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN ('PMCAL','PM') ORWORKTYPE IS NULL) AND STATUS IN ('COMP','CLOSE') AND ISTASK = 0ORDER BY ACTFINISH DESC

Kind regards
Julian

Edit: The last query returns about 16,000 results, I don't know if the amount of results is a factor regarding performance. But I tried removing that portlet in my start center and it did not get any quicker.

------------------------------
julian meyer
------------------------------

HI Julian,

Glad it worked for you.

I would suggest you familiarise yourself with the indexes available against the workorder table in database configuration. If you can structure your queries to utilise these, you will have better success relating to speed and performance.

Regarding the classes, work order tracking uses classes of workorder and activity by default.  It can use others but to your point, your data does not so no need to worry. 

The history flag is set to 1 when a work order status is set to internal CLOSE or CAN.  You can consider this archiving of the record.  By not including history flag in your where clause, you are asking Maximo to look through records it does not need to as your original query stated you did not want to see CLOSE or CAN.

Hope that makes sense

Daz

Hi @Darren Hornidge

Thanks for the suggestion. It really performs notably faster. But I don't understand why. I have two questions about your version of the query:

(woclass = 'WORKORDER' or woclass = 'ACTIVITY')

That part is always added in the automatically generated queries, and yours contains it too, just like the ones of the other guys. But I don't see why. Because the results are the same when I leave it out and the performance does not seem to be affected either. As you can see, I have not used it in any of my queries. I want to keep my queries as clean as possible, so if that "woclass" part doesn't add any value I don't want to use it.

Also, why is there an extra set of paranthesis around the first line ?

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')

I tried the query with and without it, it doesn't seem to make a difference.

I am just curious and trying to understand all of it :) The part with the history flag instead of sorting by status did make it quicker, so thank you very much for that.

Best regards
Julian

Hi Julian,

I had a look at your first query - DKNORHS åbne arbejdsordrer  

May I suggest this version below instead. Being that you are looking for workorders that are not equal to COMP CLOSE or CAN, I added in the history flag and just set status not equal to COMP.

This query should be very fast for you

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')
and status != 'COMP'
and (   worktype NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') 
        OR 
        worktype IS NULL
    )
  

Hello everyone

Our maximo is getting slower and slower. It varies, but most of the users have noticed it getting slower over the last few months or so. Sometimes it takes 1-2 minutes until all of the portlets are finished loading.
I am only admin for our site and far from a maximo expert, but not all start centers are slow so maybe it has to do with some of my queries. I make changes to the queries once in a while, but I can't say if it has gotten slower after a particular change I made.

I would be thankful if anyone with maximo and SQL experience would take a look at the queries and give some feedback. Maybe you got a suggestion how to make them perform quicker.

Here are the main queries from our maintenance start center, which is the slowest:

Portlet Type: Result SetApplication: WOTRACKDisplay Name: Åbne arbejdsordrer (rød skrift = venter på ny ejer)Query name: DKNORHS åbne arbejdsordrerQuery description: Åbne arbejdsordrer L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') OR WORKTYPE IS NULL) AND STATUS NOT IN ('COMP','CLOSE','CAN') AND ISTASK = 0ORDER BY REPORTDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Afmeldte arbejdsordrerQuery name: DKNORHS afmeldte arbejdsorderQuery description: Afmeldte arbejdsordrer L32Query:SITEID = 'DKNORHS' AND WORKTYPE != 'PMCAL' AND STATUS = 'COMP' AND ISTASK = 0ORDER BY ACTFINISH DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: ProjekterQuery name: DKNORHS udviklingsprojekterQuery description: Udviklingsprojekter L32Query:SITEID = 'DKNORHS' AND WORKTYPE IN ('PRSML','EC','TPM') AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0ORDER BY STATUSDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligehold i dagQuery name: DKNORHS planlagt vedligehold i dagQuery description: Planlagt vedligehold i dag L32Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND CAST(SCHEDSTART AS DATE) = CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligeholdQuery name: DKNORHS alt vedligeholdQuery description: Alt vedligehold L32 (undtagen fremtidig)Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0 AND CAST(SCHEDSTART AS DATE) <= CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Arbejdsordre-historikQuery name: DKNORHS arbejdsordre-historikQuery description: Arbejdsordre-historik L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN ('PMCAL','PM') ORWORKTYPE IS NULL) AND STATUS IN ('COMP','CLOSE') AND ISTASK = 0ORDER BY ACTFINISH DESC

Kind regards
Julian

Edit: The last query returns about 16,000 results, I don't know if the amount of results is a factor regarding performance. But I tried removing that portlet in my start center and it did not get any quicker.

------------------------------
julian meyer
------------------------------

Hi @Darren Hornidge

Thanks for the suggestion. It really performs notably faster. But I don't understand why. I have two questions about your version of the query:

(woclass = 'WORKORDER' or woclass = 'ACTIVITY')

That part is always added in the automatically generated queries, and yours contains it too, just like the ones of the other guys. But I don't see why. Because the results are the same when I leave it out and the performance does not seem to be affected either. As you can see, I have not used it in any of my queries. I want to keep my queries as clean as possible, so if that "woclass" part doesn't add any value I don't want to use it.

Also, why is there an extra set of paranthesis around the first line ?

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')

I tried the query with and without it, it doesn't seem to make a difference.

I am just curious and trying to understand all of it :) The part with the history flag instead of sorting by status did make it quicker, so thank you very much for that.

Best regards
Julian

Hi Julian,

I had a look at your first query - DKNORHS åbne arbejdsordrer  

May I suggest this version below instead. Being that you are looking for workorders that are not equal to COMP CLOSE or CAN, I added in the history flag and just set status not equal to COMP.

This query should be very fast for you

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')
and status != 'COMP'
and (   worktype NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') 
        OR 
        worktype IS NULL
    )
  

Hello everyone

Our maximo is getting slower and slower. It varies, but most of the users have noticed it getting slower over the last few months or so. Sometimes it takes 1-2 minutes until all of the portlets are finished loading.
I am only admin for our site and far from a maximo expert, but not all start centers are slow so maybe it has to do with some of my queries. I make changes to the queries once in a while, but I can't say if it has gotten slower after a particular change I made.

I would be thankful if anyone with maximo and SQL experience would take a look at the queries and give some feedback. Maybe you got a suggestion how to make them perform quicker.

Here are the main queries from our maintenance start center, which is the slowest:

Portlet Type: Result SetApplication: WOTRACKDisplay Name: Åbne arbejdsordrer (rød skrift = venter på ny ejer)Query name: DKNORHS åbne arbejdsordrerQuery description: Åbne arbejdsordrer L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') OR WORKTYPE IS NULL) AND STATUS NOT IN ('COMP','CLOSE','CAN') AND ISTASK = 0ORDER BY REPORTDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Afmeldte arbejdsordrerQuery name: DKNORHS afmeldte arbejdsorderQuery description: Afmeldte arbejdsordrer L32Query:SITEID = 'DKNORHS' AND WORKTYPE != 'PMCAL' AND STATUS = 'COMP' AND ISTASK = 0ORDER BY ACTFINISH DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: ProjekterQuery name: DKNORHS udviklingsprojekterQuery description: Udviklingsprojekter L32Query:SITEID = 'DKNORHS' AND WORKTYPE IN ('PRSML','EC','TPM') AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0ORDER BY STATUSDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligehold i dagQuery name: DKNORHS planlagt vedligehold i dagQuery description: Planlagt vedligehold i dag L32Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND CAST(SCHEDSTART AS DATE) = CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligeholdQuery name: DKNORHS alt vedligeholdQuery description: Alt vedligehold L32 (undtagen fremtidig)Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0 AND CAST(SCHEDSTART AS DATE) <= CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Arbejdsordre-historikQuery name: DKNORHS arbejdsordre-historikQuery description: Arbejdsordre-historik L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN ('PMCAL','PM') ORWORKTYPE IS NULL) AND STATUS IN ('COMP','CLOSE') AND ISTASK = 0ORDER BY ACTFINISH DESC

Kind regards
Julian

Edit: The last query returns about 16,000 results, I don't know if the amount of results is a factor regarding performance. But I tried removing that portlet in my start center and it did not get any quicker.

------------------------------
julian meyer
------------------------------

Hi Julian,

You asked why "(woclass = 'WORKORDER' or woclass = 'ACTIVITY')" is always added.  We'll firstly, the work order object is shared by other applications, some are Maximo IT related like INCIDENT, PROBLEM, and CHANGE.  When you create a work order it stamped as WOCLASS=WORKORDER, the if you add a child work order or a task, they are labelled "ACTIVITY", but a child work order is further labelled as ISTASK=0, whereas a task is labelled ISTASK=1.

So, in essence it just isolates that you're looking at work orders.

Hi @Darren Hornidge

Thanks for the suggestion. It really performs notably faster. But I don't understand why. I have two questions about your version of the query:

(woclass = 'WORKORDER' or woclass = 'ACTIVITY')

That part is always added in the automatically generated queries, and yours contains it too, just like the ones of the other guys. But I don't see why. Because the results are the same when I leave it out and the performance does not seem to be affected either. As you can see, I have not used it in any of my queries. I want to keep my queries as clean as possible, so if that "woclass" part doesn't add any value I don't want to use it.

Also, why is there an extra set of paranthesis around the first line ?

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')

I tried the query with and without it, it doesn't seem to make a difference.

I am just curious and trying to understand all of it :) The part with the history flag instead of sorting by status did make it quicker, so thank you very much for that.

Best regards
Julian

Hi Julian,

I had a look at your first query - DKNORHS åbne arbejdsordrer  

May I suggest this version below instead. Being that you are looking for workorders that are not equal to COMP CLOSE or CAN, I added in the history flag and just set status not equal to COMP.

This query should be very fast for you

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')
and status != 'COMP'
and (   worktype NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') 
        OR 
        worktype IS NULL
    )
  

Hello everyone

Our maximo is getting slower and slower. It varies, but most of the users have noticed it getting slower over the last few months or so. Sometimes it takes 1-2 minutes until all of the portlets are finished loading.
I am only admin for our site and far from a maximo expert, but not all start centers are slow so maybe it has to do with some of my queries. I make changes to the queries once in a while, but I can't say if it has gotten slower after a particular change I made.

I would be thankful if anyone with maximo and SQL experience would take a look at the queries and give some feedback. Maybe you got a suggestion how to make them perform quicker.

Here are the main queries from our maintenance start center, which is the slowest:

Portlet Type: Result SetApplication: WOTRACKDisplay Name: Åbne arbejdsordrer (rød skrift = venter på ny ejer)Query name: DKNORHS åbne arbejdsordrerQuery description: Åbne arbejdsordrer L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') OR WORKTYPE IS NULL) AND STATUS NOT IN ('COMP','CLOSE','CAN') AND ISTASK = 0ORDER BY REPORTDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Afmeldte arbejdsordrerQuery name: DKNORHS afmeldte arbejdsorderQuery description: Afmeldte arbejdsordrer L32Query:SITEID = 'DKNORHS' AND WORKTYPE != 'PMCAL' AND STATUS = 'COMP' AND ISTASK = 0ORDER BY ACTFINISH DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: ProjekterQuery name: DKNORHS udviklingsprojekterQuery description: Udviklingsprojekter L32Query:SITEID = 'DKNORHS' AND WORKTYPE IN ('PRSML','EC','TPM') AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0ORDER BY STATUSDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligehold i dagQuery name: DKNORHS planlagt vedligehold i dagQuery description: Planlagt vedligehold i dag L32Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND CAST(SCHEDSTART AS DATE) = CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligeholdQuery name: DKNORHS alt vedligeholdQuery description: Alt vedligehold L32 (undtagen fremtidig)Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0 AND CAST(SCHEDSTART AS DATE) <= CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Arbejdsordre-historikQuery name: DKNORHS arbejdsordre-historikQuery description: Arbejdsordre-historik L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN ('PMCAL','PM') ORWORKTYPE IS NULL) AND STATUS IN ('COMP','CLOSE') AND ISTASK = 0ORDER BY ACTFINISH DESC

Kind regards
Julian

Edit: The last query returns about 16,000 results, I don't know if the amount of results is a factor regarding performance. But I tried removing that portlet in my start center and it did not get any quicker.

------------------------------
julian meyer
------------------------------

Hi @Darren Hornidge

Thanks for the suggestion. It really performs notably faster. But I don't understand why. I have two questions about your version of the query:

(woclass = 'WORKORDER' or woclass = 'ACTIVITY')

That part is always added in the automatically generated queries, and yours contains it too, just like the ones of the other guys. But I don't see why. Because the results are the same when I leave it out and the performance does not seem to be affected either. As you can see, I have not used it in any of my queries. I want to keep my queries as clean as possible, so if that "woclass" part doesn't add any value I don't want to use it.

Also, why is there an extra set of paranthesis around the first line ?

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')

I tried the query with and without it, it doesn't seem to make a difference.

I am just curious and trying to understand all of it :) The part with the history flag instead of sorting by status did make it quicker, so thank you very much for that.

Best regards
Julian

------------------------------
julian meyer

Original Message:
Sent: Mon April 08, 2024 12:40 AM
From: Darren Hornidge
Subject: Maximo performance issues

Hi Julian,

I had a look at your first query - DKNORHS åbne arbejdsordrer  

May I suggest this version below instead. Being that you are looking for workorders that are not equal to COMP CLOSE or CAN, I added in the history flag and just set status not equal to COMP.

This query should be very fast for you

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')
and status != 'COMP'
and (   worktype NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') 
        OR 
        worktype IS NULL
    )
  

------------------------------
Darren Hornidge
Asset Care Systems Manager
Coca Cola Europacific Partners
0437215275

Original Message:
Sent: Fri April 05, 2024 02:12 AM
From: julian meyer
Subject: Maximo performance issues

Hello everyone

Our maximo is getting slower and slower. It varies, but most of the users have noticed it getting slower over the last few months or so. Sometimes it takes 1-2 minutes until all of the portlets are finished loading.
I am only admin for our site and far from a maximo expert, but not all start centers are slow so maybe it has to do with some of my queries. I make changes to the queries once in a while, but I can't say if it has gotten slower after a particular change I made.

I would be thankful if anyone with maximo and SQL experience would take a look at the queries and give some feedback. Maybe you got a suggestion how to make them perform quicker.

Here are the main queries from our maintenance start center, which is the slowest:

Portlet Type: Result SetApplication: WOTRACKDisplay Name: Åbne arbejdsordrer (rød skrift = venter på ny ejer)Query name: DKNORHS åbne arbejdsordrerQuery description: Åbne arbejdsordrer L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') OR WORKTYPE IS NULL) AND STATUS NOT IN ('COMP','CLOSE','CAN') AND ISTASK = 0ORDER BY REPORTDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Afmeldte arbejdsordrerQuery name: DKNORHS afmeldte arbejdsorderQuery description: Afmeldte arbejdsordrer L32Query:SITEID = 'DKNORHS' AND WORKTYPE != 'PMCAL' AND STATUS = 'COMP' AND ISTASK = 0ORDER BY ACTFINISH DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: ProjekterQuery name: DKNORHS udviklingsprojekterQuery description: Udviklingsprojekter L32Query:SITEID = 'DKNORHS' AND WORKTYPE IN ('PRSML','EC','TPM') AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0ORDER BY STATUSDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligehold i dagQuery name: DKNORHS planlagt vedligehold i dagQuery description: Planlagt vedligehold i dag L32Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND CAST(SCHEDSTART AS DATE) = CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligeholdQuery name: DKNORHS alt vedligeholdQuery description: Alt vedligehold L32 (undtagen fremtidig)Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0 AND CAST(SCHEDSTART AS DATE) <= CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Arbejdsordre-historikQuery name: DKNORHS arbejdsordre-historikQuery description: Arbejdsordre-historik L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN ('PMCAL','PM') ORWORKTYPE IS NULL) AND STATUS IN ('COMP','CLOSE') AND ISTASK = 0ORDER BY ACTFINISH DESC

Kind regards
Julian

Edit: The last query returns about 16,000 results, I don't know if the amount of results is a factor regarding performance. But I tried removing that portlet in my start center and it did not get any quicker.

------------------------------
julian meyer
------------------------------

Hi Darren,

I'll add to it that whenever the requirement is "not completed", I ask do you mean just the status of COMP or that + its synonyms?  This is because the status is a synonym type of domain and it's not uncommon for clients to have additional synonyms like TECHCOMP.  If that is the case, then the query needs to be 

and status not in (select value from synonymdomain where maxvalue = 'COMP' and domainid = 'WOSTATUS')

------------------------------
Regards, Craig Kokay
Principle Consultant, COSOL
https://cosol.global/

Ph: +61-411-682-040
email: craig.kokay@cosol.global
#IBMChampion

Original Message:
Sent: Mon April 08, 2024 07:58 AM
From: julian meyer
Subject: Maximo performance issues

Hi @Darren Hornidge

Thanks for the suggestion. It really performs notably faster. But I don't understand why. I have two questions about your version of the query:

(woclass = 'WORKORDER' or woclass = 'ACTIVITY')

That part is always added in the automatically generated queries, and yours contains it too, just like the ones of the other guys. But I don't see why. Because the results are the same when I leave it out and the performance does not seem to be affected either. As you can see, I have not used it in any of my queries. I want to keep my queries as clean as possible, so if that "woclass" part doesn't add any value I don't want to use it.

Also, why is there an extra set of paranthesis around the first line ?

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')

I tried the query with and without it, it doesn't seem to make a difference.

I am just curious and trying to understand all of it :) The part with the history flag instead of sorting by status did make it quicker, so thank you very much for that.

Best regards
Julian

------------------------------
julian meyer

Original Message:
Sent: Mon April 08, 2024 12:40 AM
From: Darren Hornidge
Subject: Maximo performance issues

Hi Julian,

I had a look at your first query - DKNORHS åbne arbejdsordrer  

May I suggest this version below instead. Being that you are looking for workorders that are not equal to COMP CLOSE or CAN, I added in the history flag and just set status not equal to COMP.

This query should be very fast for you

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'DKNORHS')
and status != 'COMP'
and (   worktype NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') 
        OR 
        worktype IS NULL
    )
  

------------------------------
Darren Hornidge
Asset Care Systems Manager
Coca Cola Europacific Partners
0437215275

Original Message:
Sent: Fri April 05, 2024 02:12 AM
From: julian meyer
Subject: Maximo performance issues

Hello everyone

Our maximo is getting slower and slower. It varies, but most of the users have noticed it getting slower over the last few months or so. Sometimes it takes 1-2 minutes until all of the portlets are finished loading.
I am only admin for our site and far from a maximo expert, but not all start centers are slow so maybe it has to do with some of my queries. I make changes to the queries once in a while, but I can't say if it has gotten slower after a particular change I made.

I would be thankful if anyone with maximo and SQL experience would take a look at the queries and give some feedback. Maybe you got a suggestion how to make them perform quicker.

Here are the main queries from our maintenance start center, which is the slowest:

Portlet Type: Result SetApplication: WOTRACKDisplay Name: Åbne arbejdsordrer (rød skrift = venter på ny ejer)Query name: DKNORHS åbne arbejdsordrerQuery description: Åbne arbejdsordrer L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN('PMCAL','EC','PM','PMVER','PRSML','TPM','PMAM') OR WORKTYPE IS NULL) AND STATUS NOT IN ('COMP','CLOSE','CAN') AND ISTASK = 0ORDER BY REPORTDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Afmeldte arbejdsordrerQuery name: DKNORHS afmeldte arbejdsorderQuery description: Afmeldte arbejdsordrer L32Query:SITEID = 'DKNORHS' AND WORKTYPE != 'PMCAL' AND STATUS = 'COMP' AND ISTASK = 0ORDER BY ACTFINISH DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: ProjekterQuery name: DKNORHS udviklingsprojekterQuery description: Udviklingsprojekter L32Query:SITEID = 'DKNORHS' AND WORKTYPE IN ('PRSML','EC','TPM') AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0ORDER BY STATUSDATE DESC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligehold i dagQuery name: DKNORHS planlagt vedligehold i dagQuery description: Planlagt vedligehold i dag L32Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND CAST(SCHEDSTART AS DATE) = CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Planlagt vedligeholdQuery name: DKNORHS alt vedligeholdQuery description: Alt vedligehold L32 (undtagen fremtidig)Query:SITEID = 'DKNORHS' AND WORKTYPE = 'PM' AND STATUS NOT IN ('CLOSE','COMP','CAN') AND ISTASK = 0 AND CAST(SCHEDSTART AS DATE) <= CAST(SYSDATE AS DATE)ORDER BY SCHEDSTART ASC-------------------------------------------------------------------------------------------------------------------------------------------Portlet Type: Result SetApplication: WOTRACKDisplay Name: Arbejdsordre-historikQuery name: DKNORHS arbejdsordre-historikQuery description: Arbejdsordre-historik L32Query:SITEID = 'DKNORHS' AND (WORKTYPE NOT IN ('PMCAL','PM') ORWORKTYPE IS NULL) AND STATUS IN ('COMP','CLOSE') AND ISTASK = 0ORDER BY ACTFINISH DESC

Kind regards
Julian

Edit: The last query returns about 16,000 results, I don't know if the amount of results is a factor regarding performance. But I tried removing that portlet in my start center and it did not get any quicker.

------------------------------
julian meyer
------------------------------