Does anyone out there experience random slow downs in Remedy. Like when in
User and try to fill out a Helpdesk form or loading a form? We been
troubleshooting our system and think its bad SQL statements causing the
problem, forcing the db to blow its cache by doing full table scans. Only
problem is it seems the ARS server is generating this bad SQL code...anyone
else seen or experiencing such issues? We are using ARS 4.03 & Oracle 8.16.

"There are no boundries for those with imagination and the ability to wield
it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

Hi Charles,

Please feel comforted to know that you're not the only one experiencing
bad performance. Remedy HelpDesk out-of-the-box is nice up to say 40k
records on HPD:HelpDesk, 10k on AST:Asset, 10k on CHG:Change, 20k on
CHG:Task. But it is a fact that at some points build-in searches
generates bad performance. For example:
Form Remedy Help Desk - Support: Table Field 'Support Requests' (table
scan on SHR:Consolidated)
Form HPD:HelpDesk: Table field 'tblRequesteAssets' (not-indexed search
on AST:Asset)
If your DB is growing, at some point you must redesign some workflow,
apply new indexes, etc. to keep your HelpDesk App running. I once worked
with an Remedy HD implementation with 8 mil records, which performed
rather nice after a complete tuning.

Met vriendelijke groet,

Patrick Simon, The Service Company
Tel: +31 (0) 6 41 767 743
Mail: psimon@thesco.nl
Web: http://www.thesco.nl


-----Oorspronkelijk bericht-----
Van: Action Request System discussion list(ARSList)
[mailto:ARSLIST@ARSLIST.ORG] Namens Charles Lowe
Verzonden: dinsdag 7 januari 2003 20:33
Aan: ARSLIST@ARSLIST.ORG
Onderwerp: ARS Performance/Responsiveness


Does anyone out there experience random slow downs in Remedy. Like when
in User and try to fill out a Helpdesk form or loading a form? We been
troubleshooting our system and think its bad SQL statements causing the
problem, forcing the db to blow its cache by doing full table scans.
Only problem is it seems the ARS server is generating this bad SQL
code...anyone else seen or experiencing such issues? We are using ARS
4.03 & Oracle 8.16.

"There are no boundries for those with imagination and the ability to
wield it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027



You may control your subscription options, including UNSUBSCRIBE,and
ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support,
please send eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

**

I have experienced the same problem and do not what the causes are.

Sheila Wang
Remedy Administrator

-----Original Message-----
From: Charles Lowe [ mailto:CLowe@RCCL.COM]
Sent: Tuesday, January 07, 2003 12:33 PM
To: ARSLIST@ARSLIST.ORG
Subject: ARS Performance/Responsiveness


Does anyone out there experience random slow downs in Remedy. Like when in
User and try to fill out a Helpdesk form or loading a form? We been
troubleshooting our system and think its bad SQL statements causing the
problem, forcing the db to blow its cache by doing full table scans. Only
problem is it seems the ARS server is generating this bad SQL code...anyone
else seen or experiencing such issues? We are using ARS 4.03 & Oracle 8.16.

"There are no boundries for those with imagination and the ability to wield
it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

To UNSUBSCRIBE, email the message "unsubscribe arslist" to: listserv@arslist.org You may also control your subscription options, including UNSUBSCRIBE, at http://www.ARSLIST.org This posting was submitted via the Web interface

Hello Charles,

If you're using Remedy Help Desk application, these table scans are most
likely generated by badly written macros.
We had this issue with one of the customers. The system became practically
irresponsive after end user clicks 'Details...' button on Remedy Help Desk
Support or Remedy Help Desk Management consoles to bring the ticket selected
from the table field.

One of the macros which forces Oracle to perform table scans is
SHR:OpenForm, it is used by the following active links:
name

-----------------------------------------------
RR:RSD-OpenFormDetails
RR:SHR-OpenFormDetails2
SHRA:AST-ShowRelatedDetails2
SHRC:CHG-ShowRelatedDetails2
SHRH:HPD-ShowRelatedDetails2
RR:MHR-OpenFormDetails
RR:MHR-OpenFormDetails-SLA
SHRAW:AW-ShowRelatedDetails2A4
SHRAS:AS-ShowRelatedDetails2A4
SHRAL:AL-ShowRelatedDetails2A4
SHRAM:AM-ShowRelatedDetails2A4
SHRSW:SW-ShowRelatedDetails2A4
RR:AA-ShowDetailsDownA4
RR:AA-ShowDetailsManagerA4
RR:AA-ShowDetailsOwnerA4
RR:AA-ShowDetailsRepairA4
RR:AA-ShowDetailsUserA4
AST:IN Details-Asset 2 AM4
AST:IN Details-Cmpnt 2 AM4
SHRM:SHR-ShowRelatedDetails2A4

This macro uses value in 'Case ID+' field in HPD:HelpDesk form as a search
criteria, but out-of-the-box QBE setting for this field is set to
"Anywhere", which causes table scans on Oracle and clustered index scans on
MS SQL Server.

Looks like macro was recorded using fields in the form, not Advanced Search
bar. We re-recorded this macro and it solved customer's problem.

Another good candidate for table scans is 'Assignee Group' field, if it
stores multiple groups' IDs (so called row-level access feature).
In SQL log file you'll see like this feature builds SQL statements
containing multiple LIKEs with leading and trailing wildcards linked by ORs
operators.
If you ask MS SQL Server to display the execution plan for such a query -
you'll see clustered index scan. Oracle does full table scan, quite scary
picture.

I've spoken to Remedy Engineering Team about this feature and asked whether
it is valid to have multi-valued columns in the table (it violates the First
Normal Form for database normalisation) - they say this is absolutely
correct....

Keep searching, and you'll find more beasts!

Regards,

Mikhail Gvozdev
Remedy Product Support
Planwell Technology Australia Ltd.
1-5 Railway Street
CHATSWOOD NSW 2067

Phone: +61 2 9412 - 8763
Fax: +61 2 9419 - 7803
mailto:mikhailg@planwell.com.au






-----Original Message-----
From: Charles Lowe [mailto:CLowe@RCCL.COM]
Sent: Wednesday, 8 January 2003 06:33 AM
To: ARSLIST@ARSLIST.ORG
Subject: ARS Performance/Responsiveness


Does anyone out there experience random slow downs in Remedy. Like when in
User and try to fill out a Helpdesk form or loading a form? We been
troubleshooting our system and think its bad SQL statements causing the
problem, forcing the db to blow its cache by doing full table scans. Only
problem is it seems the ARS server is generating this bad SQL code...anyone
else seen or experiencing such issues? We are using ARS 4.03 & Oracle 8.16.

"There are no boundries for those with imagination and the ability to wield
it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027



You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

Thanks Mikhail,

Our user base heavily relies on the Remedy Support console for access to
the various forms. I'll have to look into the macro thing, sounds like a
reasonable culprit.

"There are no boundries for those with imagination and the ability to wield
it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027



Mikhail Gvozdev
L.COM.AU> cc:
Sent by: "Action Subject: Re: ARS Performance/Responsiveness
Request System
discussion
list(ARSList)"
ORG>


01/07/2003 05:11
PM
Please respond to
arslist





Hello Charles,

If you're using Remedy Help Desk application, these table scans are most
likely generated by badly written macros.
We had this issue with one of the customers. The system became practically
irresponsive after end user clicks 'Details...' button on Remedy Help Desk
Support or Remedy Help Desk Management consoles to bring the ticket
selected
from the table field.

One of the macros which forces Oracle to perform table scans is
SHR:OpenForm, it is used by the following active links:
name

-----------------------------------------------
RR:RSD-OpenFormDetails
RR:SHR-OpenFormDetails2
SHRA:AST-ShowRelatedDetails2
SHRC:CHG-ShowRelatedDetails2
SHRH:HPD-ShowRelatedDetails2
RR:MHR-OpenFormDetails
RR:MHR-OpenFormDetails-SLA
SHRAW:AW-ShowRelatedDetails2A4
SHRAS:AS-ShowRelatedDetails2A4
SHRAL:AL-ShowRelatedDetails2A4
SHRAM:AM-ShowRelatedDetails2A4
SHRSW:SW-ShowRelatedDetails2A4
RR:AA-ShowDetailsDownA4
RR:AA-ShowDetailsManagerA4
RR:AA-ShowDetailsOwnerA4
RR:AA-ShowDetailsRepairA4
RR:AA-ShowDetailsUserA4
AST:IN Details-Asset 2 AM4
AST:IN Details-Cmpnt 2 AM4
SHRM:SHR-ShowRelatedDetails2A4

This macro uses value in 'Case ID+' field in HPD:HelpDesk form as a search
criteria, but out-of-the-box QBE setting for this field is set to
"Anywhere", which causes table scans on Oracle and clustered index scans on
MS SQL Server.

Looks like macro was recorded using fields in the form, not Advanced Search
bar. We re-recorded this macro and it solved customer's problem.

Another good candidate for table scans is 'Assignee Group' field, if it
stores multiple groups' IDs (so called row-level access feature).
In SQL log file you'll see like this feature builds SQL statements
containing multiple LIKEs with leading and trailing wildcards linked by ORs
operators.
If you ask MS SQL Server to display the execution plan for such a query -
you'll see clustered index scan. Oracle does full table scan, quite scary
picture.

I've spoken to Remedy Engineering Team about this feature and asked whether
it is valid to have multi-valued columns in the table (it violates the
First
Normal Form for database normalisation) - they say this is absolutely
correct....

Keep searching, and you'll find more beasts!

Regards,

Mikhail Gvozdev
Remedy Product Support
Planwell Technology Australia Ltd.
1-5 Railway Street
CHATSWOOD NSW 2067

Phone: +61 2 9412 - 8763
Fax: +61 2 9419 - 7803
mailto:mikhailg@planwell.com.au






-----Original Message-----
From: Charles Lowe [mailto:CLowe@RCCL.COM]
Sent: Wednesday, 8 January 2003 06:33 AM
To: ARSLIST@ARSLIST.ORG
Subject: ARS Performance/Responsiveness


Does anyone out there experience random slow downs in Remedy. Like when in
User and try to fill out a Helpdesk form or loading a form? We been
troubleshooting our system and think its bad SQL statements causing the
problem, forcing the db to blow its cache by doing full table scans. Only
problem is it seems the ARS server is generating this bad SQL code...anyone
else seen or experiencing such issues? We are using ARS 4.03 & Oracle 8.16.

"There are no boundries for those with imagination and the ability to wield
it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027




You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org



You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

Since you're using the Console, check the number of records in SHR:ConsolidatedList, too. Sometimes old records don't get deleted as they should, and they can really accumulate, making queries longer.

Rick

-----Original Message-----
From: Charles Lowe [mailto:CLowe@RCCL.COM]
Sent: Tuesday, January 07, 2003 14:29 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Thanks Mikhail,

Our user base heavily relies on the Remedy Support console for access to
the various forms. I'll have to look into the macro thing, sounds like a
reasonable culprit.

"There are no boundries for those with imagination and the ability to wield
it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027



Mikhail Gvozdev
L.COM.AU> cc:
Sent by: "Action Subject: Re: ARS Performance/Responsiveness
Request System
discussion
list(ARSList)"
ORG>


01/07/2003 05:11
PM
Please respond to
arslist





Hello Charles,

If you're using Remedy Help Desk application, these table scans are most
likely generated by badly written macros.
We had this issue with one of the customers. The system became practically
irresponsive after end user clicks 'Details...' button on Remedy Help Desk
Support or Remedy Help Desk Management consoles to bring the ticket
selected
from the table field.

One of the macros which forces Oracle to perform table scans is
SHR:OpenForm, it is used by the following active links:
name

-----------------------------------------------
RR:RSD-OpenFormDetails
RR:SHR-OpenFormDetails2
SHRA:AST-ShowRelatedDetails2
SHRC:CHG-ShowRelatedDetails2
SHRH:HPD-ShowRelatedDetails2
RR:MHR-OpenFormDetails
RR:MHR-OpenFormDetails-SLA
SHRAW:AW-ShowRelatedDetails2A4
SHRAS:AS-ShowRelatedDetails2A4
SHRAL:AL-ShowRelatedDetails2A4
SHRAM:AM-ShowRelatedDetails2A4
SHRSW:SW-ShowRelatedDetails2A4
RR:AA-ShowDetailsDownA4
RR:AA-ShowDetailsManagerA4
RR:AA-ShowDetailsOwnerA4
RR:AA-ShowDetailsRepairA4
RR:AA-ShowDetailsUserA4
AST:IN Details-Asset 2 AM4
AST:IN Details-Cmpnt 2 AM4
SHRM:SHR-ShowRelatedDetails2A4

This macro uses value in 'Case ID+' field in HPD:HelpDesk form as a search
criteria, but out-of-the-box QBE setting for this field is set to
"Anywhere", which causes table scans on Oracle and clustered index scans on
MS SQL Server.

Looks like macro was recorded using fields in the form, not Advanced Search
bar. We re-recorded this macro and it solved customer's problem.

Another good candidate for table scans is 'Assignee Group' field, if it
stores multiple groups' IDs (so called row-level access feature).
In SQL log file you'll see like this feature builds SQL statements
containing multiple LIKEs with leading and trailing wildcards linked by ORs
operators.
If you ask MS SQL Server to display the execution plan for such a query -
you'll see clustered index scan. Oracle does full table scan, quite scary
picture.

I've spoken to Remedy Engineering Team about this feature and asked whether
it is valid to have multi-valued columns in the table (it violates the
First
Normal Form for database normalisation) - they say this is absolutely
correct....

Keep searching, and you'll find more beasts!

Regards,

Mikhail Gvozdev
Remedy Product Support
Planwell Technology Australia Ltd.
1-5 Railway Street
CHATSWOOD NSW 2067

Phone: +61 2 9412 - 8763
Fax: +61 2 9419 - 7803
mailto:mikhailg@planwell.com.au






-----Original Message-----
From: Charles Lowe [mailto:CLowe@RCCL.COM]
Sent: Wednesday, 8 January 2003 06:33 AM
To: ARSLIST@ARSLIST.ORG
Subject: ARS Performance/Responsiveness


Does anyone out there experience random slow downs in Remedy. Like when in
User and try to fill out a Helpdesk form or loading a form? We been
troubleshooting our system and think its bad SQL statements causing the
problem, forcing the db to blow its cache by doing full table scans. Only
problem is it seems the ARS server is generating this bad SQL code...anyone
else seen or experiencing such issues? We are using ARS 4.03 & Oracle 8.16.

"There are no boundries for those with imagination and the ability to wield
it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027




You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org



You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

No probs, have a look at modified macro in the attachment.

Regards,

Mikhail



-----Original Message-----
From: Charles Lowe [mailto:CLowe@RCCL.COM]
Sent: Wednesday, 8 January 2003 09:29 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Thanks Mikhail,

Our user base heavily relies on the Remedy Support console for access to
the various forms. I'll have to look into the macro thing, sounds like a
reasonable culprit.

"There are no boundries for those with imagination and the ability to wield
it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027



Mikhail Gvozdev
L.COM.AU> cc:
Sent by: "Action Subject: Re: ARS
Performance/Responsiveness
Request System
discussion
list(ARSList)"
ORG>


01/07/2003 05:11
PM
Please respond to
arslist





Hello Charles,

If you're using Remedy Help Desk application, these table scans are most
likely generated by badly written macros.
We had this issue with one of the customers. The system became practically
irresponsive after end user clicks 'Details...' button on Remedy Help Desk
Support or Remedy Help Desk Management consoles to bring the ticket
selected
from the table field.

One of the macros which forces Oracle to perform table scans is
SHR:OpenForm, it is used by the following active links:
name

-----------------------------------------------
RR:RSD-OpenFormDetails
RR:SHR-OpenFormDetails2
SHRA:AST-ShowRelatedDetails2
SHRC:CHG-ShowRelatedDetails2
SHRH:HPD-ShowRelatedDetails2
RR:MHR-OpenFormDetails
RR:MHR-OpenFormDetails-SLA
SHRAW:AW-ShowRelatedDetails2A4
SHRAS:AS-ShowRelatedDetails2A4
SHRAL:AL-ShowRelatedDetails2A4
SHRAM:AM-ShowRelatedDetails2A4
SHRSW:SW-ShowRelatedDetails2A4
RR:AA-ShowDetailsDownA4
RR:AA-ShowDetailsManagerA4
RR:AA-ShowDetailsOwnerA4
RR:AA-ShowDetailsRepairA4
RR:AA-ShowDetailsUserA4
AST:IN Details-Asset 2 AM4
AST:IN Details-Cmpnt 2 AM4
SHRM:SHR-ShowRelatedDetails2A4

This macro uses value in 'Case ID+' field in HPD:HelpDesk form as a search
criteria, but out-of-the-box QBE setting for this field is set to
"Anywhere", which causes table scans on Oracle and clustered index scans on
MS SQL Server.

Looks like macro was recorded using fields in the form, not Advanced Search
bar. We re-recorded this macro and it solved customer's problem.

Another good candidate for table scans is 'Assignee Group' field, if it
stores multiple groups' IDs (so called row-level access feature).
In SQL log file you'll see like this feature builds SQL statements
containing multiple LIKEs with leading and trailing wildcards linked by ORs
operators.
If you ask MS SQL Server to display the execution plan for such a query -
you'll see clustered index scan. Oracle does full table scan, quite scary
picture.

I've spoken to Remedy Engineering Team about this feature and asked whether
it is valid to have multi-valued columns in the table (it violates the
First
Normal Form for database normalisation) - they say this is absolutely
correct....

Keep searching, and you'll find more beasts!

Regards,

Mikhail Gvozdev
Remedy Product Support
Planwell Technology Australia Ltd.
1-5 Railway Street
CHATSWOOD NSW 2067

Phone: +61 2 9412 - 8763
Fax: +61 2 9419 - 7803
mailto:mikhailg@planwell.com.au






-----Original Message-----
From: Charles Lowe [mailto:CLowe@RCCL.COM]
Sent: Wednesday, 8 January 2003 06:33 AM
To: ARSLIST@ARSLIST.ORG
Subject: ARS Performance/Responsiveness


