Re: Pro*C Performance Problem
Date: Wed, 12 Jan 2000 08:00:29 -0500
Message-ID: <387C7AED.787CF0E6_at_Unforgetable.com>
BillD_at_FarandAway.com 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...
>
> 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.sqlcode == 1403) /* EOF */
> break;
>
> /* Insert Archive Rows */
> EXEC SQL
> 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
> 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
> DELETE FROM ChildTable1
> WHERE Column1 = :HldColumn1
> AND Column2 = :HldColumn2
> AND Column3 = :HldColumn3;
> /* etc... for other children. Then: */
> EXEC SQL
> 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);
> }
>
> 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
Why not try fetching into an array and then just sweep through the array and do the inserts? That should help some.
...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ...................................................... ......................................................Received on Wed Jan 12 2000 - 14:00:29 CET