IDS Forum

Re: index updates

Posted By: Khaled Bentebal
Date: Thursday, 17 March 2005, at 4:55 a.m.

In Response To: RE: index updates (Chakravarthy Pathri )

Hi,

There is no way to estimate the response time since having the extra index
will push the engine to update it (update the btree) every time an
update/delete/insert is executed. At on point in time, adding a element
(item) to an index will be fast if the engine does not need to rebalance the
btree, but at certain time the engine will have to rebalance the btree and
will take longer. It also depends on the depth of the btree (number of
levels) and the type of the data that btree contains; the more rows you have
the longer it takes to update the btree.
So you cannot count on a linear extention of time if you add one index.

Remember, having an extra index might help a certain query or queries, but
might degrade the performance of some other parts of the applications that
contain a lot of insert/delete/update statements since the extra index will
have to be updated. Moreover, the extra index might also contaminate other
selects since the optimizer will have to consider this extra index if
present and the optimized will change not always for the best path (even
though in general we suppose that the optimizer will find the best possible
path).

A good advice is to start by looking at the SQL statement that causes
performance problems and try to analyse it and rewrite it if possible before
considering adding an extra index.

As I said in a previous mail, the indexes will get updated along the sql
statement (UPDATE/INSERT/DELETE) sequentially in the order of the indexes
for that tables. But as Keith said in one of the mails included, this does
not matter and in is not important since the sum of the index updates is an
integral part of the SQL statement itself, and if on fails, the whole thing
will fail.

Regards,

Khaled Bentebal
ConsultiX
Tél: 33 (0) 1 39 72 17 00
Fax: 33 (0) 1 39 72 17 01
Mobile: 33 (0) 6 07 78 41 97
Email: khaled.bentebal@consult-ix.fr
Site Web: http://www.consult-ix.fr

----- Original Message -----
From: "Chakravarth...." <pcdudyala@yahoo.com>
To: <ids@iiug.org>
Sent: Thursday, March 17, 2005 6:27 AM
Subject: RE: index updates [4529]


> Generally people percieve that by adding additional indexes, the response
times would go up. This is true but what would be the imapact on response
times.Is there any method of of calculating the response times by adding
additional indexes.
> Eg:
> Transaction T1(insert) using index I1 responds at r1. When the same
transaction with additional index I2, what would be the response time?. Is
there any method to estimate the
> T1 response time.
>
>
> Bye.
>
> "Simmons, Keith" <keith.simmons@office2office.biz> wrote:
> Of course the indexes will be updated automatically, immediately and with
> any insert, amend or delete. The DBS would be totally unusable otherwise
!!!
> Update Statistics only allows the engine to determine the number of
records
> in a table, or the distribution of values within a column. These values
are
> used to determine the best index to use to fulfil a query.
> Not sure whether the index updates happen serially or in parallel, could
> depend on the fragmentation of the table and whether the indexes are
> detached or not. Not sure it really matters, if any part of the update
fails
> the whole update will fail and all changes will rollout.
> Why do you ask?
>
> Keith
>
> -> -----Original Message-----
> -> From: Mohan, Krishna [mailto:krishna.mohan@fmr.com]
> -> Sent: Monday, March 14, 2005 8:32 AM
> -> To: ids@iiug.org
> -> Subject: RE: index updates [4490]
> ->
> ->
> -> When you run update/delete/inserts on your table indexes will not be
> -> updated automatically in Informix.
> -> You will have to run update statistics command (for command usage see
> -> SQL reference guide) on the target table or Database.
> ->
> -> Normally DBA's run update statistics on database/Table
> -> periodically(weekly/monthly..etc).
> ->
> -> Thanks,
> ->
> -> Krishna Mohan
> -> Fidelity India.
> ->
> ->
> -> Any comments or statements made in this email are not
> -> necessarily those
> -> of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity
> -> Investments group companies. The information transmitted is intended
> -> only for the person or entity to which it is addressed and
> -> may contain
> -> confidential and/or privileged material. If you have received this in
> -> error, please contact the sender and delete the material from any
> -> computer. All e-mails sent from or to Fidelity Business
> -> Services India
> -> Pvt. Ltd. may be subject to our monitoring procedures.
> ->
> ->
> ->
> -> -----Original Message-----
> -> From: PARAMESHWAR.... [mailto:pcdudyala@yahoo.com]
> -> Sent: Monday, March 14, 2005 11:45 AM
> -> To: ids@iiug.org
> -> Subject: index updates [4489]
> ->
> ->
> -> Hi,
> -> If i have several indexes on a table and i perform a update on the
> -> table, does
> -> all the indexes get updated parallely or they get updated
> -> one after the
> -> other(
> -> sequentially).
> ->
> ->
> ->
> -> Bye,
> ->
> ->
>
>
****************************************************************************
******
> This message is sent in strict confidence for the addressee only. It may
> contain legally privileged information. The contents are not to be
disclosed
> to anyone other than the addressee. Unauthorised recipients are requested
> to preserve this confidentiality and to advise the sender immediately of
any
> error in transmission.
> This footnote also confirms that this email message has been swept for the
> presence of computer viruses, however we cannot guarantee that this
message
> is free from such problems.
>
****************************************************************************
******
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
>

Messages In This Thread

• index updates
  PARAMESHWAR PATHRI -- Monday, 14 March 2005, at 1:15 a.m.
  • RE: index updates
    Mohan, Krishna -- Monday, 14 March 2005, at 3:32 a.m.
    • RE: index updates
      Simmons, Keith -- Monday, 14 March 2005, at 5:50 a.m.
      • RE: index updates
        Chakravarthy Pathri -- Thursday, 17 March 2005, at 12:27 a.m.
        • Re: index updates
          Khaled Bentebal -- Thursday, 17 March 2005, at 4:55 a.m.
        • RE: index updates
          ART KAGEL, BLOOMBERG/ 731 LEXIN -- Thursday, 17 March 2005, at 9:00 a.m.
    • Re: index updates
      Jack Parker -- Monday, 14 March 2005, at 6:14 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.