Hi,

I can't find any information on the performance differences in indexing
using different types.

My situation is that I have a structure that would be easier to
implement using a multipart 20 char key than using a two, 4 byte integer
key. There is, however, a worry that using the larger char key will have
an adverse effect on performance. Can anyone shed any light on how large
the effect will be?

TIA,

Ben.

A nice simple question for a quite complex issue. In short, NO. There are
WAY too many variables, including database size, number of records, number
of unique records, server CPU, server memory, disk I/O throughput, disk I/O
latency, and more.

However, I can offer this information:
- A larger key will take up more disk space in the file, with the difference
between a 20-byte key and an 8-byte key being about 2x (it's not 2.5x since
there is additional overhead per key that washes this out).
- A larger key will take slightly longer to compare when traversing the
B-Tree structure. This may only be 30 or 40 machine language instructions,
but it will add up over millions of times.
- A larger key will have a deeper B-Tree structure, since not as many keys
fit on a page.

Of course, on a fully loaded server, you'd likely notice the difference.
Since nobody runs servers at 100% capacity any more, you'll likely NOT see
any noticable difference unless you're dealing with millions and millions of
records. In short, don't worry about the performance difference, and simply
do what works best for you and the app.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: March, 2004: See our web site for details!


TA1 wrote:

It will depend on many of the variables already mentioned.
In most cases it will be nearly a wash. I am guessing this will be
only one of probably many keys and a small portion of the overall
record.

I have seen that the number of segments makes almost as large a
difference as the size. Of course this is exagerated only for the
most index sensitive operations of adding an index.
4 x 2 byte segment index takes much longer to add than a single
1 x 8 byte index even though it is the same number of bytes.

It may be a trade between CPU cycles at the server and CPU cycles on
the client. If every byte and CPU cycle counts use the smallest
record possible and the smallest and fewest index segments.

They do all add up.

If you are near a break point where a couple of bytes will make the
difference in the number of records on a page it can be significant.

Leonard

On 31 Jan 2004 13:03:11 EST, Bill Bach <bbach (AT) cncdsl (DOT) com> wrote: