改进数据插入并减少数据库调用 [英] Improve data insertions and reduce database calls

问题描述

您好，



我正在使用PL / SQL开发人员，而我使用的数据库是使用Visual Studio 2013的Oracle 11G R2数据库。我使用ODP.net还有。



目前我使用存储过程加载一组学生的数据，例如ClassNumber，种族等。我加载的数据取决于所请求的数据。最终用户。



我为respctive学生加载数据后，我想记录所请求学生的数据。现在，我知道如何做的唯一方法是通过传入一个StudentBioId并传入StudentBioIdentifiers数组，为每个请求的学生进行数据库调用。 StudentBioIdentifier是与学生信息相关的标识符，例如ClassNumber，Ethnicity等。



我最初的想法是将一系列StudentIds和StudentBioIds数组传递给存储过程但是我无法将StudentBioId与学生联系回来，因为学生A可能具有XYZ的种族价值，但学生B可能具有ABC种族价值。如何在不进行大量选择查询的情况下确定哪个StudentBioId属于该学生？



我的目标是更快地插入数据，正确插入数据并可能减少数据库调用。



所以目前我的存储过程中的代码如下所示：

Hi there,

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 respctive students I would like to log the data for the requested students. Now currently 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?

推荐答案

查看以下网址了解详情。



http://www.remote-dba.net/t_op_sql_insert_speed.htm [ ^ ]

Check the below URL for details.

http://www.remote-dba.net/t_op_sql_insert_speed.htm[^]

@ praveenlob7谢谢帮助。



对于我的四个商业实体，我更容易使用FORALL进行批量插入。

@praveenlob7 thanks for the help.

For four of my Business Entities it was easier for me to do the Bulk Insert using the FORALL.

这篇关于改进数据插入并减少数据库调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！