Does anyone out there experience random slow downs in Remedy. Like when in
User and try to fill out a Helpdesk form or loading a form? We been
troubleshooting our system and think its bad SQL statements causing the
problem, forcing the db to blow its cache by doing full table scans. Only
problem is it seems the ARS server is generating this bad SQL code...anyone
else seen or experiencing such issues? We are using ARS 4.03 & Oracle 8.16.

"There are no boundries for those with imagination and the ability to wield
it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027




You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org




You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org



You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org



You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

Rick's correct! This is an issue I also have picked up with some of my
cutomers using HD4. If memory serves me right, Patch 678 solved this issue,
but you have to apply the pacth just after you have installed HD4.
(DO NOT apply the patch if you have modified HD4!)


Best regards,

Theo J. Fondse
Remedy Developer
Siemens Business Services

Telephone: +27 11 652 7477
Mobile: +27 83 773 3075
e-Mail: theo.fondse@ars.smc.siemens.co.za
Web: http://www.siemens.co.za

===========================================================================


-----Original Message-----
From: Rick Cook [mailto:rcook@DENALIAI.COM]
Sent: 08 January 2003 00:51
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness

Since you're using the Console, check the number of records in
SHR:ConsolidatedList, too. Sometimes old records don't get deleted as they
should, and they can really accumulate, making queries longer.

Rick

-----Original Message-----
From: Charles Lowe [mailto:CLowe@RCCL.COM]
Sent: Tuesday, January 07, 2003 14:29 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Thanks Mikhail,

Our user base heavily relies on the Remedy Support console for access to
the various forms. I'll have to look into the macro thing, sounds like a
reasonable culprit.

"There are no boundries for those with imagination and the ability to wield
it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027



Mikhail Gvozdev
L.COM.AU> cc:
Sent by: "Action Subject: Re: ARS
Performance/Responsiveness
Request System
discussion
list(ARSList)"
ORG>


01/07/2003 05:11
PM
Please respond to
arslist





Hello Charles,

If you're using Remedy Help Desk application, these table scans are most
likely generated by badly written macros.
We had this issue with one of the customers. The system became practically
irresponsive after end user clicks 'Details...' button on Remedy Help Desk
Support or Remedy Help Desk Management consoles to bring the ticket
selected
from the table field.

One of the macros which forces Oracle to perform table scans is
SHR:OpenForm, it is used by the following active links:
name

-----------------------------------------------
RR:RSD-OpenFormDetails
RR:SHR-OpenFormDetails2
SHRA:AST-ShowRelatedDetails2
SHRC:CHG-ShowRelatedDetails2
SHRH:HPD-ShowRelatedDetails2
RR:MHR-OpenFormDetails
RR:MHR-OpenFormDetails-SLA
SHRAW:AW-ShowRelatedDetails2A4
SHRAS:AS-ShowRelatedDetails2A4
SHRAL:AL-ShowRelatedDetails2A4
SHRAM:AM-ShowRelatedDetails2A4
SHRSW:SW-ShowRelatedDetails2A4
RR:AA-ShowDetailsDownA4
RR:AA-ShowDetailsManagerA4
RR:AA-ShowDetailsOwnerA4
RR:AA-ShowDetailsRepairA4
RR:AA-ShowDetailsUserA4
AST:IN Details-Asset 2 AM4
AST:IN Details-Cmpnt 2 AM4
SHRM:SHR-ShowRelatedDetails2A4

This macro uses value in 'Case ID+' field in HPD:HelpDesk form as a search
criteria, but out-of-the-box QBE setting for this field is set to
"Anywhere", which causes table scans on Oracle and clustered index scans on
MS SQL Server.

Looks like macro was recorded using fields in the form, not Advanced Search
bar. We re-recorded this macro and it solved customer's problem.

Another good candidate for table scans is 'Assignee Group' field, if it
stores multiple groups' IDs (so called row-level access feature).
In SQL log file you'll see like this feature builds SQL statements
containing multiple LIKEs with leading and trailing wildcards linked by ORs
operators.
If you ask MS SQL Server to display the execution plan for such a query -
you'll see clustered index scan. Oracle does full table scan, quite scary
picture.

I've spoken to Remedy Engineering Team about this feature and asked whether
it is valid to have multi-valued columns in the table (it violates the
First
Normal Form for database normalisation) - they say this is absolutely
correct....

Keep searching, and you'll find more beasts!

Regards,

Mikhail Gvozdev
Remedy Product Support
Planwell Technology Australia Ltd.
1-5 Railway Street
CHATSWOOD NSW 2067

Phone: +61 2 9412 - 8763
Fax: +61 2 9419 - 7803
mailto:mikhailg@planwell.com.au






-----Original Message-----
From: Charles Lowe [mailto:CLowe@RCCL.COM]
Sent: Wednesday, 8 January 2003 06:33 AM
To: ARSLIST@ARSLIST.ORG
Subject: ARS Performance/Responsiveness


Does anyone out there experience random slow downs in Remedy. Like when in
User and try to fill out a Helpdesk form or loading a form? We been
troubleshooting our system and think its bad SQL statements causing the
problem, forcing the db to blow its cache by doing full table scans. Only
problem is it seems the ARS server is generating this bad SQL code...anyone
else seen or experiencing such issues? We are using ARS 4.03 & Oracle 8.16.

"There are no boundries for those with imagination and the ability to wield
it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027




You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org




You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org



You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org



You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

I found indexing the Dup ID+ field on HPD:HelpDesk improved performance
significantly when viewing an existing request; the table fields on the
Duplicates tab are refreshed on opening the window - without an index this
was taking over 5 seconds with 40000+ records.

Martin Crothall
Met Office, Bracknell, UK

> -----Original Message-----
> From: Fondse, Theo [SMTP:Theo.Fondse@ARS.SMC.SIEMENS.CO.ZA]
> Sent: Wednesday, January 08, 2003 10:04 AM
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: ARS Performance/Responsiveness
>
> Rick's correct! This is an issue I also have picked up with some of my
> cutomers using HD4. If memory serves me right, Patch 678 solved this
> issue,
> but you have to apply the pacth just after you have installed HD4.
> (DO NOT apply the patch if you have modified HD4!)
>
>
> Best regards,
>
> Theo J. Fondse
> Remedy Developer
> Siemens Business Services
>
> Telephone: +27 11 652 7477
> Mobile: +27 83 773 3075
> e-Mail: theo.fondse@ars.smc.siemens.co.za
> Web: http://www.siemens.co.za
>
> ==========================================================================
> =
>
>
> -----Original Message-----
> From: Rick Cook [mailto:rcook@DENALIAI.COM]
> Sent: 08 January 2003 00:51
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: ARS Performance/Responsiveness
>
> Since you're using the Console, check the number of records in
> SHR:ConsolidatedList, too. Sometimes old records don't get deleted as
> they
> should, and they can really accumulate, making queries longer.
>
> Rick
>
> -----Original Message-----
> From: Charles Lowe [mailto:CLowe@RCCL.COM]
> Sent: Tuesday, January 07, 2003 14:29 PM
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: ARS Performance/Responsiveness
>
>
> Thanks Mikhail,
>
> Our user base heavily relies on the Remedy Support console for access to
> the various forms. I'll have to look into the macro thing, sounds like a
> reasonable culprit.
>
> "There are no boundries for those with imagination and the ability to
> wield
> it."
>
> Regards,
> Charles Lowe, Remedy Analyst
> IT - Enterprise Technology & Operations
> Royal Caribbean International & Celebrity Cruises
> Phone (305)982-2937 Cell (786)218-0027
>
>
>
> Mikhail Gvozdev
> > ARSLIST@ARSLIST.ORG
> L.COM.AU> cc:
> Sent by: "Action Subject: Re: ARS
> Performance/Responsiveness
> Request System
> discussion
> list(ARSList)"
> > ORG>
>
>
> 01/07/2003 05:11
> PM
> Please respond to
> arslist
>
>
>
>
>
> Hello Charles,
>
> If you're using Remedy Help Desk application, these table scans are most
> likely generated by badly written macros.
> We had this issue with one of the customers. The system became practically
> irresponsive after end user clicks 'Details...' button on Remedy Help Desk
> Support or Remedy Help Desk Management consoles to bring the ticket
> selected
> from the table field.
>
> One of the macros which forces Oracle to perform table scans is
> SHR:OpenForm, it is used by the following active links:
> name
>
> -----------------------------------------------
> RR:RSD-OpenFormDetails
> RR:SHR-OpenFormDetails2
> SHRA:AST-ShowRelatedDetails2
> SHRC:CHG-ShowRelatedDetails2
> SHRH:HPD-ShowRelatedDetails2
> RR:MHR-OpenFormDetails
> RR:MHR-OpenFormDetails-SLA
> SHRAW:AW-ShowRelatedDetails2A4
> SHRAS:AS-ShowRelatedDetails2A4
> SHRAL:AL-ShowRelatedDetails2A4
> SHRAM:AM-ShowRelatedDetails2A4
> SHRSW:SW-ShowRelatedDetails2A4
> RR:AA-ShowDetailsDownA4
> RR:AA-ShowDetailsManagerA4
> RR:AA-ShowDetailsOwnerA4
> RR:AA-ShowDetailsRepairA4
> RR:AA-ShowDetailsUserA4
> AST:IN Details-Asset 2 AM4
> AST:IN Details-Cmpnt 2 AM4
> SHRM:SHR-ShowRelatedDetails2A4
>
> This macro uses value in 'Case ID+' field in HPD:HelpDesk form as a search
> criteria, but out-of-the-box QBE setting for this field is set to
> "Anywhere", which causes table scans on Oracle and clustered index scans
> on
> MS SQL Server.
>
> Looks like macro was recorded using fields in the form, not Advanced
> Search
> bar. We re-recorded this macro and it solved customer's problem.
>
> Another good candidate for table scans is 'Assignee Group' field, if it
> stores multiple groups' IDs (so called row-level access feature).
> In SQL log file you'll see like this feature builds SQL statements
> containing multiple LIKEs with leading and trailing wildcards linked by
> ORs
> operators.
> If you ask MS SQL Server to display the execution plan for such a query -
> you'll see clustered index scan. Oracle does full table scan, quite scary
> picture.
>
> I've spoken to Remedy Engineering Team about this feature and asked
> whether
> it is valid to have multi-valued columns in the table (it violates the
> First
> Normal Form for database normalisation) - they say this is absolutely
> correct....
>
> Keep searching, and you'll find more beasts!
>
> Regards,
>
> Mikhail Gvozdev
> Remedy Product Support
> Planwell Technology Australia Ltd.
> 1-5 Railway Street
> CHATSWOOD NSW 2067
>
> Phone: +61 2 9412 - 8763
> Fax: +61 2 9419 - 7803
> mailto:mikhailg@planwell.com.au
>
>
>
>
>
>
> -----Original Message-----
> From: Charles Lowe [mailto:CLowe@RCCL.COM]
> Sent: Wednesday, 8 January 2003 06:33 AM
> To: ARSLIST@ARSLIST.ORG
> Subject: ARS Performance/Responsiveness
>
>
> Does anyone out there experience random slow downs in Remedy. Like when in
> User and try to fill out a Helpdesk form or loading a form? We been
> troubleshooting our system and think its bad SQL statements causing the
> problem, forcing the db to blow its cache by doing full table scans. Only
> problem is it seems the ARS server is generating this bad SQL
> code...anyone
> else seen or experiencing such issues? We are using ARS 4.03 & Oracle
> 8.16.
>
> "There are no boundries for those with imagination and the ability to
> wield
> it."
>
> Regards,
> Charles Lowe, Remedy Analyst
> IT - Enterprise Technology & Operations
> Royal Caribbean International & Celebrity Cruises
> Phone (305)982-2937 Cell (786)218-0027
>
>
>
>
>
> You may control your subscription options, including UNSUBSCRIBE,and
> ACCESS
> THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
> eMail to support@arslist.org
>
>
>
>
>
> You may control your subscription options, including UNSUBSCRIBE,and
> ACCESS
> THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
> eMail to support@arslist.org
>
>
>
>
> You may control your subscription options, including UNSUBSCRIBE,and
> ACCESS
> THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
> eMail to support@arslist.org
>
>
>
>
> You may control your subscription options, including UNSUBSCRIBE,and
> ACCESS
> THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
> eMail to support@arslist.org
>
>
>
> You may control your subscription options, including UNSUBSCRIBE,and
> ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please
> send eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

Here is something else to consider. If I remember correctly whenever you
open a ticket for display in out of the box HD, all the table fields on all
tabs would refresh. To speed performance in this case I altered the
refresh of those table fields to occur only on a gain focus of the tab on
which the table field existed. When you begin to have large numbers of
tickets in the HD form, this can slow things down.

Christian Smerz

At 11:20 AM 1/8/2003 +0000, you wrote:
>I found indexing the Dup ID+ field on HPD:HelpDesk improved performance
>significantly when viewing an existing request; the table fields on the
>Duplicates tab are refreshed on opening the window - without an index this
>was taking over 5 seconds with 40000+ records.
>
>Martin Crothall
>Met Office, Bracknell, UK
>
> > -----Original Message-----
> > From: Fondse, Theo [SMTP:Theo.Fondse@ARS.SMC.SIEMENS.CO.ZA]
> > Sent: Wednesday, January 08, 2003 10:04 AM
> > To: ARSLIST@ARSLIST.ORG
> > Subject: Re: ARS Performance/Responsiveness
> >
> > Rick's correct! This is an issue I also have picked up with some of my
> > cutomers using HD4. If memory serves me right, Patch 678 solved this
> > issue,
> > but you have to apply the pacth just after you have installed HD4.
> > (DO NOT apply the patch if you have modified HD4!)
> >
> >
> > Best regards,
> >
> > Theo J. Fondse
> > Remedy Developer
> > Siemens Business Services
> >
> > Telephone: +27 11 652 7477
> > Mobile: +27 83 773 3075
> > e-Mail: theo.fondse@ars.smc.siemens.co.za
> > Web: http://www.siemens.co.za
> >
> > ==========================================================================
> > =
> >
> >
> > -----Original Message-----
> > From: Rick Cook [mailto:rcook@DENALIAI.COM]
> > Sent: 08 January 2003 00:51
> > To: ARSLIST@ARSLIST.ORG
> > Subject: Re: ARS Performance/Responsiveness
> >
> > Since you're using the Console, check the number of records in
> > SHR:ConsolidatedList, too. Sometimes old records don't get deleted as
> > they
> > should, and they can really accumulate, making queries longer.
> >
> > Rick
> >
> > -----Original Message-----
> > From: Charles Lowe [mailto:CLowe@RCCL.COM]
> > Sent: Tuesday, January 07, 2003 14:29 PM
> > To: ARSLIST@ARSLIST.ORG
> > Subject: Re: ARS Performance/Responsiveness
> >
> >
> > Thanks Mikhail,
> >
> > Our user base heavily relies on the Remedy Support console for access to
> > the various forms. I'll have to look into the macro thing, sounds like a
> > reasonable culprit.
> >
> > "There are no boundries for those with imagination and the ability to
> > wield
> > it."
> >
> > Regards,
> > Charles Lowe, Remedy Analyst
> > IT - Enterprise Technology & Operations
> > Royal Caribbean International & Celebrity Cruises
> > Phone (305)982-2937 Cell (786)218-0027
> >
> >
> >
> > Mikhail Gvozdev
> > > > ARSLIST@ARSLIST.ORG
> > L.COM.AU> cc:
> > Sent by: "Action Subject: Re: ARS
> > Performance/Responsiveness
> > Request System
> > discussion
> > list(ARSList)"
> > > > ORG>
> >
> >
> > 01/07/2003 05:11
> > PM
> > Please respond to
> > arslist
> >
> >
> >
> >
> >
> > Hello Charles,
> >
> > If you're using Remedy Help Desk application, these table scans are most
> > likely generated by badly written macros.
> > We had this issue with one of the customers. The system became practically
> > irresponsive after end user clicks 'Details...' button on Remedy Help Desk
> > Support or Remedy Help Desk Management consoles to bring the ticket
> > selected
> > from the table field.
> >
> > One of the macros which forces Oracle to perform table scans is
> > SHR:OpenForm, it is used by the following active links:
> > name
> >
> > -----------------------------------------------
> > RR:RSD-OpenFormDetails
> > RR:SHR-OpenFormDetails2
> > SHRA:AST-ShowRelatedDetails2
> > SHRC:CHG-ShowRelatedDetails2
> > SHRH:HPD-ShowRelatedDetails2
> > RR:MHR-OpenFormDetails
> > RR:MHR-OpenFormDetails-SLA
> > SHRAW:AW-ShowRelatedDetails2A4
> > SHRAS:AS-ShowRelatedDetails2A4
> > SHRAL:AL-ShowRelatedDetails2A4
> > SHRAM:AM-ShowRelatedDetails2A4
> > SHRSW:SW-ShowRelatedDetails2A4
> > RR:AA-ShowDetailsDownA4
> > RR:AA-ShowDetailsManagerA4
> > RR:AA-ShowDetailsOwnerA4
> > RR:AA-ShowDetailsRepairA4
> > RR:AA-ShowDetailsUserA4
> > AST:IN Details-Asset 2 AM4
> > AST:IN Details-Cmpnt 2 AM4
> > SHRM:SHR-ShowRelatedDetails2A4
> >
> > This macro uses value in 'Case ID+' field in HPD:HelpDesk form as a search
> > criteria, but out-of-the-box QBE setting for this field is set to
> > "Anywhere", which causes table scans on Oracle and clustered index scans
> > on
> > MS SQL Server.
> >
> > Looks like macro was recorded using fields in the form, not Advanced
> > Search
> > bar. We re-recorded this macro and it solved customer's problem.
> >
> > Another good candidate for table scans is 'Assignee Group' field, if it
> > stores multiple groups' IDs (so called row-level access feature).
> > In SQL log file you'll see like this feature builds SQL statements
> > containing multiple LIKEs with leading and trailing wildcards linked by
> > ORs
> > operators.
> > If you ask MS SQL Server to display the execution plan for such a query -
> > you'll see clustered index scan. Oracle does full table scan, quite scary
> > picture.
> >
> > I've spoken to Remedy Engineering Team about this feature and asked
> > whether
> > it is valid to have multi-valued columns in the table (it violates the
> > First
> > Normal Form for database normalisation) - they say this is absolutely
> > correct....
> >
> > Keep searching, and you'll find more beasts!
> >
> > Regards,
> >
> > Mikhail Gvozdev
> > Remedy Product Support
> > Planwell Technology Australia Ltd.
> > 1-5 Railway Street
> > CHATSWOOD NSW 2067
> >
> > Phone: +61 2 9412 - 8763
> > Fax: +61 2 9419 - 7803
> > mailto:mikhailg@planwell.com.au
> >
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: Charles Lowe [mailto:CLowe@RCCL.COM]
> > Sent: Wednesday, 8 January 2003 06:33 AM
> > To: ARSLIST@ARSLIST.ORG
> > Subject: ARS Performance/Responsiveness
> >
> >
> > Does anyone out there experience random slow downs in Remedy. Like when in
> > User and try to fill out a Helpdesk form or loading a form? We been
> > troubleshooting our system and think its bad SQL statements causing the
> > problem, forcing the db to blow its cache by doing full table scans. Only
> > problem is it seems the ARS server is generating this bad SQL
> > code...anyone
> > else seen or experiencing such issues? We are using ARS 4.03 & Oracle
> > 8.16.
> >
> > "There are no boundries for those with imagination and the ability to
> > wield
> > it."
> >
> > Regards,
> > Charles Lowe, Remedy Analyst
> > IT - Enterprise Technology & Operations
> > Royal Caribbean International & Celebrity Cruises
> > Phone (305)982-2937 Cell (786)218-0027
> >
> >
> >
> >
> >
> > You may control your subscription options, including UNSUBSCRIBE,and
> > ACCESS
> > THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
> > eMail to support@arslist.org
> >
> >
> >
> >
> >
> > You may control your subscription options, including UNSUBSCRIBE,and
> > ACCESS
> > THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
> > eMail to support@arslist.org
> >
> >
> >
> >
> > You may control your subscription options, including UNSUBSCRIBE,and
> > ACCESS
> > THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
> > eMail to support@arslist.org
> >
> >
> >
> >
> > You may control your subscription options, including UNSUBSCRIBE,and
> > ACCESS
> > THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
> > eMail to support@arslist.org
> >
> >
> >
> > You may control your subscription options, including UNSUBSCRIBE,and
> > ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please
> > send eMail to support@arslist.org
>
>
>You may control your subscription options, including UNSUBSCRIBE,and
>ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please
>send eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

