Performance Issues when using OR instead of AND 1

[cathey04]

I am using CR10, HPUX, OpenView Service Desk on Oracle 9. I have written a CR tracking Service Calls and Incidents handled by our contractor for Support Serices using Views and Tables/Fields. The History table below has gobs and gobs of data in each record. I have written three formulas to track when the call was routed to them, when they accepted it, and when they closed it. Here are the formulas

Route to Tier 3

If {ITSM_HISTORYLINES_SERVICECALL.HSC_SUBJECT} like 'Workgroup from "Tier*" to "Tier 3 AppSupport*.' or {ITSM_HISTORYLINES_SERVICECALL.HSC_SUBJECT} like ['Status set to "Routed".', 'Status from "New" to "Routed".', 'Status from "Pending" to "Routed".', 'Status from "In Progress" to "Routed".', 'Status from "Resolved" to "0_Updated By Client_0".'] and {V_SERVICECALL.ASSWORKGROUP_NAME} startswith "Tier 3 AppSupport" then dateadd("h",-9,{ITSM_HISTORYLINES_SERVICECALL.HSC_CREATED}) else {V_SERVICECALL.CREATED}

Accepted Date/ Time

If {ITSM_HISTORYLINES_SERVICECALL.HSC_SUBJECT} like ['Status from "Routed" to "In Progress".', 'Status from "Pending" to "In Progress".', 'Status from "In Progress" to "Pending".', 'Status from "Routed" to "Pending".'] then dateadd("h",-9,{ITSM_HISTORYLINES_SERVICECALL.HSC_CREATED}) else {V_SERVICECALL.CREATED}

Resolved

dateadd("h",-1,{V_SERVICECALL.ACTUALFINISH})

When I go to Selection Criteria and enter the following

{V_SERVICECALL.ASSWORKGROUP_NAME} startswith "Tier 3 AppSupport" and
{@Routed to Tier 3} in LastFullWeek and
{@Accepted Date/Time} in LastFullWeek and
{@Resolved} in LastFullWeek and
{ITSM_HISTORYLINES_SERVICECALL.HSC_SER_OID} > 2200000

the report runs fairly quick (still slow), but when I replace and with or, so I can pick up any edits during the LastFulWeek, the reports tanks and I get an error "Unable to Open a Rowset" and then another that says I have run out of temp space, which I can get fixed, but this is after three hours. What can I write in to get any edits without using or?

Thanks so much for any help!

 

[Tatertot45]

You may want to try adding Parens in your select statement, like below.

{V_SERVICECALL.ASSWORKGROUP_NAME} startswith "Tier 3 AppSupport" and
({@Routed to Tier 3} in LastFullWeek or
{@Accepted Date/Time} in LastFullWeek or
{@Resolved} in LastFullWeek) and
{ITSM_HISTORYLINES_SERVICECALL.HSC_SER_OID} > 2200000

Other option might be to create a report that would capture all of your Edits seperately.

 

[cathey04]

I will give this a try, and I can't believe I didn't try this before - I will let you know if it helps, Thanks

 

[lbass]

You can't just switch between and's and or's--you'll get entirely different sets of data. The logic of the report dictates which to use.

Do you have the option of using code fields instead of searching for these lengthy text fields? You could speed up the report by doing this differently.

I don't follow why you are using "like" without any wildcards. I would just use = or "in".

-LB

 

[cathey04]

Thanks LBass, I don't understand what you mean in the last line. Is this in the formulas or selection criteria? Also in the formulas I used an asterick in some, but I am not sure if I used it correctly or if there are other wildcards available. Any help would be appreciate big time!

 

[lbass]

In your first formula, you have asterisks nested within two sets of quotes (quotes within quotes). What did you intend for the asterisks to do? Since the entire phrase is wrapped in single quotes, it is one object in that clause, not a range of values. As for the rest of the formulas, I'm not sure what using 'Like' adds. If you place ITSM_HISTORYLINES_SERVICECALL.HSC_SUBJECT} in your detail section, how does it display? Please show some samples.

And again, do you have code fields you could use instead of these text fields?

-LB

 

[cathey04]

Here is a sample from the History Table from one Service Call - my formula is trying to determine if the call was routed to Tier 3 AppSupport (there are over 150 workgroups starting with Tier 3 AppSupport)after being sent to other groups by mistake. I don't have the code tables, although my DBA is building me a table with what I need instead of using views on Friday. I use "like" because I wasn't sure if startswith would work or not - sometimes it does and sometimes it doesn't - depends on the query.

