Как повысить производительность при вставке более 40 тыс. строк

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;

Моя попытка:

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;

В настоящее время я вставляю несколько записей, и это дает точные результаты, но на самом деле есть записи 40k+, поэтому я считаю, что это также приведет к проблемам с производительностью. Есть ли какой-нибудь способ вставить записи быстрее, потому что я напишу процедуру для этой вставки? Есть ли какой-либо другой способ, с помощью которого я могу написать запрос на вставку внутри процедуры?

Question 2

На моем ноутбуке мы можем достаточно легко масштабировать ваш тест

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>

Таким образом, около 3 секунд для 800 000 строк. Я думаю, с тобой все будет в порядке :-)

Если поисковые запросы всегда верны и ключ сохранен, вы можете получить некоторую выгоду от преобразования в соединение, например

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

На моем ноутбуке мы можем достаточно легко масштабировать ваш тест

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>

Таким образом, около 3 секунд для 800 000 строк. Я думаю, с тобой все будет в порядке :-)

Если поисковые запросы всегда верны и ключ сохранен, вы можете получить некоторую выгоду от преобразования в соединение, например

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

Спасибо за это. Я исходил из предположения, что слияние происходит намного быстрее, чем вставка. Но, как вы показали на примере, который мне помог

Как повысить производительность при вставке более 40 тыс. строк

Вопрос

Лучший ответ

На других языках

Эта страница на других языках

Популярное в этой категории

Популярные вопросы в этой категории