Comment augmenter les performances lors de l'insertion de plus de 40k+ lignes

Question 1

CREATE TABLE IF NOT EXISTS new_details_staging 
(
    e_id         NUMBER(10),
    e_name       VARCHAR2(30),
    portal_desc  VARCHAR2(50),
    risk_dec     VARCHAR2(50),
    CONSTRAINT pk_new_details_staging PRIMARY KEY (e_id)
);

INSERT IGNORE INTO new_details_staging 
VALUES (11, 'A', 'AA', 'High');

INSERT IGNORE INTO new_details_staging 
VALUES (22, 'B', 'BB', 'Low');

CREATE TABLE IF NOT EXISTS lookup_ref 
(
    ref_id       NUMBER(10),
    ref_typ      VARCHAR2(30),
    ref_typ_desc VARCHAR2(20),
    CONSTRAINT pk_lookup_ref PRIMARY KEY (ref_id)
);

INSERT IGNORE INTO lookup_ref 
VALUES (181, 'portal', 'AA');

INSERT IGNORE INTO lookup_ref 
VALUES (182, 'portal', 'BB');

INSERT IGNORE INTO lookup_ref 
VALUES (183, 'risk', 'High');

INSERT IGNORE INTO lookup_ref 
VALUES (184, 'risk', 'Low');

CREATE TABLE IF NOT EXISTS new_details_main 
(
    e_id    NUMBER(10),
    e_name  VARCHAR2(30),
    portal  NUMBER(20),
    risk    NUMBER(20),
    CONSTRAINT pk_new_details_main PRIMARY KEY (e_id)
);

COMMIT;

Ma tentative:

INSERT IGNORE INTO new_details_main (e_id, e_name, portal,risk)
    SELECT  
        n.e_id,
        n.e_name,
        (SELECT lr.ref_id
         FROM lookup_ref lr
         WHERE lr.ref_typ = 'portal'
           AND lr.ref_typ_desc = n.portal_desc),
        (SELECT lr.ref_id
         FROM lookup_ref lr
         WHERE lr.ref_typ = 'risk'
           AND lr.ref_typ_desc = n.risk_dec)
    FROM    
        new_details_staging n;

Actuellement, je suis en insérant quelques dossiers et il donne des résultats exacts, mais en réalité, il y a 40k+ dossiers donc je crois qu'il va donner des problèmes de performances aussi. Est-il possible d'insérer les enregistrements plus vite parce que je vais écrire une procédure pour cette insertion? Est-il un autre dans lequel je peux écrire une requête d'insertion à l'intérieur de la procédure?

Question 2

Sur mon portable, on peut l'échelle de votre test assez facilement

SQL>
SQL> CREATE TABLE IF NOT EXISTS new_details_staging
  2  (
  3      e_id         NUMBER(10),
  4      e_name       VARCHAR2(30),
  5      portal_desc  VARCHAR2(50),
  6      risk_dec     VARCHAR2(50),
  7      CONSTRAINT pk_new_details_staging PRIMARY KEY (e_id)
  8  );

Table created.

SQL>
SQL> INSERT IGNORE INTO new_details_staging VALUES (11, 'A', 'AA', 'High');

1 row created.

SQL> INSERT IGNORE INTO new_details_staging  VALUES (22, 'B', 'BB', 'Low');

1 row created.

SQL>
SQL> insert into new_details_staging
  2  select e_id*500000+rownum, e_name, portal_desc, risk_dec
  3  from new_details_staging,
  4   ( select 1 from dual connect by level <= 400000 );

800000 rows created.

SQL>
SQL> CREATE TABLE IF NOT EXISTS lookup_ref
  2  (
  3      ref_id       NUMBER(10),
  4      ref_typ      VARCHAR2(30),
  5      ref_typ_desc VARCHAR2(20),
  6      CONSTRAINT pk_lookup_ref PRIMARY KEY (ref_id)
  7  );

Table created.

SQL>
SQL> INSERT IGNORE INTO lookup_ref VALUES (181, 'portal', 'AA');

1 row created.

SQL> INSERT IGNORE INTO lookup_ref VALUES (182, 'portal', 'BB');

1 row created.

SQL> INSERT IGNORE INTO lookup_ref VALUES (183, 'risk', 'High');

1 row created.

SQL> INSERT IGNORE INTO lookup_ref VALUES (184, 'risk', 'Low');

1 row created.

SQL>
SQL> CREATE TABLE IF NOT EXISTS new_details_main
  2  (
  3      e_id    NUMBER(10),
  4      e_name  VARCHAR2(30),
  5      portal  NUMBER(20),
  6      risk    NUMBER(20),
  7      CONSTRAINT pk_new_details_main PRIMARY KEY (e_id)
  8  );

Table created.

SQL>
SQL> set timing on
SQL> INSERT IGNORE INTO new_details_main (e_id, e_name, portal,risk)
  2      SELECT
  3          n.e_id,
  4          n.e_name,
  5          (SELECT lr.ref_id
  6           FROM lookup_ref lr
  7           WHERE lr.ref_typ = 'portal'
  8             AND lr.ref_typ_desc = n.portal_desc),
  9          (SELECT lr.ref_id
 10           FROM lookup_ref lr
 11           WHERE lr.ref_typ = 'risk'
 12             AND lr.ref_typ_desc = n.risk_dec)
 13      FROM
 14          new_details_staging n;