**
We are at 44k for helpdesk 4.0 out of the box and are going through some of this as well especially since they required attachment of request forms to be scanned and attached to tickets some as big as 5 or 6 pages...kevin

W. Kevin Colwell
Help Desk Administrator
CDPHP
1223 Washington Avenue
Albany, New York 12206
phone (518) 641-4446
fax (518) 641-4503
e-mail: kcolwell@cdphp.com

CONFIDENTIALITY NOTICE: This e-mail, including attachments, is for the sole use of the individual to whom it is addressed, and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you have received this e-mail in error, please notify the sender by reply e-mail and destroy this message and its attachments.

>>> SDWang@SSG.PETSMART.COM 01/07/03 04:56PM >>>
**

I have experienced the same problem and do not what the causes are.

Sheila Wang
Remedy Administrator

-----Original Message-----
From: Charles Lowe [ mailto:CLowe@RCCL.COM]
Sent: Tuesday, January 07, 2003 12:33 PM
To: ARSLIST@ARSLIST.ORG
Subject: ARS Performance/Responsiveness


Does anyone out there experience random slow downs in Remedy. Like when in
User and try to fill out a Helpdesk form or loading a form? We been
troubleshooting our system and think its bad SQL statements causing the
problem, forcing the db to blow its cache by doing full table scans. Only
problem is it seems the ARS server is generating this bad SQL code...anyone
else seen or experiencing such issues? We are using ARS 4.03 & Oracle 8.16.

"There are no boundries for those with imagination and the ability to wield
it."

Regards,
Charles Lowe, Remedy Analyst
IT - Enterprise Technology & Operations
Royal Caribbean International & Celebrity Cruises
Phone (305)982-2937 Cell (786)218-0027


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

To UNSUBSCRIBE, email the message "unsubscribe arslist" to: listserv@arslist.org You may also control your subscription options, including UNSUBSCRIBE, at http://www.ARSLIST.org This posting was submitted via the Web interface
To UNSUBSCRIBE, email the message "unsubscribe arslist" to: listserv@arslist.org You may also control your subscription options, including UNSUBSCRIBE, at http://www.ARSLIST.org This posting was submitted via the Web interface

Have you considered archiveing your records?

I recently helped a customer with Remedy HelpDesk and 320.000 records to do
that.

We used rrrChive to do it.
You can get it for free at rrrOnline, http://www.rrr.se/en/

One script runs each night and MOVES untouched records that has been closed
for three months.

Another instance is run from a filter whenever a ticket is closed, and
COPIES the ticket to the archive form.

The archive form is a copy of the main HelpDesk-form.

Whe left child records in their original forms. This was easier to do and
did not affect performance much.

Best Regards - Misi, RRR AB, http://www.rrr.se/en/

> We are at 44k for helpdesk 4.0 out of the box and are going through
> some of this as well especially since they required attachment of
> request forms to be scanned and attached to tickets some as big as 5 or
> 6 pages...kevin
>
> W. Kevin Colwell
> Help Desk Administrator
> CDPHP
> 1223 Washington Avenue
> Albany, New York 12206
> phone (518) 641-4446
> fax (518) 641-4503
> e-mail: kcolwell@cdphp.com
>
> CONFIDENTIALITY NOTICE: This e-mail, including attachments, is for the
> sole use of the individual to whom it is addressed, and may contain
> confidential and privileged information. Any unauthorized review, use,
> disclosure or distribution is prohibited. If you have received this
> e-mail in error, please notify the sender by reply e-mail and destroy
> this message and its attachments.
>
>>>> SDWang@SSG.PETSMART.COM 01/07/03 04:56PM >>>
> **
> I have experienced the same problem and do not what the causes are.
> Sheila Wang
> Remedy Administrator
> -----Original Message-----
> From: Charles Lowe [mailto:CLowe@RCCL.COM]
> Sent: Tuesday, January 07, 2003 12:33 PM
> To: ARSLIST@ARSLIST.ORG
> Subject: ARS Performance/Responsiveness
>
>
> Does anyone out there experience random slow downs in Remedy. Like when
> in User and try to fill out a Helpdesk form or loading a form? We
> been troubleshooting our system and think its bad SQL statements
> causing the problem, forcing the db to blow its cache by doing full
> table scans. Only problem is it seems the ARS server is generating
> this bad SQL code...anyone else seen or experiencing such issues? We
> are using ARS 4.03 & Oracle 8.16. "There are no boundries for those
> with imagination and the ability to wield it."
> Regards,
> Charles Lowe, Remedy Analyst
> IT - Enterprise Technology & Operations
> Royal Caribbean International & Celebrity Cruises
> Phone (305)982-2937 Cell (786)218-0027
>


> You may control your subscription options, including UNSUBSCRIBE,and
> ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support,
> please send eMail to support@arslist.org
>


> To UNSUBSCRIBE, email the message "unsubscribe arslist" to:
> listserv@arslist.org You may also control your subscription options,
> including UNSUBSCRIBE, at http://www.ARSLIST.org This posting was
> submitted via the Web interface
>
>


> You may control your subscription options, including UNSUBSCRIBE,and
> ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support,
> please send eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

Hi Rick, Ver,

I live under the impression that based on opperations and availability a
select statement generated from remedy always use indexes. It is not Remedy
who decides to use them or not. If you have index on field A and statement
with ... where A="Hardware" then sure the index is used. Then Rick has the
point that not all operations can use index , LIKE "%Hardware%" is allways a
scan, etc. I may not agree with Rick's 2), where he says the order of
appearance of the fields in the statement matters. In my oppinion, if you
have where... AND A="Hardware" AND ..... will use index if there is one
built for field A, regardless where A is in the statement, first or last.

Just my $.02

Nicky
----- Original Message -----
From: "Rick Cook"
To:
Sent: Thursday, February 06, 2003 10:12 AM
Subject: Re: ARS Performance/Responsiveness


Ver,

Thanks for the expansion of your earlier comments, and please DO engage us
in discussions - that's what this list is for, and how we all learn new
things. My question was intended to determine whether we had something to
teach you, or you had something to teach us. Since our collective knowledge
level is increased in either case, I don't really care which it is. Anyone
offended by learning something new won't be very comfortable here for very
long.

I am sure that looking at the SQL logs, it must seem very weird to see
simple SQL statements that don't use indexes. To really understand why they
are or are not used, you must understand how Remedy determines that. The
Performance Tuning class is great at that; I think it is probably the most
valuable of all the Remedy training courses, but in a nutshell here's how it
works (at least to the best of my understanding):

A SQL query, executed via Remedy clients or workflow, is dependent upon
several variables to be able to use a defined index.

1) The selectiveness of a field. A search that returns less than 5% of the
total data in the form is more likely to benefit from the use of an index.
So if you are indexing a field with less than 20 values (on average), that
index will not be used as often as you think.
2) The structure of the SQL statement. If the query leads with a
non-indexed field, or one for whom a defined index isn't selective enough,
the benefits of the indexes on other fields in the SQL call won't be
realized, because a table scan will have already happened. Better to have
the indexed field(s) first, so that the scan will be on a subset of the
table, not the entire thing.
3) The use of NOT or != operators in the query will almost guarantee a
table scan, even if every field referenced in the query is indexed. Rather
than 'Status' != "Closed", use 'Status' <= "Pending".
4) The use of Wildcard searches, or LIKE statements will never use an
index.
5) The QBE match settings, and the use of QBE. Check the docs for the full
explanation on that. In fact, the 5.1 docs are VERY good at explaining all
of this.

That's just a short list, but covers most of the main issues with Remedy's
use of indexes. I'd love to see the guts of Remedy's query optimizer to see
what it's REALLY using for criteria. I think I remember hearing once that
it will restructure the queries to optimize the potential use of indexes,
but the docs don't back that up, so I don't know if that's accurate. If
anyone can expand on (or correct) what I stated, please do.

Ver, if you're seeing something that indicates that properly configured SQL
calls are not using appropriately defined indexes, please let us - and
Remedy Support - know about it.

And DO keep posting, please!

Rick

-----Original Message-----
From: Ver Holipaz [mailto:vholipa@NGCO.COM]
Sent: Thursday, February 06, 2003 6:12 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


I'm sorry if I gave the wrong impression. It was not meant to be a blanket
statement. It was a "hunch", that's why I said, "Remedy does not SEEM to
optimize by using the table indexes.".

I'm not quite sure how Remedy utilizes indexes. I am just basing this on an
SQL Log that we turned on to capture SQL statements that Remedy issues to
Oracle.

It seems, again I say SEEMS, that all the SQL statements logged are simple
ones and none of them utilized the existing indexes for the table.

If i am totally wrong, then again, I'm sorry. I am just trying to help,
guys. I'm not trying to put down anything or anybody. I love Remedy and I
believe it is a great tool. I just believe that, like any software tool, it
has it's own limitations.

I hope this clears the air. Perhaps I shouldn't meddle with any discussion
in this list again.


On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook wrote:

>Upon what do you base your blanket statement that Remedy does not use
indexes? It has a built-in query optimizer that works with the DBMS to
determine whether a specified index might or might not be the fastest way
to retrieve the results. I doubt it's perfect, but I doubt even more that
it's totally broken.
>
>Can you substantiate your claim, for the benefit of all, please?
>
>Rick Cook
>Senior Remedy Consultant
>Denali Advanced Integration
>
>-----Original Message-----
>From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>Sent: Friday, January 31, 2003 6:33 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Remedy passes simple SQL statements to Oracle and does complete table
>scans. Remedy does not seem to optimize by using the table indexes.
>
>You might want to try the following system parameter setting in Oracle.
>
>"alter session set optimizerindexcostadj=1"
>
>This forces Oracle to use indexes instead of complete table scans everytime
>Remedy issues an SQL statement.
>
>

>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



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

Sorry, Nicky, but you need to check the docs again, because they back up my statement. The position of the parameters DOES matter. I was looking at the 5.1 doc as I wrote that big email, to ensure that I kept my errors to a minimum.

Rick

-----Original Message-----
From: Nicky Madjarov [mailto:nickym@EARTHLINK.NET]
Sent: Thursday, February 06, 2003 1:04 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Hi Rick, Ver,

I live under the impression that based on opperations and availability a
select statement generated from remedy always use indexes. It is not Remedy
who decides to use them or not. If you have index on field A and statement
with ... where A="Hardware" then sure the index is used. Then Rick has the
point that not all operations can use index , LIKE "%Hardware%" is allways a
scan, etc. I may not agree with Rick's 2), where he says the order of
appearance of the fields in the statement matters. In my oppinion, if you
have where... AND A="Hardware" AND ..... will use index if there is one
built for field A, regardless where A is in the statement, first or last.

Just my $.02

Nicky
----- Original Message -----
From: "Rick Cook"
To:
Sent: Thursday, February 06, 2003 10:12 AM
Subject: Re: ARS Performance/Responsiveness


Ver,

Thanks for the expansion of your earlier comments, and please DO engage us
in discussions - that's what this list is for, and how we all learn new
things. My question was intended to determine whether we had something to
teach you, or you had something to teach us. Since our collective knowledge
level is increased in either case, I don't really care which it is. Anyone
offended by learning something new won't be very comfortable here for very
long.

I am sure that looking at the SQL logs, it must seem very weird to see
simple SQL statements that don't use indexes. To really understand why they
are or are not used, you must understand how Remedy determines that. The
Performance Tuning class is great at that; I think it is probably the most
valuable of all the Remedy training courses, but in a nutshell here's how it
works (at least to the best of my understanding):

A SQL query, executed via Remedy clients or workflow, is dependent upon
several variables to be able to use a defined index.

1) The selectiveness of a field. A search that returns less than 5% of the
total data in the form is more likely to benefit from the use of an index.
So if you are indexing a field with less than 20 values (on average), that
index will not be used as often as you think.
2) The structure of the SQL statement. If the query leads with a
non-indexed field, or one for whom a defined index isn't selective enough,
the benefits of the indexes on other fields in the SQL call won't be
realized, because a table scan will have already happened. Better to have
the indexed field(s) first, so that the scan will be on a subset of the
table, not the entire thing.
3) The use of NOT or != operators in the query will almost guarantee a
table scan, even if every field referenced in the query is indexed. Rather
than 'Status' != "Closed", use 'Status' <= "Pending".
4) The use of Wildcard searches, or LIKE statements will never use an
index.
5) The QBE match settings, and the use of QBE. Check the docs for the full
explanation on that. In fact, the 5.1 docs are VERY good at explaining all
of this.

That's just a short list, but covers most of the main issues with Remedy's
use of indexes. I'd love to see the guts of Remedy's query optimizer to see
what it's REALLY using for criteria. I think I remember hearing once that
it will restructure the queries to optimize the potential use of indexes,
but the docs don't back that up, so I don't know if that's accurate. If
anyone can expand on (or correct) what I stated, please do.

Ver, if you're seeing something that indicates that properly configured SQL
calls are not using appropriately defined indexes, please let us - and
Remedy Support - know about it.

And DO keep posting, please!

Rick

-----Original Message-----
From: Ver Holipaz [mailto:vholipa@NGCO.COM]
Sent: Thursday, February 06, 2003 6:12 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


I'm sorry if I gave the wrong impression. It was not meant to be a blanket
statement. It was a "hunch", that's why I said, "Remedy does not SEEM to
optimize by using the table indexes.".

I'm not quite sure how Remedy utilizes indexes. I am just basing this on an
SQL Log that we turned on to capture SQL statements that Remedy issues to
Oracle.

It seems, again I say SEEMS, that all the SQL statements logged are simple
ones and none of them utilized the existing indexes for the table.

If i am totally wrong, then again, I'm sorry. I am just trying to help,
guys. I'm not trying to put down anything or anybody. I love Remedy and I
believe it is a great tool. I just believe that, like any software tool, it
has it's own limitations.

I hope this clears the air. Perhaps I shouldn't meddle with any discussion
in this list again.


On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook wrote:

>Upon what do you base your blanket statement that Remedy does not use
indexes? It has a built-in query optimizer that works with the DBMS to
determine whether a specified index might or might not be the fastest way
to retrieve the results. I doubt it's perfect, but I doubt even more that
it's totally broken.
>
>Can you substantiate your claim, for the benefit of all, please?
>
>Rick Cook
>Senior Remedy Consultant
>Denali Advanced Integration
>
>-----Original Message-----
>From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>Sent: Friday, January 31, 2003 6:33 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Remedy passes simple SQL statements to Oracle and does complete table
>scans. Remedy does not seem to optimize by using the table indexes.
>
>You might want to try the following system parameter setting in Oracle.
>
>"alter session set optimizerindexcostadj=1"
>
>This forces Oracle to use indexes instead of complete table scans everytime
>Remedy issues an SQL statement.
>
>

>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



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

And you can always check to see which index is being used by checking the
operation at the database level

