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/