Fellow Remedy Developers....

Well.. I just did it again...
Enabled an escalation which fired off hundreds of FALSE warning emails to
staff members..

I just sent my appology email.. and disabled the escalation....

HELP...

the escalation in question is supposed to send off an email message
indicating a request is past it's due date.. when the status of the
request is not yet completed... and a previous warning message was not yet
sent.

The qualification for this escalation follows:

( 'Due Date' < $DATE$) AND ( 'Status' < "Completed") AND ( 'Escalation
Email' = $NULL$ )

The Due Date fields is datatype Date..

For some reason, entries with Due Date values in the future are passing
the qualification.. and messages are flying..

ANY IDEAS... isn't the $DATE$ reference in a qualification of an
escalation correct? or should I be using the $\DATE$ keyword?

Eggman..... Phil Mougis

BAD QUAL -- BAD -- Evil !!!

( 'Due Date' <= $DATE$) AND ( 'Status' <= "ONE BEFORE Completed") AND (
'Escalation Email' = $NULL$ )
Note:: < If AT ALL POSSIBLE:: use an = with EVERY > or < otherwise you
will NEVER use an index.
Speed is a Killer of the Common Server ! Or Lack thereof !

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as
a helpful Hint !
If this were an Real Emergency your server would not be running, but
Begging for relief.

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Phil Mougis
Sent: Thursday, March 18, 2004 10:25 AM
To: ARSLIST@ARSLIST.ORG
Subject: EGG on my FACE


Fellow Remedy Developers....

Well.. I just did it again...
Enabled an escalation which fired off hundreds of FALSE warning emails to
staff members..

I just sent my appology email.. and disabled the escalation....

HELP...

the escalation in question is supposed to send off an email message
indicating a request is past it's due date.. when the status of the request
is not yet completed... and a previous warning message was not yet sent.

The qualification for this escalation follows:

( 'Due Date' < $DATE$) AND ( 'Status' < "Completed") AND ( 'Escalation
Email' = $NULL$ )

The Due Date fields is datatype Date..

For some reason, entries with Due Date values in the future are passing the
qualification.. and messages are flying..

ANY IDEAS... isn't the $DATE$ reference in a qualification of an
escalation correct? or should I be using the $\DATE$ keyword?

Eggman..... Phil Mougis



UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com

Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the
performance... not the result set?

Is there a BUG in Remedy at play here?

Now.. if I use your suggestion... I should probably use a representation for
Yesterday.
$DATE$ - 86400...

( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND (
'Escalation Email' = $NULL$ )

There is a known bug with $DATE$ and Date fields in qualifications. As a
work around you must add a Date/Time field which is set to the value of 'Due
Date' on Submit and Modify, and use that in your qualification instead.

At least you can take solace from the thought it wasn't your silly mistake
but Remedy's.

David Adams
Corporate Information Services
Information Systems Services
University of Southampton

----- Original Message -----
From: "Phil Mougis"
To:
Sent: Thursday, March 18, 2004 3:24 PM
Subject: EGG on my FACE


> Fellow Remedy Developers....
>
> Well.. I just did it again...
> Enabled an escalation which fired off hundreds of FALSE warning emails to
> staff members..
>
> I just sent my appology email.. and disabled the escalation....
>
> HELP...
>
> the escalation in question is supposed to send off an email message
> indicating a request is past it's due date.. when the status of the
> request is not yet completed... and a previous warning message was not yet
> sent.
>
> The qualification for this escalation follows:
>
> ( 'Due Date' < $DATE$) AND ( 'Status' < "Completed") AND ( 'Escalation
> Email' = $NULL$ )
>
> The Due Date fields is datatype Date..
>
> For some reason, entries with Due Date values in the future are passing
> the qualification.. and messages are flying..
>
> ANY IDEAS... isn't the $DATE$ reference in a qualification of an
> escalation correct? or should I be using the $\DATE$ keyword?
>
> Eggman..... Phil Mougis
>
>


> UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com
>

Actually, I just had that problem on something I am designing from scratch,
so it isn't as bad when I spammed myself. What I found is for a "DATE"
field as opposed to a date/time field, you can't use the keyword $DATE$.
$DATE$ returns the number of second from Jan 1, 1970 to the start of the
current day. Where as a "DATE" field uses the number of days from some date
of which I am not sure. So you almost have to do a $DATE$/86400 into an
integer to drop the remainder. What I found to be easier, although not
necessarily so in an escalation, was to put $DATE$ into a "DATE" field and
then compare the two fields.

I hope this gives you some direction.

Brian Goralczyk
Senior Remedy Developer
Verizon Wireless
5165 Emerald Parkway
Dublin, OH 43017

Phone : 614.560.8867
Mobile : 614.203.8866


-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: Thursday, March 18, 2004 10:50 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: [ARSLIST] EGG on my FACE


BAD QUAL -- BAD -- Evil !!!

( 'Due Date' <= $DATE$) AND ( 'Status' <= "ONE BEFORE Completed") AND (
'Escalation Email' = $NULL$ )
Note:: < If AT ALL POSSIBLE:: use an = with EVERY > or < otherwise you
will NEVER use an index.
Speed is a Killer of the Common Server ! Or Lack thereof !

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as
a helpful Hint !
If this were an Real Emergency your server would not be running, but
Begging for relief.

-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Phil Mougis
Sent: Thursday, March 18, 2004 10:25 AM
To: ARSLIST@ARSLIST.ORG
Subject: EGG on my FACE


Fellow Remedy Developers....

Well.. I just did it again...
Enabled an escalation which fired off hundreds of FALSE warning emails to
staff members..

I just sent my appology email.. and disabled the escalation....

HELP...

the escalation in question is supposed to send off an email message
indicating a request is past it's due date.. when the status of the request
is not yet completed... and a previous warning message was not yet sent.

The qualification for this escalation follows:

( 'Due Date' < $DATE$) AND ( 'Status' < "Completed") AND ( 'Escalation
Email' = $NULL$ )

The Due Date fields is datatype Date..

For some reason, entries with Due Date values in the future are passing the
qualification.. and messages are flying..

ANY IDEAS... isn't the $DATE$ reference in a qualification of an
escalation correct? or should I be using the $\DATE$ keyword?

Eggman..... Phil Mougis



UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com



UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com

Phil,
If the notification should only be sent when a request is past its due date,
you should logically use 'Due Date' >= $DATE$ (instead of 'Due Date' <
$DATE$).

However, since you said the 'Due Date' field is a Date field (not a
Date/Time field), this still won't work due to an ARS bug that involves the
use of the $DATE$ keyword in workflow qualifications against a Date field.
Essentially, the $DATE$ keyword incorrectly returns a Date/Time value,
regardless of whether it is being evaluated against a Date field or a
Date/Time field.

One way to resolve this would be to replace your Date field with a Date/Time
field. You can set the display properties of the Date/Time field to display
as "Date Only" if desired.

Another method would be to add another Date field to your form (e.g.,
'tmpCurrentDate'). This would need to be a permanent field (not
display-only). Then run a daily escalation that fires before your
notification escalation for all records where ('Status' < "Completed").
This escalation would need a single set fields action to set the
'tmpCurrentDate' field to $DATE$ (the $DATE$ keyword does return correctly
when used as a set fields value for a Date field, it just doesn't work
consistently in qualifications). In your notification escalation, you could
then replace the $DATE$ keyword with the 'tmpCurrentDate' field. For
example:

( 'Due Date' >= 'tmpCurrentDate') AND ( 'Status' < "Completed") AND (
'EscalationEmail' = $NULL$ )

Hope this helps.

Regards,

Thomas

----- Original Message -----
From: "Phil Mougis"
Newsgroups: cogniza.remedy.arsystem.general
To:
Sent: Thursday, March 18, 2004 9:24 AM
Subject: EGG on my FACE


> Fellow Remedy Developers....
>
> Well.. I just did it again...
> Enabled an escalation which fired off hundreds of FALSE warning emails to
> staff members..
>
> I just sent my appology email.. and disabled the escalation....
>
> HELP...
>
> the escalation in question is supposed to send off an email message
> indicating a request is past it's due date.. when the status of the
> request is not yet completed... and a previous warning message was not yet
> sent.
>
> The qualification for this escalation follows:
>
> ( 'Due Date' < $DATE$) AND ( 'Status' < "Completed") AND ( 'Escalation
> Email' = $NULL$ )
>
> The Due Date fields is datatype Date..
>
> For some reason, entries with Due Date values in the future are passing
> the qualification.. and messages are flying..
>
> ANY IDEAS... isn't the $DATE$ reference in a qualification of an
> escalation correct? or should I be using the $\DATE$ keyword?
>
> Eggman..... Phil Mougis
>
>


> UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com

**

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.

I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...

-----Original Message-----
From: Action Request System discussion list(ARSList) [ mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?

Is there a BUG in Remedy at play here?

Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...

( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )




This posting was submitted via the Web interface

**
That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?

Gareth

-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.

I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...

-----Original Message-----
From: Action Request System discussion list(ARSList) [ mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?

Is there a BUG in Remedy at play here?

Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...

( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )




This posting was submitted via the Web interface


This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.

This posting was submitted via the Web interface

This is not always true, it all depend on the optimizer and the query alanyzer, and how they decide to do the query:

USES INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'

Regards,
Jarl


-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG]On Behalf Of Thomas Bean
Sent: 18. mars 2004 20:06
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
Pat,
LIKE statements should still use indexes as long as you don't use wildcards at the beginning of the comparison string. For example (assume 'mycolumn' is indexed):
USES INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'
DOES NOT USE INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE '%some text%'
--Thomas
----- Original Message -----
From: Zandi Patrick S TSgt AFRL/IFOSS
Newsgroups: cogniza.remedy.arsystem.general
To: ARSLIST@ARSLIST.ORG
Sent: Thursday, March 18, 2004 12:21 PM
Subject: Re: EGG on my FACE


**
ALL --- I have been to 2 (yes this is true) Remedy Performance Classes.. and this is a Fundamental Teaching.

This is also and has also be taught at all 5 years of (yes 5) Remedy User Group Conferences in both Tutorials, and Sessions.

This is also True when looking at some books out there... I have..
Oracle Performance books, ARS Guides on Query's, I guess I will stop there for now.

"=" equals Index if Available.
Like equals Table Scan forever.
> equals Table scan of all rows

Test it yourself.. ensure the table is not in memory first, now with some DB's you can do that.. but a large server will not.. let say 30 million rows...
you can cache some but not all...
Take the 30K row and do a query on a field let say a Char field, and put the query in as a >
Do the same thing with a >=
I have tested and tested this.. with Oracle and mssql and it is consistent...
30 seconds for first one
1 second for the second.
NOTE:: If there is an index for that field. If no index -- it just doesn't matter.. it is still a Table Scan...
OR a LIKE statement will Kill you too...

http://www.atg.com/repositories/ContentCatalogRepositoryen/manuals/ATG6.2.0/installdas/installdas2207.html

http://www.teradataforum.com/teradata/20020610191632.htm

http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf/@GenericBookTextView/2137;pt=2087

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des051.htm

Those Above Might help you out a little...

First Ensure your Queries are properly formatted,
Then if you still have issues, look to make the table into RAM for Performance enhancements.

Hope that helps some...

Don't get me wrong.. Sometime.. Not often.. you cannot do a >= .... So don't panic too much...
Just a little Performance Reminder from your Performance Conscious Fellow ARSlist'er....








-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thackeray, Gareth
Sent: Thursday, March 18, 2004 12:09 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?

Gareth
-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.
I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...
-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?
Is there a BUG in Remedy at play here?
Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...
( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )


This posting was submitted via the Web interface

This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
This posting was submitted via the Web interface
This posting was submitted via the Web interface
This posting was submitted via the Web interface

Jarl,

How could a search where the ONLY criteria was a LIKE substring search NOT do a table scan?

Rick

-----Original Message-----
From: Action Request System discussion list(ARSList) on behalf of Jarl Groneng
Sent: Thu 3/18/2004 11:46 AM
To: ARSLIST@ARSLIST.ORG
Cc:
Subject: Re: EGG on my FACE [bcc][faked-from]



This is not always true, it all depend on the optimizer and the query alanyzer, and how they decide to do the query:

USES INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'

Regards,
Jarl


-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG]On Behalf Of Thomas Bean
Sent: 18. mars 2004 20:06
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
Pat,
LIKE statements should still use indexes as long as you don't use wildcards at the beginning of the comparison string. For example (assume 'mycolumn' is indexed):
USES INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'
DOES NOT USE INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE '%some text%'
--Thomas
----- Original Message -----
From: Zandi Patrick S TSgt AFRL/IFOSS
Newsgroups: cogniza.remedy.arsystem.general
To: ARSLIST@ARSLIST.ORG
Sent: Thursday, March 18, 2004 12:21 PM
Subject: Re: EGG on my FACE


**
ALL --- I have been to 2 (yes this is true) Remedy Performance Classes.. and this is a Fundamental Teaching.

This is also and has also be taught at all 5 years of (yes 5) Remedy User Group Conferences in both Tutorials, and Sessions.

This is also True when looking at some books out there... I have..
Oracle Performance books, ARS Guides on Query's, I guess I will stop there for now.

"=" equals Index if Available.
Like equals Table Scan forever.
> equals Table scan of all rows

Test it yourself.. ensure the table is not in memory first, now with some DB's you can do that.. but a large server will not.. let say 30 million rows...
you can cache some but not all...
Take the 30K row and do a query on a field let say a Char field, and put the query in as a >
Do the same thing with a >=
I have tested and tested this.. with Oracle and mssql and it is consistent...
30 seconds for first one
1 second for the second.
NOTE:: If there is an index for that field. If no index -- it just doesn't matter.. it is still a Table Scan...
OR a LIKE statement will Kill you too...

http://www.atg.com/repositories/ContentCatalogRepositoryen/manuals/ATG6.2.0/installdas/installdas2207.html

http://www.teradataforum.com/teradata/20020610191632.htm

http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf/@GenericBookTextView/2137;pt=2087

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des051.htm

Those Above Might help you out a little...

First Ensure your Queries are properly formatted,
Then if you still have issues, look to make the table into RAM for Performance enhancements.

Hope that helps some...

Don't get me wrong.. Sometime.. Not often.. you cannot do a >= .... So don't panic too much...
Just a little Performance Reminder from your Performance Conscious Fellow ARSlist'er....








-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thackeray, Gareth
Sent: Thursday, March 18, 2004 12:09 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?

Gareth
-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.
I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...
-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?
Is there a BUG in Remedy at play here?
Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...
( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )


This posting was submitted via the Web interface

This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
This posting was submitted via the Web interface
This posting was submitted via the Web interface
This posting was submitted via the Web interface

It can depend on the amout of records in the table.

On Oracle we had a lot of issues where AR search the arreference table (where the filter/al guides are stored). Orcle would not use the index, and opening an filter guide could take 30-40 seconds. Even there is an index on the table. The only solution was to force Oracle to bypass the analyser. (this was done by our DBA)

What I'm trying to say is; does not always trust your database and indexes. Doing an explain table and also check bytes read will easily show if indexes are used.


Regards,
Jarl


> -----Original Message-----
> From: Action Request System discussion list(ARSList)
> [mailto:ARSLIST@ARSLIST.ORG]On Behalf Of Rick Cook
> Sent: 18. mars 2004 21:33
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: EGG on my FACE
>
>
> Jarl,
>
> How could a search where the ONLY criteria was a LIKE
> substring search NOT do a table scan?
>
> Rick
>
> -----Original Message-----
> From: Action Request System discussion list(ARSList) on
> behalf of Jarl Groneng
> Sent: Thu 3/18/2004 11:46 AM
> To: ARSLIST@ARSLIST.ORG
> Cc:
> Subject: Re: EGG on my FACE [bcc][faked-from]
>
>
>
> This is not always true, it all depend on the optimizer
> and the query alanyzer, and how they decide to do the query:
>
> USES INDEX:
> SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'
>
> Regards,
> Jarl
>
>
> -----Original Message-----
> From: Action Request System discussion list(ARSList)
> [mailto:ARSLIST@ARSLIST.ORG]On Behalf Of Thomas Bean
> Sent: 18. mars 2004 20:06
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: EGG on my FACE
>
>
> **
> Pat,
> LIKE statements should still use indexes as long as you
> don't use wildcards at the beginning of the comparison
> string. For example (assume 'mycolumn' is indexed):
> USES INDEX:
> SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'
> DOES NOT USE INDEX:
> SELECT * FROM mytable WHERE mycolumn LIKE '%some text%'
> --Thomas
> ----- Original Message -----
> From: Zandi Patrick S TSgt AFRL/IFOSS
> Newsgroups: cogniza.remedy.arsystem.general
> To: ARSLIST@ARSLIST.ORG
> Sent: Thursday, March 18, 2004 12:21 PM
> Subject: Re: EGG on my FACE
>
>
> **
> ALL --- I have been to 2 (yes this is true) Remedy
> Performance Classes.. and this is a Fundamental Teaching.
>
> This is also and has also be taught at all 5 years of
> (yes 5) Remedy User Group Conferences in both Tutorials, and Sessions.
>
> This is also True when looking at some books out
> there... I have..
> Oracle Performance books, ARS Guides on Query's, I
> guess I will stop there for now.
>
> "=" equals Index if Available.
> Like equals Table Scan forever.
> > equals Table scan of all rows
>
> Test it yourself.. ensure the table is not in memory
> first, now with some DB's you can do that.. but a large
> server will not.. let say 30 million rows...
> you can cache some but not all...
> Take the 30K row and do a query on a field let say
> a Char field, and put the query in as a >
> Do the same thing with a >=
> I have tested and tested this.. with Oracle and mssql
> and it is consistent...
> 30 seconds for first one
> 1 second for the second.
> NOTE:: If there is an index for that field. If no index
> -- it just doesn't matter.. it is still a Table Scan...
> OR a LIKE statement will Kill you too...
>
>
> http://www.atg.com/repositories/ContentCatalogRepositoryen/ma
> nuals/ATG6.2.0/installdas/installdas2207.html
>
> http://www.teradataforum.com/teradata/20020610191632.htm
>
>
> http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseper
> f/@GenericBookTextView/2137;pt=2087
>
>
> http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des
> 051.htm
>
> Those Above Might help you out a little...
>
> First Ensure your Queries are properly formatted,
> Then if you still have issues, look to make the table
> into RAM for Performance enhancements.
>
> Hope that helps some...
>
> Don't get me wrong.. Sometime.. Not often.. you cannot
> do a >= .... So don't panic too much...
> Just a little Performance Reminder from your
> Performance Conscious Fellow ARSlist'er....
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: Action Request System discussion list(ARSList)
> [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thackeray, Gareth
> Sent: Thursday, March 18, 2004 12:09 PM
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: EGG on my FACE
>
>
> **
> That seems crazy! Surely a decent DBMS should not have
> a hard and fast rule like that. In most cases <= and < would
> return almost the same dataset. Which DBMS are you talking
> about in particular? Or does it affect all of them?
>
> Gareth
> -----Original Message-----
> From: Zandi Patrick S TSgt AFRL/IFOSS
[mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.
I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...
-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?
Is there a BUG in Remedy at play here?
Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...
( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )


This posting was submitted via the Web interface

This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
This posting was submitted via the Web interface
This posting was submitted via the Web interface
This posting was submitted via the Web interface

**
**

I’m new to this forum, but very familiar with Access. < and <= can be very different.



Example, if you were querying for the first 6 months in a year Vs the last 6 months in a year. If Month < 6 or if Month > 6 totally leaves out June. Simple example but my point is that 8.33% of the records would not be picked up.



HTH



-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thackeray, Gareth
Sent: Thursday, March 18, 2004 11:09 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE



That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?



Gareth

-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE

**

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.

I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...

-----Original Message-----
From: Action Request System discussion list(ARSList) [ mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE



Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?

Is there a BUG in Remedy at play here?

Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...

( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )




This posting was submitted via the Web interface


This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.

This posting was submitted via the Web This posting was submitted via the Web interface interface

**
ALL --- I have been to 2 (yes this is true) Remedy Performance Classes.. and this is a Fundamental Teaching.

This is also and has also be taught at all 5 years of (yes 5) Remedy User Group Conferences in both Tutorials, and Sessions.

This is also True when looking at some books out there... I have..
Oracle Performance books, ARS Guides on Query's, I guess I will stop there for now.

"=" equals Index if Available.
Like equals Table Scan forever.
> equals Table scan of all rows

Test it yourself.. ensure the table is not in memory first, now with some DB's you can do that.. but a large server will not.. let say 30 million rows...
you can cache some but not all...
Take the 30K row and do a query on a field let say a Char field, and put the query in as a >
Do the same thing with a >=
I have tested and tested this.. with Oracle and mssql and it is consistent...
30 seconds for first one
1 second for the second.
NOTE:: If there is an index for that field. If no index -- it just doesn't matter.. it is still a Table Scan...
OR a LIKE statement will Kill you too...

http://www.atg.com/repositories/ContentCatalogRepositoryen/manuals/ATG6.2.0/installdas/installdas2207.html

http://www.teradataforum.com/teradata/20020610191632.htm

http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf/@GenericBookTextView/2137;pt=2087

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des051.htm

Those Above Might help you out a little...

First Ensure your Queries are properly formatted,
Then if you still have issues, look to make the table into RAM for Performance enhancements.

Hope that helps some...

Don't get me wrong.. Sometime.. Not often.. you cannot do a >= .... So don't panic too much...
Just a little Performance Reminder from your Performance Conscious Fellow ARSlist'er....









-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thackeray, Gareth
Sent: Thursday, March 18, 2004 12:09 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?

Gareth

-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.

I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...

-----Original Message-----
From: Action Request System discussion list(ARSList) [ mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?

Is there a BUG in Remedy at play here?

Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...

( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )




This posting was submitted via the Web interface


This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
This posting was submitted via the Web interface

This posting was submitted via the Web interface

**
Gareth,
I don't think that is a hard and fast rule, at least not with every RDBMS. My database is Informix, I ran a test with two separate queries, one used "< 2" and another used "<= 1" against an indexed integer field (arprstatus). This is actually a view column that references the indexed c7 column in my t58 table. Running these queries with "set explain on" shows that the index is used in either case:

QUERY:
------
select distinct arprstatus,count(*)
from cpipcrrecords
where arprstatus < 2
group by 1 order by 1

Estimated Cost: 2
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) root.t58: INDEX PATH

(1) Index Keys: c7 (Key-Only) (Serial, fragments: ALL)
Upper Index Filter: root.t58.c7 < 2



QUERY:
------
select distinct arprstatus,count(*)
from cpipcrrecords
where arprstatus <= 1
group by 1 order by 1

Estimated Cost: 2
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) root.t58: INDEX PATH

(1) Index Keys: c7 (Key-Only) (Serial, fragments: ALL)
Upper Index Filter: root.t58.c7 <= 1

If the index had not been used, it would have shown "SEQUENTIAL SCAN" instead of "INDEX PATH". I haven't tested with other databases, but it would appear that using "<" instead of "<=" does not cause sequential scans on Informix. Even if it did, I do not agree that not using an "=" with EVERY ">" or "<" would result in an index NEVER being used, even when there are other indexed columns in the query. I think that a true sequential scan should only result when there are NO indexed columns in the query.

Thomas

----- Original Message -----
From: Thackeray, Gareth
Newsgroups: cogniza.remedy.arsystem.general
To: ARSLIST@ARSLIST.ORG
Sent: Thursday, March 18, 2004 11:09 AM
Subject: Re: EGG on my FACE

**
That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?

Gareth

-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.

I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...

-----Original Message-----
From: Action Request System discussion list(ARSList) [ mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?

Is there a BUG in Remedy at play here?

Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...

( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )




This posting was submitted via the Web interface


This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
This posting was submitted via the Web interface

This posting was submitted via the Web interface

**
Pat,
LIKE statements should still use indexes as long as you don't use wildcards at the beginning of the comparison string. For example (assume 'mycolumn' is indexed):

* USES INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'
* DOES NOT USE INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE '%some text%'

--Thomas

----- Original Message -----
From: Zandi Patrick S TSgt AFRL/IFOSS
Newsgroups: cogniza.remedy.arsystem.general
To: ARSLIST@ARSLIST.ORG
Sent: Thursday, March 18, 2004 12:21 PM
Subject: Re: EGG on my FACE

**
ALL --- I have been to 2 (yes this is true) Remedy Performance Classes.. and this is a Fundamental Teaching.

This is also and has also be taught at all 5 years of (yes 5) Remedy User Group Conferences in both Tutorials, and Sessions.

This is also True when looking at some books out there... I have..
Oracle Performance books, ARS Guides on Query's, I guess I will stop there for now.

"=" equals Index if Available.
Like equals Table Scan forever.
> equals Table scan of all rows

Test it yourself.. ensure the table is not in memory first, now with some DB's you can do that.. but a large server will not.. let say 30 million rows...
you can cache some but not all...
Take the 30K row and do a query on a field let say a Char field, and put the query in as a >
Do the same thing with a >=
I have tested and tested this.. with Oracle and mssql and it is consistent...
30 seconds for first one
1 second for the second.
NOTE:: If there is an index for that field. If no index -- it just doesn't matter.. it is still a Table Scan...
OR a LIKE statement will Kill you too...

http://www.atg.com/repositories/ContentCatalogRepositoryen/manuals/ATG6.2.0/installdas/installdas2207.html

http://www.teradataforum.com/teradata/20020610191632.htm

http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf/@GenericBookTextView/2137;pt=2087

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des051.htm

Those Above Might help you out a little...

First Ensure your Queries are properly formatted,
Then if you still have issues, look to make the table into RAM for Performance enhancements.

Hope that helps some...

Don't get me wrong.. Sometime.. Not often.. you cannot do a >= .... So don't panic too much...
Just a little Performance Reminder from your Performance Conscious Fellow ARSlist'er....









-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thackeray, Gareth
Sent: Thursday, March 18, 2004 12:09 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?

Gareth

-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.

I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...

-----Original Message-----
From: Action Request System discussion list(ARSList) [ mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?

Is there a BUG in Remedy at play here?

Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...

( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )




This posting was submitted via the Web interface


This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
This posting was submitted via the Web interface

This posting was submitted via the Web interface

This posting was submitted via the Web interface

**
Well, hey, since you got me on a Passionate Rampage...

I figured I will just throw this one in for Free..
Do not use a ! either.. it is not efficient... Pure Table Scan Baby ! even a > is better than that ...
Oooh,
Don't ever do MATH on the LEFT Either I.E

1) $TIMESTAMP$ - 'Create date' > 60*60*24 <>
2) 'Create Date' < $TIMESTAMP$ - 60*60*24 <>
3) 'Create Date' <= $TIMESTAMP$ - 60*60*24 <>

With a table of 300,000 records..
#1 = 300,000 Math Equations Compared to Each row << Nasty !! >>
#2 = 1 Value Compared to 300,000 rows (sometimes if index is available, index will be used)
#3 = 1 Value Compared to 3,000 Rows of Indexed data First, then Table scan if one is not available.



See some of the differences...

I may not be perfect in my explanations.. but I know this is pretty important stuff....
I am sure someone on the list will correct some typo or improperly formatted email I am Sure !!!
There are a lot of Great folks out there...

Have a Great Day !!! =8 ^)



-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS
Sent: Thursday, March 18, 2004 1:21 PM
To: 'arslist@ARSLIST.ORG'
Subject: RE: EGG on my FACE


ALL --- I have been to 2 (yes this is true) Remedy Performance Classes.. and this is a Fundamental Teaching.

This is also and has also be taught at all 5 years of (yes 5) Remedy User Group Conferences in both Tutorials, and Sessions.

This is also True when looking at some books out there... I have..
Oracle Performance books, ARS Guides on Query's, I guess I will stop there for now.

"=" equals Index if Available.
Like equals Table Scan forever.
> equals Table scan of all rows

Test it yourself.. ensure the table is not in memory first, now with some DB's you can do that.. but a large server will not.. let say 30 million rows...
you can cache some but not all...
Take the 30K row and do a query on a field let say a Char field, and put the query in as a >
Do the same thing with a >=
I have tested and tested this.. with Oracle and mssql and it is consistent...
30 seconds for first one
1 second for the second.
NOTE:: If there is an index for that field. If no index -- it just doesn't matter.. it is still a Table Scan...
OR a LIKE statement will Kill you too...

http://www.atg.com/repositories/ContentCatalogRepositoryen/manuals/ATG6.2.0/installdas/installdas2207.html

http://www.teradataforum.com/teradata/20020610191632.htm

http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf/@GenericBookTextView/2137;pt=2087

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des051.htm

Those Above Might help you out a little...

First Ensure your Queries are properly formatted,
Then if you still have issues, look to make the table into RAM for Performance enhancements.

Hope that helps some...

Don't get me wrong.. Sometime.. Not often.. you cannot do a >= .... So don't panic too much...
Just a little Performance Reminder from your Performance Conscious Fellow ARSlist'er....









-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thackeray, Gareth
Sent: Thursday, March 18, 2004 12:09 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?

Gareth

-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.

I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...

-----Original Message-----
From: Action Request System discussion list(ARSList) [ mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?

Is there a BUG in Remedy at play here?

Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...

( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )




This posting was submitted via the Web interface


This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
This posting was submitted via the Web interface

This posting was submitted via the Web interface

**
Sir,
That is correct..
I guess I am being a little General in my statements.. and not being Clear..

Just read the Doc's that the vendor provides in the arena of Performance .. and you will get it..


-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thomas Bean
Sent: Thursday, March 18, 2004 2:06 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE



**
Pat,
LIKE statements should still use indexes as long as you don't use wildcards at the beginning of the comparison string. For example (assume 'mycolumn' is indexed):

* USES INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'
* DOES NOT USE INDEX:
SELECT * FROM mytable WHERE mycolumn LIKE '%some text%'

--Thomas

----- Original Message -----
From: Zandi Patrick S TSgt AFRL/IFOSS
Newsgroups: cogniza.remedy.arsystem.general
To: ARSLIST@ARSLIST.ORG
Sent: Thursday, March 18, 2004 12:21 PM
Subject: Re: EGG on my FACE

**
ALL --- I have been to 2 (yes this is true) Remedy Performance Classes.. and this is a Fundamental Teaching.

This is also and has also be taught at all 5 years of (yes 5) Remedy User Group Conferences in both Tutorials, and Sessions.

This is also True when looking at some books out there... I have..
Oracle Performance books, ARS Guides on Query's, I guess I will stop there for now.

"=" equals Index if Available.
Like equals Table Scan forever.
> equals Table scan of all rows

Test it yourself.. ensure the table is not in memory first, now with some DB's you can do that.. but a large server will not.. let say 30 million rows...
you can cache some but not all...
Take the 30K row and do a query on a field let say a Char field, and put the query in as a >
Do the same thing with a >=
I have tested and tested this.. with Oracle and mssql and it is consistent...
30 seconds for first one
1 second for the second.
NOTE:: If there is an index for that field. If no index -- it just doesn't matter.. it is still a Table Scan...
OR a LIKE statement will Kill you too...

http://www.atg.com/repositories/ContentCatalogRepositoryen/manuals/ATG6.2.0/installdas/installdas2207.html

http://www.teradataforum.com/teradata/20020610191632.htm

http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf/@GenericBookTextView/2137;pt=2087

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des051.htm

Those Above Might help you out a little...

First Ensure your Queries are properly formatted,
Then if you still have issues, look to make the table into RAM for Performance enhancements.

Hope that helps some...

Don't get me wrong.. Sometime.. Not often.. you cannot do a >= .... So don't panic too much...
Just a little Performance Reminder from your Performance Conscious Fellow ARSlist'er....









-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thackeray, Gareth
Sent: Thursday, March 18, 2004 12:09 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?

Gareth

-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: 18 March 2004 16:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**

P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
If this were an Real Emergency your server would not be running, but Begging for relief.

I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...

-----Original Message-----
From: Action Request System discussion list(ARSList) [ mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Thursday, March 18, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Thanks Patrick...
Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?

Is there a BUG in Remedy at play here?

Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...

( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )




This posting was submitted via the Web interface


This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
This posting was submitted via the Web interface

This posting was submitted via the Web interface

This posting was submitted via the Web interface

This posting was submitted via the Web interface

** ?
Rick,
In MOST cases, LIKE comparisons will not cause a sequential table scan unless leading wildcards are used. As Jarl pointed out, this can depend upon the optimizer/query analyzer. Here is an example of the Informix 'SET EXPLAIN' output for a LIKE comparison query using a trailing wildcard in the search string (c536870915 is an indexed varchar column):


QUERY:
------
select count(*) from t58
where c536870915 LIKE '46%'

Estimated Cost: 64
Estimated # of Rows Returned: 1

1) root.t58: INDEX PATH

(1) Index Keys: c536870915 (Serial, fragments: ALL)
Lower Index Filter: root.t58.c536870915 LIKE '46%'


I have not encountered any cases where such a query would not use an index, but I suppose there could always be an exception.

--Thomas

----- Original Message -----
From: "Rick Cook" < rcook@DENALIAI.COM>
Newsgroups: cogniza.remedy.arsystem.general
To: < ARSLIST@ARSLIST.ORG>
Sent: Thursday, March 18, 2004 2:32 PM
Subject: Re: EGG on my FACE


> Jarl,
>
> How could a search where the ONLY criteria was a LIKE substring search NOT do a table scan?
>
> Rick
>
> -----Original Message-----
> From: Action Request System discussion list(ARSList) on behalf of Jarl Groneng
> Sent: Thu 3/18/2004 11:46 AM
> To: ARSLIST@ARSLIST.ORG
> Cc:
> Subject: Re: EGG on my FACE [bcc][faked-from]
>
>
>
> This is not always true, it all depend on the optimizer and the query alanyzer, and how they decide to do the query:
>
> USES INDEX:
> SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'
>
> Regards,
> Jarl
>
>
> -----Original Message-----
> From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG]On Behalf Of Thomas Bean
> Sent: 18. mars 2004 20:06
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: EGG on my FACE
>
>
> **
> Pat,
> LIKE statements should still use indexes as long as you don't use wildcards at the beginning of the comparison string. For example (assume 'mycolumn' is indexed):
> USES INDEX:
> SELECT * FROM mytable WHERE mycolumn LIKE 'some text%'
> DOES NOT USE INDEX:
> SELECT * FROM mytable WHERE mycolumn LIKE '%some text%'
> --Thomas
> ----- Original Message -----
> From: Zandi Patrick S TSgt AFRL/IFOSS
> Newsgroups: cogniza.remedy.arsystem.general
> To: ARSLIST@ARSLIST.ORG
> Sent: Thursday, March 18, 2004 12:21 PM
> Subject: Re: EGG on my FACE
>
>
> **
> ALL --- I have been to 2 (yes this is true) Remedy Performance Classes.. and this is a Fundamental Teaching.
>
> This is also and has also be taught at all 5 years of (yes 5) Remedy User Group Conferences in both Tutorials, and Sessions.
>
> This is also True when looking at some books out there... I have..
> Oracle Performance books, ARS Guides on Query's, I guess I will stop there for now.
>
> "=" equals Index if Available.
> Like equals Table Scan forever.
> > equals Table scan of all rows
>
> Test it yourself.. ensure the table is not in memory first, now with some DB's you can do that.. but a large server will not.. let say 30 million rows...
> you can cache some but not all...
> Take the 30K row and do a query on a field let say a Char field, and put the query in as a >
> Do the same thing with a >=
> I have tested and tested this.. with Oracle and mssql and it is consistent...
> 30 seconds for first one
> 1 second for the second.
> NOTE:: If there is an index for that field. If no index -- it just doesn't matter.. it is still a Table Scan...
> OR a LIKE statement will Kill you too...
>
> http://www.atg.com/repositories/ContentCatalogRepositoryen/manuals/ATG6.2.0/installdas/installdas2207.html
>
> http://www.teradataforum.com/teradata/20020610191632.htm
>
> http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf/@GenericBookTextView/2137;pt=2087
>
> http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des051.htm
>
> Those Above Might help you out a little...
>
> First Ensure your Queries are properly formatted,
> Then if you still have issues, look to make the table into RAM for Performance enhancements.
>
> Hope that helps some...
>
> Don't get me wrong.. Sometime.. Not often.. you cannot do a >= .... So don't panic too much...
> Just a little Performance Reminder from your Performance Conscious Fellow ARSlist'er....
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thackeray, Gareth
> Sent: Thursday, March 18, 2004 12:09 PM
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: EGG on my FACE
>
>
> **
> That seems crazy! Surely a decent DBMS should not have a hard and fast rule like that. In most cases <= and < would return almost the same dataset. Which DBMS are you talking about in particular? Or does it affect all of them?
>
> Gareth
> -----Original Message-----
> From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
> Sent: 18 March 2004 16:40
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: EGG on my FACE
>
>
> **
> P.S. This was just presented by the "Emergency Bad Qual Broadcast System" as a helpful Hint !
> If this were an Real Emergency your server would not be running, but Begging for relief.
> I only Addressed "BAD QUAL" -- nothing else.. It was Screaming at me... over the problem or situation in general...
> -----Original Message-----
> From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
> Sent: Thursday, March 18, 2004 11:13 AM
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: EGG on my FACE
>
>
> Thanks Patrick...
> Interesting fact about the index.. but shouldn't that mearly impact the performance... not the result set?
> Is there a BUG in Remedy at play here?
> Now.. if I use your suggestion... I should probably use a representation for Yesterday. $DATE$ - 86400...
> ( 'Due Date' <= $DATE$ - 86400) AND ( 'Status' <= "Accepted") AND ( 'Escalation Email' = $NULL$ )
>
>
> This posting was submitted via the Web interface
>
> This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
> This posting was submitted via the Web interface
> This posting was submitted via the Web interface
> This posting was submitted via the Web interface
>
>
>
>
>
>
>
>
This posting was submitted via the Web interface

Hi Patrick,

Thanks for all your information. There's a few bits that don't seem to ring
too true, though.

>ALL --- I have been to 2 (yes this is true) Remedy Performance Classes..
and this is a Fundamental >Teaching.

>"=" equals Index if Available.
>Like equals Table Scan forever.
> equals Table scan of all rows

My experience is that there are very few exception-free rules in db
performance. It very much depends on your data and the type of queries you
run.

>Test it yourself.. ensure the table is not in memory first, now with some
DB's you can do that.. >> >but a large server will not.. let say 30 million
rows...

Well unfortunately I don't have a table with 30 million rows, but
conceptually I think it should be the same for a table with 60000 rows, so I
tried this.

> you can cache some but not all...
> Take the 30K row and do a query on a field let say a Char field, and
put the query in as a >
> Do the same thing with a >=
>I have tested and tested this.. with Oracle and mssql and it is
consistent...
> 30 seconds for first one
> 1 second for the second.

My understanding is that it is not the length of time that is interesting,
it is the number of logical reads, and the mechanism used to retreive the
data. This is because you cannot know which result sets / base data /
whatever will be in memory when you run the query.

Since in your example, assuming there is a fairly even distribution across
the rows (and not some spike at the very value on which you are searching)
the results would be the same. I think the 2nd query would be quicker
because the results were already in memory, rather than because indexes were
/ were not used. What if you try the ">" query again immediately
afterwards?

On my experiment with 6000 rows on SQL Server 2000, I found that the same
execution plan was used for both queries.

>http://www.atg.com/repositories/ContentCatalogRepositoryen/manuals/ATG6.2.
0/installdas/installdas207.html

>http://www.teradataforum.com/teradata/20020610191632.htm

>http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf/@GenericB
ookTextView/2137;pt=2087

>http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des051.htm

>Those Above Might help you out a little...

Well I had a look, and none of them seemed to mention this issue we're
dealing with here. The first one gave the example of

SELECT *
FROM product
WHERE sku > 'abc'

being a bad query as it was not very selective. It is my understanding that
it is the selectivity of a query that is the main issue in determining
whether indices are used or not. In this example, replacing the > with a >=
would in most cases do nothing to make the query more selective.

I'd be interested to here if the Remedy performance classes actually gave
any justification why > is bad.

Best regards,

Gareth

This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.

ALL,

Thanks for ALL the responses..

My concern was not performance.. However, I will use the <= (or >=) in the
future..

As to my concern.. Isn't there a Keyword which Remedy provides which maps
effectively to DATE fields?

For such a robust product.. you would think this problem could be avoided...

for DATETIME field use.. $DATE$
for DATE field use.. something else..

By the way.. I will also mention the second part of my problem (and Why i
still have egg on my face)..

my escalatioin does 2 things...
first fires of the notifcation (too many unfortunately)...
second pushs a value to a field (which is a flag indicating the
escalation fired for the record in question)..

Well.. the second action.. the Set Fields push is not working... so I end up
sending the same emails.. over and over..

Any thoughts on why the second step in my escalation is NOT firing...

as background.. I will mention in fact there are two fields which are
supposed to be set...

Escalated - which is a data type Selection field... the Value gets set to
"Yes"
Escalation Email - which is a character field (label on this hidden field
on the form is (Escalation Email)

Any thoughts on when the set fields is NOT working?

Phil

I would recommend.. IMHO
Setting your Escalation to fire with the Qual's exactly like they
are now.. With 1 addition.
Add in the exact "Entry ID" of the One (1) you want to specifically
test this on...
Then turn on Logging.. And let her rip -- Giving Tech's a warning if
needed.. Otherwise use one for your email address..

Then watch what happens.. Use the api and sql with the same log name this
way it will be in order..
As soon as it fires, turn the log off, then Rip into the log and if you do
not get it then.. Pass the info out here
Well look her over.. And I know someone will find it..


-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Mougis, Phil
Sent: Friday, March 19, 2004 8:49 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


ALL,

Thanks for ALL the responses..

My concern was not performance.. However, I will use the <= (or >=) in the
future..

As to my concern.. Isn't there a Keyword which Remedy provides which maps
effectively to DATE fields?

For such a robust product.. you would think this problem could be avoided...

for DATETIME field use.. $DATE$
for DATE field use.. something else..

By the way.. I will also mention the second part of my problem (and Why i
still have egg on my face)..

my escalatioin does 2 things...
first fires of the notifcation (too many unfortunately)...
second pushs a value to a field (which is a flag indicating the
escalation fired for the record in question)..

Well.. the second action.. the Set Fields push is not working... so I end up
sending the same emails.. over and over..

Any thoughts on why the second step in my escalation is NOT firing...

as background.. I will mention in fact there are two fields which are
supposed to be set...

Escalated - which is a data type Selection field... the Value gets set to
"Yes" Escalation Email - which is a character field (label on this hidden
field on the form is (Escalation Email)

Any thoughts on when the set fields is NOT working?

Phil



UNSUBSCRIBE or access ARSList Archives at http://www.ARSLIST.org (Support:
mailto:support@arslist.org ) ARSList is hosted by QMX SUPPORT SERVICES at
www.QMXS.com

**

I am not a manual, nor do I write them for a living.. Not my job. Nor could it be, as I Stink at writing..


The Link http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf/@GenericBookTextView/2137;pt=2087
Still a valid sybase link.. If you , after reading this page went to the next logical conclusion.. The little index button..

and then looked at of Sybase examples in the maunal.. It explains this for sybase.. Right ?
I mean this is the manual and it is still correct right ?


The link http://www.teradataforum.com/teradata/20020610191632.htm .. That just turned out to be the wrong link..
I thought I had the correct on .. But I did not .. I am sorry..


The link http://www.atg.com/repositories/ContentCatalogRepositoryen/manuals/ATG6.2.0/installdas/installdas2207.html
-----
The following query is fine:

SELECT *
FROM product
WHERE sku = 'a12345'
That query will not cause performance problems because the WHERE clause refers to a very specific condition on a column with an index.

Here is an example of a query that is likely to cause problems:

SELECT *
FROM product
WHERE description LIKE '%shoes%'
This query causes a table scan, since the indexes can't help the database to optimize the query. Queries like this on a large table will result in an unacceptable performance drag and therefore should not be allowed in a production system.

Here are some more queries that are likely to cause performance problems. The following query is inadvisable because, although it refers to the indexed sku column, it is not very selective and could return millions of rows:

SELECT *
FROM product
WHERE sku > 'abc'
The following query is bad because, although it is relatively selective, it will cause a table scan (at least, on most DBMSs). A LIKE query with a leading wildcard typically cannot be optimized:

SELECT *
FROM product
WHERE name LIKE '%stereo'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
What is wrong with the above ????? Looks True to me.. And helpful...


The Link http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des051.htm

Did you click on the composite index or read
Queries using LIKE comparisons can benefit from an index if the pattern starts with a specific character string, for example 'abc%', but not if the pattern starts with a wildcard search, for example '%xyz'.

Still looks True to me for MSSQL .. And Helpful..
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
The issue here is did they validate my argument.. And I would have to say.. You are correct.. I did not specifically show the argument of = or not by links clear..

My fault...
I appreciate the corrections.. Thanks.


-----Original Message-----
From: Action Request System discussion list(ARSList) [ mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thackeray, Gareth
Sent: Friday, March 19, 2004 4:56 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


Hi Patrick,

Thanks for all your information. There's a few bits that don't seem to ring too true, though.

>ALL --- I have been to 2 (yes this is true) Remedy Performance
>Classes..
and this is a Fundamental >Teaching.

>"=" equals Index if Available.
>Like equals Table Scan forever.
> equals Table scan of all rows

My experience is that there are very few exception-free rules in db performance. It very much depends on your data and the type of queries you run.

>Test it yourself.. ensure the table is not in memory first, now with
>some
DB's you can do that.. >> >but a large server will not.. let say 30 million rows...

Well unfortunately I don't have a table with 30 million rows, but conceptually I think it should be the same for a table with 60000 rows, so I tried this.

> you can cache some but not all...
> Take the 30K row and do a query on a field let say a Char field,
> and
put the query in as a >
> Do the same thing with a >=
>I have tested and tested this.. with Oracle and mssql and it is
consistent...
> 30 seconds for first one
> 1 second for the second.

My understanding is that it is not the length of time that is interesting, it is the number of logical reads, and the mechanism used to retreive the data. This is because you cannot know which result sets / base data / whatever will be in memory when you run the query.

Since in your example, assuming there is a fairly even distribution across the rows (and not some spike at the very value on which you are searching) the results would be the same. I think the 2nd query would be quicker because the results were already in memory, rather than because indexes were / were not used. What if you try the ">" query again immediately afterwards?

On my experiment with 6000 rows on SQL Server 2000, I found that the same execution plan was used for both queries.

> http://www.atg.com/repositories/ContentCatalogRepositoryen/manuals/ATG
>6.2.
0/installdas/installdas207.html

> http://www.teradataforum.com/teradata/20020610191632.htm

> http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf/@Generi
>cB
ookTextView/2137;pt=2087

> http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8des051.htm

>Those Above Might help you out a little...

Well I had a look, and none of them seemed to mention this issue we're dealing with here. The first one gave the example of

SELECT *
FROM product
WHERE sku > 'abc'

being a bad query as it was not very selective. It is my understanding that it is the selectivity of a query that is the main issue in determining whether indices are used or not. In this example, replacing the > with a >= would in most cases do nothing to make the query more selective.

I'd be interested to here if the Remedy performance classes actually gave any justification why > is bad.

Best regards,

Gareth

This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.




This posting was submitted via the Web interface

**
Phil,
Unfortunately, there is no other keyword that will correctly return the current date value for Date fields. In most cases, $DATE$ does work with Date fields, for example:

* As a set fields value for a Date field
* In a WUT search in the Advanced query bar, compared to a Date field

However, it doesn't seem to work in workflow qualifications such as Run If statements, table field qualifications, etc. when compared to a Date field. According to my ARS documentation, the $DATE$ keyword is supposed to function for both Date and Date/Time fields. I have a ticket in open bug status with Remedy support for this issue (bug ID is 180639). I'm guessing this wasn't resolved in 6.0, but I'm not certain.
You should be able to implement one of the work-arounds in my original posting on this thread to resolve this problem.

Concerning the issue with the Set Fields action, what value are you setting in the 'Escalation Email' field? Is this a permanent field or display-only? I would suggest turning on escalation and SQL logging on your server to try to determine what value, if any, is being set. These logs can grow very quickly, so I would suggest performing this on a non-production server if available.

Regards,

Thomas

----- Original Message -----
From: "Mougis, Phil" < phil.mougis@ACS-INC.COM>
Newsgroups: cogniza.remedy.arsystem.general
To: < ARSLIST@ARSLIST.ORG>
Sent: Friday, March 19, 2004 7:48 AM
Subject: Re: EGG on my FACE


> ALL,
>
> Thanks for ALL the responses..
>
> My concern was not performance.. However, I will use the <= (or >=) in the
> future..
>
> As to my concern.. Isn't there a Keyword which Remedy provides which maps
> effectively to DATE fields?
>
> For such a robust product.. you would think this problem could be avoided...
>
> for DATETIME field use.. $DATE$
> for DATE field use.. something else..
>
> By the way.. I will also mention the second part of my problem (and Why i
> still have egg on my face)..
>
> my escalatioin does 2 things...
> first fires of the notifcation (too many unfortunately)...
> second pushs a value to a field (which is a flag indicating the
> escalation fired for the record in question)..
>
> Well.. the second action.. the Set Fields push is not working... so I end up
> sending the same emails.. over and over..
>
> Any thoughts on why the second step in my escalation is NOT firing...
>
> as background.. I will mention in fact there are two fields which are
> supposed to be set...
>
> Escalated - which is a data type Selection field... the Value gets set to
> "Yes"
> Escalation Email - which is a character field (label on this hidden field
> on the form is (Escalation Email)
>
> Any thoughts on when the set fields is NOT working?
>
> Phil
>
>
>
This posting was submitted via the Web interface

**

I looked at the escalation log...

the second line does have a Filter operation in escalation failed, errno=30042 but the interesting thing is the next line relates to something totally unrelated to the problem... the problem being the set fields function for testting the field Escalation Email (536870975) = Past Due 3 is not successfully occurring...



TECHNICAL Request REQ000000001068 is past due.

Filter operation in escalation failed, errno=30042

Only Submitter, CRM or Member of Target Group can update a Request

REQ000000001069

0: Set Fields

Escalation Email (536870975) = Past Due 3

Escalated (536870916) = 1

1: Notify

Priority: 0 Mechanism: Email To: pmougis

TECHNICAL Request REQ000000001069 is past due.

TECHNICAL Request REQ000000001069 is past due.

Filter operation in escalation failed, errno=30042

Only Submitter, CRM or Member of Target Group can update a Request

REQ000000001210

0: Set Fields

Escalation Email (536870975) = Past Due 3

Escalated (536870916) = 1

1: Notify

Priority: 0 Mechanism: Email To: pmougis

TECHNICAL Request REQ000000001210 is past due.

-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG]On Behalf Of Thomas Bean
Sent: Friday, March 19, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
Phil,
Unfortunately, there is no other keyword that will correctly return the current date value for Date fields. In most cases, $DATE$ does work with Date fields, for example:

* As a set fields value for a Date field
* In a WUT search in the Advanced query bar, compared to a Date field

However, it doesn't seem to work in workflow qualifications such as Run If statements, table field qualifications, etc. when compared to a Date field. According to my ARS documentation, the $DATE$ keyword is supposed to function for both Date and Date/Time fields. I have a ticket in open bug status with Remedy support for this issue (bug ID is 180639). I'm guessing this wasn't resolved in 6.0, but I'm not certain.
You should be able to implement one of the work-arounds in my original posting on this thread to resolve this problem.

Concerning the issue with the Set Fields action, what value are you setting in the 'Escalation Email' field? Is this a permanent field or display-only? I would suggest turning on escalation and SQL logging on your server to try to determine what value, if any, is being set. These logs can grow very quickly, so I would suggest performing this on a non-production server if available.

Regards,

Thomas

----- Original Message -----
From: "Mougis, Phil" < phil.mougis@ACS-INC.COM>
Newsgroups: cogniza.remedy.arsystem.general
To: < ARSLIST@ARSLIST.ORG>
Sent: Friday, March 19, 2004 7:48 AM
Subject: Re: EGG on my FACE


> ALL,
>
> Thanks for ALL the responses..
>
> My concern was not performance.. However, I will use the <= (or >=) in the
> future..
>
> As to my concern.. Isn't there a Keyword which Remedy provides which maps
> effectively to DATE fields?
>
> For such a robust product.. you would think this problem could be avoided...
>
> for DATETIME field use.. $DATE$
> for DATE field use.. something else..
>
> By the way.. I will also mention the second part of my problem (and Why i
> still have egg on my face)..
>
> my escalatioin does 2 things...
> first fires of the notifcation (too many unfortunately)...
> second pushs a value to a field (which is a flag indicating the
> escalation fired for the record in question)..
>
> Well.. the second action.. the Set Fields push is not working... so I end up
> sending the same emails.. over and over..
>
> Any thoughts on why the second step in my escalation is NOT firing...
>
> as background.. I will mention in fact there are two fields which are
> supposed to be set...
>
> Escalated - which is a data type Selection field... the Value gets set to
> "Yes"
> Escalation Email - which is a character field (label on this hidden field
> on the form is (Escalation Email)
>
> Any thoughts on when the set fields is NOT working?
>
> Phil
>
>
> This posting was submitted via the Web interface

This posting was submitted via the Web interface

**
Phil,
Where is this error message coming from?

Only Submitter, CRM or Member of Target Group can update a Request

Is this the line that you said was unrelated?
When you said "testting the field Escalation Email (536870975) = Past Due 3 is not successfully occurring", are you referring the the 'Run If' for the escalation testing whether the field is null? This probably wouldn't show up in the escalation log, you would need to add SQL logging to see the query that is being issued to generate the list of entries that trigger the notification.

--Thomas

----- Original Message -----
From: Mougis, Phil
Newsgroups: cogniza.remedy.arsystem.general
To: ARSLIST@ARSLIST.ORG
Sent: Friday, March 19, 2004 2:45 PM
Subject: Re: EGG on my FACE

**

I looked at the escalation log...

the second line does have a Filter operation in escalation failed, errno=30042 but the interesting thing is the next line relates to something totally unrelated to the problem... the problem being the set fields function for testting the field Escalation Email (536870975) = Past Due 3 is not successfully occurring...



TECHNICAL Request REQ000000001068 is past due.

Filter operation in escalation failed, errno=30042

Only Submitter, CRM or Member of Target Group can update a Request

REQ000000001069

0: Set Fields

Escalation Email (536870975) = Past Due 3

Escalated (536870916) = 1

1: Notify

Priority: 0 Mechanism: Email To: pmougis

TECHNICAL Request REQ000000001069 is past due.

TECHNICAL Request REQ000000001069 is past due.

Filter operation in escalation failed, errno=30042

Only Submitter, CRM or Member of Target Group can update a Request

REQ000000001210

0: Set Fields

Escalation Email (536870975) = Past Due 3

Escalated (536870916) = 1

1: Notify

Priority: 0 Mechanism: Email To: pmougis

TECHNICAL Request REQ000000001210 is past due.

-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG]On Behalf Of Thomas Bean
Sent: Friday, March 19, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
Phil,
Unfortunately, there is no other keyword that will correctly return the current date value for Date fields. In most cases, $DATE$ does work with Date fields, for example:

* As a set fields value for a Date field
* In a WUT search in the Advanced query bar, compared to a Date field

However, it doesn't seem to work in workflow qualifications such as Run If statements, table field qualifications, etc. when compared to a Date field. According to my ARS documentation, the $DATE$ keyword is supposed to function for both Date and Date/Time fields. I have a ticket in open bug status with Remedy support for this issue (bug ID is 180639). I'm guessing this wasn't resolved in 6.0, but I'm not certain.
You should be able to implement one of the work-arounds in my original posting on this thread to resolve this problem.

Concerning the issue with the Set Fields action, what value are you setting in the 'Escalation Email' field? Is this a permanent field or display-only? I would suggest turning on escalation and SQL logging on your server to try to determine what value, if any, is being set. These logs can grow very quickly, so I would suggest performing this on a non-production server if available.

Regards,

Thomas

----- Original Message -----
From: "Mougis, Phil" < phil.mougis@ACS-INC.COM>
Newsgroups: cogniza.remedy.arsystem.general
To: < ARSLIST@ARSLIST.ORG>
Sent: Friday, March 19, 2004 7:48 AM
Subject: Re: EGG on my FACE


> ALL,
>
> Thanks for ALL the responses..
>
> My concern was not performance.. However, I will use the <= (or >=) in the
> future..
>
> As to my concern.. Isn't there a Keyword which Remedy provides which maps
> effectively to DATE fields?
>
> For such a robust product.. you would think this problem could be avoided...
>
> for DATETIME field use.. $DATE$
> for DATE field use.. something else..
>
> By the way.. I will also mention the second part of my problem (and Why i
> still have egg on my face)..
>
> my escalatioin does 2 things...
> first fires of the notifcation (too many unfortunately)...
> second pushs a value to a field (which is a flag indicating the
> escalation fired for the record in question)..
>
> Well.. the second action.. the Set Fields push is not working... so I end up
> sending the same emails.. over and over..
>
> Any thoughts on why the second step in my escalation is NOT firing...
>
> as background.. I will mention in fact there are two fields which are
> supposed to be set...
>
> Escalated - which is a data type Selection field... the Value gets set to
> "Yes"
> Escalation Email - which is a character field (label on this hidden field
> on the form is (Escalation Email)
>
> Any thoughts on when the set fields is NOT working?
>
> Phil
>
>
> This posting was submitted via the Web interface

This posting was submitted via the Web interface

This posting was submitted via the Web interface

**
correct me if I am wrong here Thomas
Filter operation in escalation failed, errno=30042
this is a filter with number 30042 that is blocking the escalation.. am I wrong here ?

-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thomas Bean
Sent: Friday, March 19, 2004 7:45 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
Phil,
Where is this error message coming from?

Only Submitter, CRM or Member of Target Group can update a Request

Is this the line that you said was unrelated?
When you said "testting the field Escalation Email (536870975) = Past Due 3 is not successfully occurring", are you referring the the 'Run If' for the escalation testing whether the field is null? This probably wouldn't show up in the escalation log, you would need to add SQL logging to see the query that is being issued to generate the list of entries that trigger the notification.

--Thomas

----- Original Message -----
From: Mougis, Phil
Newsgroups: cogniza.remedy.arsystem.general
To: ARSLIST@ARSLIST.ORG
Sent: Friday, March 19, 2004 2:45 PM
Subject: Re: EGG on my FACE

**

I looked at the escalation log...

the second line does have a Filter operation in escalation failed, errno=30042 but the interesting thing is the next line relates to something totally unrelated to the problem... the problem being the set fields function for testting the field Escalation Email (536870975) = Past Due 3 is not successfully occurring...



TECHNICAL Request REQ000000001068 is past due.

Filter operation in escalation failed, errno=30042

Only Submitter, CRM or Member of Target Group can update a Request

REQ000000001069

0: Set Fields

Escalation Email (536870975) = Past Due 3

Escalated (536870916) = 1

1: Notify

Priority: 0 Mechanism: Email To: pmougis

TECHNICAL Request REQ000000001069 is past due.

TECHNICAL Request REQ000000001069 is past due.

Filter operation in escalation failed, errno=30042

Only Submitter, CRM or Member of Target Group can update a Request

REQ000000001210

0: Set Fields

Escalation Email (536870975) = Past Due 3

Escalated (536870916) = 1

1: Notify

Priority: 0 Mechanism: Email To: pmougis

TECHNICAL Request REQ000000001210 is past due.

-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG]On Behalf Of Thomas Bean
Sent: Friday, March 19, 2004 11:13 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE


**
Phil,
Unfortunately, there is no other keyword that will correctly return the current date value for Date fields. In most cases, $DATE$ does work with Date fields, for example:

* As a set fields value for a Date field
* In a WUT search in the Advanced query bar, compared to a Date field

However, it doesn't seem to work in workflow qualifications such as Run If statements, table field qualifications, etc. when compared to a Date field. According to my ARS documentation, the $DATE$ keyword is supposed to function for both Date and Date/Time fields. I have a ticket in open bug status with Remedy support for this issue (bug ID is 180639). I'm guessing this wasn't resolved in 6.0, but I'm not certain.
You should be able to implement one of the work-arounds in my original posting on this thread to resolve this problem.

Concerning the issue with the Set Fields action, what value are you setting in the 'Escalation Email' field? Is this a permanent field or display-only? I would suggest turning on escalation and SQL logging on your server to try to determine what value, if any, is being set. These logs can grow very quickly, so I would suggest performing this on a non-production server if available.

Regards,

Thomas

----- Original Message -----
From: "Mougis, Phil" < phil.mougis@ACS-INC.COM>
Newsgroups: cogniza.remedy.arsystem.general
To: < ARSLIST@ARSLIST.ORG>
Sent: Friday, March 19, 2004 7:48 AM
Subject: Re: EGG on my FACE


> ALL,
>
> Thanks for ALL the responses..
>
> My concern was not performance.. However, I will use the <= (or >=) in the
> future..
>
> As to my concern.. Isn't there a Keyword which Remedy provides which maps
> effectively to DATE fields?
>
> For such a robust product.. you would think this problem could be avoided...
>
> for DATETIME field use.. $DATE$
> for DATE field use.. something else..
>
> By the way.. I will also mention the second part of my problem (and Why i
> still have egg on my face)..
>
> my escalatioin does 2 things...
> first fires of the notifcation (too many unfortunately)...
> second pushs a value to a field (which is a flag indicating the
> escalation fired for the record in question)..
>
> Well.. the second action.. the Set Fields push is not working... so I end up
> sending the same emails.. over and over..
>
> Any thoughts on why the second step in my escalation is NOT firing...
>
> as background.. I will mention in fact there are two fields which are
> supposed to be set...
>
> Escalated - which is a data type Selection field... the Value gets set to
> "Yes"
> Escalation Email - which is a character field (label on this hidden field
> on the form is (Escalation Email)
>
> Any thoughts on when the set fields is NOT working?
>
> Phil
>
>
> This posting was submitted via the Web interface

This posting was submitted via the Web interface

This posting was submitted via the Web interface

This posting was submitted via the Web interface

**
**

Patrick: That’s not the filter number, but rather the error number assigned (manually) to the Message action in the filter that is blocking the escalation. If you have access to SQL tools, search the table “filtermessage” for msgNum = 30042. (Note that msgNum is an integer field, not character.)



Mike Luttmann

Senior Database Engineer

Michael.Luttmann@peterson.af.mil





-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: Friday, March 19, 2004 5:57 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE



correct me if I am wrong here Thomas

Filter operation in escalation failed, errno=30042

this is a filter with number 30042 that is blocking the escalation.. am I wrong here ?

-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thomas Bean
Sent: Friday, March 19, 2004 7:45 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE

**

Phil,

Where is this error message coming from?



Only Submitter, CRM or Member of Target Group can update a Request



Is this the line that you said was unrelated?

When you said "testting the field Escalation Email (536870975) = Past Due 3 is not successfully occurring", are you referring the the 'Run If' for the escalation testing whether the field is null? This probably wouldn't show up in the escalation log, you would need to add SQL logging to see the query that is being issued to generate the list of entries that trigger the notification.



--Thomas



This posting was submitted via the Web This posting was submitted via the Web interface interface

**
Agreed.. Funny, I never have been great at the Proper terminology at times..
But yeah ... "What you said"

-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Luttmann Michael W Contr 21 SCS/SCBBN
Sent: Monday, March 22, 2004 9:28 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE



** **

Patrick: That's not the filter number, but rather the error number assigned (manually) to the Message action in the filter that is blocking the escalation. If you have access to SQL tools, search the table "filtermessage" for msgNum = 30042. (Note that msgNum is an integer field, not character.)



Mike Luttmann

Senior Database Engineer

Michael.Luttmann@peterson.af.mil





-----Original Message-----
From: Zandi Patrick S TSgt AFRL/IFOSS [mailto:Patrick.Zandi@RL.AF.MIL]
Sent: Friday, March 19, 2004 5:57 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE



correct me if I am wrong here Thomas

Filter operation in escalation failed, errno=30042

this is a filter with number 30042 that is blocking the escalation.. am I wrong here ?

-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Thomas Bean
Sent: Friday, March 19, 2004 7:45 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: EGG on my FACE

**

Phil,

Where is this error message coming from?



Only Submitter, CRM or Member of Target Group can update a Request



Is this the line that you said was unrelated?

When you said "testting the field Escalation Email (536870975) = Past Due 3 is not successfully occurring", are you referring the the 'Run If' for the escalation testing whether the field is null? This probably wouldn't show up in the escalation log, you would need to add SQL logging to see the query that is being issued to generate the list of entries that trigger the notification.



--Thomas



This posting was submitted via the Web This posting was submitted via the Web interface interface

This posting was submitted via the Web interface