"showplan xxxxx" works for Sybase - this will return which index the
database is going to use (and/or if it's going to do a table scan)

Barb Wagner
IBM Global Services
612-330-2920
barbwa@us.ibm.com




Rick Cook
OM> cc:
Sent by: "Action Subject: Re: ARS Performance/Responsiveness
Request System
discussion
list(ARSList)"
ORG>


02/06/2003 03:30
PM
Please respond to
arslist






Sorry, Nicky, but you need to check the docs again, because they back up my
statement. The position of the parameters DOES matter. I was looking at
the 5.1 doc as I wrote that big email, to ensure that I kept my errors to a
minimum.

Rick

-----Original Message-----
From: Nicky Madjarov [mailto:nickym@EARTHLINK.NET]
Sent: Thursday, February 06, 2003 1:04 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Hi Rick, Ver,

I live under the impression that based on opperations and availability a
select statement generated from remedy always use indexes. It is not Remedy
who decides to use them or not. If you have index on field A and statement
with ... where A="Hardware" then sure the index is used. Then Rick has the
point that not all operations can use index , LIKE "%Hardware%" is allways
a
scan, etc. I may not agree with Rick's 2), where he says the order of
appearance of the fields in the statement matters. In my oppinion, if you
have where... AND A="Hardware" AND ..... will use index if there is one
built for field A, regardless where A is in the statement, first or last.

Just my $.02

Nicky
----- Original Message -----
From: "Rick Cook"
To:
Sent: Thursday, February 06, 2003 10:12 AM
Subject: Re: ARS Performance/Responsiveness


Ver,

Thanks for the expansion of your earlier comments, and please DO engage us
in discussions - that's what this list is for, and how we all learn new
things. My question was intended to determine whether we had something to
teach you, or you had something to teach us. Since our collective
knowledge
level is increased in either case, I don't really care which it is. Anyone
offended by learning something new won't be very comfortable here for very
long.

I am sure that looking at the SQL logs, it must seem very weird to see
simple SQL statements that don't use indexes. To really understand why
they
are or are not used, you must understand how Remedy determines that. The
Performance Tuning class is great at that; I think it is probably the most
valuable of all the Remedy training courses, but in a nutshell here's how
it
works (at least to the best of my understanding):

A SQL query, executed via Remedy clients or workflow, is dependent upon
several variables to be able to use a defined index.

1) The selectiveness of a field. A search that returns less than 5% of
the
total data in the form is more likely to benefit from the use of an index.
So if you are indexing a field with less than 20 values (on average), that
index will not be used as often as you think.
2) The structure of the SQL statement. If the query leads with a
non-indexed field, or one for whom a defined index isn't selective enough,
the benefits of the indexes on other fields in the SQL call won't be
realized, because a table scan will have already happened. Better to have
the indexed field(s) first, so that the scan will be on a subset of the
table, not the entire thing.
3) The use of NOT or != operators in the query will almost guarantee a
table scan, even if every field referenced in the query is indexed. Rather
than 'Status' != "Closed", use 'Status' <= "Pending".
4) The use of Wildcard searches, or LIKE statements will never use an
index.
5) The QBE match settings, and the use of QBE. Check the docs for the
full
explanation on that. In fact, the 5.1 docs are VERY good at explaining all
of this.

That's just a short list, but covers most of the main issues with Remedy's
use of indexes. I'd love to see the guts of Remedy's query optimizer to
see
what it's REALLY using for criteria. I think I remember hearing once that
it will restructure the queries to optimize the potential use of indexes,
but the docs don't back that up, so I don't know if that's accurate. If
anyone can expand on (or correct) what I stated, please do.

Ver, if you're seeing something that indicates that properly configured SQL
calls are not using appropriately defined indexes, please let us - and
Remedy Support - know about it.

And DO keep posting, please!

Rick

-----Original Message-----
From: Ver Holipaz [mailto:vholipa@NGCO.COM]
Sent: Thursday, February 06, 2003 6:12 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


I'm sorry if I gave the wrong impression. It was not meant to be a blanket
statement. It was a "hunch", that's why I said, "Remedy does not SEEM to
optimize by using the table indexes.".

I'm not quite sure how Remedy utilizes indexes. I am just basing this on an
SQL Log that we turned on to capture SQL statements that Remedy issues to
Oracle.

It seems, again I say SEEMS, that all the SQL statements logged are simple
ones and none of them utilized the existing indexes for the table.

If i am totally wrong, then again, I'm sorry. I am just trying to help,
guys. I'm not trying to put down anything or anybody. I love Remedy and I
believe it is a great tool. I just believe that, like any software tool, it
has it's own limitations.

I hope this clears the air. Perhaps I shouldn't meddle with any discussion
in this list again.


On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook wrote:

>Upon what do you base your blanket statement that Remedy does not use
indexes? It has a built-in query optimizer that works with the DBMS to
determine whether a specified index might or might not be the fastest way
to retrieve the results. I doubt it's perfect, but I doubt even more that
it's totally broken.
>
>Can you substantiate your claim, for the benefit of all, please?
>
>Rick Cook
>Senior Remedy Consultant
>Denali Advanced Integration
>
>-----Original Message-----
>From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>Sent: Friday, January 31, 2003 6:33 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Remedy passes simple SQL statements to Oracle and does complete table
>scans. Remedy does not seem to optimize by using the table indexes.
>
>You might want to try the following system parameter setting in Oracle.
>
>"alter session set optimizerindexcostadj=1"
>
>This forces Oracle to use indexes instead of complete table scans
everytime
>Remedy issues an SQL statement.
>
>


>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




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



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

All DBMS have optimizers. One of the first things the DB does is parse
the query and determine the most efficient (cost or performance based)
query method available to it. In SQL server this is based upon
statistics that the DB keeps on such things as data distribution across
index fields, etc. The issue with QBE is that you have no idea what the
user is placing in the field. Wildcard searches will invoke a full
table scan (very bad) or worse, the dynamic creation of a temporary
index (VERY bad). Remedy always has a unique key build on the Entry
ID(C1) of each table(form). Within workflow, common sense can prevail.
Make sure that your workflow has indexes build behind it. For example,
if you have a table field based upon an ascending/descend date create
an index to support it. The DBMS optimizer will make use of the index.
Anytime you have a master/detail relationship always use retrieve the
record by the Entry ID.

You can gain a lot of performance by following the rules that Rick and
others have suggested. There is no magic here just applied common sense.

Peter McMullen
QMX Support Services
Voice: 250.701.3748


-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Rick Cook
Sent: Thursday, February 06, 2003 8:22 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness

Mauricio,

This is my understanding of the function of the Remedy query optimizer.
It basically is a switch that determines that the query either uses the
index or doesn't. I could be wrong, but I don't think it's any more -
or less - complex than that.

I just think it would be a great thing if it did what the name implies,
which is to restructure queries to make them operate in the most
efficient manner possible. I think that some DBMS systems (Oracle being
the most likely) have their own that could be used as a model for this.
In fact, since most modern DBMS have FTS embedded, it makes sense for
the DBMS to be the home for the query optimizer as well. Being able to
do this in a way that actually uses less time than performing the search
is the trick. I'll bring this up to Remedy Engineering next chance I
get (probably RUG).

Rick

-----Original Message-----
From: Mauricio Meraz [mailto:maurma@BITAL.COM.MX]
Sent: Thursday, February 06, 2003 8:03 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Hello Rick

From what I remember, and that is what I got and remember from the PTT
class, pardon me if I am wrong, I understand Remedy only passes the
SQL statements to the RDBMS right from workflow, so this last one, the
RDMBS determines with its query optimizer if it uses the index or not.
So that is why we need to build better escalations, set fields, search
menues, etc, everything else thinking in terms of performance.

Regards,Mauricio

----- Original Message -----
From: Rick Cook
Date: Thursday, February 6, 2003 9:33 am
Subject: Re: ARS Performance/Responsiveness

> As a post you by now have seen elucidates, I know that there IS a
> Remedy query optimizer, but I don't know exactly what it DOES. I
> think that it just determines whether or not to use the indexes in
> favor of a table scan, but I don't know how well it does that.
> The docs don't indicate that it actually restructures the SQL to
> make it the most efficient, but that would be pretty nice,
> wouldn't it? Then we could hold an obfuscated SQL code contest
> (like the C contest) to see how screwed up we could make a SQL
> call look and still have it work right. THAT would be fun.
>
> Rick
>
> -----Original Message-----
> From: Michael Worts [mailto:Michael.Worts@NTL.COM]
> Sent: Thursday, February 06, 2003 7:15 AM
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: ARS Performance/Responsiveness
>
>
> Hi Rick,
>
> Are you saying that Remedy has a built in query optimiser? This is
> somethingthat I have not heard of before. Would you be able to
> explain more about it?
> I guess Remedy does build SQL statements based on the objects that
you
> develop but does it actually optimise the query looking for the
> best or most
> suited way to use the table indexes.
>
> Mike.
>
> Michael Worts
> IBM Global Services
> Phone: 01483 875273
> Mobile: 07801 755346
> VPN: (716) 5273
> mailto:michael.worts@ntl.com
>
> -----Original Message-----
> From: Jose Leon [mailto:jose.leon@AMERICAS.BNPPARIBAS.COM]
> Sent: 06 February 2003 14:43
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: ARS Performance/Responsiveness
>
>
> I think Rick Cook just wanted to know where you were coming from.
> All messages in this list (in my short time on it) do not seem to be
> taken personal when making statements about the Remedy products.
> For example, there are people here that do not hesitate to mentioned
> the limitations of Remedy Migrator.
>
> I would encourage you to continue to make contributions to this list
> whenever you think it might benefit the group or a specific person.
>
> Jose Leon
>
>
>
> Internet
> vholipa@NGCO.COM@ARSLIST.ORG - 02/06/2003 09:11 AM
>
>
> Please respond to arslist@ARSLIST.ORG
>
> Sent by: ARSLIST@ARSLIST.ORG
>
> To: ARSLIST
>
> cc:
>
>
> Subject: Re: ARS Performance/Responsiveness
>
>
> I'm sorry if I gave the wrong impression. It was not meant to be a
> blanketstatement. It was a "hunch", that's why I said, "Remedy
> does not SEEM to
> optimize by using the table indexes.".
>
> I'm not quite sure how Remedy utilizes indexes. I am just basing
> this on an
> SQL Log that we turned on to capture SQL statements that Remedy
> issues to
> Oracle.
>
> It seems, again I say SEEMS, that all the SQL statements logged
> are simple
> ones and none of them utilized the existing indexes for the table.
>
> If i am totally wrong, then again, I'm sorry. I am just trying to
> help,guys. I'm not trying to put down anything or anybody. I love
> Remedy and I
> believe it is a great tool. I just believe that, like any software
> tool, it
> has it's own limitations.
>
> I hope this clears the air. Perhaps I shouldn't meddle with any
> discussionin this list again.
>
>
> On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook
> wrote:
> >Upon what do you base your blanket statement that Remedy does not
use
> indexes? It has a built-in query optimizer that works with the
> DBMS to
> determine whether a specified index might or might not be the
> fastest way
> to retrieve the results. I doubt it's perfect, but I doubt even
> more that
> it's totally broken.
> >
> >Can you substantiate your claim, for the benefit of all, please?
> >
> >Rick Cook
> >Senior Remedy Consultant
> >Denali Advanced Integration
> >
> >-----Original Message-----
> >From: Ver Holipaz [mailto:vholipa@NGCO.COM]
> >Sent: Friday, January 31, 2003 6:33 AM
> >To: ARSLIST@ARSLIST.ORG
> >Subject: Re: ARS Performance/Responsiveness
> >
> >
> >Remedy passes simple SQL statements to Oracle and does complete
table
> >scans. Remedy does not seem to optimize by using the table indexes.
> >
> >You might want to try the following system parameter setting in
> Oracle.>
> >"alter session set optimizerindexcostadj=1"
> >
> >This forces Oracle to use indexes instead of complete table scans
> everytime
> >Remedy issues an SQL statement.
> >
> >
>


>
> >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
>
>


>
>
> 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
>
>
>
>
>
> This message and any attachments (the "message") is
> intended solely for the addressees and is confidential.
> If you receive this message in error, please delete it and
> immediately notify the sender. Any use not in accord with
> its purpose, any dissemination or disclosure, either whole
> or partial, is prohibited except formal approval. The internet
> can not guarantee the integrity of this message.
> BNP PARIBAS (and its subsidiaries) shall (will) not
> therefore be liable for the message if modified.
>
> ---------------------------------------------
>
> Ce message et toutes les pieces jointes (ci-apres le
> "message") sont etablis a l'intention exclusive de ses
> destinataires et sont confidentiels. Si vous recevez ce
> message par erreur, merci de le detruire et d'en avertir
> immediatement l'expediteur. Toute utilisation de ce
> message non conforme a sa destination, toute diffusion
> ou toute publication, totale ou partielle, est interdite, sauf
> autorisation expresse. L'internet ne permettant pas
> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
> filiales) decline(nt) toute responsabilite au titre de ce
> message, dans l'hypothese ou il aurait ete modifie.
>
>


>
> 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
>
>
> The contents of this email and any attachments are sent for the
> personal attention
> of the addressee(s) only and may be confidential. If you are not
> the intended
> addressee, any use, disclosure or copying of this email and any
> attachments is
> unauthorised - please notify the sender by return and delete the
> message. Any
> representations or commitments expressed in this email are subject
> to contract.
>
> ntl Group Limited
>
>


> 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
>



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

If you are concerned about performance, you should also check things on
the db side of the house. After doing a lot of development on a
machine, there are some things that can be done to the database to get
large performance increases.

1. Defrag the indexes in the database. After deleting columns, creating
columns, adding and dropping sample data, the indexes become very
fragmented.

2. Keep your index usage statistics up to date.

I know how to do these things in SQL server, but not other databases.
Here are the sql commands I use to accomplish this:

1. I use this TSQL script to set defrag any indexes whose fragmentation
has exceeded 1% fragmentation. (May seem a bit excessive, but I like
well oiled machines). I perform this on all tablespaces.

This script is compliments of MSDN (Microsoft Developer Network)

****
**** Begin Script
****

-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 1.0

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLENAME
FROM INFORMATIONSCHEMA.TABLES
WHERE TABLETYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCHSTATUS = 0
BEGIN

-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALLINDEXES, NOINFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCHSTATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
Go

****
**** End Script
****

Sample output:

Executing DBCC INDEXDEFRAG (0, filterpush,

1) - fragmentation currently 13%
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
36 28 4

(1 row(s) affected)


(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

2. To keep the usage stats up to date, I use this command. It is also
run against all tablespaces.

DBCC UPDATEUSAGE (ARSystem)

This updates the page usage of the indexes and spreads them out over
more pages, reserves more pages, as needed.

Sample output:

DBCC UPDATEUSAGE: sysindexes row updated for table 'T368' (index ID 4):
USED pages: Changed from (4) to (5) pages.
RSVD pages: Changed from (4) to (5) pages.

Kind Regards,
Axton Grams
University of North Texas
ACS, Computing Services, CSS IV
940.565.3287



-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Barb Wagner
Sent: Thursday, February 06, 2003 3:46 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness

And you can always check to see which index is being used by checking
the
operation at the database level

"showplan xxxxx" works for Sybase - this will return which index the
database is going to use (and/or if it's going to do a table scan)

Barb Wagner
IBM Global Services
612-330-2920
barbwa@us.ibm.com




Rick Cook
ARSLIST@ARSLIST.ORG
OM> cc:
Sent by: "Action Subject: Re: ARS
Performance/Responsiveness
Request System
discussion
list(ARSList)"
ORG>


02/06/2003 03:30
PM
Please respond to
arslist






Sorry, Nicky, but you need to check the docs again, because they back up
my
statement. The position of the parameters DOES matter. I was looking
at
the 5.1 doc as I wrote that big email, to ensure that I kept my errors
to a
minimum.

Rick

-----Original Message-----
From: Nicky Madjarov [mailto:nickym@EARTHLINK.NET]
Sent: Thursday, February 06, 2003 1:04 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Hi Rick, Ver,

I live under the impression that based on opperations and availability a
select statement generated from remedy always use indexes. It is not
Remedy
who decides to use them or not. If you have index on field A and
statement
with ... where A="Hardware" then sure the index is used. Then Rick has
the
point that not all operations can use index , LIKE "%Hardware%" is
allways
a
scan, etc. I may not agree with Rick's 2), where he says the order of
appearance of the fields in the statement matters. In my oppinion, if
you
have where... AND A="Hardware" AND ..... will use index if there is one
built for field A, regardless where A is in the statement, first or
last.

Just my $.02

Nicky
----- Original Message -----
From: "Rick Cook"
To:
Sent: Thursday, February 06, 2003 10:12 AM
Subject: Re: ARS Performance/Responsiveness


Ver,

Thanks for the expansion of your earlier comments, and please DO engage
us
in discussions - that's what this list is for, and how we all learn new
things. My question was intended to determine whether we had something
to
teach you, or you had something to teach us. Since our collective
knowledge
level is increased in either case, I don't really care which it is.
Anyone
offended by learning something new won't be very comfortable here for
very
long.

I am sure that looking at the SQL logs, it must seem very weird to see
simple SQL statements that don't use indexes. To really understand why
they
are or are not used, you must understand how Remedy determines that.
The
Performance Tuning class is great at that; I think it is probably the
most
valuable of all the Remedy training courses, but in a nutshell here's
how
it
works (at least to the best of my understanding):

A SQL query, executed via Remedy clients or workflow, is dependent upon
several variables to be able to use a defined index.

1) The selectiveness of a field. A search that returns less than 5% of
the
total data in the form is more likely to benefit from the use of an
index.
So if you are indexing a field with less than 20 values (on average),
that
index will not be used as often as you think.
2) The structure of the SQL statement. If the query leads with a
non-indexed field, or one for whom a defined index isn't selective
enough,
the benefits of the indexes on other fields in the SQL call won't be
realized, because a table scan will have already happened. Better to
have
the indexed field(s) first, so that the scan will be on a subset of the
table, not the entire thing.
3) The use of NOT or != operators in the query will almost guarantee a
table scan, even if every field referenced in the query is indexed.
Rather
than 'Status' != "Closed", use 'Status' <= "Pending".
4) The use of Wildcard searches, or LIKE statements will never use an
index.
5) The QBE match settings, and the use of QBE. Check the docs for the
full
explanation on that. In fact, the 5.1 docs are VERY good at explaining
all
of this.

That's just a short list, but covers most of the main issues with
Remedy's
use of indexes. I'd love to see the guts of Remedy's query optimizer to
see
what it's REALLY using for criteria. I think I remember hearing once
that
it will restructure the queries to optimize the potential use of
indexes,
but the docs don't back that up, so I don't know if that's accurate. If
anyone can expand on (or correct) what I stated, please do.

Ver, if you're seeing something that indicates that properly configured
SQL
calls are not using appropriately defined indexes, please let us - and
Remedy Support - know about it.

And DO keep posting, please!

Rick

-----Original Message-----
From: Ver Holipaz [mailto:vholipa@NGCO.COM]
Sent: Thursday, February 06, 2003 6:12 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


I'm sorry if I gave the wrong impression. It was not meant to be a
blanket
statement. It was a "hunch", that's why I said, "Remedy does not SEEM to
optimize by using the table indexes.".

I'm not quite sure how Remedy utilizes indexes. I am just basing this on
an
SQL Log that we turned on to capture SQL statements that Remedy issues
to
Oracle.

