One last question. If a form had several indexes on it.
Index1: field1, field2, field3 Index2: field1, field2 Index3: field1 And the query was 'field1' = "1" which index would it use? Would it use Index1 and ignore the other indexes or would it skip to Index3? Christopher Pruitt Business Consulting III HP Enterprises Services [email protected] www.hp.com Confidentiality Notice: This message and any files transmitted with it are intended for the sole use of the entity or individual to whom it is addressed, and may contain information that is confidential, privileged, and exempt from disclosure under applicable law. If you are not the intended addressee for this e-mail, you are hereby notified that any copying, distribution, or dissemination of this e-mail is strictly prohibited. If you have received this e-mail in error, please immediately destroy, erase, or discard this message. Please notify the sender immediately by return e-mail if you have received this e-mail by mistake. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Pruitt, Christopher (Bank of America Account) Sent: Wednesday, April 13, 2011 1:12 PM To: [email protected] Subject: Re: Question about Indexes and Order of Qualification Statements in Filters or Active Links I additional questions... If you have a single index using field1, field2, field3, would there be a performance difference in the following qualifications? 1) 'field1' = "1" AND 'field2' = "2" AND 'field3' = "3" 2) 'field3' = "3" AND 'field2' = "2" AND 'field1' = "1" 3) 'field1' = "1" AND 'field2' = "2" AND 'field3' = "3" AND 'field4' = "4" 4) 'field4' = "4" AND 'field1' = "1" AND 'field2' = "2" AND 'field3' = "3" Would there be a performance difference between the following indexes for the query 'field1' = "1" Index: field1 OR Index: field1, field2, field3 Christopher Pruitt Business Consulting III HP Enterprises Services [email protected] www.hp.com Confidentiality Notice: This message and any files transmitted with it are intended for the sole use of the entity or individual to whom it is addressed, and may contain information that is confidential, privileged, and exempt from disclosure under applicable law. If you are not the intended addressee for this e-mail, you are hereby notified that any copying, distribution, or dissemination of this e-mail is strictly prohibited. If you have received this e-mail in error, please immediately destroy, erase, or discard this message. Please notify the sender immediately by return e-mail if you have received this e-mail by mistake. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of LJ LongWing Sent: Wednesday, April 13, 2011 12:31 PM To: [email protected] Subject: Re: Question about Indexes and Order of Qualification Statements in Filters or Active Links Christopher, 1 - Run-If quals don't hit the DB (unless it contains a DB., but even then it doesn't use your qual), so no the run-if order does not matter 2 - The Set/Push if quals do however matter. Based on my research and what I've been told by others significantly more qualified to answer this question than myself, the order of the variables matters, and that in general, a DB will only use a single index for a single query. So if you have two fields, status and create date, with an index on both and a search on both, the DB will figure out which one will give it the better results, and use that one, but not both. If you want it to use both, it would need to be a composite index so if your query was 'Status' = "Resolved" AND 'Create Date' > $DATE$, your composite index would need to be status, then create date, in that order...putting it in the other order would not have it use the index at all. And it is my understanding that the indexed fields should be the first fields in the query statement to better enable the DB to utilize the indexes. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Pruitt, Christopher (Bank of America Account) Sent: Wednesday, April 13, 2011 10:52 AM To: [email protected] Subject: Question about Indexes and Order of Qualification Statements in Filters or Active Links Hello Listers, We are looking at ways to make workflow (Filters/Active links) queries to the database quicker. We have been working on optimizing our indexes but a question came up that really stumped me. The question was "Does the order in a qualification matter?" I am hoping someone wiser than me on this list could provide some insight. Say you have three fields, as a single index, and you are referencing those three fields in a Run If, Set Fields If, or Push Fields If qualification. For this question let say you have 3 indexed fields in the qualification along with 2 non-indexed fields. Should those indexed fields always start the qualification statements first or does it matter where they are placed in the qualification? The question gets down to: If 2 non-indexed fields start the qualification and the 3 indexed fields follow, will this cause a slower response from the database, as opposed to using the 3 indexed fields first and then the 2 non-indexed field last? Christopher Pruitt Business Consulting III HP Enterprises Services [email protected] www.hp.com<http://www.hp.com/> Confidentiality Notice: This message and any files transmitted with it are intended for the sole use of the entity or individual to whom it is addressed, and may contain information that is confidential, privileged, and exempt from disclosure under applicable law. If you are not the intended addressee for this e-mail, you are hereby notified that any copying, distribution, or dissemination of this e-mail is strictly prohibited. If you have received this e-mail in error, please immediately destroy, erase, or discard this message. Please notify the sender immediately by return e-mail if you have received this e-mail by mistake. ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
