Re: Pro*C Performance Problem

A copy of this was sent to BillD_at_FarandAway.com [Quoted] (if that email address didn't require changing) On 11 Jan 2000 19:18:10 GMT, you wrote:

>I'm looking for a way to make my logic more efficient for a nightly
>batch process that moves rows from active to archive tables.
>Here is what I'm doing...
>

the fastest way will be to size your rollback appropriately for this and do it in one fell swoop -- eg:

  insert into archiveParentTable
   select * from parentTable
  where statusCode in ( 'X', 'Y', 'Z' );

  insert into archiveChildTable1
   select * from childTable1 where (column1,column2,column3) in     ( select column1,column2,column3 from parentTable

       where statusCode in ( 'X', 'Y', 'Z' ) );

   delete from childTable1 where (column1,column2,column3) in     ( select column1,column2,column3 from parentTable

       where statusCode in ( 'X', 'Y', 'Z' ) );

  delete from parentTable
  where statusCode in ( 'X', 'Y', 'Z' );

  commit;

Another way would be to change your host variables HldColumn1 .. 3 from SCALARS (assuming they are) into Arrays. You would Array Fetch some rows and then Array process them. Lets say HdlColumn1 .. 3 are defined as:

int HldColumn1[100];
int HldColumn2[100];
int HldColumn3[100];

/* you can code something like: */

>
>EXEC SQL DECLARE BigCursor CURSOR FOR
> SELECT Column1, Column2, Column3
> FROM ParentTable
> WHERE StatusCode IN ('X','Y','Z');
>
>main()
>{
> EXEC ORACLE OPTION (HOLD_CURSOR=YES);
> EXEC ORACLE OPTION (RELEASE_CURSOR=NO);
>
> /* Connect to database */
> EXEC SQL CONNECT :UserName
> IDENTIFIED BY :Password;
>
> /* open the cursor */
> EXEC SQL OPEN BigCursor;
>
> /* Fetch matching rows. */
> while(1)
> {
> EXEC SQL FETCH BigCursor INTO :HldColumn1, :HldColumn2, :HldColumn3;

    if(sqlca.sqlerrd[2] == 0) /* EOF */
      break;

    nRows = sqlca.sqlerrd[2];
>
> /* Insert Archive Rows */

    EXEC SQL for :nRows
> INSERT INTO ArchiveParentTable
> SELECT *
> FROM ParentTable
> WHERE Column1 = :HldColumn1
> AND Column2 = :HldColumn2
> AND Column3 = :HldColumn3;
> /* Do the same thing for all of the other tables that have rows to
> be archived. */

    EXEC SQL for :nRows
> INSERT INTO ArchiveChildTable1
> SELECT *
> FROM ChildTable1
> WHERE Column1 = :HldColumn1
> AND Column2 = :HldColumn2
> AND Column3 = :HldColumn3;
> /* Same for other children */
>
> /* Delete the rows from the active tables, children first, then parent.
>*/

    EXEC SQL for :nRows
> DELETE FROM ChildTable1
> WHERE Column1 = :HldColumn1
> AND Column2 = :HldColumn2
> AND Column3 = :HldColumn3;
> /* etc... for other children. Then: */

    EXEC SQL for :nRows
> DELETE FROM ParentTable
> WHERE Column1 = :HldColumn1
> AND Column2 = :HldColumn2
> AND Column3 = :HldColumn3;
> EXEC SQL COMMIT WORK;
> EXEC SQL CLOSE BigCursor;
> EXEC SQL OPEN BigCursor;
> }
> EXEC ORACLE OPTION (RELEASE_CURSOR=YES);
>
> EXEC SQL CLOSE BigCursor;
>
> /* Disconnect from database */
> EXEC SQL COMMIT WORK RELEASE;
>
> exit(0);
>}
>

That'll fetch upto 100 sets of columns1..3 sqlca.sqlerrd[2] tells us how many it fetched. We then ARRAY execute the insert for that many rows into the archive tables and then ARRAY delete from the child / parent tables....

Yet another option would be to run >1 copy of the program. Parallel process it. Perhaps instead of saying "statusCode in ( 'x','y','z' )" you said: "statusCode = :bind_variable" and passed in the status code. You could then run 3 copies of this, one doing X, one doing Y, one doing Z and so on...

>I first had the logic so I didn't close and reopen BigCursor after the commit
>work but I was getting error ORA-01555: snapshot too old: rollback segment
>number 5 with name "Whatever" too small.
>So, I added logic to count the number of commits and close and reopen the
>cursor after 100 commits but I still got the same error so I ended up with
>this logic which works but seems painfully slow.
>It seems to me that I'm not doing this the fastest way. Any ideas on how I
>could speed this up? Thanks for your help.
>
>I use Oracle 7.3.4 and the matching Pro*C which I think is 2.3, maybe 2.2.
>Mode=oracle, dbms=v7.
>
>
> ----- Posted via NewsOne.Net: Free Usenet News via the Web -----
> ----- http://newsone.net/ -- Discussions on every subject. -----
> NewsOne.Net prohibits users from posting spam. If this or other posts
>made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

[Quoted] Opinions are mine and do not necessarily reflect those of Oracle Corporation