It seems, again I say SEEMS, that all the SQL statements logged are
simple
ones and none of them utilized the existing indexes for the table.

If i am totally wrong, then again, I'm sorry. I am just trying to help,
guys. I'm not trying to put down anything or anybody. I love Remedy and
I
believe it is a great tool. I just believe that, like any software tool,
it
has it's own limitations.

I hope this clears the air. Perhaps I shouldn't meddle with any
discussion
in this list again.


On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook
wrote:

>Upon what do you base your blanket statement that Remedy does not use
indexes? It has a built-in query optimizer that works with the DBMS to
determine whether a specified index might or might not be the fastest
way
to retrieve the results. I doubt it's perfect, but I doubt even more
that
it's totally broken.
>
>Can you substantiate your claim, for the benefit of all, please?
>
>Rick Cook
>Senior Remedy Consultant
>Denali Advanced Integration
>
>-----Original Message-----
>From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>Sent: Friday, January 31, 2003 6:33 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Remedy passes simple SQL statements to Oracle and does complete table
>scans. Remedy does not seem to optimize by using the table indexes.
>
>You might want to try the following system parameter setting in Oracle.
>
>"alter session set optimizerindexcostadj=1"
>
>This forces Oracle to use indexes instead of complete table scans
everytime
>Remedy issues an SQL statement.
>
>



>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





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




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



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

In the Oracle 9i world:

To rebuild an index (as the name suggests, it recreates the index, which
implies defragmentation), and gather statistics in one shot:

alter index rebuild online compute statistics

Nothing against SQL server, but in this case, the Oracle way is much
simpler!!
(there is a general believe Oracle is more complex than SQL server, however
this is less true nowadays, as SQL Server becomes more complex and Oracle
administration becomes easier)

Oracle 8i users cannot rebuild the index "online", they need to use the same
command without the online option:

alter index rebuild compute statistics

However, rebuilding the index locks the table for updates and inserts. The
table locking period is greatly reduced in 9i with the online option, since
a journal of modifications will be kept online in memory while the index is
being rebuilt, and then the record modifications (with corresponding indexes
updates) will be applied to the new index in memory. This scenario iterates
itself I think 3 or 4 times in memory, until the last journal is applied
(which hopefully is very small), and then the index data is copied to disk.

Guillaume

-----Original Message-----
From: Axton Grams [mailto:arslist@INSPIRATIONALTECHNOLOGIES.COM]
Sent: Thursday, February 06, 2003 04:40
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


If you are concerned about performance, you should also check things on
the db side of the house. After doing a lot of development on a
machine, there are some things that can be done to the database to get
large performance increases.

1. Defrag the indexes in the database. After deleting columns, creating
columns, adding and dropping sample data, the indexes become very
fragmented.

2. Keep your index usage statistics up to date.

I know how to do these things in SQL server, but not other databases.
Here are the sql commands I use to accomplish this:

1. I use this TSQL script to set defrag any indexes whose fragmentation
has exceeded 1% fragmentation. (May seem a bit excessive, but I like
well oiled machines). I perform this on all tablespaces.

This script is compliments of MSDN (Microsoft Developer Network)

****
**** Begin Script
****

-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 1.0

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLENAME
FROM INFORMATIONSCHEMA.TABLES
WHERE TABLETYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCHSTATUS = 0
BEGIN

-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALLINDEXES, NOINFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCHSTATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
Go

****
**** End Script
****

Sample output:

Executing DBCC INDEXDEFRAG (0, filterpush,

1) - fragmentation currently 13%
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
36 28 4

(1 row(s) affected)


(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

2. To keep the usage stats up to date, I use this command. It is also
run against all tablespaces.

DBCC UPDATEUSAGE (ARSystem)

This updates the page usage of the indexes and spreads them out over
more pages, reserves more pages, as needed.

Sample output:

DBCC UPDATEUSAGE: sysindexes row updated for table 'T368' (index ID 4):
USED pages: Changed from (4) to (5) pages.
RSVD pages: Changed from (4) to (5) pages.

Kind Regards,
Axton Grams
University of North Texas
ACS, Computing Services, CSS IV
940.565.3287



-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:ARSLIST@ARSLIST.ORG] On Behalf Of Barb Wagner
Sent: Thursday, February 06, 2003 3:46 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness

And you can always check to see which index is being used by checking
the
operation at the database level

"showplan xxxxx" works for Sybase - this will return which index the
database is going to use (and/or if it's going to do a table scan)

Barb Wagner
IBM Global Services
612-330-2920
barbwa@us.ibm.com




Rick Cook
ARSLIST@ARSLIST.ORG
OM> cc:
Sent by: "Action Subject: Re: ARS
Performance/Responsiveness
Request System
discussion
list(ARSList)"
ORG>


02/06/2003 03:30
PM
Please respond to
arslist






Sorry, Nicky, but you need to check the docs again, because they back up
my
statement. The position of the parameters DOES matter. I was looking
at
the 5.1 doc as I wrote that big email, to ensure that I kept my errors
to a
minimum.

Rick

-----Original Message-----
From: Nicky Madjarov [mailto:nickym@EARTHLINK.NET]
Sent: Thursday, February 06, 2003 1:04 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Hi Rick, Ver,

I live under the impression that based on opperations and availability a
select statement generated from remedy always use indexes. It is not
Remedy
who decides to use them or not. If you have index on field A and
statement
with ... where A="Hardware" then sure the index is used. Then Rick has
the
point that not all operations can use index , LIKE "%Hardware%" is
allways
a
scan, etc. I may not agree with Rick's 2), where he says the order of
appearance of the fields in the statement matters. In my oppinion, if
you
have where... AND A="Hardware" AND ..... will use index if there is one
built for field A, regardless where A is in the statement, first or
last.

Just my $.02

Nicky
----- Original Message -----
From: "Rick Cook"
To:
Sent: Thursday, February 06, 2003 10:12 AM
Subject: Re: ARS Performance/Responsiveness


Ver,

Thanks for the expansion of your earlier comments, and please DO engage
us
in discussions - that's what this list is for, and how we all learn new
things. My question was intended to determine whether we had something
to
teach you, or you had something to teach us. Since our collective
knowledge
level is increased in either case, I don't really care which it is.
Anyone
offended by learning something new won't be very comfortable here for
very
long.

I am sure that looking at the SQL logs, it must seem very weird to see
simple SQL statements that don't use indexes. To really understand why
they
are or are not used, you must understand how Remedy determines that.
The
Performance Tuning class is great at that; I think it is probably the
most
valuable of all the Remedy training courses, but in a nutshell here's
how
it
works (at least to the best of my understanding):

A SQL query, executed via Remedy clients or workflow, is dependent upon
several variables to be able to use a defined index.

1) The selectiveness of a field. A search that returns less than 5% of
the
total data in the form is more likely to benefit from the use of an
index.
So if you are indexing a field with less than 20 values (on average),
that
index will not be used as often as you think.
2) The structure of the SQL statement. If the query leads with a
non-indexed field, or one for whom a defined index isn't selective
enough,
the benefits of the indexes on other fields in the SQL call won't be
realized, because a table scan will have already happened. Better to
have
the indexed field(s) first, so that the scan will be on a subset of the
table, not the entire thing.
3) The use of NOT or != operators in the query will almost guarantee a
table scan, even if every field referenced in the query is indexed.
Rather
than 'Status' != "Closed", use 'Status' <= "Pending".
4) The use of Wildcard searches, or LIKE statements will never use an
index.
5) The QBE match settings, and the use of QBE. Check the docs for the
full
explanation on that. In fact, the 5.1 docs are VERY good at explaining
all
of this.

That's just a short list, but covers most of the main issues with
Remedy's
use of indexes. I'd love to see the guts of Remedy's query optimizer to
see
what it's REALLY using for criteria. I think I remember hearing once
that
it will restructure the queries to optimize the potential use of
indexes,
but the docs don't back that up, so I don't know if that's accurate. If
anyone can expand on (or correct) what I stated, please do.

Ver, if you're seeing something that indicates that properly configured
SQL
calls are not using appropriately defined indexes, please let us - and
Remedy Support - know about it.

And DO keep posting, please!

Rick

-----Original Message-----
From: Ver Holipaz [mailto:vholipa@NGCO.COM]
Sent: Thursday, February 06, 2003 6:12 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


I'm sorry if I gave the wrong impression. It was not meant to be a
blanket
statement. It was a "hunch", that's why I said, "Remedy does not SEEM to
optimize by using the table indexes.".

I'm not quite sure how Remedy utilizes indexes. I am just basing this on
an
SQL Log that we turned on to capture SQL statements that Remedy issues
to
Oracle.

It seems, again I say SEEMS, that all the SQL statements logged are
simple
ones and none of them utilized the existing indexes for the table.

If i am totally wrong, then again, I'm sorry. I am just trying to help,
guys. I'm not trying to put down anything or anybody. I love Remedy and
I
believe it is a great tool. I just believe that, like any software tool,
it
has it's own limitations.

I hope this clears the air. Perhaps I shouldn't meddle with any
discussion
in this list again.


On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook
wrote:

>Upon what do you base your blanket statement that Remedy does not use
indexes? It has a built-in query optimizer that works with the DBMS to
determine whether a specified index might or might not be the fastest
way
to retrieve the results. I doubt it's perfect, but I doubt even more
that
it's totally broken.
>
>Can you substantiate your claim, for the benefit of all, please?
>
>Rick Cook
>Senior Remedy Consultant
>Denali Advanced Integration
>
>-----Original Message-----
>From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>Sent: Friday, January 31, 2003 6:33 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Remedy passes simple SQL statements to Oracle and does complete table
>scans. Remedy does not seem to optimize by using the table indexes.
>
>You might want to try the following system parameter setting in Oracle.
>
>"alter session set optimizerindexcostadj=1"
>
>This forces Oracle to use indexes instead of complete table scans
everytime
>Remedy issues an SQL statement.
>
>



>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





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




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



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

Rick,

Select A,B, C from A where B="Open" AND C="Hardware"
and
Select A,B, C from A where C="Hardware" AND B="Open"

where there is an existing index on C

will use this index in both cases because the DBMS query optimizer.

If Remedy manual says it makes defference, then they are wrong, or I did
misunderstand your point.
I'm just curious, where exactly did you read something like that.

Nicky
----- Original Message -----
From: "Rick Cook"
To:
Sent: Thursday, February 06, 2003 4:30 PM
Subject: Re: ARS Performance/Responsiveness


Sorry, Nicky, but you need to check the docs again, because they back up my
statement. The position of the parameters DOES matter. I was looking at
the 5.1 doc as I wrote that big email, to ensure that I kept my errors to a
minimum.

Rick

-----Original Message-----
From: Nicky Madjarov [mailto:nickym@EARTHLINK.NET]
Sent: Thursday, February 06, 2003 1:04 PM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Hi Rick, Ver,

I live under the impression that based on opperations and availability a
select statement generated from remedy always use indexes. It is not Remedy
who decides to use them or not. If you have index on field A and statement
with ... where A="Hardware" then sure the index is used. Then Rick has the
point that not all operations can use index , LIKE "%Hardware%" is allways a
scan, etc. I may not agree with Rick's 2), where he says the order of
appearance of the fields in the statement matters. In my oppinion, if you
have where... AND A="Hardware" AND ..... will use index if there is one
built for field A, regardless where A is in the statement, first or last.

Just my $.02

Nicky
----- Original Message -----
From: "Rick Cook"
To:
Sent: Thursday, February 06, 2003 10:12 AM
Subject: Re: ARS Performance/Responsiveness


Ver,

Thanks for the expansion of your earlier comments, and please DO engage us
in discussions - that's what this list is for, and how we all learn new
things. My question was intended to determine whether we had something to
teach you, or you had something to teach us. Since our collective knowledge
level is increased in either case, I don't really care which it is. Anyone
offended by learning something new won't be very comfortable here for very
long.

I am sure that looking at the SQL logs, it must seem very weird to see
simple SQL statements that don't use indexes. To really understand why they
are or are not used, you must understand how Remedy determines that. The
Performance Tuning class is great at that; I think it is probably the most
valuable of all the Remedy training courses, but in a nutshell here's how it
works (at least to the best of my understanding):

A SQL query, executed via Remedy clients or workflow, is dependent upon
several variables to be able to use a defined index.

1) The selectiveness of a field. A search that returns less than 5% of the
total data in the form is more likely to benefit from the use of an index.
So if you are indexing a field with less than 20 values (on average), that
index will not be used as often as you think.
2) The structure of the SQL statement. If the query leads with a
non-indexed field, or one for whom a defined index isn't selective enough,
the benefits of the indexes on other fields in the SQL call won't be
realized, because a table scan will have already happened. Better to have
the indexed field(s) first, so that the scan will be on a subset of the
table, not the entire thing.
3) The use of NOT or != operators in the query will almost guarantee a
table scan, even if every field referenced in the query is indexed. Rather
than 'Status' != "Closed", use 'Status' <= "Pending".
4) The use of Wildcard searches, or LIKE statements will never use an
index.
5) The QBE match settings, and the use of QBE. Check the docs for the full
explanation on that. In fact, the 5.1 docs are VERY good at explaining all
of this.

That's just a short list, but covers most of the main issues with Remedy's
use of indexes. I'd love to see the guts of Remedy's query optimizer to see
what it's REALLY using for criteria. I think I remember hearing once that
it will restructure the queries to optimize the potential use of indexes,
but the docs don't back that up, so I don't know if that's accurate. If
anyone can expand on (or correct) what I stated, please do.

Ver, if you're seeing something that indicates that properly configured SQL
calls are not using appropriately defined indexes, please let us - and
Remedy Support - know about it.

And DO keep posting, please!

Rick

-----Original Message-----
From: Ver Holipaz [mailto:vholipa@NGCO.COM]
Sent: Thursday, February 06, 2003 6:12 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


I'm sorry if I gave the wrong impression. It was not meant to be a blanket
statement. It was a "hunch", that's why I said, "Remedy does not SEEM to
optimize by using the table indexes.".

I'm not quite sure how Remedy utilizes indexes. I am just basing this on an
SQL Log that we turned on to capture SQL statements that Remedy issues to
Oracle.

It seems, again I say SEEMS, that all the SQL statements logged are simple
ones and none of them utilized the existing indexes for the table.

If i am totally wrong, then again, I'm sorry. I am just trying to help,
guys. I'm not trying to put down anything or anybody. I love Remedy and I
believe it is a great tool. I just believe that, like any software tool, it
has it's own limitations.

I hope this clears the air. Perhaps I shouldn't meddle with any discussion
in this list again.


On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook wrote:

>Upon what do you base your blanket statement that Remedy does not use
indexes? It has a built-in query optimizer that works with the DBMS to
determine whether a specified index might or might not be the fastest way
to retrieve the results. I doubt it's perfect, but I doubt even more that
it's totally broken.
>
>Can you substantiate your claim, for the benefit of all, please?
>
>Rick Cook
>Senior Remedy Consultant
>Denali Advanced Integration
>
>-----Original Message-----
>From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>Sent: Friday, January 31, 2003 6:33 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Remedy passes simple SQL statements to Oracle and does complete table
>scans. Remedy does not seem to optimize by using the table indexes.
>
>You might want to try the following system parameter setting in Oracle.
>
>"alter session set optimizerindexcostadj=1"
>
>This forces Oracle to use indexes instead of complete table scans everytime
>Remedy issues an SQL statement.
>
>

>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



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



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

Nicky Madjarov wrote:
> Rick,
>
> Select A,B, C from A where B="Open" AND C="Hardware"
> and
> Select A,B, C from A where C="Hardware" AND B="Open"
>
> where there is an existing index on C
>
> will use this index in both cases because the DBMS query optimizer.
>
> If Remedy manual says it makes defference, then they are wrong, or I did
> misunderstand your point.
> I'm just curious, where exactly did you read something like that.
>
> Nicky

I can only comment on Oracle, but here the underlying database has both
a cost-based and a rules-based optimizer, and works from these to decide
how to process a query.

