CODEWITHSUNDEEP
databaseoracle-databaseperformanceplsqlinsert-into
KI1
KI1

Reputation: 929

Reducing Database calls in application and improve data insertions

I am using PL/SQL developer and the database I am using is Oracle 11G R2 database with Visual Studio 2013. I use ODP.net as well.

Currently i load data for a set of Students using a stored proc e.g ClassNumber, Ethnicity etc. The data I load is dependent on what has been requested by the end user.

After i have loaded the data for the respective students I would like to log the data for the requested students. Now the only way i know how to do it is to do a database call for each requested student by passing in a single StudentBioId and pass in the array of StudentBioIdentifiers. A StudentBioIdentifier is an identifier relating to the student information e.g ClassNumber, Ethnicity etc.

My original idea was to pass an array of StudentIds and array of StudentBioIds into the stored procedure but i wouldn't be able to relate the StudentBioId back to the Student because Student A may have Ethnicity value of XYZ but Student B could have ABC Ethnicity value. How can i work out which StudentBioId belongs to that Student without doing heavy select queries?

My goal is to make the data insertions faster, insert data correctly and potentially reduce database calls.

So currently the code in my stored proc looks like this:

CREATE OR REPLACE TYPE StudentBioIdArray IS VARRAY(100) OF NUMBER(2);

 PROCEDURE InsertDummyStudentAuditData(studentID IN NUMBER, studentBioIds IN    StudentBioIdArray) IS
    BEGIN
      IF studentBioIds IS NOT NULL AND studentBioIds.Count > 0 THEN
         FORALL i IN 1..studentBioIds.COUNT
             INSERT INTO audit_table(Id, Student_id, StudentBioId) 
               VALUES  (-1, studentID, studentBioIds(i));
       END IF;
     END InsertDummyStudentAuditData;

Questions

-Even if i call the stored procedure for each student would it still be faster?

-Is there a better alternative?

Upvotes: 1

Views: 73

Answers (1)

Dave Costa
Dave Costa

Reputation: 48121

You ask:

Even if i call the stored procedure for each student would it still be faster?

Faster than what? You've described two approaches - calling the procedure once for all students, and calling it once for each. This question seems to imply that there is a third option that you think is even slower, but you don't know say that third option is.

You seem to describe two stored procedures - one to enter the data and another to log audit records. Do you mean that the first procedure calls the other one, or that the application first calls one, then the other? If the application calls both directly, I think that is bad design - not just for performance but also from a transactional consistency point of view.

The audit data should be logged essentially as a side effect of the main inserts, and either committed or rolled back along with it; otherwise it's not really reliable. If your first procedure calls the second one, I think that should be fine, and probably calling it once per student is reasonable. Another way to write the procedure would be to pass a single array of a nested table type that includes both a StudentID and a StudentBioID in each row; this could simply be bulk-inserted into the audit table. But for the kind of volumes I imagine you are doing, I doubt that doing one insert per student, all within the same PL/SQL call, would be significant drag on performance.

Another approach is to use triggers to automatically insert into the audit table, probably for every insert into the StudentBio table. This is not uncommon for audit data, but does imply one recursive insert for each insert into the main table, which would likely be somewhat slower. Again, whether the slowness is significant depends on your volumes and expected response times.

Upvotes: 1

Related Questions