A few observations on UUIDs, UUID Form and PKs
Dave Nasralla (4/1/14 1:41PM)
Walt Nelson (4/8/14 5:10PM)
Dave Nasralla (4/1/14 1:41 PM)
<CAHTQw=ih794wL_6VZb1yL7KapjLVOwjpbhB_Ob_WVyMkhW37SQ@mail.gmail.com>
Hey All,
Here are a few things I have noticed.
- If you declare a field as a PK, regardless of the unique check box,
uniqueness in enforced (indexing is not). The fact that the unique
check
box is NOT forced to show reality is a bug in the interface (IMHO). I
made
a feature request in the forum if you would like to vote. (GET FIELD
PROPERTIES will always show a PK as being unique - regardless of the
checkbox.)
- So, if you are doing a large import (or creating a 1,000,000)
records,
you had better have indexing ON for the PK field. If you don't, life
grinds
to a crawl as the engine tries to enforce uniqueness on a non-indexed
field.
- Regardless of what you put in the alpha field (NULL, Blank, UUID,
UUID
in UUID Form) the datafiles came out the same size on my test database
(144
MB). This is true even if the field is declared to be 20 or 255 in
length.
(I think the field length simply enforces truncation.)
- UUIDs stored in UUID Form are slightly more compressible. A test
table
with 1,000,000 records was 144 MB uncompressed. When the datafile was
zipped the table with the UUID Form set on the field was 27.5MB as
compared
to 34.4MB when UUID Form was not checked. (It can matter if you need to
transmit data files a lot over the internet.)
- The downside of the UUID Form is that any related fields must also
use
UUID form. I'm not sure what that bothers me, but it does. As best I
can
tell UUID form just enforces that each of the 32 characters is a hex
character.
FWIW, I was working with v13.4.
Cheers,
dave
--
David Nasralla
Clean Air Engineering
Walt Nelson (4/8/14 5:10 PM)
On Apr 2, 2014, at 8:53 AM, Dave Nasralla <dnasralla@... wrote:
color><param>00000,0000,DDEE/param>IIn v13.4, you don't get indexing
unless you set it, even after restart,
even if the field is unique. What was throwing me was (in v13.4):
- I create a new table with and set PK on an ID field
- The Unique checkbox does not not get checked and the index is not
set.
- After reboot, the Unique box does get checked - (this is what I did
not
realize)
You are still not indexed until until you manual set it v13.4.
/color>
Dave,
I can confirm this behavior in v14.0. I was doing a big import that
was grinding to a halt as you said before. It was due to having a
field set to unique, but without an index. I had to manually set the
index for normal performance. I was a bit surprised by this 'feature',
too.
I haven't verified if this is still true in v14.1 or not, but it was
true in v14.0.
Thanks,
Walt Nelson (Seattle)
New stuff coming!
www.foundationshell.com
walt@...
Reply to this message
Summary created 4/9/14 at 1:43AM by Intellex Corporation
Comments welcome at: feedback@intellexcorp.com