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