So, I think (but don't know for sure) that where Oracle is the
underlying database, it will not matter what order you write the SQL,
since the optimizers will sort out how best to perform the query.

Having said that, writing the code to run optimally is no bad thing, either.


--
Dr Dan Holdsworth
Remedy ARS Administrator, Manchester Computing
daniel.r.holdsworth@man.ac.uk
0161 275 0606

Remedy's guru Doug Mueller has these comments.

There is no Remedy Optimizer.

The AR System generates SQL commands. Those commands are issued
to the database. The database is who actually parses the SQL
command and figures out the most efficient way to perform that
command within their environment. All the databases have a phase
of processing the command that is called the optimizer. Its job
is to look at the command that is being issued, determine if there
are indexes that are appropriate for the type of qualifications
that are issued and the amount of data in tables, and then perform
the appropriate operations to fulfill the command.

There are often different style of optimizers within the various
databases and Oracle happens to have several major styles of
optimization you can select from. We do not specify any
particular style. We let the style that the database instance
is configured with be used to perform our commands.

If you define indexes on forms, we create the corresponding
indexes on the tables within the database. The database layer
is responsible for using those indexes (and it does).

The position of the qualification items is not
the primary consideration although many optimizers may choose the
first one if there is a tie in their "cost algorithm" when
determining which index to use if there are multiple indexes. But,
in general, the database will scan the entire qualification and
pick the best thing to use as an index if there are any.

I hope this is useful,

Doug

-----Original Message-----
From: Dan Holdsworth [mailto:daniel.r.holdsworth@MAN.AC.UK]
Sent: Friday, February 07, 2003 1:40 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Nicky Madjarov wrote:
> Rick,
>
> Select A,B, C from A where B="Open" AND C="Hardware"
> and
> Select A,B, C from A where C="Hardware" AND B="Open"
>
> where there is an existing index on C
>
> will use this index in both cases because the DBMS query optimizer.
>
> If Remedy manual says it makes defference, then they are wrong, or I did
> misunderstand your point.
> I'm just curious, where exactly did you read something like that.
>
> Nicky

I can only comment on Oracle, but here the underlying database has both
a cost-based and a rules-based optimizer, and works from these to decide
how to process a query.

So, I think (but don't know for sure) that where Oracle is the
underlying database, it will not matter what order you write the SQL,
since the optimizers will sort out how best to perform the query.

Having said that, writing the code to run optimally is no bad thing, either.


--
Dr Dan Holdsworth
Remedy ARS Administrator, Manchester Computing
daniel.r.holdsworth@man.ac.uk
0161 275 0606

OK, while I'm not going to call Doug a liar, I'm not pulling this term from thin air - I have seen and heard references to it from Remedy sources. Is he saying that there is nothing by that name, or nothing that actually does optimization? I believe the latter to be true, but not the former.

Rick

-----Original Message-----
From: O'Hara, Jim [mailto:JLO4@PGE.COM]
Sent: Friday, February 07, 2003 9:17 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Remedy's guru Doug Mueller has these comments.

There is no Remedy Optimizer.

The AR System generates SQL commands. Those commands are issued
to the database. The database is who actually parses the SQL
command and figures out the most efficient way to perform that
command within their environment. All the databases have a phase
of processing the command that is called the optimizer. Its job
is to look at the command that is being issued, determine if there
are indexes that are appropriate for the type of qualifications
that are issued and the amount of data in tables, and then perform
the appropriate operations to fulfill the command.

There are often different style of optimizers within the various
databases and Oracle happens to have several major styles of
optimization you can select from. We do not specify any
particular style. We let the style that the database instance
is configured with be used to perform our commands.

If you define indexes on forms, we create the corresponding
indexes on the tables within the database. The database layer
is responsible for using those indexes (and it does).

The position of the qualification items is not
the primary consideration although many optimizers may choose the
first one if there is a tie in their "cost algorithm" when
determining which index to use if there are multiple indexes. But,
in general, the database will scan the entire qualification and
pick the best thing to use as an index if there are any.

I hope this is useful,

Doug

-----Original Message-----
From: Dan Holdsworth [mailto:daniel.r.holdsworth@MAN.AC.UK]
Sent: Friday, February 07, 2003 1:40 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Nicky Madjarov wrote:
> Rick,
>
> Select A,B, C from A where B="Open" AND C="Hardware"
> and
> Select A,B, C from A where C="Hardware" AND B="Open"
>
> where there is an existing index on C
>
> will use this index in both cases because the DBMS query optimizer.
>
> If Remedy manual says it makes defference, then they are wrong, or I did
> misunderstand your point.
> I'm just curious, where exactly did you read something like that.
>
> Nicky

I can only comment on Oracle, but here the underlying database has both
a cost-based and a rules-based optimizer, and works from these to decide
how to process a query.

So, I think (but don't know for sure) that where Oracle is the
underlying database, it will not matter what order you write the SQL,
since the optimizers will sort out how best to perform the query.

Having said that, writing the code to run optimally is no bad thing, either.


--
Dr Dan Holdsworth
Remedy ARS Administrator, Manchester Computing
daniel.r.holdsworth@man.ac.uk
0161 275 0606

Remedy passes simple SQL statements to Oracle and does complete table
scans. Remedy does not seem to optimize by using the table indexes.

You might want to try the following system parameter setting in Oracle.

"alter session set optimizerindexcostadj=1"

This forces Oracle to use indexes instead of complete table scans everytime
Remedy issues an SQL statement.

Hi Ver,

Granted, Remedy does not use bind variables however Remedy's admins' have
the capacity to index fields and if they know what they are doing can give
the oracle optimiser the best opportunity to select the index for searching.
You know the usual drill of index key lengths, selectivity of field values,
frequency of searching by users/code & statement syntax. It is very
important for remedy/oracle sites that deal with large volumes of data that
an appropriate indexing strategy which includes informed statistics
gathering.

In short the SQL generated by Remedy is simple, but ultimately the Oracle
optimiser determines which access method is best given it's deck of cards.

Regards,
Kevin

PS For Oracle persons not familiar with this init parameter here is some
info on it


OPTIMIZERINDEXCOSTADJ lets you tune optimizer behavior for access path
selection to be more or less index friendly-that is, to make the optimizer
more or
less prone to selecting an index access path over a full table scan.
The default for this parameter is 100 percent, at which the optimizer
evaluates index
access paths at the regular cost. Any other value makes the optimizer
evaluate the
access path at that percentage of the regular cost. For example, a setting
of 50 makes
the index access path look half as expensive as normal.
Parameter type Integer
Default value 100
Parameter class Dynamic: ALTER SESSION
Range of values 1 to 10000

-----Original Message-----
From: Ver Holipaz [mailto:vholipa@NGCO.COM]
Sent: 31 January 2003 14:33
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Remedy passes simple SQL statements to Oracle and does complete table
scans. Remedy does not seem to optimize by using the table indexes.

You might want to try the following system parameter setting in Oracle.

"alter session set optimizerindexcostadj=1"

This forces Oracle to use indexes instead of complete table scans everytime
Remedy issues an SQL statement.



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

Upon what do you base your blanket statement that Remedy does not use indexes? It has a built-in query optimizer that works with the DBMS to determine whether a specified index might or might not be the fastest way to retrieve the results. I doubt it's perfect, but I doubt even more that it's totally broken.

Can you substantiate your claim, for the benefit of all, please?

Rick Cook
Senior Remedy Consultant
Denali Advanced Integration

-----Original Message-----
From: Ver Holipaz [mailto:vholipa@NGCO.COM]
Sent: Friday, January 31, 2003 6:33 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Remedy passes simple SQL statements to Oracle and does complete table
scans. Remedy does not seem to optimize by using the table indexes.

You might want to try the following system parameter setting in Oracle.

"alter session set optimizerindexcostadj=1"

This forces Oracle to use indexes instead of complete table scans everytime
Remedy issues an SQL statement.

I'm sorry if I gave the wrong impression. It was not meant to be a blanket
statement. It was a "hunch", that's why I said, "Remedy does not SEEM to
optimize by using the table indexes.".

I'm not quite sure how Remedy utilizes indexes. I am just basing this on an
SQL Log that we turned on to capture SQL statements that Remedy issues to
Oracle.

It seems, again I say SEEMS, that all the SQL statements logged are simple
ones and none of them utilized the existing indexes for the table.

If i am totally wrong, then again, I'm sorry. I am just trying to help,
guys. I'm not trying to put down anything or anybody. I love Remedy and I
believe it is a great tool. I just believe that, like any software tool, it
has it's own limitations.

I hope this clears the air. Perhaps I shouldn't meddle with any discussion
in this list again.


On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook wrote:

>Upon what do you base your blanket statement that Remedy does not use
indexes? It has a built-in query optimizer that works with the DBMS to
determine whether a specified index might or might not be the fastest way
to retrieve the results. I doubt it's perfect, but I doubt even more that
it's totally broken.
>
>Can you substantiate your claim, for the benefit of all, please?
>
>Rick Cook
>Senior Remedy Consultant
>Denali Advanced Integration
>
>-----Original Message-----
>From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>Sent: Friday, January 31, 2003 6:33 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Remedy passes simple SQL statements to Oracle and does complete table
>scans. Remedy does not seem to optimize by using the table indexes.
>
>You might want to try the following system parameter setting in Oracle.
>
>"alter session set optimizerindexcostadj=1"
>
>This forces Oracle to use indexes instead of complete table scans everytime
>Remedy issues an SQL statement.
>
>

>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

I think Rick Cook just wanted to know where you were coming from.
All messages in this list (in my short time on it) do not seem to be
taken personal when making statements about the Remedy products.
For example, there are people here that do not hesitate to mentioned
the limitations of Remedy Migrator.

I would encourage you to continue to make contributions to this list
whenever you think it might benefit the group or a specific person.

Jose Leon



Internet
vholipa@NGCO.COM@ARSLIST.ORG - 02/06/2003 09:11 AM


Please respond to arslist@ARSLIST.ORG

Sent by: ARSLIST@ARSLIST.ORG

To: ARSLIST

cc:


Subject: Re: ARS Performance/Responsiveness


I'm sorry if I gave the wrong impression. It was not meant to be a blanket
statement. It was a "hunch", that's why I said, "Remedy does not SEEM to
optimize by using the table indexes.".

I'm not quite sure how Remedy utilizes indexes. I am just basing this on an
SQL Log that we turned on to capture SQL statements that Remedy issues to
Oracle.

It seems, again I say SEEMS, that all the SQL statements logged are simple
ones and none of them utilized the existing indexes for the table.

If i am totally wrong, then again, I'm sorry. I am just trying to help,
guys. I'm not trying to put down anything or anybody. I love Remedy and I
believe it is a great tool. I just believe that, like any software tool, it
has it's own limitations.

I hope this clears the air. Perhaps I shouldn't meddle with any discussion
in this list again.


On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook wrote:

>Upon what do you base your blanket statement that Remedy does not use
indexes? It has a built-in query optimizer that works with the DBMS to
determine whether a specified index might or might not be the fastest way
to retrieve the results. I doubt it's perfect, but I doubt even more that
it's totally broken.
>
>Can you substantiate your claim, for the benefit of all, please?
>
>Rick Cook
>Senior Remedy Consultant
>Denali Advanced Integration
>
>-----Original Message-----
>From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>Sent: Friday, January 31, 2003 6:33 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Remedy passes simple SQL statements to Oracle and does complete table
>scans. Remedy does not seem to optimize by using the table indexes.
>
>You might want to try the following system parameter setting in Oracle.
>
>"alter session set optimizerindexcostadj=1"
>
>This forces Oracle to use indexes instead of complete table scans
everytime
>Remedy issues an SQL statement.
>
>


>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



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





This message and any attachments (the "message") is
intended solely for the addressees and is confidential.
If you receive this message in error, please delete it and
immediately notify the sender. Any use not in accord with
its purpose, any dissemination or disclosure, either whole
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message.
BNP PARIBAS (and its subsidiaries) shall (will) not
therefore be liable for the message if modified.

---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le
"message") sont etablis a l'intention exclusive de ses
destinataires et sont confidentiels. Si vous recevez ce
message par erreur, merci de le detruire et d'en avertir
immediatement l'expediteur. Toute utilisation de ce
message non conforme a sa destination, toute diffusion
ou toute publication, totale ou partielle, est interdite, sauf
autorisation expresse. L'internet ne permettant pas
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce
message, dans l'hypothese ou il aurait ete modifie.

Hi Rick,

Are you saying that Remedy has a built in query optimiser? This is something
that I have not heard of before. Would you be able to explain more about it?
I guess Remedy does build SQL statements based on the objects that you
develop but does it actually optimise the query looking for the best or most
suited way to use the table indexes.

Mike.

Michael Worts
IBM Global Services
Phone: 01483 875273
Mobile: 07801 755346
VPN: (716) 5273
mailto:michael.worts@ntl.com

-----Original Message-----
From: Jose Leon [mailto:jose.leon@AMERICAS.BNPPARIBAS.COM]
Sent: 06 February 2003 14:43
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


I think Rick Cook just wanted to know where you were coming from.
All messages in this list (in my short time on it) do not seem to be
taken personal when making statements about the Remedy products.
For example, there are people here that do not hesitate to mentioned
the limitations of Remedy Migrator.

I would encourage you to continue to make contributions to this list
whenever you think it might benefit the group or a specific person.

Jose Leon



Internet
vholipa@NGCO.COM@ARSLIST.ORG - 02/06/2003 09:11 AM


Please respond to arslist@ARSLIST.ORG

Sent by: ARSLIST@ARSLIST.ORG

To: ARSLIST

cc:


Subject: Re: ARS Performance/Responsiveness


I'm sorry if I gave the wrong impression. It was not meant to be a blanket
statement. It was a "hunch", that's why I said, "Remedy does not SEEM to
optimize by using the table indexes.".

I'm not quite sure how Remedy utilizes indexes. I am just basing this on an
SQL Log that we turned on to capture SQL statements that Remedy issues to
Oracle.

It seems, again I say SEEMS, that all the SQL statements logged are simple
ones and none of them utilized the existing indexes for the table.

If i am totally wrong, then again, I'm sorry. I am just trying to help,
guys. I'm not trying to put down anything or anybody. I love Remedy and I
believe it is a great tool. I just believe that, like any software tool, it
has it's own limitations.

I hope this clears the air. Perhaps I shouldn't meddle with any discussion
in this list again.


On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook wrote:

>Upon what do you base your blanket statement that Remedy does not use
indexes? It has a built-in query optimizer that works with the DBMS to
determine whether a specified index might or might not be the fastest way
to retrieve the results. I doubt it's perfect, but I doubt even more that
it's totally broken.
>
>Can you substantiate your claim, for the benefit of all, please?
>
>Rick Cook
>Senior Remedy Consultant
>Denali Advanced Integration
>
>-----Original Message-----
>From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>Sent: Friday, January 31, 2003 6:33 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Remedy passes simple SQL statements to Oracle and does complete table
>scans. Remedy does not seem to optimize by using the table indexes.
>
>You might want to try the following system parameter setting in Oracle.
>
>"alter session set optimizerindexcostadj=1"
>
>This forces Oracle to use indexes instead of complete table scans
everytime
>Remedy issues an SQL statement.
>
>


>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




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





This message and any attachments (the "message") is
intended solely for the addressees and is confidential.
If you receive this message in error, please delete it and
immediately notify the sender. Any use not in accord with
its purpose, any dissemination or disclosure, either whole
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message.
BNP PARIBAS (and its subsidiaries) shall (will) not
therefore be liable for the message if modified.

---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le
"message") sont etablis a l'intention exclusive de ses
destinataires et sont confidentiels. Si vous recevez ce
message par erreur, merci de le detruire et d'en avertir
immediatement l'expediteur. Toute utilisation de ce
message non conforme a sa destination, toute diffusion
ou toute publication, totale ou partielle, est interdite, sauf
autorisation expresse. L'internet ne permettant pas
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce
message, dans l'hypothese ou il aurait ete modifie.



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


The contents of this email and any attachments are sent for the personal attention
of the addressee(s) only and may be confidential. If you are not the intended
addressee, any use, disclosure or copying of this email and any attachments is
unauthorised - please notify the sender by return and delete the message. Any
representations or commitments expressed in this email are subject to contract.

ntl Group Limited

Ver,

Thanks for the expansion of your earlier comments, and please DO engage us in discussions - that's what this list is for, and how we all learn new things. My question was intended to determine whether we had something to teach you, or you had something to teach us. Since our collective knowledge level is increased in either case, I don't really care which it is. Anyone offended by learning something new won't be very comfortable here for very long.

I am sure that looking at the SQL logs, it must seem very weird to see simple SQL statements that don't use indexes. To really understand why they are or are not used, you must understand how Remedy determines that. The Performance Tuning class is great at that; I think it is probably the most valuable of all the Remedy training courses, but in a nutshell here's how it works (at least to the best of my understanding):

A SQL query, executed via Remedy clients or workflow, is dependent upon several variables to be able to use a defined index.

1) The selectiveness of a field. A search that returns less than 5% of the total data in the form is more likely to benefit from the use of an index. So if you are indexing a field with less than 20 values (on average), that index will not be used as often as you think.
2) The structure of the SQL statement. If the query leads with a non-indexed field, or one for whom a defined index isn't selective enough, the benefits of the indexes on other fields in the SQL call won't be realized, because a table scan will have already happened. Better to have the indexed field(s) first, so that the scan will be on a subset of the table, not the entire thing.
3) The use of NOT or != operators in the query will almost guarantee a table scan, even if every field referenced in the query is indexed. Rather than 'Status' != "Closed", use 'Status' <= "Pending".
4) The use of Wildcard searches, or LIKE statements will never use an index.
5) The QBE match settings, and the use of QBE. Check the docs for the full explanation on that. In fact, the 5.1 docs are VERY good at explaining all of this.

That's just a short list, but covers most of the main issues with Remedy's use of indexes. I'd love to see the guts of Remedy's query optimizer to see what it's REALLY using for criteria. I think I remember hearing once that it will restructure the queries to optimize the potential use of indexes, but the docs don't back that up, so I don't know if that's accurate. If anyone can expand on (or correct) what I stated, please do.

Ver, if you're seeing something that indicates that properly configured SQL calls are not using appropriately defined indexes, please let us - and Remedy Support - know about it.

And DO keep posting, please!

Rick

-----Original Message-----
From: Ver Holipaz [mailto:vholipa@NGCO.COM]
Sent: Thursday, February 06, 2003 6:12 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


I'm sorry if I gave the wrong impression. It was not meant to be a blanket
statement. It was a "hunch", that's why I said, "Remedy does not SEEM to
optimize by using the table indexes.".

I'm not quite sure how Remedy utilizes indexes. I am just basing this on an
SQL Log that we turned on to capture SQL statements that Remedy issues to
Oracle.

It seems, again I say SEEMS, that all the SQL statements logged are simple
ones and none of them utilized the existing indexes for the table.

If i am totally wrong, then again, I'm sorry. I am just trying to help,
guys. I'm not trying to put down anything or anybody. I love Remedy and I
believe it is a great tool. I just believe that, like any software tool, it
has it's own limitations.

I hope this clears the air. Perhaps I shouldn't meddle with any discussion
in this list again.


On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook wrote:

>Upon what do you base your blanket statement that Remedy does not use
indexes? It has a built-in query optimizer that works with the DBMS to
determine whether a specified index might or might not be the fastest way
to retrieve the results. I doubt it's perfect, but I doubt even more that
it's totally broken.
>
>Can you substantiate your claim, for the benefit of all, please?
>
>Rick Cook
>Senior Remedy Consultant
>Denali Advanced Integration
>
>-----Original Message-----
>From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>Sent: Friday, January 31, 2003 6:33 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Remedy passes simple SQL statements to Oracle and does complete table
>scans. Remedy does not seem to optimize by using the table indexes.
>
>You might want to try the following system parameter setting in Oracle.
>
>"alter session set optimizerindexcostadj=1"
>
>This forces Oracle to use indexes instead of complete table scans everytime
>Remedy issues an SQL statement.
>
>

>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

As a post you by now have seen elucidates, I know that there IS a Remedy query optimizer, but I don't know exactly what it DOES. I think that it just determines whether or not to use the indexes in favor of a table scan, but I don't know how well it does that. The docs don't indicate that it actually restructures the SQL to make it the most efficient, but that would be pretty nice, wouldn't it? Then we could hold an obfuscated SQL code contest (like the C contest) to see how screwed up we could make a SQL call look and still have it work right. THAT would be fun.

Rick

-----Original Message-----
From: Michael Worts [mailto:Michael.Worts@NTL.COM]
Sent: Thursday, February 06, 2003 7:15 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Hi Rick,

Are you saying that Remedy has a built in query optimiser? This is something
that I have not heard of before. Would you be able to explain more about it?
I guess Remedy does build SQL statements based on the objects that you
develop but does it actually optimise the query looking for the best or most
suited way to use the table indexes.

Mike.

Michael Worts
IBM Global Services
Phone: 01483 875273
Mobile: 07801 755346
VPN: (716) 5273
mailto:michael.worts@ntl.com

-----Original Message-----
From: Jose Leon [mailto:jose.leon@AMERICAS.BNPPARIBAS.COM]
Sent: 06 February 2003 14:43
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


I think Rick Cook just wanted to know where you were coming from.
All messages in this list (in my short time on it) do not seem to be
taken personal when making statements about the Remedy products.
For example, there are people here that do not hesitate to mentioned
the limitations of Remedy Migrator.

I would encourage you to continue to make contributions to this list
whenever you think it might benefit the group or a specific person.

Jose Leon



Internet
vholipa@NGCO.COM@ARSLIST.ORG - 02/06/2003 09:11 AM


Please respond to arslist@ARSLIST.ORG

Sent by: ARSLIST@ARSLIST.ORG

To: ARSLIST

cc:


Subject: Re: ARS Performance/Responsiveness


I'm sorry if I gave the wrong impression. It was not meant to be a blanket
statement. It was a "hunch", that's why I said, "Remedy does not SEEM to
optimize by using the table indexes.".

I'm not quite sure how Remedy utilizes indexes. I am just basing this on an
SQL Log that we turned on to capture SQL statements that Remedy issues to
Oracle.

It seems, again I say SEEMS, that all the SQL statements logged are simple
ones and none of them utilized the existing indexes for the table.

If i am totally wrong, then again, I'm sorry. I am just trying to help,
guys. I'm not trying to put down anything or anybody. I love Remedy and I
believe it is a great tool. I just believe that, like any software tool, it
has it's own limitations.

I hope this clears the air. Perhaps I shouldn't meddle with any discussion
in this list again.


On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook wrote:

>Upon what do you base your blanket statement that Remedy does not use
indexes? It has a built-in query optimizer that works with the DBMS to
determine whether a specified index might or might not be the fastest way
to retrieve the results. I doubt it's perfect, but I doubt even more that
it's totally broken.
>
>Can you substantiate your claim, for the benefit of all, please?
>
>Rick Cook
>Senior Remedy Consultant
>Denali Advanced Integration
>
>-----Original Message-----
>From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>Sent: Friday, January 31, 2003 6:33 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Remedy passes simple SQL statements to Oracle and does complete table
>scans. Remedy does not seem to optimize by using the table indexes.
>
>You might want to try the following system parameter setting in Oracle.
>
>"alter session set optimizerindexcostadj=1"
>
>This forces Oracle to use indexes instead of complete table scans
everytime
>Remedy issues an SQL statement.
>
>


>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




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





This message and any attachments (the "message") is
intended solely for the addressees and is confidential.
If you receive this message in error, please delete it and
immediately notify the sender. Any use not in accord with
its purpose, any dissemination or disclosure, either whole
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message.
BNP PARIBAS (and its subsidiaries) shall (will) not
therefore be liable for the message if modified.

---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le
"message") sont etablis a l'intention exclusive de ses
destinataires et sont confidentiels. Si vous recevez ce
message par erreur, merci de le detruire et d'en avertir
immediatement l'expediteur. Toute utilisation de ce
message non conforme a sa destination, toute diffusion
ou toute publication, totale ou partielle, est interdite, sauf
autorisation expresse. L'internet ne permettant pas
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce
message, dans l'hypothese ou il aurait ete modifie.



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


The contents of this email and any attachments are sent for the personal attention
of the addressee(s) only and may be confidential. If you are not the intended
addressee, any use, disclosure or copying of this email and any attachments is
unauthorised - please notify the sender by return and delete the message. Any
representations or commitments expressed in this email are subject to contract.

ntl Group Limited

Hello Rick

From what I remember, and that is what I got and remember from the PTT
class, pardon me if I am wrong, I understand Remedy only passes the
SQL statements to the RDBMS right from workflow, so this last one, the
RDMBS determines with its query optimizer if it uses the index or not.
So that is why we need to build better escalations, set fields, search
menues, etc, everything else thinking in terms of performance.

Regards,Mauricio

----- Original Message -----
From: Rick Cook
Date: Thursday, February 6, 2003 9:33 am
Subject: Re: ARS Performance/Responsiveness

> As a post you by now have seen elucidates, I know that there IS a
> Remedy query optimizer, but I don't know exactly what it DOES. I
> think that it just determines whether or not to use the indexes in
> favor of a table scan, but I don't know how well it does that.
> The docs don't indicate that it actually restructures the SQL to
> make it the most efficient, but that would be pretty nice,
> wouldn't it? Then we could hold an obfuscated SQL code contest
> (like the C contest) to see how screwed up we could make a SQL
> call look and still have it work right. THAT would be fun.
>
> Rick
>
> -----Original Message-----
> From: Michael Worts [mailto:Michael.Worts@NTL.COM]
> Sent: Thursday, February 06, 2003 7:15 AM
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: ARS Performance/Responsiveness
>
>
> Hi Rick,
>
> Are you saying that Remedy has a built in query optimiser? This is
> somethingthat I have not heard of before. Would you be able to
> explain more about it?
> I guess Remedy does build SQL statements based on the objects that
you
> develop but does it actually optimise the query looking for the
> best or most
> suited way to use the table indexes.
>
> Mike.
>
> Michael Worts
> IBM Global Services
> Phone: 01483 875273
> Mobile: 07801 755346
> VPN: (716) 5273
> mailto:michael.worts@ntl.com
>
> -----Original Message-----
> From: Jose Leon [mailto:jose.leon@AMERICAS.BNPPARIBAS.COM]
> Sent: 06 February 2003 14:43
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: ARS Performance/Responsiveness
>
>
> I think Rick Cook just wanted to know where you were coming from.
> All messages in this list (in my short time on it) do not seem to be
> taken personal when making statements about the Remedy products.
> For example, there are people here that do not hesitate to mentioned
> the limitations of Remedy Migrator.
>
> I would encourage you to continue to make contributions to this list
> whenever you think it might benefit the group or a specific person.
>
> Jose Leon
>
>
>
> Internet
> vholipa@NGCO.COM@ARSLIST.ORG - 02/06/2003 09:11 AM
>
>
> Please respond to arslist@ARSLIST.ORG
>
> Sent by: ARSLIST@ARSLIST.ORG
>
> To: ARSLIST
>
> cc:
>
>
> Subject: Re: ARS Performance/Responsiveness
>
>
> I'm sorry if I gave the wrong impression. It was not meant to be a
> blanketstatement. It was a "hunch", that's why I said, "Remedy
> does not SEEM to
> optimize by using the table indexes.".
>
> I'm not quite sure how Remedy utilizes indexes. I am just basing
> this on an
> SQL Log that we turned on to capture SQL statements that Remedy
> issues to
> Oracle.
>
> It seems, again I say SEEMS, that all the SQL statements logged
> are simple
> ones and none of them utilized the existing indexes for the table.
>
> If i am totally wrong, then again, I'm sorry. I am just trying to
> help,guys. I'm not trying to put down anything or anybody. I love
> Remedy and I
> believe it is a great tool. I just believe that, like any software
> tool, it
> has it's own limitations.
>
> I hope this clears the air. Perhaps I shouldn't meddle with any
> discussionin this list again.
>
>
> On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook
> wrote:
> >Upon what do you base your blanket statement that Remedy does not
use
> indexes? It has a built-in query optimizer that works with the
> DBMS to
> determine whether a specified index might or might not be the
> fastest way
> to retrieve the results. I doubt it's perfect, but I doubt even
> more that
> it's totally broken.
> >
> >Can you substantiate your claim, for the benefit of all, please?
> >
> >Rick Cook
> >Senior Remedy Consultant
> >Denali Advanced Integration
> >
> >-----Original Message-----
> >From: Ver Holipaz [mailto:vholipa@NGCO.COM]
> >Sent: Friday, January 31, 2003 6:33 AM
> >To: ARSLIST@ARSLIST.ORG
> >Subject: Re: ARS Performance/Responsiveness
> >
> >
> >Remedy passes simple SQL statements to Oracle and does complete
table
> >scans. Remedy does not seem to optimize by using the table indexes.
> >
> >You might want to try the following system parameter setting in
> Oracle.>
> >"alter session set optimizerindexcostadj=1"
> >
> >This forces Oracle to use indexes instead of complete table scans
> everytime
> >Remedy issues an SQL statement.
> >
> >
>


>
> >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
>
>


>
>
> 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
>
>
>
>
>
> This message and any attachments (the "message") is
> intended solely for the addressees and is confidential.
> If you receive this message in error, please delete it and
> immediately notify the sender. Any use not in accord with
> its purpose, any dissemination or disclosure, either whole
> or partial, is prohibited except formal approval. The internet
> can not guarantee the integrity of this message.
> BNP PARIBAS (and its subsidiaries) shall (will) not
> therefore be liable for the message if modified.
>
> ---------------------------------------------
>
> Ce message et toutes les pieces jointes (ci-apres le
> "message") sont etablis a l'intention exclusive de ses
> destinataires et sont confidentiels. Si vous recevez ce
> message par erreur, merci de le detruire et d'en avertir
> immediatement l'expediteur. Toute utilisation de ce
> message non conforme a sa destination, toute diffusion
> ou toute publication, totale ou partielle, est interdite, sauf
> autorisation expresse. L'internet ne permettant pas
> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
> filiales) decline(nt) toute responsabilite au titre de ce
> message, dans l'hypothese ou il aurait ete modifie.
>
>


>
> 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
>
>
> The contents of this email and any attachments are sent for the
> personal attention
> of the addressee(s) only and may be confidential. If you are not
> the intended
> addressee, any use, disclosure or copying of this email and any
> attachments is
> unauthorised - please notify the sender by return and delete the
> message. Any
> representations or commitments expressed in this email are subject
> to contract.
>
> ntl Group Limited
>
>


> 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
>

Mauricio,

This is my understanding of the function of the Remedy query optimizer. It basically is a switch that determines that the query either uses the index or doesn't. I could be wrong, but I don't think it's any more - or less - complex than that.

I just think it would be a great thing if it did what the name implies, which is to restructure queries to make them operate in the most efficient manner possible. I think that some DBMS systems (Oracle being the most likely) have their own that could be used as a model for this. In fact, since most modern DBMS have FTS embedded, it makes sense for the DBMS to be the home for the query optimizer as well. Being able to do this in a way that actually uses less time than performing the search is the trick. I'll bring this up to Remedy Engineering next chance I get (probably RUG).

Rick

-----Original Message-----
From: Mauricio Meraz [mailto:maurma@BITAL.COM.MX]
Sent: Thursday, February 06, 2003 8:03 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Hello Rick

From what I remember, and that is what I got and remember from the PTT
class, pardon me if I am wrong, I understand Remedy only passes the
SQL statements to the RDBMS right from workflow, so this last one, the
RDMBS determines with its query optimizer if it uses the index or not.
So that is why we need to build better escalations, set fields, search
menues, etc, everything else thinking in terms of performance.

Regards,Mauricio

----- Original Message -----
From: Rick Cook
Date: Thursday, February 6, 2003 9:33 am
Subject: Re: ARS Performance/Responsiveness

> As a post you by now have seen elucidates, I know that there IS a
> Remedy query optimizer, but I don't know exactly what it DOES. I
> think that it just determines whether or not to use the indexes in
> favor of a table scan, but I don't know how well it does that.
> The docs don't indicate that it actually restructures the SQL to
> make it the most efficient, but that would be pretty nice,
> wouldn't it? Then we could hold an obfuscated SQL code contest
> (like the C contest) to see how screwed up we could make a SQL
> call look and still have it work right. THAT would be fun.
>
> Rick
>
> -----Original Message-----
> From: Michael Worts [mailto:Michael.Worts@NTL.COM]
> Sent: Thursday, February 06, 2003 7:15 AM
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: ARS Performance/Responsiveness
>
>
> Hi Rick,
>
> Are you saying that Remedy has a built in query optimiser? This is
> somethingthat I have not heard of before. Would you be able to
> explain more about it?
> I guess Remedy does build SQL statements based on the objects that
you
> develop but does it actually optimise the query looking for the
> best or most
> suited way to use the table indexes.
>
> Mike.
>
> Michael Worts
> IBM Global Services
> Phone: 01483 875273
> Mobile: 07801 755346
> VPN: (716) 5273
> mailto:michael.worts@ntl.com
>
> -----Original Message-----
> From: Jose Leon [mailto:jose.leon@AMERICAS.BNPPARIBAS.COM]
> Sent: 06 February 2003 14:43
> To: ARSLIST@ARSLIST.ORG
> Subject: Re: ARS Performance/Responsiveness
>
>
> I think Rick Cook just wanted to know where you were coming from.
> All messages in this list (in my short time on it) do not seem to be
> taken personal when making statements about the Remedy products.
> For example, there are people here that do not hesitate to mentioned
> the limitations of Remedy Migrator.
>
> I would encourage you to continue to make contributions to this list
> whenever you think it might benefit the group or a specific person.
>
> Jose Leon
>
>
>
> Internet
> vholipa@NGCO.COM@ARSLIST.ORG - 02/06/2003 09:11 AM
>
>
> Please respond to arslist@ARSLIST.ORG
>
> Sent by: ARSLIST@ARSLIST.ORG
>
> To: ARSLIST
>
> cc:
>
>
> Subject: Re: ARS Performance/Responsiveness
>
>
> I'm sorry if I gave the wrong impression. It was not meant to be a
> blanketstatement. It was a "hunch", that's why I said, "Remedy
> does not SEEM to
> optimize by using the table indexes.".
>
> I'm not quite sure how Remedy utilizes indexes. I am just basing
> this on an
> SQL Log that we turned on to capture SQL statements that Remedy
> issues to
> Oracle.
>
> It seems, again I say SEEMS, that all the SQL statements logged
> are simple
> ones and none of them utilized the existing indexes for the table.
>
> If i am totally wrong, then again, I'm sorry. I am just trying to
> help,guys. I'm not trying to put down anything or anybody. I love
> Remedy and I
> believe it is a great tool. I just believe that, like any software
> tool, it
> has it's own limitations.
>
> I hope this clears the air. Perhaps I shouldn't meddle with any
> discussionin this list again.
>
>
> On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook
> wrote:
> >Upon what do you base your blanket statement that Remedy does not
use
> indexes? It has a built-in query optimizer that works with the
> DBMS to
> determine whether a specified index might or might not be the
> fastest way
> to retrieve the results. I doubt it's perfect, but I doubt even
> more that
> it's totally broken.
> >
> >Can you substantiate your claim, for the benefit of all, please?
> >
> >Rick Cook
> >Senior Remedy Consultant
> >Denali Advanced Integration
> >
> >-----Original Message-----
> >From: Ver Holipaz [mailto:vholipa@NGCO.COM]
> >Sent: Friday, January 31, 2003 6:33 AM
> >To: ARSLIST@ARSLIST.ORG
> >Subject: Re: ARS Performance/Responsiveness
> >
> >
> >Remedy passes simple SQL statements to Oracle and does complete
table
> >scans. Remedy does not seem to optimize by using the table indexes.
> >
> >You might want to try the following system parameter setting in
> Oracle.>
> >"alter session set optimizerindexcostadj=1"
> >
> >This forces Oracle to use indexes instead of complete table scans
> everytime
> >Remedy issues an SQL statement.
> >
> >
>


>
> >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
>
>


>
>
> 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
>
>
>
>
>
> This message and any attachments (the "message") is
> intended solely for the addressees and is confidential.
> If you receive this message in error, please delete it and
> immediately notify the sender. Any use not in accord with
> its purpose, any dissemination or disclosure, either whole
> or partial, is prohibited except formal approval. The internet
> can not guarantee the integrity of this message.
> BNP PARIBAS (and its subsidiaries) shall (will) not
> therefore be liable for the message if modified.
>
> ---------------------------------------------
>
> Ce message et toutes les pieces jointes (ci-apres le
> "message") sont etablis a l'intention exclusive de ses
> destinataires et sont confidentiels. Si vous recevez ce
> message par erreur, merci de le detruire et d'en avertir
> immediatement l'expediteur. Toute utilisation de ce
> message non conforme a sa destination, toute diffusion
> ou toute publication, totale ou partielle, est interdite, sauf
> autorisation expresse. L'internet ne permettant pas
> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
> filiales) decline(nt) toute responsabilite au titre de ce
> message, dans l'hypothese ou il aurait ete modifie.
>
>


>
> 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
>
>
> The contents of this email and any attachments are sent for the
> personal attention
> of the addressee(s) only and may be confidential. If you are not
> the intended
> addressee, any use, disclosure or copying of this email and any
> attachments is
> unauthorised - please notify the sender by return and delete the
> message. Any
> representations or commitments expressed in this email are subject
> to contract.
>
> ntl Group Limited
>
>


> 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
>

On Thu, 6 Feb 2003 10:03:07 -0600, Mauricio Meraz wrote:

>Hello Rick
>
>From what I remember, and that is what I got and remember from the PTT
>class, pardon me if I am wrong, I understand Remedy only passes the
>SQL statements to the RDBMS right from workflow, so this last one, the
>RDMBS determines with its query optimizer if it uses the index or not.
>So that is why we need to build better escalations, set fields, search
>menues, etc, everything else thinking in terms of performance.
I think you are correct in Remedy just passing SQL *statements
supplied via workflow" to the DB - But I think the start of the
thread was more talking about all the other SQL that remedy generates
for itself.

You have to remember that Remedy runs on several different RDBMS's
and prior to V5 on a flat file as well. They *had* to use a lowest
common denominator approach to the SQL to avoid hugely different
support for each vendor. This is why for instance there is no
referential integrity - unless you do it yourself.

Now that they support less vendors - basically Oracle, DB2 & MS SQL
Server it may change but I doubt it. It is much cheaper for Remedy to
use basic ANSI (ish) SQL to keep the code bases as similar as
possible, rather than exploit all the bells & whistles of each
vendors' extensions. Then all they have to worry about is who uses
double or single quotes :-) Well not quite but you get the general
idea.


