Agreed, that this is how it is often done.
But there is nothing that says that this is how it has to be done OR
even how it should be done.
These 2 files could be setup such that :
The CUSTOMER file contains no such embedded 'cross reference' field
for appointments
The APPOINTMENTS file could be keyed as :
@ID = CUSTOMERNO*APPTDATE*APPTTIME*APPTTYPE
With ITYPE entries setup for
CUSTOMERNO = @ID['*',1,1]
APPTDATE = @ID['*',2,1]
APPTTIME = @ID['*',3,1]
APPTYPE = @ID['*',4,1]
-- OR --
@ID = sequentially generated
With data fields defined for CUSTOMERNO, APPTDATE, APPTTIME &
APPTYPE
In both cases, indexed on CUSTOMERNO.
I believe you will find the following 2 snippets pretty comparable
performance wise :
* I know there are other better (?) ways to do this
* insert your own list traversal code
appcnt=dcount(custrec<appxref>,@VM )
for appidx = 1 to appcnt
appid = custrec<appxref,appidx>
next
execute 'SELECT APPOINTMENTS WITH CUSTOMERNO = ':squote(custno)
loop while readnext appid
repeat
Dropping the xref field in the parent table would help immensely with
file sizing as records would tend to be a lot closer in size to the
average instead of having CUSTOMER records that have accumulate massive
xref fields over time - this is a problem I see in systems all the time.
This also gets around your problem of knowing what is related to what -
you know that all keys are automatic indexes in sql server and anything
that could be used for lookup/joining purposes should also have an
explicit index in sql server else it would be a bottleneck in sql server
as well.
There is also nothing that says that a file's key value has to be based
on anything related to the data in the record - some would argue that
key values should NEVER be related to the data. The only reason to
prefer the 1st method over the 2nd is for human readability which imo
shouldn't be a concern anyway.
The 2nd scheme also gets around the sql->mv field mapping, no defined
relation, no key, non-unique key and key length max exceeded problems,
and would definitely produce a much more even hash distribution.
Just some thoughts, Gerry
-----Original Message-----
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben
Sent: December 24, 2010 01:41 AM
To: U2 Users List
Subject: Re: [U2] Migration
Oh, one more point. What if your SQL environment had NOT defined a
primary key for APPOINTMENTS, but had multiple indexes, one of which
happened to have CUSTOMERNO, APPTDATE, APPTTIME and APPTTYPE. How would
you figure out what to use as the item-id of the PICK file?
What if you had a SQL table that actually did not have a set of fields
that guaranteed a unique value? Then you have NOTHING to create an
item-id from!
I have to stop this, it will consume me! :o But the list goes on. Oh
the humanity!
-----Original Message-----
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben
Sent: Thursday, December 23, 2010 10:36 PM
To: U2 Users List
Subject: Re: [U2] Migration
I may have been unclear in my earlier post, so I'll clarify.
Consider a CUSTOMER file and an APPOINTMENTS file. The item-id of the
CUSTOMER file is the customer number. The item-id of the APPOINTMENTS
file is CUSTOMERNO*APPTDATE*APPTTIME*APPTTYPE.
When you have a parent/child relationship in multiple files in MV,
except in those cases where the key to a child file is the item-id of
the parent file with a sequential ordinal, the common way to indicate a
parent-child relationship involving more than one file in MV is to
embed, in the parent item a multivalued set of either all the entire
item-ids of the child table, or the portion that needs to be
concatenated to the parent's item-id. That is what I call a cross
reference field. If you don't have this, you are faced with trying to
scan the whole APPOINTMENTS file to find all item-ids that start with
your CUSTOMERNO value. What you might actually have in the CUSTOMER
file is a set of 3 correlated multivalued attributes that have APPTDATE,
APPTTIME, and APPTTYPE values for all the APPOINTMENTS items that
pertain to the CUSTOMER item.
In a SQL environment, the primary key to the child table would consist
of at least two fields, one or more of which would be the full primary
key of the parent table. In SQL Server a true primary key forces the
file to actually be sorted by those key fields (it forces a clustered
index). You can also have secondary indexes that are also pre-sorted by
their indexed columns. They are effectively complete copies of the
indexed fields and a copy of the primary key so it can directly read the
data once you've found the index entries that match your query. In our
example above, you'd have CUSTOMERNO as a primary key to the CUSTOMER
table, and 4 separate fields (no "*" delimiter) that make up the primary
key of the APPOINTMENTS table.
I'm not exactly sure how you'd accomplish the same thing in a
MultiValued environment if you just copied all the tables as flat MV
files. You'd lose the ability to access the child records without doing
a complete table scan. Unless of course you analyzed the data, and
created some special linking files, but the objective seems to be to
avoid human intervention. You might actually be able to do something
with an MV index on a dictionary record that references just the portion
of the child file's item-id that makes up the parent file's item-id (in
our example, the CUSTOMERNO). But you'd have to add that, at the very
least, and that would mean creating a dictionary record as part of what
you create. And that's NOT how you'd do it in SQL Server, for instance.
You might have defined foreign key references in the child table, but
that's not a given, so how you'd even know there was a parent-child
relationship in place is not clear. In some cases the naming of the
keys in the files can give you a hint, (that's how MS Access always
tried to figure it out and it worked a surprising amount of the time),
but you are not guaranteed that this will work in all cases. I've seen
plenty of cases where this did not work.
So, you could, in theory get all the data over, but you'd still be faced
with making it usable in a truly performant way.
Here's another gotcha to consider:
In many SQL tables, including our above example, the primary key will
consist of several fields. What do you do in PICK where you have one,
and only one, item-id attribute? Do you concatenate and assume fixed
length, or do you concatenate, choose a separator character and pray
it's not contained in the data? What if the concatenation of these
fields exceeds the length limit of an MV item-id? I know of real world
applications where this is the case... There are workarounds, but they
are NOT automatic!
You will have to choose some rules to work around issues. You will hit
exceptions that you'll actually have to think about, and you may have to
redesign some structures, to make them practical.
In short, there are things that you would do in a relational environment
that don't really have an exact analog that works in the MV world, and
vice versa.
There, that's a bit more encompassing. I'm out of time, but I hope this
has been helpful.
-----Original Message-----
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of
fft2...@aol.com
Sent: Thursday, December 23, 2010 8:46 PM
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] Migration
In a message dated 12/23/2010 4:28:38 PM Pacific Standard Time,
antli...@youngman.org.uk writes:
> > SQL uses indexes. MV uses cross references to item-ids (MV
> > sometimes
> supports indexes, but they don't always work as well as in the
> relational
> world.)
> >
>
> I don't know as that is true ... or are you using the word "index" to
> mean something completely different to me? I'll agree the
> implementation of indices can be buggy, but surely that's true of
relational engines too?
>
I'm not quite sure I'm confortable with the idea (expressed in the
prior-prior posting of which I here quote and enquote the reponse) that
MV uses cross-references to item-ids.
To me a hash table, isn't the same thing as a cross-reference which
sounds a lot like a secondary key. Hashing calculates an exact jump
point at which a group of related records are kept. They are related by
having the same hash value. But the hash value itself isn't looked up,
it's a calculation.
I wonder if you can setup a first normal form table in such a way, that
it maintains a constant sorted order ? Sorting on the primary key,
would then be merely display time bound, there is no effort to it. I
suppose you could even pick up and lay down the database periodically so
the sort order matches the actual disk layout. Pick could never do
something like that. There is always going to be effort involved in any
sorting, even if you're simply traversing the index tree and grabbing
the underlying data records.
But back to reality, I don't think SQL works this way anyway. The
perception that it sorts much faster is probably related more closely to
the horsepower behind the scenes. Pick systems tend to be installed on
slower systems because they are so efficient and most users are cheap
with their database, and expensive with their graphics. So they install
the SQL type databases on speedy machines.
_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
_______________________________________________
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