800002 rows created.

Elapsed: 00:00:02.97
SQL>
SQL>

Donc autour de 3 secondes pour 800 000 lignes. Je pense que vous serez bien :-)

Si les recherches sont toujours vraies et les principaux conserves de, vous pourriez obtenir certains avantages de la conversion d'une jointure, par exemple

SQL> set timing on
SQL> INSERT IGNORE INTO new_details_main (e_id, e_name, portal,risk)
  2      SELECT
  3          n.e_id,
  4          n.e_name,
  5          lr.ref_id,
  6          lr1.ref_id
  7      FROM
  8          new_details_staging n,
  9          lookup_ref lr,
 10          lookup_ref lr1
 11  where lr.ref_typ = 'portal'
 12  AND lr.ref_typ_desc = n.portal_desc
 13  and lr1.ref_typ = 'risk'
 14  AND lr1.ref_typ_desc = n.risk_dec ;

800002 rows created.

Elapsed: 00:00:02.64

Connor McDonald · Answer 1 · 2021-11-25T03:41:45

Sur mon portable, on peut l'échelle de votre test assez facilement

SQL>
SQL> CREATE TABLE IF NOT EXISTS new_details_staging
  2  (
  3      e_id         NUMBER(10),
  4      e_name       VARCHAR2(30),
  5      portal_desc  VARCHAR2(50),
  6      risk_dec     VARCHAR2(50),
  7      CONSTRAINT pk_new_details_staging PRIMARY KEY (e_id)
  8  );

Table created.

SQL>
SQL> INSERT IGNORE INTO new_details_staging VALUES (11, 'A', 'AA', 'High');

1 row created.

SQL> INSERT IGNORE INTO new_details_staging  VALUES (22, 'B', 'BB', 'Low');

1 row created.

SQL>
SQL> insert into new_details_staging
  2  select e_id*500000+rownum, e_name, portal_desc, risk_dec
  3  from new_details_staging,
  4   ( select 1 from dual connect by level <= 400000 );

800000 rows created.

SQL>
SQL> CREATE TABLE IF NOT EXISTS lookup_ref
  2  (
  3      ref_id       NUMBER(10),
  4      ref_typ      VARCHAR2(30),
  5      ref_typ_desc VARCHAR2(20),
  6      CONSTRAINT pk_lookup_ref PRIMARY KEY (ref_id)
  7  );

Table created.

SQL>
SQL> INSERT IGNORE INTO lookup_ref VALUES (181, 'portal', 'AA');

1 row created.

SQL> INSERT IGNORE INTO lookup_ref VALUES (182, 'portal', 'BB');

1 row created.

SQL> INSERT IGNORE INTO lookup_ref VALUES (183, 'risk', 'High');

1 row created.

SQL> INSERT IGNORE INTO lookup_ref VALUES (184, 'risk', 'Low');

1 row created.

SQL>
SQL> CREATE TABLE IF NOT EXISTS new_details_main
  2  (
  3      e_id    NUMBER(10),
  4      e_name  VARCHAR2(30),
  5      portal  NUMBER(20),
  6      risk    NUMBER(20),
  7      CONSTRAINT pk_new_details_main PRIMARY KEY (e_id)
  8  );

Table created.

SQL>
SQL> set timing on
SQL> INSERT IGNORE INTO new_details_main (e_id, e_name, portal,risk)
  2      SELECT
  3          n.e_id,
  4          n.e_name,
  5          (SELECT lr.ref_id
  6           FROM lookup_ref lr
  7           WHERE lr.ref_typ = 'portal'
  8             AND lr.ref_typ_desc = n.portal_desc),
  9          (SELECT lr.ref_id
 10           FROM lookup_ref lr
 11           WHERE lr.ref_typ = 'risk'
 12             AND lr.ref_typ_desc = n.risk_dec)
 13      FROM
 14          new_details_staging n;

800002 rows created.

Elapsed: 00:00:02.97
SQL>
SQL>

Donc autour de 3 secondes pour 800 000 lignes. Je pense que vous serez bien :-)

Si les recherches sont toujours vraies et les principaux conserves de, vous pourriez obtenir certains avantages de la conversion d'une jointure, par exemple

SQL> set timing on
SQL> INSERT IGNORE INTO new_details_main (e_id, e_name, portal,risk)
  2      SELECT
  3          n.e_id,
  4          n.e_name,
  5          lr.ref_id,
  6          lr1.ref_id
  7      FROM
  8          new_details_staging n,
  9          lookup_ref lr,
 10          lookup_ref lr1
 11  where lr.ref_typ = 'portal'
 12  AND lr.ref_typ_desc = n.portal_desc
 13  and lr1.ref_typ = 'risk'
 14  AND lr1.ref_typ_desc = n.risk_dec ;

800002 rows created.

Elapsed: 00:00:02.64

Merci pour cette. J'ai été sur l'hypothèse que la fusion est beaucoup plus rapide que l'insert. Mais comme vous l'a montré par un exemple qui m'a aidé

Comment augmenter les performances lors de l'insertion de plus de 40k+ lignes

La question

La meilleure réponse

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie