Date: Thu, 29 Jul 1999 14:51:26 -0700
Reply-To: "William W. Viergever" <wwvierg@IBM.NET>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "William W. Viergever" <wwvierg@IBM.NET>
Subject: Re: FW: Proc SQL Performance takes a hit from Nested Sub Query
In-Reply-To: <851200E65752D211BC89006008CE53537772CD@Z9999021.core.afcc. com>
Content-Type: text/plain; charset="us-ascii"
Yah, I agree.
This is too much like my kids: "I know a secret, but I won't tell you"....
besides, the SAS-L help wasn't proprietary! <g>.
Later
At 02:56 PM 7/29/99 -0500, Lambert, Bob wrote:
>Why don't we thusly dispense with the proprietary bullshit. Show me the
>code.
>
>Bob Lambert
>
>> -----Original Message-----
>> From: Ashiru, Babatunde [SMTP:AshiruB@WHITEOAKSEMI.COM]
>> Sent: Wednesday, July 28, 1999 11:58 AM
>> Subject: Re: Proc SQL Performance takes a hit from Nested Sub Query
>>
>> Hello All,
>>
>> Thanks to everybody that took the time-out to respond to my initial
>> posting.
>> While all tips given by all respondents were invaluable they, however,
>> couldn't be applied successfully to solve the unique oracle table
>> conglomeration in my shop. I resolved the problem by writing a rather
>> tarse
>> algorithm that not only eliminated the nested sub-query but introduced
>> marginal nested loops to circumvent oracle limitation, i.e., less than 255
>> list values allowed in the IN operator. In the event of list values equal
>> to or greater than 255, my algorithm breaks the list down to multiples of
>> 254 values and loops through the IN operator per multiple. This way,
>> Oracle
>> seems pleased while Proc SQL runs much faster than before, i.e., a
>> SQLPassThru that initially runs for 4+ hours now only runs for less than
>> 25
>> minutes, and that's a significant improvement here in my shop. I will be
>> more than glad to share the algorithm with any interested SAS-Ler,
>> however,
>> portion of the complete code is deemed proprietary thusly, I can only
>> disseminate segments of the algorithm.
>>
>> Best Regards,
>>
>> Babatunde R Ashiru
>>
>> White Oak Semiconductor
>> Voice: (804)952-7944
>> Fax: (804)952-7902
>> Pager: 1(800)759-8888 or http://www.skytel.com/paging/1way.html
>> PIN#: 1589371
>> E-Mail: ashirub@whiteoaksemi.com
>> E-Page: 1589371@skytel.com*
>> *Strings sent via the E-Page must be less than or equal to a max of 500
>> characters.
>> " I suffer premonition, confirmed suspicions of all the holocaust to
>> come..." Roger Waters
----------------------------------------------------------------------------
William W. Viergever Voice : (916) 483-8398
Viergever & Associates Fax : (916) 486-1488
A SAS Institute Quality Partner (USA) E-mail : wwvierg@ibm.net
Sacramento, CA 95825
"Age is a question of mind over matter. If you don't mind, it don't matter."
- Satchel Paige -
"Reality is merely an illusion, albeit a very persistent one."
- Albert Einstein -
----------------------------------------------------------------------------
| 