SC# Date Subject
2237722 9/16/07 6:46 Status set to "New".
2237722 9/16/07 6:46 Folder set to "Public".
2237722 9/16/07 6:46 Priority set to "Severity 3 - High [48 Hours]".
2237722 9/16/07 6:46 Workgroup set to "Tier 1 Service Desk".
2237722 9/16/07 6:46 Impact set to "Severity 3 (Small number of users impacted or workaround exists)".
2237722 9/16/07 6:46 Deadline set to "09/18/07 06:46 MST".
2237722 9/16/07 6:46 Reported Method set to "Phone".
2237722 9/16/07 6:47 Alternate Phone set to "781.596.9625".
2237722 9/16/07 6:47 Category set to "Software/Application Problem".
2237722 9/16/07 6:54 Description set to "ARx- Ins billing is going through at midnight and is getting rejected".
2237722 9/16/07 6:56 Planned Finish set to "09/18/07 06:46 America/Denver".
2237722 9/16/07 6:56 Configuration Item set to "SWAPARX".
2237722 9/16/07 6:56 Service set to "Prescription Fulfillment ".
2237722 9/16/07 6:56 Planned Finish from "09/18/07 06:46 America/Denver" to "09/17/07 06:46 America/Denver".
2237722 9/16/07 6:56 Impact from "Severity 3 (Small number of users impacted or workaround exists)" to "Severity 2 (Site/Organization affected and workaround exists)".
2237722 9/16/07 6:56 Priority from "Severity 3 - High [48 Hours]" to "Severity 2 - High [24 Hours]".
2237722 9/16/07 6:56 Deadline from "09/18/07 06:46 MST" to "09/17/07 06:46 MST".
2237722 9/16/07 6:58 Workgroup from "Tier 1 Service Desk" to "Tier 2 Ops ARx Applications & Peripherals".
2237722 9/16/07 6:58 Status from "New" to "Routed".
2237722 9/16/07 8:40 Priority from "Severity 2 - High [24 Hours]" to "Severity 2 - Medium [48 Hours]".
2237722 9/16/07 8:41 Deadline from "09/17/07 07:46 CST" to "09/18/07 07:46 CST".
2237722 9/16/07 8:41 Planned Finish from "09/17/07 06:46 America/Denver" to "09/18/07 06:46 America/Denver".
2237722 9/17/07 5:42 Actual Start set to "09/17/07 06:42 CST".
2237722 9/17/07 5:42 Status from "Routed" to "In Progress".
2237722 9/17/07 5:42 Assignee set to "Judy Howard".
2237722 9/17/07 12:36 Status from "In Progress" to "Routed".
2237722 9/17/07 12:36 Assignee has been cleared.
2237722 9/17/07 12:36 Workgroup from "Tier 2 Ops ARx Applications & Peripherals" to "Tier 3 AppSupport HCS ARx".
2237722 9/18/07 11:12 Status from "Routed" to "In Progress".
2237722 9/18/07 11:12 Assignee set to "Susan Thomas".
2237722 9/18/07 12:28 Description from "ARx- Ins billing is going through at midnight and is getting rejected" to "S7578 - ARx- Ins billing going through at midnight and getting rejected".
2237722 9/20/07 11:18 "Solution set to ""There is no current easy resolution. Jira ARx#1218 scheduled for 1.8.0.
Talked to store to let them know.""."
2237722 9/20/07 11:18 Actual Finish set to "09/20/07 12:18 CST".
2237722 9/20/07 11:18 Status from "In Progress" to "Resolved".
2237722 9/20/07 11:18 Actual Duration set to "77:35".
2237722 9/20/07 11:18 Closure code set to "Software".
2237722 9/23/07 12:12 Status from "Resolved" to "Closed".

 

[lbass]

It appears that the phrases you are checking for appear in total your formula, so there is no need for "like", but maybe changing it to "in" wouldn't affect performance much anyway.

Perhaps the new table will help resolve the issues.

-LB

 

[cathey04]

I will try subsituting "in" for "like" and give it a shot. Thanks for your help!

 

[cathey04]

I replaced all the formula "like" for "in" and it cut the time down by two hours. Thank you so much!