RE: [U2] broken up SELECTS
It's something I've always done over the last twenty years. The select processor in theory should apply optimisiation but I always break my statements down to reduce the working record set and to increase readability. Makes very little difference on smaller amount of records but makes a huge difference when record counts get into the millions, especially on distributed files or selects which work on a mixture of non-indexed and indexed fields. From: [EMAIL PROTECTED] on behalf of Mark Johnson Sent: Thu 11/11/2004 13:58 To: [EMAIL PROTECTED] Subject: Re: [U2] PICK Assembler Language In all of my travels of code before me at my clients, I have never seen a convoluted SELECT statement broken up into 2 or more pieces for effeciency reasons. I stumbled on the idea around 9 years ago trying to squeeze a few more drops of speed from a 386-based system running InfoQuest. I saw the 95% wasted translates based on my original example and got a 50% increase in speed running a process that had to run about 15 times per user per day. Almost won the Nobel Prize with that client. Not that I've seen everything or more than all, but in my 25+ years of pick covering about 40 different platforms and environments I have never seen a SELECT statement broken up. Thanks. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 11, 2004 6:13 AM Subject: Re: [U2] PICK Assembler Language Just because we have a quadrillion microseconds to play with, instead of a 50 millisecond timeslice, doesn't mean we have to waste them. A few moments to consider performance, done once in the development cycle, will pay benefits every time a more efficient program is run. I have to believe that Adm Grace Hopper is smiling down on you both. Regards, Charlie Noah [EMAIL PROTECTED] (mailto:[EMAIL PROTECTED]) writes: -Original Message- snip I can't imagine anyone on this forum having to break up this sentence likewise on the current platforms. On this client's older MCD, it truly makes a difference. /snip - Ah, Mark..I do this all the time for the performance reasons mentioned :) Rich Taylor | Senior Programmer/Analyst| VERTIS 250 W. Pratt Street | Baltimore, MD 21201 P 410.361.8688 | F 410.528.0319 [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. The more they complicate the plumbing the easier it is to stop up the drain - Montgomery Scott NCC-1701 --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/ The information contained in this email is strictly confidential and for the use of the addressee only, unless otherwise indicated. If you are not the intended recipient, please do not read, copy, use or disclose to others this message or any attachment. Please also notify the sender by replying to this email or by telephone +44 (0)20 7896 0011 and then delete the email and any copies of it. Opinions, conclusions (etc.) that do not relate to the official business of this company shall be understood as neither given nor endorsed by it. IG Markets Limited and IG Index Plc are authorised and regulated by the Financial Services Authority and, in Australia, by the Australian Securities and Investments Commission. [demime 1.01d removed an attachment of type application/ms-tnef which had a name of winmail.dat] --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] broken up SELECTS
The UV keyword REQUIRE.SELECT makes this a possibility. On the Mentor and Ultimate systems I cut my teeth on, if the 1st select returned zero records, then the second select would select against the entire file. Once I moved to UV, and learned about REQUIRE.SELECT, then breaking out SELECT statements made more sense. --Ron P. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Adrian Matthews Sent: Thursday, November 11, 2004 9:27 AM To: [EMAIL PROTECTED] Subject: RE: [U2] broken up SELECTS It's something I've always done over the last twenty years. The select processor in theory should apply optimisiation but I always break my statements down to reduce the working record set and to increase readability. Makes very little difference on smaller amount of records but makes a huge difference when record counts get into the millions, especially on distributed files or selects which work on a mixture of non-indexed and indexed fields. --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] broken up SELECTS
Adrian Matthews [EMAIL PROTECTED] wrote on 11/11/2004 10:26:48 AM: It's something I've always done over the last twenty years. The select processor in theory should apply optimisiation but I always break my statements down to reduce the working record set and to increase readability. Makes very little difference on smaller amount of records but makes a huge difference when record counts get into the millions, especially on distributed files or selects which work on a mixture of non-indexed and indexed fields. Of course, like so many things in the MV world (and life in general) this is an it depends scenario. If you're selecting based on three fields that reside in the record, the additional overhead of comparing them will be negligible compared to making a second pass through. It also depends on whether one of the criteria would be expected to reduce the working set of records significantly. If so, a second pass may be worth it. If you can reduce the working set significantly by selecting on a resident field, and save a nasty translate for a small percentage of the entire file, a two-pass approach is almost a no-brainer. But if the resident field only pares out 10% of the records, you'll be passing through 90% of the file twice, and you have to determine whether the second pass is saving work or creating more. With a particularly nasty dictionary item, it may be worth it. Tim Snyder IBM Information Management Consulting I/T Specialist , U2 Professional Services --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] broken up SELECTS
Ron, I frequently break up selects for readability by whomsoever will follow after me. I've managed to avoid awkward AND and OR logic - or at least limit it - by cascading selects. - Charles Readability Barouch Pingilley, Ron wrote: The UV keyword REQUIRE.SELECT makes this a possibility. On the Mentor and Ultimate systems I cut my teeth on, if the 1st select returned zero records, then the second select would select against the entire file. Once I moved to UV, and learned about REQUIRE.SELECT, then breaking out SELECT statements made more sense. --Ron P. -- - Charles Barouch (718) 762-3884 x 1 - Key Ally Voice Mail [EMAIL PROTECTED] - Consulting services [EMAIL PROTECTED] - News [EMAIL PROTECTED] - U2-Users Moderator [EMAIL PROTECTED]- Zeus Data Integration Mount Olympus, Home of Zeus Data Integration --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] broken up SELECTS
One can also create an I-descriptor that returns a true/false (then SELECT ... WITH XYZ.FLAG ... ). Buried in the I-descriptor can be explicit control (via IF,AND,OR, parenthases) of whether or not you do certain expensive tests depending on results of cheaper tests. You can also apply parenthases to the SELECT statement itself. On Universe, use EXPLAIN keyword to see how RetrieVe will process the command. DLIST shows you how an I-descriptor compiles. Or use SQL for more versatility. SQL's OPTIMIZE / NO.OPTIMIZE can help you explicitly control how and sql select executes. As for TRANS(), there is one efficiency built in: TRANS() will cache a trans'd record just in case the next call to TRANS() asks for something from the same record. for what it is worth, cds -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Adrian Matthews Sent: Thursday, November 11, 2004 7:27 AM To: [EMAIL PROTECTED] Subject: RE: [U2] broken up SELECTS It's something I've always done over the last twenty years. The select processor in theory should apply optimisiation but I always break my statements down to reduce the working record set and to increase readability. Makes very little difference on smaller amount of records but makes a huge difference when record counts get into the millions, especially on distributed files or selects which work on a mixture of non-indexed and indexed fields. From: [EMAIL PROTECTED] on behalf of Mark Johnson Sent: Thu 11/11/2004 13:58 To: [EMAIL PROTECTED] Subject: Re: [U2] PICK Assembler Language In all of my travels of code before me at my clients, I have never seen a convoluted SELECT statement broken up into 2 or more pieces for effeciency reasons. I stumbled on the idea around 9 years ago trying to squeeze a few more drops of speed from a 386-based system running InfoQuest. I saw the 95% wasted translates based on my original example and got a 50% increase in speed running a process that had to run about 15 times per user per day. Almost won the Nobel Prize with that client. Not that I've seen everything or more than all, but in my 25+ years of pick covering about 40 different platforms and environments I have never seen a SELECT statement broken up. Thanks. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 11, 2004 6:13 AM Subject: Re: [U2] PICK Assembler Language Just because we have a quadrillion microseconds to play with, instead of a 50 millisecond timeslice, doesn't mean we have to waste them. A few moments to consider performance, done once in the development cycle, will pay benefits every time a more efficient program is run. I have to believe that Adm Grace Hopper is smiling down on you both. Regards, Charlie Noah [EMAIL PROTECTED] (mailto:[EMAIL PROTECTED]) writes: -Original Message- snip I can't imagine anyone on this forum having to break up this sentence likewise on the current platforms. On this client's older MCD, it truly makes a difference. /snip - Ah, Mark..I do this all the time for the performance reasons mentioned :) Rich Taylor | Senior Programmer/Analyst| VERTIS 250 W. Pratt Street | Baltimore, MD 21201 P 410.361.8688 | F 410.528.0319 [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. The more they complicate the plumbing the easier it is to stop up the drain - Montgomery Scott NCC-1701 --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/ The information contained in this email is strictly confidential and for the use of the addressee only, unless otherwise indicated. If you are not the intended recipient, please do not read, copy, use or disclose to others this message or any attachment. Please also notify the sender by replying to this email or by telephone +44 (0)20 7896 0011 and then delete the email and any copies of it. Opinions, conclusions (etc.) that do not relate to the official business of this company shall be understood as neither given nor endorsed by it. IG Markets Limited and IG Index Plc are authorised and regulated by the Financial Services Authority and, in Australia, by the Australian Securities and Investments Commission. [demime 1.01d removed an attachment of type application/ms-tnef which had a name of winmail.dat] --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list [EMAIL PROTECTED] To unsubscribe please visit http
