Re: Using DBMS_JOB and DBMS_PIPE to mimic multithreaded PL/SQL application
From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 18 Nov 2003 16:12:09 -0800
Message-ID: <1069200751.632226_at_yasure>
>
>
> We currently have a batch process that takes 5 hours to process around
> 5000 records. Not very efficient (CPU not used effectively), but the
> processing logic is very complex and we've already made a lot of gain
> by fixing the logic and SQL. However, we are performing the same
> logic on all 5000 records, so it stands to reason that I can increase
> performance if I can process more than one at a time.
>
> The way I will do it is:
> - Select 5000 records using complex SQL
> - Insert these records into a table with the addition of a batch
> number (ie. records 1-100, batch number is 1000; records 101-200,
> batch number is 1001; etc)
> - Spawn a child thread or job to process one batch number of 100
> records.
Date: Tue, 18 Nov 2003 16:12:09 -0800
Message-ID: <1069200751.632226_at_yasure>
Paul Jones wrote:
>>BTW, why are you trying to do this? >>
>
>
> We currently have a batch process that takes 5 hours to process around
> 5000 records. Not very efficient (CPU not used effectively), but the
> processing logic is very complex and we've already made a lot of gain
> by fixing the logic and SQL. However, we are performing the same
> logic on all 5000 records, so it stands to reason that I can increase
> performance if I can process more than one at a time.
>
> The way I will do it is:
> - Select 5000 records using complex SQL
> - Insert these records into a table with the addition of a batch
> number (ie. records 1-100, batch number is 1000; records 101-200,
> batch number is 1001; etc)
> - Spawn a child thread or job to process one batch number of 100
> records.
While acknowledging that I knowing little or nothing of what you are trying to do the above looks absolutely preposterous.
What will your organization pay into a scholarship fund if one of my students gets this down to less than one hour? How about less than 30 minutes? How about less than 10 minutes?
-- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan_at_x.washington.edu (replace 'x' with a 'u' to reply)Received on Wed Nov 19 2003 - 01:12:09 CET