Pro*C Performance Problem

From: <BillD_at_FarandAway.com>
Date: 11 Jan 2000 19:18:10 GMT
Message-ID: <85fvli$1hu$1_at_news.netmar.com>

[Quoted] [Quoted] 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...

EXEC SQL DECLARE BigCursor CURSOR FOR
  SELECT Column1, Column2, Column3
  FROM ParentTable
[Quoted]   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.sqlcode == 1403) /* EOF */
      break;     

[Quoted] /* Insert Archive Rows */

    EXEC SQL

[Quoted]       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
[Quoted]       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

[Quoted]       DELETE FROM ChildTable1
        WHERE Column1 = :HldColumn1
          AND Column2 = :HldColumn2
          AND Column3 = :HldColumn3;

/* etc... for other children. Then: */
    EXEC SQL

[Quoted]       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);
}

[Quoted] 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

Received on Tue Jan 11 2000 - 20:18:10 CET