Какой самый быстрый способ вставить данные в PostgreSQL в таблицу N:M?

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

Ситуация в каждой вставке, мне нужно узнать id токена и для этого я ставлю SELECT внутри вставки.

Что может быть лучшим решением для максимального ускорения вставки в таблицу textBlockHasToken, включая SQL и Java?

Редактировать: на данный момент это работает, но у меня большой поток данных. Некоторые числа, 100 000 разных строк в таблице токенов и 2 миллиона строк в textBlockHasToken.

У меня есть эти три таблицы и две вставки SQL:

-- Text blocks of the pages
CREATE TABLE textBlock(
    id                 INTEGER    PRIMARY KEY,
    pageId             INTEGER    REFERENCES page,
    text               VARCHAR    NOT NULL, 
    position           INTEGER    NOT NULL  
);

-- Tokens in the text blocks
CREATE TABLE token(
    id                 INTEGER    PRIMARY KEY,  
    text               VARCHAR    NOT NULL, 
    charType           VARCHAR    NOT NULL,
    grammar            VARCHAR    NOT NULL,
    category           VARCHAR[]  NOT NULL, 
    stopWord           BOOLEAN    NOT NULL    DEFAULT false,
    UNIQUE(text)
);

-- N:N relationship between textblock and token 
CREATE TABLE textBlockHasToken(
    textBlockId        INTEGER    REFERENCES textBlock NOT NULL,
    tokenId            INTEGER    REFERENCES token NOT NULL,
    sentence           INTEGER    NOT NULL,
    position           INTEGER    NOT NULL
);

-- Insert the token
INSERT INTO token(id,text,charType,grammar,category)
VALUES(nextval('tokenIdSqc'),?,?,?,?);

-- Insert in relationship in N:M
INSERT INTO textBlockHasToken(textblockId,tokenId,sentence,position)
VALUES(?,(SELECT id FROM token WHERE text = ?),?,?);

postgresql java sql
person Renato Dinhani    schedule 31.10.2011    source источник
comment
см. также stackoverflow.com/questions/4348511/   -  person Cito    schedule 01.11.2011

Ответы (4)


arrow_upward
5
arrow_downward

Лучшее, что вы можете сделать, это убедиться, что все переменные WHERE имеют связанные с ними индексы. Они автоматически создаются для первичных ключей; убедитесь, что столбцы внешнего ключа также имеют индексы.

Другим соображением является пакетная обработка запросов. Не делайте обход сети для каждого INSERT; объединить их в куски и зафиксировать каждый кусок. Это будет означать меньшее количество циклов передачи по сети и управляемые журналы транзакций.

person duffymo    schedule 31.10.2011

arrow_upward
3
arrow_downward

  • не используйте зарезервированные слова в качестве идентификатора (текст, дата)
  • (пожалуйста, не используйте MixedCaseIdentifiers)
  • ваше определение таблицы НЕ РАБОТАЕТ. Тот, что ниже, работает.
-- Text blocks of the pages

DROP TABLE tmp.textblock CASCADE;
CREATE TABLE tmp.textblock
    ( id                 INTEGER    PRIMARY KEY
    , pageid             INTEGER    -- REFERENCES page
    , ztext               VARCHAR    NOT NULL
    , position           INTEGER    NOT NULL
    );

-- Tokens in the text blocks
DROP TABLE tmp.token CASCADE;
DROP SEQUENCE tmp.tokenidsqc;
CREATE SEQUENCE tmp.tokenidsqc;

CREATE TABLE tmp.token
    ( id                 INTEGER    PRIMARY KEY DEFAULT nextval('tmp.tokenidsqc')
    , ztext               VARCHAR    NOT NULL
    , chartype           VARCHAR    NOT NULL
    , grammar            VARCHAR    NOT NULL
    , category           VARCHAR  NOT NULL
    , stopword           BOOLEAN    NOT NULL    DEFAULT false
    , UNIQUE(ztext)
    );

-- N:N relationship between textblock and token 
DROP TABLE tmp.textblockhastoken CASCADE;
CREATE TABLE tmp.textblockhastoken
    ( textblockid        INTEGER    NOT NULL REFERENCES tmp.textblock(id)
    , tokenid            INTEGER    NOT NULL REFERENCES tmp.token(id)
    , sentence           INTEGER    NOT NULL
    , position           INTEGER    NOT NULL
    );

-- Insert the token
INSERT INTO tmp.token(ztext,chartype,grammar,category)
VALUES('foo' , 'T' , 'a', 'a' ), ('bar' , 'T' , 'a', 'a' );

SELECT * FROM tmp.token;
person wildplasser    schedule 31.10.2011
comment
Рекомендуется избегать использования зарезервированного слова. для идентификаторов. Однако text не является зарезервированным в PostgreSQL и во всех стандартах SQL. (Я бы все равно не использовал его, чтобы избежать путаницы, так как это также имя типа данных.) - person Erwin Brandstetter; 01.11.2011
comment
В любом случае, это не очень описательно. Лучшее имя было бы более самодокументируемым. - person duffymo; 01.11.2011

arrow_upward
1
arrow_downward

Я полностью согласен с duffymo, но я бы также добавил функция COPY в Postgres - это вкусный способ импорта данных.

Конечно, это не всегда возможно сделать, особенно из кода. вот почему я также согласен с duffymo, если вам НУЖНО сделать это из кода на отдельной машине, сделайте то, что сказал duffymo.

person zie    schedule 31.10.2011

arrow_upward
1
arrow_downward

duffymo уже упоминал пакетные обновления. На всякий случай добавлю:

  • Вы экспериментировали/измеряли разные размеры одной транзакции?
  • Вы уже используете и повторно используете подготовленные операторы?
  • Вы экспериментировали/измеряли с «INSERT INTO token... RETURNING id» и вводили его непосредственно во второй оператор вставки?

Но ничего не зная о существующем коде, это все очень похоже на угадывание хрустального шара.

person A.H.    schedule 31.10.2011