Блог

Я делаю некоторые вещи в Excel VBA, где мне нужно прочитать около 850 или более строк из листа Excel и обновить их в существующей таблице в oracle 11g. К счастью, меня интересуют всего 2 столбца.

Единственный способ, которым я сейчас занимаюсь, — это просто перебирать диапазон в Excel и создавать по одному UPDATE myTable SET Temp = Array(i, 2) WHERE id = Array(i,1) для каждого поля в массиве.

Теперь… я вполне осознаю, что это не так быстро и не элегантно, и именно поэтому я ищу советы и рекомендации по оптимизации такой вещи.

Я очень благодарен за каждый предоставленный совет.

=== ОБНОВЛЕНИЕ ===

После тестирования предложения Kacpers путем создания временных таблиц результатом является то, что при использовании Excel, где меня интересуют 2 столбца (около 850 строк), требуется около 2 минут, чтобы прочитать данные и записать их в таблицу, используя INSERT ALL метод с 850 вставками.
В другой таблице и файле Excel около 970 строк и 3 столбца, в то время как отфильтрованный я пишу около 700 строк, а stll занимает около минуты для завершения.

Последняя часть объединяется с существующей таблицей, в то время как процесс до этой реализации занимал около 35 секунд, теперь занимает 45-55 секунд.

Это все еще кажется довольно медленным методом. Особенно часть вставки.
Как я уже говорил в комментариях, у меня нет доступа к серверной системе хранения, поэтому загрузка или создание CSV для импорта данных не является вариантом.

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

Сначала, пожалуйста, импортируйте свой excel в таблицу или создайте внешнюю таблицу на основе данных csv из файла xls. Допустим, вы импортировали данные в таблицу t1 со столбцами c1, c2

Затем вы можете выполнить операцию слияния:

 merge into myTable mt
using t1
on (mt.id = t1.c1)
when matched then update set
Temp = c2;
  

здесь вы можете дополнительно when not matched then insert указать, нужно ли вставлять строки, которых нет в myTable;

Вы должны использовать подготовленные инструкции с переменными привязки, подобными этому (не проверено):

 Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = con ' your "ADODB.Connection" object
cmd.CommandType = adCmdText
cmd.CommandText = "UPDATE myTable SET Temp = ? WHERE id = ?"    
cmd.Parameters.Append cmd.CreateParameter("newVal", adVarChar, adParamInput, 10)
cmd.Parameters.Append cmd.CreateParameter("id", adBigInt, adParamInput)

con.BeginTrans
For i = 1 To 850
   cmd.Parameters("newVal").Value = Array(i, 2)
   cmd.Parameters("id").Value = Array(i, 1)
   cmd.Execute
Next
con.CommitTrans
  

Важно: cmd.Parameters.Append должно выполняться только один раз вне цикла. В противном случае вы ничего не получите.

Скорее всего, это будет не так быстро, как ВСТАВКА прямого пути (например, внешняя таблица), но производительность должна быть достаточной.

Хорошо, итак, я использовал половину решения, предложенного Каспером, то есть я ввел временную таблицу, которую я сначала заполнил, используя ~ 850 вставок в вопросе: INSERT ALL INSERT ... END Однако выполнение решения заняло около 1-2 минут.

В другой теме предлагалось «объединить» мои данные в одну команду insert и выполнить ее только один раз.
Это казалось наиболее эффективным методом, поэтому моя вставка в настоящее время выглядит так:

 INSERT INTO myTempTable (id, temp) SELECT 'data1', 'data2' from dual
UNION ALL SELECT 'data3', 'data4' from dual
UNION ALL SELECT 'data5', 'data6' from dual
...
  

В результате для выполнения всех вставок 850 требуется всего около секунды, а обновление myTable путем присоединения продлевается в среднем всего на 5 секунд.

Спасибо всем за участие и за предоставленные полезные советы!