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"
