Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Скорость работы UPDATE в ADO
 
#1
13.04.2014 01:15:04
и снова о любимой мозоли... :)

есть файл t.xls с одним листом tn - таблица с заголовками (7 столбцов)
есть другой файл с листом mmm и таблицей из трёх столбцов

нужно изменить данные в первом файле на основе данных второго файла
в первом файле 29364 записи
во втором 216

делаю так:
только не смейтесь...


выполняется всё это из второго файла при закрытом первом
на не слишком тормозном ноуте "2 ядра - 3 гига" :) мой Excel 2003 отрабатывает вторую процедуру за 14,5сек., первую - примерно за 4,2сек.

кроме обычных "что делать?" и "кто виноват?" закрадывается сомнение - а нет ли другого варианта для апдейта, чтоб жужжало побыстрее? само собой - через ADO и SQL, а иначе неинтересно (точнее - иначе оно мне не надо)

примеры файлов не прикладываю.
вообще не знаю, что с ними делать... :(
заархивированный первый весит чуть больше 400 килобайт.
а на маленьком объеме и вопроса не будет.
но, может быть, профи и без примера подскажут чего?..

(желающим могу выслать файлики на мейл - ничего секретного там нет)
Изменено: ikki - 13.04.2014 11:16:23
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#2
13.04.2014 06:07:57
Доброе время суток Александр
Первый вариант - слишком много работы со строками и преобразованиями типов, поэтому, на мой взгляд и медленнее. Думаю такой запрос на обновление должен, по идее, быть быстрее, так как не инициализирует каждый раз обновление (естественно, нужно указать путь к файлу с листом mmm и предполагается, что названия полей в обоих таблицах одинаковые).

Код
 a.Execute "Update [tn$] As t1 Inner Join [Excel 8.0;Database=d:\path\mmm_sheet_file.xls;HDR=YES].[mmm$] As t2 On ((t1.idMat=t2.idMat) And (t1.idIzd=t2.idIzd) And (t1.normSZ=t2.normSZ)) Set t1.normDel=1"
Попробуйте сравнить. Успехов.
 
 
 
#3
13.04.2014 07:36:33
доброго утра, Андрей

Run-time error '-2147217911 (80040e09)':
Обновление невозможно. База данных или бъект доступны только для чтения.

:(
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#4
13.04.2014 07:54:14
Вариант для примера. Не должно быть по идее. Макрос в книге tn. Оба файла tn и mmm должны лежать в одной папке. Пробуйте.
 
 
 
#5
13.04.2014 08:04:41
Цитата
anvg пишет:
Пробуйте.
я, наверное, ещё не проснулся.

Run-time error '-2147217865 (80040e37)':
Объект 'mmm$' не найден ядром базы данных Microsoft Jet.
Проверьте существование объекта и правильность имени и пути.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#6
13.04.2014 08:08:05
чисто для тестирования - засунул лист mmm в ту же книгу, что и tn (на реальных данных, в своём файле)
команда
Код
    .Execute "Update [tn$] As t1 Inner Join [mmm$] As t2 " _
        & "On ((t1.idMat=t2.idMat) And (t1.idIzd=t2.idIzd) And (t1.normSZ=t2.normSZ)) " _
      & "Set t1.normDel=1"

отработала "на ура".
примерно за 0,24 сек.

пс. запускал при закрытой книге t.xls из другого файла.
Изменено: ikki - 13.04.2014 08:09:24
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#7
13.04.2014 08:14:45
anvg, признаю свою ошибку: примеры файлов нужны.
сейчас пообрезаю и выложу.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#8
13.04.2014 08:33:36
собственно вот.
в модуле файлика m.xls 4 варианта.
результаты:
Код
ADO_upd_norm1  265 
ADO_upd_norm2  250 
ADO_upd_norm3  16


ADO_upd_norm4 выдает ошибку из сообщения #3 на строке .Execute

пс. anvg, Вы так быстро отвечаете, что я тоже вынужден спешить  :)
возможно, если я сейчас позавтракаю, покурю и подумаю, то найду свой ляп...

ппс. сейчас заметил - у меня используется ссылка на Microsoft ActiveX Data Objects 2.8 Library (а не 2.0).
попробовал и так и эдак - чего-то пока ничего...
Изменено: ikki - 13.04.2014 08:47:44
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#9
13.04.2014 08:46:40
Александр. У вас в Ado_upd_norm4 лишние кавычки ' вокруг имени файла - без них обновление выполнилось.
Почему не отработал мой вариант в example не знаю. Попробовал в Excel 2010 на Win7 и на виртуальной машине в Excel 2003 под WinXP - полёт нормальный.
P. S. тестовый пример для tn на 65000 строк и mmm на 3250 отработал за 1,8 секунды. Если что, то завтра посмотрю, если вы так и не решите проблему.
 
 
 
#10
13.04.2014 08:56:39
Цитата
anvg пишет:
лишние кавычки ' вокруг имени файла
йесссссссс!!!
всё получилось!

я ж так и думал:
Цитата
ikki пишет: ...свой ляп.
пс. ай!..  :(  копировал листы туда-сюда. и случайно удалил лист tn с реальными данными.  :cry:
так что скорость работы теперь только завтра смогу проверить.
на тестовых - 0,047 против 0,016 в ADO_upd_norm3, но это, я так понимаю, в пределах шума.
хотя по сравнению с лучшей из моих ADO_upd_norm1 (0,265) - всё равно отлично!

большущее спасибо.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#11
13.04.2014 09:07:08
Цитата
anvg пишет:
Почему не отработал мой вариант в example не знаю.
нашёл.
путь к папке у меня был нехороший - с пробелами.
перетащил папку example в корень диска - замечательно отработало.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#12
13.04.2014 11:12:52
нашёл файлик в бэкапе на флешке.
протестировал на реальном объеме.
0,25 сек.

правда, запрос пришлось чуть усложнить из-за того, что одно из полей необязательное и может быть пустым:
Код
    .Execute "Update [tn$] As t1 Inner Join [Excel 8.0;Database=" _
      & ThisWorkbook.FullName & ";HDR=YES].[mmn$] As t2 " _
        & "On ((t1.idMat=t2.idMat) And (t1.idIzd=t2.idIzd) And ((t1.normSZ=t2.normSZ) " _
              & "OR ((IsNull(t1.normSZ) OR t1.normSZ='') AND (IsNull(t2.normSZ) OR t2.normSZ='')))) " _
      & "Set t1.normDel=1"

на времени выполнения это не сказалось.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#13
14.04.2014 02:50:52
Цитата
0,25 сек.
Вот и хорошо - вроде как ускорилось чуток ;)
Цитата
путь к папке у меня был нехороший - с пробелами
Пробелы вряд ли могут повлиять, а вот скобки [] или = ! могут. По крайней мере у меня: в папке с пробелами выполнилось, а в папке с именем =[] выскочило сообщение "Ошибка синтаксиса в инструкции Update".
 
 
 
#14
16.04.2014 02:09:16
а почему так сильно увеличивается размер файла?
чуть более чем в 2 раза!
что интересно - если этот увеличенный файл просто открыть в excel и сохранить - размер приходит "в норму"...
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#15
16.04.2014 08:26:22
Цитата
ikki пишет:
а почему так сильно увеличивается размер файла?
чуть более чем в 2 раза!
Можно посмотреть как "строки" записаны в ячейки. Этом может быть связано с особенностями хранения строк внутри файла .xml

Возможно, сразу после обработки в каждую ячейку записана строка. После того, как вы открываете файл и сохраняете его файл "перестраивается". Строки собираются в sharedStrings.xml а ячейки туда просто ссылаются.

p/s потом увидел что речь идет об .XLS В .XLS информация организована иначе, но механизм может быть похож
Изменено: pharmaprofi - 16.04.2014 08:30:25
 
 
 
Страницы: 1
Наверх