--
Regards

Dave Saville

Sounds right, Dave. Thanks for the insight.

Rick

-----Original Message-----
From: Dave Saville [mailto:dave.saville@NTLWORLD.COM]
Sent: Thursday, February 06, 2003 10:54 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


On Thu, 6 Feb 2003 10:03:07 -0600, Mauricio Meraz wrote:

>Hello Rick
>
>From what I remember, and that is what I got and remember from the PTT
>class, pardon me if I am wrong, I understand Remedy only passes the
>SQL statements to the RDBMS right from workflow, so this last one, the
>RDMBS determines with its query optimizer if it uses the index or not.
>So that is why we need to build better escalations, set fields, search
>menues, etc, everything else thinking in terms of performance.
I think you are correct in Remedy just passing SQL *statements
supplied via workflow" to the DB - But I think the start of the
thread was more talking about all the other SQL that remedy generates
for itself.

You have to remember that Remedy runs on several different RDBMS's
and prior to V5 on a flat file as well. They *had* to use a lowest
common denominator approach to the SQL to avoid hugely different
support for each vendor. This is why for instance there is no
referential integrity - unless you do it yourself.

Now that they support less vendors - basically Oracle, DB2 & MS SQL
Server it may change but I doubt it. It is much cheaper for Remedy to
use basic ANSI (ish) SQL to keep the code bases as similar as
possible, rather than exploit all the bells & whistles of each
vendors' extensions. Then all they have to worry about is who uses
double or single quotes :-) Well not quite but you get the general
idea.


--
Regards

Dave Saville

Never heard of it. How can Remedy affect how the database decide to do the
query? Even if a table is indexed, the database optimizer can decide not to
use index? ARS only pass straight forward SQL stmt to the database, as you can
see of the logs. Compare the AR sql log with database log....

I think

--
Jarl

>===== Original Message From Rick Cook =====
>Mauricio,
>
>This is my understanding of the function of the Remedy query optimizer. It
basically is a switch that determines that the query either uses the index or
doesn't. I could be wrong, but I don't think it's any more - or less -
complex than that.
>
>I just think it would be a great thing if it did what the name implies, which
is to restructure queries to make them operate in the most efficient manner
possible. I think that some DBMS systems (Oracle being the most likely) have
their own that could be used as a model for this. In fact, since most modern
DBMS have FTS embedded, it makes sense for the DBMS to be the home for the
query optimizer as well. Being able to do this in a way that actually uses
less time than performing the search is the trick. I'll bring this up to
Remedy Engineering next chance I get (probably RUG).
>
>Rick
>
>-----Original Message-----
>From: Mauricio Meraz [mailto:maurma@BITAL.COM.MX]
>Sent: Thursday, February 06, 2003 8:03 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Hello Rick
>
>From what I remember, and that is what I got and remember from the PTT
>class, pardon me if I am wrong, I understand Remedy only passes the
>SQL statements to the RDBMS right from workflow, so this last one, the
>RDMBS determines with its query optimizer if it uses the index or not.
>So that is why we need to build better escalations, set fields, search
>menues, etc, everything else thinking in terms of performance.
>
>Regards,Mauricio
>
>----- Original Message -----
>From: Rick Cook
>Date: Thursday, February 6, 2003 9:33 am
>Subject: Re: ARS Performance/Responsiveness
>
>> As a post you by now have seen elucidates, I know that there IS a
>> Remedy query optimizer, but I don't know exactly what it DOES. I
>> think that it just determines whether or not to use the indexes in
>> favor of a table scan, but I don't know how well it does that.
>> The docs don't indicate that it actually restructures the SQL to
>> make it the most efficient, but that would be pretty nice,
>> wouldn't it? Then we could hold an obfuscated SQL code contest
>> (like the C contest) to see how screwed up we could make a SQL
>> call look and still have it work right. THAT would be fun.
>>
>> Rick
>>
>> -----Original Message-----
>> From: Michael Worts [mailto:Michael.Worts@NTL.COM]
>> Sent: Thursday, February 06, 2003 7:15 AM
>> To: ARSLIST@ARSLIST.ORG
>> Subject: Re: ARS Performance/Responsiveness
>>
>>
>> Hi Rick,
>>
>> Are you saying that Remedy has a built in query optimiser? This is
>> somethingthat I have not heard of before. Would you be able to
>> explain more about it?
>> I guess Remedy does build SQL statements based on the objects that
>you
>> develop but does it actually optimise the query looking for the
>> best or most
>> suited way to use the table indexes.
>>
>> Mike.
>>
>> Michael Worts
>> IBM Global Services
>> Phone: 01483 875273
>> Mobile: 07801 755346
>> VPN: (716) 5273
>> mailto:michael.worts@ntl.com
>>
>> -----Original Message-----
>> From: Jose Leon [mailto:jose.leon@AMERICAS.BNPPARIBAS.COM]
>> Sent: 06 February 2003 14:43
>> To: ARSLIST@ARSLIST.ORG
>> Subject: Re: ARS Performance/Responsiveness
>>
>>
>> I think Rick Cook just wanted to know where you were coming from.
>> All messages in this list (in my short time on it) do not seem to be
>> taken personal when making statements about the Remedy products.
>> For example, there are people here that do not hesitate to mentioned
>> the limitations of Remedy Migrator.
>>
>> I would encourage you to continue to make contributions to this list
>> whenever you think it might benefit the group or a specific person.
>>
>> Jose Leon
>>
>>
>>
>> Internet
>> vholipa@NGCO.COM@ARSLIST.ORG - 02/06/2003 09:11 AM
>>
>>
>> Please respond to arslist@ARSLIST.ORG
>>
>> Sent by: ARSLIST@ARSLIST.ORG
>>
>> To: ARSLIST
>>
>> cc:
>>
>>
>> Subject: Re: ARS Performance/Responsiveness
>>
>>
>> I'm sorry if I gave the wrong impression. It was not meant to be a
>> blanketstatement. It was a "hunch", that's why I said, "Remedy
>> does not SEEM to
>> optimize by using the table indexes.".
>>
>> I'm not quite sure how Remedy utilizes indexes. I am just basing
>> this on an
>> SQL Log that we turned on to capture SQL statements that Remedy
>> issues to
>> Oracle.
>>
>> It seems, again I say SEEMS, that all the SQL statements logged
>> are simple
>> ones and none of them utilized the existing indexes for the table.
>>
>> If i am totally wrong, then again, I'm sorry. I am just trying to
>> help,guys. I'm not trying to put down anything or anybody. I love
>> Remedy and I
>> believe it is a great tool. I just believe that, like any software
>> tool, it
>> has it's own limitations.
>>
>> I hope this clears the air. Perhaps I shouldn't meddle with any
>> discussionin this list again.
>>
>>
>> On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook
>> wrote:
>> >Upon what do you base your blanket statement that Remedy does not
>use
>> indexes? It has a built-in query optimizer that works with the
>> DBMS to
>> determine whether a specified index might or might not be the
>> fastest way
>> to retrieve the results. I doubt it's perfect, but I doubt even
>> more that
>> it's totally broken.
>> >
>> >Can you substantiate your claim, for the benefit of all, please?
>> >
>> >Rick Cook
>> >Senior Remedy Consultant
>> >Denali Advanced Integration
>> >
>> >-----Original Message-----
>> >From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>> >Sent: Friday, January 31, 2003 6:33 AM
>> >To: ARSLIST@ARSLIST.ORG
>> >Subject: Re: ARS Performance/Responsiveness
>> >
>> >
>> >Remedy passes simple SQL statements to Oracle and does complete
>table
>> >scans. Remedy does not seem to optimize by using the table indexes.
>> >
>> >You might want to try the following system parameter setting in
>> Oracle.>
>> >"alter session set optimizerindexcostadj=1"
>> >
>> >This forces Oracle to use indexes instead of complete table scans
>> everytime
>> >Remedy issues an SQL statement.
>> >
>> >
>>
>
>
>>
>> >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
>>
>>
>
>
>>
>>
>> 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
>>
>>
>>
>>
>>
>> This message and any attachments (the "message") is
>> intended solely for the addressees and is confidential.
>> If you receive this message in error, please delete it and
>> immediately notify the sender. Any use not in accord with
>> its purpose, any dissemination or disclosure, either whole
>> or partial, is prohibited except formal approval. The internet
>> can not guarantee the integrity of this message.
>> BNP PARIBAS (and its subsidiaries) shall (will) not
>> therefore be liable for the message if modified.
>>
>> ---------------------------------------------
>>
>> Ce message et toutes les pieces jointes (ci-apres le
>> "message") sont etablis a l'intention exclusive de ses
>> destinataires et sont confidentiels. Si vous recevez ce
>> message par erreur, merci de le detruire et d'en avertir
>> immediatement l'expediteur. Toute utilisation de ce
>> message non conforme a sa destination, toute diffusion
>> ou toute publication, totale ou partielle, est interdite, sauf
>> autorisation expresse. L'internet ne permettant pas
>> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
>> filiales) decline(nt) toute responsabilite au titre de ce
>> message, dans l'hypothese ou il aurait ete modifie.
>>
>>
>
>
>>
>> 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
>>
>>
>> The contents of this email and any attachments are sent for the
>> personal attention
>> of the addressee(s) only and may be confidential. If you are not
>> the intended
>> addressee, any use, disclosure or copying of this email and any
>> attachments is
>> unauthorised - please notify the sender by return and delete the
>> message. Any
>> representations or commitments expressed in this email are subject
>> to contract.
>>
>> ntl Group Limited
>>
>>
>
>
>> 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
>>
>
>

>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

I have heard the Remedy Query Analyzer referred to several times by Remedy people, including Support and in PTT training. Maybe it does only what you say it does, Jarl, but it does exist. Maybe its only purpose is to format the SQL (with quotes, parens, etc.) so that the current DBMS can process it?

Rick

-----Original Message-----
From: Jarl Groneng [mailto:jarl.groneng@TELENOR.COM]
Sent: Thursday, February 06, 2003 11:08 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: ARS Performance/Responsiveness


Never heard of it. How can Remedy affect how the database decide to do the
query? Even if a table is indexed, the database optimizer can decide not to
use index? ARS only pass straight forward SQL stmt to the database, as you can
see of the logs. Compare the AR sql log with database log....

I think

--
Jarl

>===== Original Message From Rick Cook =====
>Mauricio,
>
>This is my understanding of the function of the Remedy query optimizer. It
basically is a switch that determines that the query either uses the index or
doesn't. I could be wrong, but I don't think it's any more - or less -
complex than that.
>
>I just think it would be a great thing if it did what the name implies, which
is to restructure queries to make them operate in the most efficient manner
possible. I think that some DBMS systems (Oracle being the most likely) have
their own that could be used as a model for this. In fact, since most modern
DBMS have FTS embedded, it makes sense for the DBMS to be the home for the
query optimizer as well. Being able to do this in a way that actually uses
less time than performing the search is the trick. I'll bring this up to
Remedy Engineering next chance I get (probably RUG).
>
>Rick
>
>-----Original Message-----
>From: Mauricio Meraz [mailto:maurma@BITAL.COM.MX]
>Sent: Thursday, February 06, 2003 8:03 AM
>To: ARSLIST@ARSLIST.ORG
>Subject: Re: ARS Performance/Responsiveness
>
>
>Hello Rick
>
>From what I remember, and that is what I got and remember from the PTT
>class, pardon me if I am wrong, I understand Remedy only passes the
>SQL statements to the RDBMS right from workflow, so this last one, the
>RDMBS determines with its query optimizer if it uses the index or not.
>So that is why we need to build better escalations, set fields, search
>menues, etc, everything else thinking in terms of performance.
>
>Regards,Mauricio
>
>----- Original Message -----
>From: Rick Cook
>Date: Thursday, February 6, 2003 9:33 am
>Subject: Re: ARS Performance/Responsiveness
>
>> As a post you by now have seen elucidates, I know that there IS a
>> Remedy query optimizer, but I don't know exactly what it DOES. I
>> think that it just determines whether or not to use the indexes in
>> favor of a table scan, but I don't know how well it does that.
>> The docs don't indicate that it actually restructures the SQL to
>> make it the most efficient, but that would be pretty nice,
>> wouldn't it? Then we could hold an obfuscated SQL code contest
>> (like the C contest) to see how screwed up we could make a SQL
>> call look and still have it work right. THAT would be fun.
>>
>> Rick
>>
>> -----Original Message-----
>> From: Michael Worts [mailto:Michael.Worts@NTL.COM]
>> Sent: Thursday, February 06, 2003 7:15 AM
>> To: ARSLIST@ARSLIST.ORG
>> Subject: Re: ARS Performance/Responsiveness
>>
>>
>> Hi Rick,
>>
>> Are you saying that Remedy has a built in query optimiser? This is
>> somethingthat I have not heard of before. Would you be able to
>> explain more about it?
>> I guess Remedy does build SQL statements based on the objects that
>you
>> develop but does it actually optimise the query looking for the
>> best or most
>> suited way to use the table indexes.
>>
>> Mike.
>>
>> Michael Worts
>> IBM Global Services
>> Phone: 01483 875273
>> Mobile: 07801 755346
>> VPN: (716) 5273
>> mailto:michael.worts@ntl.com
>>
>> -----Original Message-----
>> From: Jose Leon [mailto:jose.leon@AMERICAS.BNPPARIBAS.COM]
>> Sent: 06 February 2003 14:43
>> To: ARSLIST@ARSLIST.ORG
>> Subject: Re: ARS Performance/Responsiveness
>>
>>
>> I think Rick Cook just wanted to know where you were coming from.
>> All messages in this list (in my short time on it) do not seem to be
>> taken personal when making statements about the Remedy products.
>> For example, there are people here that do not hesitate to mentioned
>> the limitations of Remedy Migrator.
>>
>> I would encourage you to continue to make contributions to this list
>> whenever you think it might benefit the group or a specific person.
>>
>> Jose Leon
>>
>>
>>
>> Internet
>> vholipa@NGCO.COM@ARSLIST.ORG - 02/06/2003 09:11 AM
>>
>>
>> Please respond to arslist@ARSLIST.ORG
>>
>> Sent by: ARSLIST@ARSLIST.ORG
>>
>> To: ARSLIST
>>
>> cc:
>>
>>
>> Subject: Re: ARS Performance/Responsiveness
>>
>>
>> I'm sorry if I gave the wrong impression. It was not meant to be a
>> blanketstatement. It was a "hunch", that's why I said, "Remedy
>> does not SEEM to
>> optimize by using the table indexes.".
>>
>> I'm not quite sure how Remedy utilizes indexes. I am just basing
>> this on an
>> SQL Log that we turned on to capture SQL statements that Remedy
>> issues to
>> Oracle.
>>
>> It seems, again I say SEEMS, that all the SQL statements logged
>> are simple
>> ones and none of them utilized the existing indexes for the table.
>>
>> If i am totally wrong, then again, I'm sorry. I am just trying to
>> help,guys. I'm not trying to put down anything or anybody. I love
>> Remedy and I
>> believe it is a great tool. I just believe that, like any software
>> tool, it
>> has it's own limitations.
>>
>> I hope this clears the air. Perhaps I shouldn't meddle with any
>> discussionin this list again.
>>
>>
>> On Fri, 31 Jan 2003 07:46:53 -0800, Rick Cook
>> wrote:
>> >Upon what do you base your blanket statement that Remedy does not
>use
>> indexes? It has a built-in query optimizer that works with the
>> DBMS to
>> determine whether a specified index might or might not be the
>> fastest way
>> to retrieve the results. I doubt it's perfect, but I doubt even
>> more that
>> it's totally broken.
>> >
>> >Can you substantiate your claim, for the benefit of all, please?
>> >
>> >Rick Cook
>> >Senior Remedy Consultant
>> >Denali Advanced Integration
>> >
>> >-----Original Message-----
>> >From: Ver Holipaz [mailto:vholipa@NGCO.COM]
>> >Sent: Friday, January 31, 2003 6:33 AM
>> >To: ARSLIST@ARSLIST.ORG
>> >Subject: Re: ARS Performance/Responsiveness
>> >
>> >
>> >Remedy passes simple SQL statements to Oracle and does complete
>table
>> >scans. Remedy does not seem to optimize by using the table indexes.
>> >
>> >You might want to try the following system parameter setting in
>> Oracle.>
>> >"alter session set optimizerindexcostadj=1"
>> >
>> >This forces Oracle to use indexes instead of complete table scans
>> everytime
>> >Remedy issues an SQL statement.
>> >
>> >
>>
>
>
>>
>> >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
>>
>>
>
>
>>
>>
>> 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
>>
>>
>>
>>
>>
>> This message and any attachments (the "message") is
>> intended solely for the addressees and is confidential.
>> If you receive this message in error, please delete it and
>> immediately notify the sender. Any use not in accord with
>> its purpose, any dissemination or disclosure, either whole
>> or partial, is prohibited except formal approval. The internet
>> can not guarantee the integrity of this message.
>> BNP PARIBAS (and its subsidiaries) shall (will) not
>> therefore be liable for the message if modified.
>>
>> ---------------------------------------------
>>
>> Ce message et toutes les pieces jointes (ci-apres le
>> "message") sont etablis a l'intention exclusive de ses
>> destinataires et sont confidentiels. Si vous recevez ce
>> message par erreur, merci de le detruire et d'en avertir
>> immediatement l'expediteur. Toute utilisation de ce
>> message non conforme a sa destination, toute diffusion
>> ou toute publication, totale ou partielle, est interdite, sauf
>> autorisation expresse. L'internet ne permettant pas
>> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
>> filiales) decline(nt) toute responsabilite au titre de ce
>> message, dans l'hypothese ou il aurait ete modifie.
>>
>>
>
>
>>
>> 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
>>
>>
>> The contents of this email and any attachments are sent for the
>> personal attention
>> of the addressee(s) only and may be confidential. If you are not
>> the intended
>> addressee, any use, disclosure or copying of this email and any
>> attachments is
>> unauthorised - please notify the sender by return and delete the
>> message. Any
>> representations or commitments expressed in this email are subject
>> to contract.
>>
>> ntl Group Limited
>>
>>
>
>
>> 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
>>
>
>

>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