Board index » delphi » Overhead of addn'l 2ndary indexes?

Overhead of addn'l 2ndary indexes?

The long and short of it is this: what is the marginal performance hit
on table navigation of adding another maintained secondary index to a
table?

I want users to be able to view a grid in one of maybe 4-5 orders, so
that means I could have that many maintained secondary indexes.  Right
now we have one for last name+ID and another for graduation year; they
are in addition to the primary key, of course.

Is there even an answer to this question? ;-)

The tables are kept on a Compaq prolient p90 netware 3.12 server and
most users have at least some kind of pentium box.  We mostly use
Paradox DOS 4.5 right now, though most new code is PdxWin and more so
Delphi.

Thanks.
--
Frank Burleigh 812-333-7082 (home); 812-855-9170 (work)
School of Law, Indiana University, Bloomington, IN  47405
Internet burle...@bronze.ucs.indiana.edu; CIS 72730,1017

 

Re:Overhead of addn'l 2ndary indexes?


Quote
burle...@bronze.ucs.indiana.edu (Frank Burleigh) wrote:
>The long and short of it is this: what is the marginal performance hit
>on table navigation of adding another maintained secondary index to a
>table?

        The performance hit would be primarily on the updates to the table,
not the read(s). I guess if you had a LARGE number of secondary
indexes, the SQL Prepare might take a bit longer (to determine whether
it would use the additional indexes) - but this S/B minimal. The
access to the table would not suffer (and may improve if any of the
secondary indexes help out).

        If your users toggle between several of your "views" - using the
different indices, you may not get optimal "caching" of the index /
table, but this also would be negligible.

        The performance hit on the Updates on the other hand could be costly.
It would depend on record size, number of records, size of keys, etc.
But I believe you phrased your question specifically away from the
update issue.

        HTH.

Jim

Re:Overhead of addn'l 2ndary indexes?


In <43au3t...@news.iadfw.net> jim...@iadfw.net (JCat) writes:

Quote
>burle...@bronze.ucs.indiana.edu (Frank Burleigh) wrote:
>>The long and short of it is this: what is the marginal performance hit
>>on table navigation of adding another maintained secondary index to a
>>table?
>    The performance hit would be primarily on the updates to the table,
[snip]
>    The performance hit on the Updates on the other hand could be costly.
>It would depend on record size, number of records, size of keys, etc.
>But I believe you phrased your question specifically away from the
>update issue.

I shouldn't have, as that too is important.  Please do comment about
the update issue.  Number of records is under 20k for the "master."
The keys would be these:

  LastName, ID (ID is A11)
  Degree Year (S), ID
  Zip (A10), ID

Not much more than that, I wouldn't think.

Thanks.

--
Frank Burleigh 812-333-7082 (home); 812-855-9170 (work)
School of Law, Indiana University, Bloomington, IN  47405
Internet burle...@bronze.ucs.indiana.edu; CIS 72730,1017

Re:Overhead of addn'l 2ndary indexes?


Quote
burle...@bronze.ucs.indiana.edu (Frank Burleigh) wrote:
>I shouldn't have, as that too is important.  Please do comment about
>the update issue.  Number of records is under 20k for the "master."
>The keys would be these:
>  LastName, ID (ID is A11)
>  Degree Year (S), ID
>  Zip (A10), ID

        I'm not real sure whether you're adding 2 or 3 new keys here - but it
doesn't really matter. For an single update - I'd roughly guess the
update to take a 50 to 100% hit. While you're adding 2/3 additional
keys, the data record does not need to be written again - so you
shouldn't suffer the full 200 - 300% hit.

        For the online portion - actual time becomes a consideration. If it
was taking 0.25 seconds and increases to 0.5 (or even 1 second) - it's
probably not a big concern. If it was already taking 10 seconds (seems
unlikely) then adding another 10 seconds would not be acceptable. If
you have an offline (batch) mode to consider, the percentages would be
similar - but the times greatly increased.

        Unfortunately, this logic won't always work in the real world. While
switching from 1 data write and 1 key write to 1 data write and 3 key
writes should not cause even a 300% increase in time, other factors
can influence it. If your free memory is such that everything was well
cached and operating optimally initially - the additional indices may
alter this. You could then have a tremendous performance penalty (10x
or worse). The only way I know to be sure is to try it.

        As an option, if one of the queries is rarely used - or once the user
enters that mode of navigation (ie Zip code order) - they stay in it
for long periods: consider sorting via a query and using live results
rather than a secondary index. With 20K records, this is probably not
a good approach except under very special circumstances. You'd end up
with a long initial wait, but the only performace hit afterwards would
be that incurred by using a live result instead of a static result.
BTW, I have never used a live result in an app, so this may end up be
horrendous ;-) - let me know if you use it.

        Good luck.

Jim

Re:Overhead of addn'l 2ndary indexes?


I am developing an application that creates secondary indexes.  I create a
results table and move some data into it using query and batch move
components.  I need to give the user the option of specifying the order the
grid is displayed in.  Since the table is keyed, secondary indexes seemed
like the obvious solution.

This application has a {*word*193} set-up time.  But once it gets going, it is
quick.  Without the secondary indexes it was taking about 2 minutes on a
486/66 to start up (it does a lot).  When I added 24 seconary indexes, the
start up time went to around a half an hour.  That is a bit too long.

So I take a sightly different approach.  The list of possible indexes:
their name, human readable description, and field definitions are stored in
a paradox table.  The user selects the sort order via a Lookup-ComboBox.
When that changes, the program checks to see if the index exists.  If so it
just switches to that index.  If not, it generates the index.  This gets
past the huge setup time (that is due to lots of batch moves).

So don't do batch moves into tables with lots of secondary indexes.  Do the
batch move and then create the secondary index.
----
    Jeffrey M\kern-.05em\raise.5ex\hbox{\b c}\kern-.05emArthur
    a.k.a. Jeffrey McArthur          email: j_mcart...@bix.com
    home:  (410) 290-6935

The opinions expressed are mine.  They do not reflect the opinions
of my employer.  My access to the Internet is NOT paid for by my
employer. My access to the Internet is on my own time at my own
expense.

Other Threads