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.

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
    )
  

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
    )
  

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
    )
  

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
    )
  

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
    )
  

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
    )
  

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
    )
  

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

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