Re: Max Index on Form!
Tadeu, You probably will not find a white paper, however there is knowledge base @ BMC. KM-00011012 Regards, Robert - Start Original Message - Sent: Wed, 14 May 2008 12:59:28 -0500 From: "Pierson, Shawn" To: arslist@ARSLIST.ORG Subject: Re: Max Index on Form! > > ** Tadeu, I dont think its a matter of what the system allows, but what is best practices. There is no single right answer. You have to consider how many records are in the table, what the most common searches will be, etc. Its something that you need to work with a DBA on. They can run SQL Profiler and other tools to be able to determine what is causing the most slowdowns in the queries. Then, when you look at that and see the need for a new index, you can create a new one. Shawn Pierson From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Tadeu Augusto Dutra Pinto Sent: Wednesday, May 14, 2008 12:36 PM To: arslist@ARSLIST.ORG Subject: Max Index on Form! ** Hi all, I'd like to know if has a white paper that describes what's max number of index to a form... I've read a document from 2003 and I've noticed that more than 7 index (or 4 composite index) in one form is bad... I have an application that has a form with 8 index (some of them composite index) ... and we're having problems with performance (searches in database).. I'd like to know if it's possible to insert more index in this form! Remembering: I'd like to improve performance... Att, Tadeu Augusto Dutra Pinto __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ Private and confidential as detailed here. If you cannot access hyperlink, please e-mail sender. __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ - End Original Message - ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
Re: Max Index on Form! - Rick's response is recommended reading
Great explanation Rick. Short, not too involved but yet covers the topic nicely. Something everyone needs to review periodically. Thanks, Susan On Wed, May 14, 2008 at 12:53 PM, Rick Cook <[EMAIL PROTECTED]> wrote: > ** The two things to look in determining the appropriate maximum number of > indexes for a form are the usage of the form and the number of rows > involved. If you have only a few hundred or thousand rows in a table, > indexes probably won't help all that much, and performance might be > negatively impacted if you went way over the top in your indexing. (Aside: > Remedy put like 16 on HPD:HelpDesk in ITSM 7. Draw whatever conclusions you > like from that). > > On tables with ~100k+ rows, the basic rule is that indexes speed up > queries, but slow down submits, because on each submit, every index must be > updated. So on forms where lots of records are submitted, you might want to > keep the number of indexes lower than on a form against which reports are > run. 7 indexes is not excessive, but is starting to get to the point where > adding more would probably be noticed on submits. There isn't a hard number > at which your performance will fall off a cliff, you will just notice > gradual degradation of performance as the number increases beyond somewhere > around 7. It's up to you to find the best balance of performance and > functionality for your installation. > > One other thing to look at, if you haven't already, is how the indexes you > have in place are being used, and whether changing or removing one or more > of those makes sense. That takes a lot of analysis of SQL logs, and working > with your DBA to determine which searches are taking the most time, etc. > > Rick > > > On Wed, May 14, 2008 at 10:35 AM, Tadeu Augusto Dutra Pinto < > [EMAIL PROTECTED]> wrote: > >> ** Hi all, >> >> I'd like to know if has a white paper that describes what's max number of >> index to a form... >> >> I've read a document from 2003 and I've noticed that more than 7 index (or >> 4 composite index) in one form is bad... >> >> >> I have an application that has a form with 8 index (some of them composite >> index) ... and we're having problems with performance (searches in >> database).. I'd like to know if it's possible to insert more index in this >> form! >> >> Remembering: I'd like to improve performance... >> >> >> Att, >> Tadeu Augusto Dutra Pinto >> __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" >> html___ > > > __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" > html___ > ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
RES: Max Index on Form!
Thanks for your reply Rick, John and Shawn... Some data: - form receives about 15K records/month; - total number of records is almost 480K; - number of fields on form are 168; - 8 simple index; - 2 composite index (two fields per index); Thankx! Tadeu Augusto Dutra Pinto De: Action Request System discussion list(ARSList) em nome de Rick Cook Enviada: qua 14/5/2008 14:53 Para: arslist@ARSLIST.ORG Assunto: Re: Max Index on Form! ** The two things to look in determining the appropriate maximum number of indexes for a form are the usage of the form and the number of rows involved. If you have only a few hundred or thousand rows in a table, indexes probably won't help all that much, and performance might be negatively impacted if you went way over the top in your indexing. (Aside: Remedy put like 16 on HPD:HelpDesk in ITSM 7. Draw whatever conclusions you like from that). On tables with ~100k+ rows, the basic rule is that indexes speed up queries, but slow down submits, because on each submit, every index must be updated. So on forms where lots of records are submitted, you might want to keep the number of indexes lower than on a form against which reports are run. 7 indexes is not excessive, but is starting to get to the point where adding more would probably be noticed on submits. There isn't a hard number at which your performance will fall off a cliff, you will just notice gradual degradation of performance as the number increases beyond somewhere around 7. It's up to you to find the best balance of performance and functionality for your installation. One other thing to look at, if you haven't already, is how the indexes you have in place are being used, and whether changing or removing one or more of those makes sense. That takes a lot of analysis of SQL logs, and working with your DBA to determine which searches are taking the most time, etc. Rick On Wed, May 14, 2008 at 10:35 AM, Tadeu Augusto Dutra Pinto <[EMAIL PROTECTED]> wrote: ** Hi all, I'd like to know if has a white paper that describes what's max number of index to a form... I've read a document from 2003 and I've noticed that more than 7 index (or 4 composite index) in one form is bad... I have an application that has a form with 8 index (some of them composite index) ... and we're having problems with performance (searches in database).. I'd like to know if it's possible to insert more index in this form! Remembering: I'd like to improve performance... Att, Tadeu Augusto Dutra Pinto __Platinum Sponsor: www.rmsportal.com <http://www.rmsportal.com/> ARSlist: "Where the Answers Are" html___ __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
Re: Max Index on Form!
Tadeu, I don't think it's a matter of what the system allows, but what is best practices. There is no single right answer. You have to consider how many records are in the table, what the most common searches will be, etc. It's something that you need to work with a DBA on. They can run SQL Profiler and other tools to be able to determine what is causing the most slowdowns in the queries. Then, when you look at that and see the need for a new index, you can create a new one. Shawn Pierson From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Tadeu Augusto Dutra Pinto Sent: Wednesday, May 14, 2008 12:36 PM To: arslist@ARSLIST.ORG Subject: Max Index on Form! ** Hi all, I'd like to know if has a white paper that describes what's max number of index to a form... I've read a document from 2003 and I've noticed that more than 7 index (or 4 composite index) in one form is bad... I have an application that has a form with 8 index (some of them composite index) ... and we're having problems with performance (searches in database).. I'd like to know if it's possible to insert more index in this form! Remembering: I'd like to improve performance... Att, Tadeu Augusto Dutra Pinto __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ Private and confidential as detailed here: http://www.sug.com/disclaimers/default.htm#Mail . If you cannot access the link, please e-mail sender. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
Re: Max Index on Form!
Tadeu, Indexesare a balencing act. the more you have the more options the sql analyzer has to construct an optimal execution plan. The cost is at the write, meaning commit, time. the system has to update all the indexes to maintain system integrity. This cost time during the commit phase. If your data is infrequently written or change, more indexes are ok. If the data is volital or not highly selective, more indexes are bad. the answer is it depends on your data. John - Original Message From: Tadeu Augusto Dutra Pinto <[EMAIL PROTECTED]> To: arslist@ARSLIST.ORG Sent: Wednesday, May 14, 2008 1:35:46 PM Subject: Max Index on Form! ** Hi all, I'd like to know if has a white paper that describes what's max number of index to a form... I've read a document from 2003 and I've noticed that more than 7 index (or 4 composite index) in one form is bad... I have an application that has a form with 8 index (some of them composite index) ... and we're having problems with performance (searches in database).. I'd like to know if it's possible to insert more index in this form! Remembering: I'd like to improve performance... Att, Tadeu Augusto Dutra Pinto__Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
Re: Max Index on Form!
The two things to look in determining the appropriate maximum number of indexes for a form are the usage of the form and the number of rows involved. If you have only a few hundred or thousand rows in a table, indexes probably won't help all that much, and performance might be negatively impacted if you went way over the top in your indexing. (Aside: Remedy put like 16 on HPD:HelpDesk in ITSM 7. Draw whatever conclusions you like from that). On tables with ~100k+ rows, the basic rule is that indexes speed up queries, but slow down submits, because on each submit, every index must be updated. So on forms where lots of records are submitted, you might want to keep the number of indexes lower than on a form against which reports are run. 7 indexes is not excessive, but is starting to get to the point where adding more would probably be noticed on submits. There isn't a hard number at which your performance will fall off a cliff, you will just notice gradual degradation of performance as the number increases beyond somewhere around 7. It's up to you to find the best balance of performance and functionality for your installation. One other thing to look at, if you haven't already, is how the indexes you have in place are being used, and whether changing or removing one or more of those makes sense. That takes a lot of analysis of SQL logs, and working with your DBA to determine which searches are taking the most time, etc. Rick On Wed, May 14, 2008 at 10:35 AM, Tadeu Augusto Dutra Pinto < [EMAIL PROTECTED]> wrote: > ** Hi all, > > I'd like to know if has a white paper that describes what's max number of > index to a form... > > I've read a document from 2003 and I've noticed that more than 7 index (or > 4 composite index) in one form is bad... > > > I have an application that has a form with 8 index (some of them composite > index) ... and we're having problems with performance (searches in > database).. I'd like to know if it's possible to insert more index in this > form! > > Remembering: I'd like to improve performance... > > > Att, > Tadeu Augusto Dutra Pinto > __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" > html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
Max Index on Form!
Hi all, I'd like to know if has a white paper that describes what's max number of index to a form... I've read a document from 2003 and I've noticed that more than 7 index (or 4 composite index) in one form is bad... I have an application that has a form with 8 index (some of them composite index) ... and we're having problems with performance (searches in database).. I'd like to know if it's possible to insert more index in this form! Remembering: I'd like to improve performance... Att, Tadeu Augusto Dutra Pinto ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
