> Can anyone give some guidence?
>
> I want to use a GUID as the primary key in my data tables. The project
> is in its early stages and I have been using a VarChar to store the GUID.
>
> As part of the feasibility testing I inserted one million records into
> a table. A simple SELECT query took 6 seconds to return a result, very
> impressive, considering it was done on a 2100+ AMD with 512 MB.
>
> But...
>
> My question: Is there a more efficient way that I can use GUID's. I
> have read on this list people achieving sub 1 second transactions.
>
> Thank-you In Advance
>
> Luke

straight GUIDs (e.g. generated by Delphi written application) has their
significant part at the end which does not provide for optimal indexing. Use
of the UDF generating UUID (reverse GUID) is said to improve performance
over the PK dramatically.
As for fieldtype - there are some suggestions here on the gourp as to what
datatype to use.
Here I quote a recent discussion with some pros and cons:

(read from the bottom perhaps)
It is a valid point, but you need to do the math for the specific problem
being resolved to decide how valid. With small index column sizes you need
much more detail than I have at my fingertips to compute a real value, but
let's say that you go to a 4 byte integer key. You lose 34 bytes from the
index entry footprint, giving 50-34=16 bytes per entry plus some overheads.
On an 8k page this gives you roughly 500 entries. At two levels in the B+
tree you address 250,000 rows. At three levels you can address 125,000,000
rows.

If you table space is expected to grow to 4,000,000 rows then you still have
to expand the index to three levels, but you can go a lot further on three
levels than you can with a larger key.

GUID's are ideal for distributed systems, but are not the most efficient way
of handling centralized systems.

It's a matter of the favorite hammer. Sometimes you need a paintbrush, and
a hammer just won't do. You can't let your (or my) favorite solution be
the only solution you know because it is not appropriate for every
situation.

----- Original Message -----
From: Alan McDonald
To: firebird-support@yahoogroups.com
Sent: Sunday, February 01, 2004 2:32 PM
Subject: RE: [firebird-support] MS SQL Vs. Firebird Again


> Using 8k page size and 50 character more or less keys+pointers
> entries, you get roughly 160 index entries per page. The first
> two layers of the B+ tree can address 160^2 records if the
> indeces are full. A third layer addresses 4,096,000 records. A
> max operation against an ascending index on a 4,000,000 row table
> should only need to read three pages maximum. This is consistent
> with the results I achieved in the random read tests. Note that
> my primary key is quite large - using an integer key would result
> in even greater efficiency in indexing.

So this is a small but valid argument against using GUID/UUIDs for the
primary key?
Alan