Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Unanswered: New to stored procedures, please help

    I need to create a stored procedure to solve some major performance issues I have run accross.

    Sorry my SQL is horrible.. I don't have a textbook near me and I haven't much experience outside the theoretical, but here is some pseudo code.

    CREATE PROCEDURE update_and_audit

    //Each dataset should be about 100 records that are 1KB each
    //This will be tunable in the application however

    @detailsDataSet DataSet, //how do i declare datasets
    @activitiesDataSet DataSet
    @auditInfoDataSet DataSet


    AutoCommit = off
    Begin Transaction

    for each Row in detailsDataSet {
    IF (row.rid = TABLE_DETAILS.rid){
    ELSE {
    } //END FOR

    Same for loop for activitiesDataSet

    BATCH INSERT into AUDIT_TABLE auditInfoDataSet

    END transaction


    Basically the way the program that calls this stored procedure works is it parses about 100 records from the ORIGINAL_TABLE then stores them into datasets. These Datasets are passed to the stored procedure. These 100 records have to be populated into the TABLE_ACTIVITIES and TABLE_DETAILS, then these 100 records from the ORIGINAL_TABLE need to be put into the AUDIT_TABLE and then DELETED from the ORIGINAL_TABLE.

    Currently all this is being run from a client program, one insert/update/delete at a time, and it is doing hundreds of these per minute. I'm hoping to find a way to batch these calls in 100's to see if there is a difference.

    Basically I want some help on whether or not my logic is right?

    Is there a way to batch the inserts/updates in the for loop?

    Where can I find some good documentation on batch processing, cursors and transactions for Oracle?

    Am I putting too much stuff in one transaction?

    Is sending 300kb of data into the parameter too much?

  2. #2
    Join Date
    Jul 2003
    look up docs regarding cursors.
    I think that is what you mean for a dataset.

    open each cursor (dataset) and then loop through the dataset data.

    example cursor in your declare section:
    PHP Code:

    or replace procedure TEST_PROC

      cursor detailsDataSet is
    from tableA
         where col1 in 
    (select col3 from tableB where 1 2);


    For vDetails IN detailsDataSet loop

    if vDetails.column4 'F' then
           select a from b
    end if;
    end loop;


    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Mar 2004
    Thanks for the reply.. The cursor code there does look to be very useful, but I'm not sure it will work in my case. I know I was pretty unclear.. I didn't/don't really understand the capabilities of procedures in SQL.

    Really what I'm trying to do is parse an XML clob from the DB to get a row of data and then merging this row of data into the DB. (all done in c# now)

    I was thinking if i built a Dataset/table in my programming language of all the rows to be merged, by parsing 100 XML msgs, I could reduce the I/O on the DB by doing 100 merges at once, to save on the network transfer and database connection overhead. I was hoping to pass all the data to one procedure to do all this.

    I'm not sure what I was planning on doing is possible. I found a few articles with people who had similar problems of not being able to pass an entire table to a stored procedure, and they hacked a solution using the image type and then parsing the data out of the image data.

    It is looking like it will be too hard for me to do this with my inexperience

    It looks like from some of the books and documentation I have been reading that I can just parse the XML and insert right into the DB.

    If I can do all this right on the DB, batching the merge seems less important.

    I think I'll try that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts