Преобразование данных из промежуточной таблицы в итоговую таблицу

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

INSERT INTO foo
SELECT
convert(decimal(10,2),col1),
convert(date, col2),
convert(decimal(10,2),col3),
convert(int, col4),
convert(decimal(10,2),col5)
...
from foo_staging

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

РЕДАКТИРОВАТЬ: я использую Bulkinsert в промежуточной таблице, и файлы действительно большие.


sql-server staging
person McNade    schedule 04.02.2016    source источник
comment
Зачем нужны две одинаковые таблицы с разными типами данных   -  person TheGameiswar    schedule 04.02.2016
comment
Данные, которые я получаю из источника, действительно перепутаны во многих отношениях (например, в числовых значениях - в первой позиции или в конце, текстовый квалификатор, экранированные символы, которые необходимо заменить исходными символами...), поэтому я подумал, что размещение испорченных данных в промежуточной таблице и после очистки в окончательной таблице было бы более эффективным, чем операции ввода-вывода с исходными данными.   -  person McNade    schedule 04.02.2016
comment
Можете ли вы иметь триггер в промежуточной таблице, который преобразует данные, как указано выше, и вставляет их в итоговую таблицу каждый раз, когда вставляется запись?   -  person TheGameiswar    schedule 04.02.2016
comment
Я не уверен, есть ли преимущества при использовании триггера, я должен был упомянуть, что я сразу вставляю большие куски данных (40 млн строк) в промежуточную таблицу. Я извиняюсь, собираюсь отредактировать это.   -  person McNade    schedule 04.02.2016

Ответы (1)


arrow_upward
0
arrow_downward

Несколько быстрых мыслей, так как здесь нет ни одного правильного ответа:

  1. Загрузить данные в staging
  2. Очистить/преобразовать путем копирования во вторую промежуточную таблицу, определенную с использованием нужных типов данных. Хорошие данные скопированы, плохие оставлены
  3. Скопируйте данные из «чистой» таблицы в «живую» таблицу

Для копирования данных из таблицы в таблицу, пожалуй, самым быстрым вариантом является использование секционирования таблицы. Это может стать сложным, вам нужно прочитать концепцию и понять, применима ли она к вашим данным или нет. (Кроме того, вам понадобится версия SQL Server Enterprise.)

Альтернативой является «разбиение» копируемых данных на части, разбивая их (скажем) на наборы из 10 000 строк или произвольное количество и повторяя цикл до тех пор, пока вы не закончите. Это можно сделать с помощью синтаксиса TOP N, вычислив даты или диапазоны ключей на основе загружаемых данных или, возможно, даже используя трюки с row_number().

(Еще раз обратите внимание, что доступность этих функций зависит от используемой вами версии/редакции SQL.)

person Philip Kelley    schedule 04.02.2016
comment
Большое тебе спасибо! Я мог улучшить производительность, потому что разбил данные на фрагменты, используя измененную версию это решение. - person McNade; 24.03.2016