The comment about the WHEN-BY.EXP connection was removed a few releases
back. Now, if you use WHEN it will pick off only the multi-value that
matches and the rest do not display.
______________________________________________________________
From: "Stevenson, Charles" <[EMAIL PROTECTED]>
Reply-To: u2-users@listserver.u2ug.org
To: <u2-users@listserver.u2ug.org>
Subject: RE: [U2] [UV] Speeding up a big BY.EXP select
Date: Thu, 9 Aug 2007 11:57:15 -0400
Without a doubt:
SELECT BIGFILE BY @ID BY.EXP MV.ATTRIBUTE
-----------------
It has always frosted my shorts that in order for WHEN to kick in, you
need BY.EXP even if you don't care about the sort order.
So, Given that sorting is syntactically necessary,
and Given that you don't really care about the order,
you should, without a doubt:
SELECT BIGFILE BY @ID BY.EXP MV.ATTRIBUTE
Reason: _S_U_B_S_E_Q_U_E_N_T_ use of the resultant list.
All the select list entries for each record will be lumped together so
the disk read of that record occurs once, for the 1st mv occurrence.
Subsequent requests for values from that record will still be in cache.
If you sort by MV.ATTRIBUTE before @ID, you will have multiple reads on
the
Example:
1st sort: BY @ID BY.EXP MV.ATTRIBUTE
Resultant
List: X}1|7}0 X}1|7}0
X}2|7}0 Z}3|7}0
X}3|7}0 X}2|7}0
Y}7|7}0 Y}7|7}0
Y}8|7}0 Z}10|7}0
Z}3|7}0 X}3|7}0
Z}10|7}0 Y}8|7}0
Z}5|7}0 Z}5|7}0
The select (or saved) list generated by BY.EXP selections has value &
subvalue marks in it, the extra stuff telling RetrieVe (or READNEXT)
where to dig out the relevant value & subvalues.
Notice that the 1st column is in id order, the 2nd will bounce around.
This is significant when the list is millions long.
cds
P.S. I'm going to post a question about UVTSORT config parameter
separately. I don't know its impact here.
-----Original Message-----
From: Boydell, Stuart
I have a large file with an MV set I need to do an exploding sort
against (about 2 million records, about 20 MVs per record). I'm not
actually after any particular sort order but it's taking hours.
I'm wondering if there is any efficiency advantage by telling it to use
a primary sort on the ID or just to sort on the MV attribute?
My (probably flawed) reasoning is that if the primary sort is on the id
then the sort process wouldn't have to build up one big explode - as it
would have a primary sort by record, it then only needs to explode each
record at a time.
Eg.
SSELECT BIGFILE BY @ID BY.EXP MV.ATTRIBUTE Or SSELECT BIGFILE BY.EXP
MV.ATTRIBUTE
Anyone know the answer to that one?
Stuart Boydell
-------
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
_________________________________________________________________
Messenger CafC) open for fun 24/7. Hot games, cool activities served daily.
Visit now.
-------
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
