Sql insert in child and parent tables

Question

I have two tables with many relationships. (Oracle)

**Table: PARENT** Field: A (PK) Field: B Field: C1 Field: C2 Field: C3 Field: C4 Field: C5 **Table CHILD** Field: A (PK) (FK to PARENT.A) Field: D (PK) Field: E 

Records inserted into child and parent tables are inserted at the same time.

What interests me is the most efficient way to do this.

Currently, there is one stored procedure called by the calling application for the entire insertion phase. The stored procedure currently has the following link:

 Field: A Field: B Field: C (dilimited string) Field: D (dilimited string) Field: E (dilimited string) 

The procedure goes through C from the input and stores each of the values ​​in an array. It then uses this array along with A and B from the input to insert into the PARENT table.

Then it uses A from the input and goes through D and E from the input and inserts into the CHILD table for each element in the agreed rows.

It will be triggered up to 3 million times a day. This should be as effective as possible.

How effective is the loss of multiple SP calls, not just one?

All loops to take care of thickened strings seem like a lot of work!

I thought that the calling application could make separate SP calls for each entry in CHILD. However, how can I guarantee that somehow the insert in CHILD will not happen before the insert in PARENT ... and this will be more calls to stored procedures. (in many cases there are no child records to insert when they are usually less than 10, but maybe as many as 50)

I am also opening another way to get the information in extended string C.

Is there something more efficient than a while loop to get information from strength strings?

I did not write SP. I was asked to make a small modification and make it more efficient, if possible.

Any ideas?

Notes:

I simplified the tables, in fact there are 10 elements in the allowed row of C not 5, there are two more more stretched rows like C that are inserted into the PARENT table. The tables also have several fields than shown

Entries are deleted after 30 days.

Answer 1

There are a couple of things.

First, if you loop a row with delimiters to place similar elements in similar columns, you will likely have to reorganize your tables so that they are more normal. For example, if C is a list of delimited phone numbers, and columns C1 - C5 are equal to phone1 - phone5 , you probably should have a separate child table called phone . It depends on the use case, but poses me as a potential future problem (i.e. Knowledge of the delimiter). If the delimited line contains dissociated data (phone number, city, name, etc.), please make separate input parameters for each individual data element. This is an even bigger potential problem (because if the order of the records is important, you are screwed, period).

You're right, interlacing lines with delimiters is a lot of work. Not necessarily the best way to do this, if only, perhaps if your RDBMS has some kind of built-in split function or something (or you can call an external function). I would rather avoid this if I could, and call the stored procedure child . It should be for every child, but it is actually better anyway - not necessarily performance, but for conceptualization and future support.

How to prevent child string setting without parent ? Use a foreign key constraint. If the restriction is violated, it is the caller’s error, not the DB.

Well, some of them require significant changes for the SP, and some require changes to the basic structure of the table (which, if they were initially set up correctly, should be mostly transparent to users). But this is what I would try ...

Oh, and please tell me that everything works under the control of commitment ...

Answer 2

The most efficient way to handle this is likely to be to use user-defined data types to pass lists of values ​​into a stored procedure as arrays, rather than as delimited strings. Something like that:

 CREATE TYPE r_child IS OBJECT (a NUMBER, d VARCHAR2(20), e VARCHAR2(20)); CREATE TYPE nt_child AS TABLE OF r_child; CREATE TYPE nt_c AS TABLE OF VARCHAR2(20); CREATE PROCEDURE insert_data( p_a NUMBER, p_b VARCHAR2, p_c nt_c, p_child nt_child ) AS v_parent parent%ROWTYPE; i NUMBER; BEGIN v_parent.a := p_a; v_parent.b := p_a; FOR i IN p_c.FIRST .. p_c.LAST LOOP CASE i WHEN 1 THEN v_parent.c1 := p_c(i); WHEN 2 THEN v_parent.c2 := p_c(i); WHEN 3 THEN v_parent.c3 := p_c(i); WHEN 4 THEN v_parent.c4 := p_c(i); WHEN 5 THEN v_parent.c5 := p_c(i); END CASE; END LOOP; INSERT INTO parent( a, b, c1, c2, c3, c4, c5 ) VALUES v_parent; FORALL i IN p_child.FIRST .. p_child.LAST INSERT INTO child( a, d, e ) VALUES ( p_a, p_child(i).d, p_child(i).e ); END insert_data; 

Wrapping inserts in a package will not add significant runtime if the package is well designed. This includes the use of bulk inserts (as shown here, where I used forall ), and the use of data structures that the database can read naturally, instead of encoding and decoding data (as you now do with delimiters).