RE: [U2] [UV] Speeding up a big BY.EXP select
But then it wouldn't be as much fun. :-) -Original Message- From: Clifton Oliver [mailto:[EMAIL PROTECTED] Sent: Saturday, August 11, 2007 1:13 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] [UV] Speeding up a big BY.EXP select GEEZ people. Why don't you just ask the computer rather than debating it? And recognize that the U2 platforms differ depending on the flavor the account is in. Us old timers (33 years in MultiValue) need to remember that everything you think you know is wrong. On Aug 10, 2007, at 8:41 PM, MAJ Programming wrote: My premise was that SSELECT FILE BY @ID BY FIELDNAME SSELECT TEST_FILE BY @ID BY FIELDNAME EXPLAIN Optimizing query block 0 Driver source: TEST_FILE Access method: file scan Sorted by: @[EMAIL PROTECTED] UniVerse/SQL: Press any key to continue or 'Q' to quit is not the same as SSELECT FILE BY FIELDNAME. SSELECT TEST_FILE BY FIELDNAME EXPLAIN Optimizing query block 0 Driver source: TEST_FILE Access method: file scan Sorted by: [EMAIL PROTECTED] UniVerse/SQL: Press any key to continue or 'Q' to quit 0 record(s) selected to SELECT list #0. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] [UV] Speeding up a big BY.EXP select
Well, there is that, I guess. Plus, the furious typing makes the boss think something productive involving coding or testing is occurring. ;-) On Aug 13, 2007, at 6:03 AM, Jerry Banker wrote: But then it wouldn't be as much fun. :-) -Original Message- From: Clifton Oliver [mailto:[EMAIL PROTECTED] Sent: Saturday, August 11, 2007 1:13 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] [UV] Speeding up a big BY.EXP select GEEZ people. Why don't you just ask the computer rather than debating it? And recognize that the U2 platforms differ depending on the flavor the account is in. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] [UV] Speeding up a big BY.EXP select
GEEZ people. Why don't you just ask the computer rather than debating it? And recognize that the U2 platforms differ depending on the flavor the account is in. Us old timers (33 years in MultiValue) need to remember that everything you think you know is wrong. On Aug 10, 2007, at 8:41 PM, MAJ Programming wrote: My premise was that SSELECT FILE BY @ID BY FIELDNAME SSELECT TEST_FILE BY @ID BY FIELDNAME EXPLAIN Optimizing query block 0 Driver source: TEST_FILE Access method: file scan Sorted by: @[EMAIL PROTECTED] UniVerse/SQL: Press any key to continue or 'Q' to quit is not the same as SSELECT FILE BY FIELDNAME. SSELECT TEST_FILE BY FIELDNAME EXPLAIN Optimizing query block 0 Driver source: TEST_FILE Access method: file scan Sorted by: [EMAIL PROTECTED] UniVerse/SQL: Press any key to continue or 'Q' to quit 0 record(s) selected to SELECT list #0. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2][UV] Speeding up a big BY.EXP select
This is looking very promising but probably also needs 'niced up' dictionaries - our collection of randomly generated dicts doesn't cut it and I'm getting some fields unnesting and some fields as MV lists per unnested row. AA 12345 description of 12345 Description of Description of 7625 AA description of 12345 Description of Description of 7625 AA 7625 description of 12345 Description of Description of 7625 Should I be looking at using @ASSOC_KEY? -Original Message- If so, use an SQL UNNEST keyword, e.g. SELECT SURNAME,FORENAME,TITLE_ID,QTY,PRICE FROM UNNEST BOOK_SALES ON SALE_ITEMS; You can explode a field to a select list by using the TO SLIST clause, e.g. SELECT TITLE_ID TO SLIST 0 FROM UNNEST BOOK_SALES ON SALE_ITEMS; ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this communication in error, please reply to this e-mail to notify the sender of its incorrect delivery and then delete it and your reply. It is your responsibility to check this email and any attachments for viruses and defects before opening or sending them on. Spotless collects information about you to provide and market our services. For information about use, disclosure and access, see our privacy policy at http://www.spotless.com.au Please consider our environment before printing this email. ** --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] [UV] Speeding up a big BY.EXP select
I'm not exactly sure how the programming is done on this but from the outside it is all relative to how you look at it. Let's take the statement: SSELECT CUSTOMERS BY NAME BY CITY In one way it can be seen as: Sort by name with each name sort by city with each city sort by key. In another way of looking at it: Sort by key go through the list pulling out, but retaining the key order, sorting the cities then, retaining that order, sort by name. Either way if you throw a BY @ID in there the SSELECT becomes redundant and the SSELECT is done for no reason. Jerry -Original Message- From: MAJ Programming [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 6:24 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] [UV] Speeding up a big BY.EXP select Jerry: Are you sure. Please make sure that what you are saying is correct. The SSELECT statement has traditionally sorted on the @ID last for the last 30 years. It would be news to me to learn that it sorts BY @ID automatically first because of the double S in SSELECT. Thus SSELECT FILE BY CITY should be different than SSELECT FILE BY @ID BY CITY as the first would gather all the records BY CITY then within each CITY, they would be sorted by @ID (the default). Using @ID anywhere but the last sort field (or implied) would not make sense as they are guaranteed to be unique and not need a tie breaker. Please check again. Thanks Mark Johnson - Original Message - From: Jerry Banker [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Thursday, August 09, 2007 9:39 AM Subject: RE: [U2] [UV] Speeding up a big BY.EXP select The way I see it the first one will take longer than the second, although it would have to be a large file you are selecting on. The reason would be that the first one sorts the ID's, redundantly, twice before it gets to the BY.EXP whereas the second one sorts by the ID only once. -Original Message- From: Boydell, Stuart [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 2:27 AM To: u2-users@listserver.u2ug.org Subject: [U2] [UV] Speeding up a big BY.EXP select 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 ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this communication in error, please reply to this e-mail to notify the sender of its incorrect delivery and then delete it and your reply. It is your responsibility to check this email and any attachments for viruses and defects before opening or sending them on. Spotless collects information about you to provide and market our services. For information about use, disclosure and access, see our privacy policy at http://www.spotless.com.au Please consider our environment before printing this email. ** --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] [UV] Speeding up a big BY.EXP select
My premise was that SSELECT FILE BY @ID BY FIELDNAME is not the same as SSELECT FILE BY FIELDNAME. The second one is technically SSELECT FILE BY FIELDNAME BY @ID My 1 cent Mark Johnson - Original Message - From: Jerry Banker [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Friday, August 10, 2007 10:32 AM Subject: RE: [U2] [UV] Speeding up a big BY.EXP select I'm not exactly sure how the programming is done on this but from the outside it is all relative to how you look at it. Let's take the statement: SSELECT CUSTOMERS BY NAME BY CITY In one way it can be seen as: Sort by name with each name sort by city with each city sort by key. In another way of looking at it: Sort by key go through the list pulling out, but retaining the key order, sorting the cities then, retaining that order, sort by name. Either way if you throw a BY @ID in there the SSELECT becomes redundant and the SSELECT is done for no reason. Jerry -Original Message- From: MAJ Programming [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 6:24 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] [UV] Speeding up a big BY.EXP select Jerry: Are you sure. Please make sure that what you are saying is correct. The SSELECT statement has traditionally sorted on the @ID last for the last 30 years. It would be news to me to learn that it sorts BY @ID automatically first because of the double S in SSELECT. Thus SSELECT FILE BY CITY should be different than SSELECT FILE BY @ID BY CITY as the first would gather all the records BY CITY then within each CITY, they would be sorted by @ID (the default). Using @ID anywhere but the last sort field (or implied) would not make sense as they are guaranteed to be unique and not need a tie breaker. Please check again. Thanks Mark Johnson - Original Message - From: Jerry Banker [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Thursday, August 09, 2007 9:39 AM Subject: RE: [U2] [UV] Speeding up a big BY.EXP select The way I see it the first one will take longer than the second, although it would have to be a large file you are selecting on. The reason would be that the first one sorts the ID's, redundantly, twice before it gets to the BY.EXP whereas the second one sorts by the ID only once. -Original Message- From: Boydell, Stuart [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 2:27 AM To: u2-users@listserver.u2ug.org Subject: [U2] [UV] Speeding up a big BY.EXP select 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 ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this communication in error, please reply to this e-mail to notify the sender of its incorrect delivery and then delete it and your reply. It is your responsibility to check this email and any attachments for viruses and defects before opening or sending them on. Spotless collects information about you to provide and market our services. For information about use, disclosure and access, see our privacy policy at http://www.spotless.com.au Please consider our environment before printing this email. ** --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] [UV] Speeding up a big BY.EXP select
Add the EXPLAIN keyword to your query and see what the UV query analyzer says...sometimes useful, mostly not though ;) Another thing to try is an SQL SELECT, that'll give you a few more grey hairs...! Regards David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Boydell, Stuart Sent: Thursday, August 09, 2007 5:27 PM To: u2-users@listserver.u2ug.org Subject: [U2] [UV] Speeding up a big BY.EXP select 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/
RE: [U2] [UV] Speeding up a big BY.EXP select
Just one thing to be aware of: SSELECT implies BY @ID so the BY @ID in your statement is redundant. To prevent sorting by @ID just use SELECT not SSELECT. So, if you are using SSELECT then you're already sorting by @ID, so I doubt you are going to make it any quicker by modifying your SELECT statement. One cheap nasty way to speed up a sort would be to programmatically dump all the keys to a flat file (e.g. 10 lines for each @ID concatenated with each MV formatted), and then use the UNIX sort command. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Boydell, Stuart Sent: Thursday, 9 August 2007 3:27 PM To: u2-users@listserver.u2ug.org Subject: [U2] [UV] Speeding up a big BY.EXP select 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 DISCLAIMER: Disclaimer. This e-mail is private and confidential. If you are not the intended recipient, please advise us by return e-mail immediately, and delete the e-mail and any attachments without using or disclosing the contents in any way. The views expressed in this e-mail are those of the author, and do not represent those of this company unless this is clearly indicated. You should scan this e-mail and any attachments for viruses. This company accepts no liability for any direct or indirect damage or loss resulting from the use of any attachments to this e-mail. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2][UV] Speeding up a big BY.EXP select
Stuart Do you mean you need to explode them, but not sort them? If so, use an SQL UNNEST keyword, e.g. SELECT SURNAME,FORENAME,TITLE_ID,QTY,PRICE FROM UNNEST BOOK_SALES ON SALE_ITEMS; You can explode a field to a select list by using the TO SLIST clause, e.g. SELECT TITLE_ID TO SLIST 0 FROM UNNEST BOOK_SALES ON SALE_ITEMS; Regards Brian --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] [UV] Speeding up a big BY.EXP select
My first response would be try it see - and as others have pointed out, using SELECT rather than SSELECT would have an impact BUT, then I got to thinking sort order isn't important? So, my question is what comes next? What are you feeding the select into, that doesn't need information in any particular order? Do you even need a BY-EXP if the data can be processed by a program directly from the multi-values ... so, what consumes the list (and why?) Ross Ferris Stamina Software Visage Better by Design! -Original Message- From: [EMAIL PROTECTED] [mailto:owner-u2- [EMAIL PROTECTED] On Behalf Of Boydell, Stuart Sent: Thursday, 9 August 2007 5:27 PM To: u2-users@listserver.u2ug.org Subject: [U2] [UV] Speeding up a big BY.EXP select 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 ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this communication in error, please reply to this e-mail to notify the sender of its incorrect delivery and then delete it and your reply. It is your responsibility to check this email and any attachments for viruses and defects before opening or sending them on. Spotless collects information about you to provide and market our services. For information about use, disclosure and access, see our privacy policy at http://www.spotless.com.au Please consider our environment before printing this email. ** --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2][UV] Speeding up a big BY.EXP select
Cool - looks interesting - I'll try that in the morning - thanks. -Original Message- Do you mean you need to explode them, but not sort them? If so, use an SQL UNNEST keyword, e.g. ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this communication in error, please reply to this e-mail to notify the sender of its incorrect delivery and then delete it and your reply. It is your responsibility to check this email and any attachments for viruses and defects before opening or sending them on. Spotless collects information about you to provide and market our services. For information about use, disclosure and access, see our privacy policy at http://www.spotless.com.au Please consider our environment before printing this email. ** --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] [UV] Speeding up a big BY.EXP select
I did do some small sample testing and found not much difference between the 2 methods - though on larger sets the BY.EXP seemed marginally faster than the BY @ID BY.EXP. So barring margins of error due to machine load I guess that answers most of my question. The background is extracting data to an xml document. The select feeds a LIST statement to produce a 'normalised' selection to build an attribute-centric xml document (a rather large document at that). So what the select does is creates repeating values from singlevalued fields and flattens the MVs ORDER 12345 001 A12312 002 12345]87654 003 WIDGETS]FIBONUCCIS 004 99]2 ... SELECT ORDERS BY.EXP PRODUCT.ID LIST ORDERS CUSTOMER.ID SINGLEVALUED PRODUCT.ID SINGLEVALUED PROD.DESC SINGLEVALUED ORD.QTY SINGLEVALUED TOXML Which produces: ROOT ORDERS ORDER.ID = 12345 CUSTOMER.ID = A12312 PRODUCT.ID=12345 PROD.DESC = WIDGETS ORD.QTY = 99 / ORDERS ORDER.ID = 12345 CUSTOMER.ID = A12312 PRODUCT.ID=87654 PROD.DESC = FIBONUCCIS ORD.QTY = 2 / ... I haven't seen any difference between the output of the SELECT v SSELECT. I think as long as you have a BY clause in the statement, the SSELECT semantics are used. Stuart. -Original Message- My first response would be try it see - and as others have pointed out, using SELECT rather than SSELECT would have an impact BUT, then I got to thinking sort order isn't important? So, my question is what comes next? What are you feeding the select into, that doesn't need information in any particular order? Do you even need a BY-EXP if the data can be processed by a program directly from the multi-values ... so, what consumes the list (and why?) ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this communication in error, please reply to this e-mail to notify the sender of its incorrect delivery and then delete it and your reply. It is your responsibility to check this email and any attachments for viruses and defects before opening or sending them on. Spotless collects information about you to provide and market our services. For information about use, disclosure and access, see our privacy policy at http://www.spotless.com.au Please consider our environment before printing this email. ** --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] [UV] Speeding up a big BY.EXP select
Actually, the 2 SSELECT statements are not the same: Eg. SSELECT BIGFILE BY @ID BY.EXP MV.ATTRIBUTE Or SSELECT BIGFILE BY.EXP MV.ATTRIBUTE Adrian's correct in that BY @ID is implied when using the SSELECT but the implication comes as a tie breaker after all the other specified sort criteria area processed. Thus the first one puts the entire file in its normal @ID order then within each record, the mv's are sorted. The second exploded the entire mess (all 2 million records) and then sorts them by the individual multi-values. Two different results. There is a trade off with so many records using BY.EXP. If you include the time to populate a WORK file to then sort it without the exploding, that may be the best bet. If the pursuit is to have the first sentence, ie BY ID then BY.EXP, then perhaps the application could store the mv's in sorted order per record so they don't have to be exploded. If it is the second sentence, then you have to hit it with this kind of query/process. My 2,000,000 cents Mark Johnson - Original Message - From: Womack, Adrian [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Thursday, August 09, 2007 4:15 AM Subject: RE: [U2] [UV] Speeding up a big BY.EXP select Just one thing to be aware of: SSELECT implies BY @ID so the BY @ID in your statement is redundant. To prevent sorting by @ID just use SELECT not SSELECT. So, if you are using SSELECT then you're already sorting by @ID, so I doubt you are going to make it any quicker by modifying your SELECT statement. One cheap nasty way to speed up a sort would be to programmatically dump all the keys to a flat file (e.g. 10 lines for each @ID concatenated with each MV formatted), and then use the UNIX sort command. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Boydell, Stuart Sent: Thursday, 9 August 2007 3:27 PM To: u2-users@listserver.u2ug.org Subject: [U2] [UV] Speeding up a big BY.EXP select 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 DISCLAIMER: Disclaimer. This e-mail is private and confidential. If you are not the intended recipient, please advise us by return e-mail immediately, and delete the e-mail and any attachments without using or disclosing the contents in any way. The views expressed in this e-mail are those of the author, and do not represent those of this company unless this is clearly indicated. You should scan this e-mail and any attachments for viruses. This company accepts no liability for any direct or indirect damage or loss resulting from the use of any attachments to this e-mail. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] [UV] Speeding up a big BY.EXP select
The way I see it the first one will take longer than the second, although it would have to be a large file you are selecting on. The reason would be that the first one sorts the ID's, redundantly, twice before it gets to the BY.EXP whereas the second one sorts by the ID only once. -Original Message- From: Boydell, Stuart [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 2:27 AM To: u2-users@listserver.u2ug.org Subject: [U2] [UV] Speeding up a big BY.EXP select 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 ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this communication in error, please reply to this e-mail to notify the sender of its incorrect delivery and then delete it and your reply. It is your responsibility to check this email and any attachments for viruses and defects before opening or sending them on. Spotless collects information about you to provide and market our services. For information about use, disclosure and access, see our privacy policy at http://www.spotless.com.au Please consider our environment before printing this email. ** --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] [UV] Speeding up a big BY.EXP select
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 @IDBY.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/
Re: [U2] [UV] Speeding up a big BY.EXP select
On 8/9/07, Womack, Adrian [EMAIL PROTECTED] wrote: Just one thing to be aware of: SSELECT implies BY @ID so the BY @ID in your statement is redundant. Adrian, I don't think that is correct (at least for UV). The implied BY @ID sort is always the last sort performed, e.g. SORT MYFILE BY THING1 BY THING2 BY @ID, so BY @ID BY-EXP THING1 is most definitely not the same as just BY-EXP THING1. What you get by sorting BY @ID then BY-EXP is the sorted mv field within each @ID, much like just sorting the file and listing the mv THING1 field (except that the THING1 field is sorted by within each @ID). However, since you are still turning the entire file and sorting it (whether by the @ID or a mv field) I doubt that the SORT BY @ID BY-EXP is materially faster than the plain SORT BY-EXP. Also, my quick test indicates that when you SELECT BY-EXP, the BY-EXP implies a sort, and the results are the same as SSELECT BY-EXP. /Scott Ballinger Pareto Corporation Edmonds WA USA 206 713 6006 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2][UV] Speeding up a big BY.EXP select
Boydell, Stuart wrote: Cool - looks interesting - I'll try that in the morning - thanks. I was curious so I did some testing. Your SSELECT is building very large temp files in /tmp. My test had 20 MVs of 16 bytes each. I ended up with almost 2G of temp files. If your MVs are longer (and if they are XML they probably are), you could have some substantial temp space in use. My test case took about 9 minutes on a single-proc single-drive Linux box. I don't know if this will help much. If UVTEMP points to some poor storage volume, you might want to check your system config. The IO appears to be mostly linear, so this is a bandwidth issue not a random database issue. --- Doug Dumitru --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2][UV] Speeding up a big BY.EXP select
Sure - but how long does it take on one of your fast new MFT drives ;-) Colin Alfke Calgary Canada P.S. Give me a call when they work on 64-bit windows servers... -Original Message- From: Doug Dumitru Boydell, Stuart wrote: Cool - looks interesting - I'll try that in the morning - thanks. I was curious so I did some testing. Your SSELECT is building very large temp files in /tmp. My test had 20 MVs of 16 bytes each. I ended up with almost 2G of temp files. If your MVs are longer (and if they are XML they probably are), you could have some substantial temp space in use. My test case took about 9 minutes on a single-proc single-drive Linux box. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] [UV] Speeding up a big BY.EXP select
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 @IDBY.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/
Re: [U2][UV] Speeding up a big BY.EXP select
[EMAIL PROTECTED] wrote: Sure - but how long does it take on one of your fast new MFT drives ;-) Actually, it is exactly the same speed. The temp files that UV uses to do sorts are accessed linearly so regular arrays work just great. It is random access and updates that kill rotating disks. With Linux it is pretty easy to tell what is going on. 'iostat' can tell you the average IO length and give you an idea of how busy your drives or arrays are. I was kinda surprised that he was taking several hours and my test was 9 minutes. After all, I was testing on 1 80G WD 7200 RPM IDE drive on an AMD Athlon 64 3400 (single core). Not exactly a high end server. He must have a much larger data set or his server is really busy. --- Doug Dumitru --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2][UV] Speeding up a big BY.EXP select
Sorry if multiple posts (connection died): If you are using a SAN or RAID array with read ahead enabled I find it runs REALLY REALLY faster if I turn it off. Read ahead is optimised for large block transfers on sequential SQL SELECTs and does not mix well with small transfers with a random pattern (i.e, hashed files). Of course a HUGE disk cache will offset this to some extent (depends). Regards JayJay -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Doug Dumitru Sent: 09 August 2007 22:27 To: u2-users@listserver.u2ug.org Subject: Re: [U2][UV] Speeding up a big BY.EXP select [EMAIL PROTECTED] wrote: Sure - but how long does it take on one of your fast new MFT drives ;-) Actually, it is exactly the same speed. The temp files that UV uses to do sorts are accessed linearly so regular arrays work just great. It is random access and updates that kill rotating disks. With Linux it is pretty easy to tell what is going on. 'iostat' can tell you the average IO length and give you an idea of how busy your drives or arrays are. I was kinda surprised that he was taking several hours and my test was 9 minutes. After all, I was testing on 1 80G WD 7200 RPM IDE drive on an AMD Athlon 64 3400 (single core). Not exactly a high end server. He must have a much larger data set or his server is really busy. --- Doug Dumitru --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] [UV] Speeding up a big BY.EXP select
Is there an index on the MV field? If there is, this will speed up an unconstrained sort on that field. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] [UV] Speeding up a big BY.EXP select
Jerry: Are you sure. Please make sure that what you are saying is correct. The SSELECT statement has traditionally sorted on the @ID last for the last 30 years. It would be news to me to learn that it sorts BY @ID automatically first because of the double S in SSELECT. Thus SSELECT FILE BY CITY should be different than SSELECT FILE BY @ID BY CITY as the first would gather all the records BY CITY then within each CITY, they would be sorted by @ID (the default). Using @ID anywhere but the last sort field (or implied) would not make sense as they are guaranteed to be unique and not need a tie breaker. Please check again. Thanks Mark Johnson - Original Message - From: Jerry Banker [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Thursday, August 09, 2007 9:39 AM Subject: RE: [U2] [UV] Speeding up a big BY.EXP select The way I see it the first one will take longer than the second, although it would have to be a large file you are selecting on. The reason would be that the first one sorts the ID's, redundantly, twice before it gets to the BY.EXP whereas the second one sorts by the ID only once. -Original Message- From: Boydell, Stuart [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 2:27 AM To: u2-users@listserver.u2ug.org Subject: [U2] [UV] Speeding up a big BY.EXP select 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 ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this communication in error, please reply to this e-mail to notify the sender of its incorrect delivery and then delete it and your reply. It is your responsibility to check this email and any attachments for viruses and defects before opening or sending them on. Spotless collects information about you to provide and market our services. For information about use, disclosure and access, see our privacy policy at http://www.spotless.com.au Please consider our environment before printing this email. ** --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] [UV] Speeding up a big BY.EXP select
This is so great!! the WHEN-BY.EXP connection was removed a few releases back. On my 10.0.16 I live under the tyranny of the old regime. My shorts have thawed . . . Wait, now they've frosted over again: I'm reading release notes for 10.1 10.2 and I don't see anything about WHEN/BY.EXP. Did I miss an important doc? I admit I haven't read everything yet. Where how was I supposed to find this out? cds P.S. My comment about sorting by id before mv.attribute still stands. All else being equal, if you don't are about the order, lump all the select list entries for the same record together for the sake of subsequent use of the resultant list. Under the new regime I suppose the proper syntax is: SELECT BIGFILE WHEN MV.ATTRIBUTE = xyz (or whatever the criteria are.) -Original Message- From: Ron Hutchings 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 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. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
