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

    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
    )

    AS

    AutoCommit = off
    Begin Transaction

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

    Same for loop for activitiesDataSet

    BATCH INSERT into AUDIT_TABLE auditInfoDataSet
    BATCH DELETE from ORIGINAL_TABLE auditInfoDataSet

    END transaction
    Commit.

    RETURN

    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
    Posts
    2,296
    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:

    create 
    or replace procedure TEST_PROC
    IS

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

    BEGIN

      
    For vDetails IN detailsDataSet loop

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

    end;

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

  3. #3
    Join Date
    Mar 2004
    Posts
    2
    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
  •