Страницы: 1
RSS
Быстрый доступ из макроса (через ADO) к массиву пар Ключ|Значение, хранящемся в текстовом файле
 
Всем привет.
Появилась задача, которую не знаю каким способом лучше реализовать.

Проблема: нужно при работе макроса кешировать пары вида Ключ|Значение
(Имеем ключ. Если в текстовом файле уже есть пара для этого ключа, надо считать значение, а если нет, то позже из того же макроса дописать туда эту пару, когда станет известно значение). Файл предназначен для использования этих данных при следующих запусках макроса (каждый запуск макроса может немного пополнять этот файл)

Ситуация осложняется следующим:

1) количество пар, которые надо кешировать, от 200 тысяч до 2 миллионов (максимум - 5 млн)
Было бы из 10-20 тысяч, — считывал бы текстовый файл, и загонял в словарь (dictionary) или коллекцию, а потом бы из словаря / коллекции в цикле формировал текстовую строку, и перезаписывал файл.
Но при больших объемах записей, на заполнение словаря / коллекции будет уходить очень много времени, да и Excel жрёт сотни мегабайтов памяти (а таких текстовых файлов может быть несколько, для разных данных)

2) есть понимание, что нужно использовать что-то типа ADO, но ранее с ним не работал.
В каком формате хранить данные - csv, sql, mdb или ещё что-то, тоже не знаю (есть разница?)

3) всё это должно работать на любой версии Excel (2003-2019) под любой версией windows (XP-10) любой разрядности (32/64), причем работать это будет на тысячах разных компов, т.е. нужно что-то такое, что будет одинаково стабильно работать на любом компе.

4) при запуске макрос должен подключаться к файлу (но желательно не считывать всё его содержимое в память, ибо Excel не любит, когда он занимает в памяти 1 гиг), и иметь возможность через ранее установленное соединение БЫСТРО находить значение по ключу (или по массиву из 100 ключей получить 100 значений), а также сравнительно быстро дописывать новые пары Ключ|Значение (с перезаписью старых ключей, если вдруг такой ключ уже существует).

Вопросы:

1) что посоветуете использовать?
ADO? другие варианты?

2) Если ADO - это на всех компах будет работать?
Там у ConnectionString всего 2 варианта написания, для Excel версии ниже 11 и выше 11, и всё?
(одной из этих 2 строк подключения достаточно, чтобы обеспечить работу на любой версии Office?)

3) в каком формате хранить этот файл с данными?

4) реально ли, чтобы после команды добавления записи, не требовалось принудительное сохранение файла?
(чтобы если макрос внезапно завершил работу, не закрыв соединение, добавленные данные не потерялись)

5) как думаете, это совсем извращением будет, если хранить все эти данные в реестре Windows?
(записывая / считывая данные через штатные функции GetSetting / SaveSetting)
В принципе, ветку реестра можно экспортировать в файл, и импортировать на другом компе.
Просто загаживать реестр винды не хочется, хотя это было бы самым простым решением.


PS: прочитал кучу тем на форуме про использование ADO, но больше путаницы, чем понимания.
Почти во всех примерах - чтение данных с листа Excel, а с поиском записи в текстовом файле - особо примеров не нашёл.

В идеале хочу получить что-то примерно такого вида:
Код
Public Connections As Collection
' коллекция Connections содержит какие-то объекты, каждый объект - подключение к одному из файлов
' но только чтобы эта коллекция подключений не занимала кучу памяти в Excel


Function GetValue(ByVal key$, ByVal filename$) As String
    ' подключается к файлу filename$ (если подключение требуется, и ранее не установлено)
    ' ищет в нём ключ key$, и возвращает значение, соответствующее ключу
End Function

Sub SetValue(ByVal key$, ByVal value$, ByVal filename$)
    ' подключается к файлу filename$ (если подключение требуется, и ранее не установлено)
    ' и добавляет к нему запись key$|value$
End Sub
Изменено: Игорь - 28.05.2020 21:18:19
 
Доброе время суток.
Честно говоря, не рекомендовал бы на таких условиях
Цитата
Игорь написал:
всё это должно работать на любой версии Excel (2003-2019) под любой версией windows (XP-10) любой разрядности (32/64), причем работать это будет на тысячах разных компов,
не рекомендовал бы использовать ADO. Дело в том, что оно требует движка. Да, старая 32бит версия Access Jet есть на всех типах Windows, а вот ACE для 64 бит может и не быть - прецеденты были, когда требовалось установить Access runtime. Плюс, если говорить об универсальности, то на больших объёмах - это текстовые файлы и на 5 миллионов записей - те ещё тормоза будут.
Попробуйте присмотреться к использованию govert / SQLiteForExcel. Набросал небольшой примерчик (72 мегабайта в архиве) - давно не ковырял эту конструкцию. Для тех самых пяти миллионов записей скорость выборки Udf-функцией на лист для 2000 запросов по ключу у меня получилось около четырёх секунд. Если не лист, а загрузкой во временную таблицу ключей и через Join обратно выбрать - то будет на порядок быстрее.
Плюс, скорость вставки 2000 новых значений ключ/значение 0,2 секунды! Для массовой загрузки исходных данных можно использовать TablePlus или вполне себе свободный и портабельный SQLiteStudio.
Успехов.
Изменено: Андрей VG - 29.05.2020 00:04:46
 
Андрей, спасибо, но мне никак нельзя использовать дополнительные библиотеки / файлы.
Этот код будет встроен в мою надстройку для Excel, которую и без того людям непросто установить и запустить (это ж надо разблокировать файл, макросы включить),
а тут нужно ещё какие-то файлы в нужные папки устанавливать (ради одной из 500 функций программы)
Слишком сложно это будет использовать на практике (хотя, как вариант, рассмотрю, если по-другому вообще никак).

Нужно что-то, что работает на чистом VBA (без использования библиотек, которых нет в стандартной поставке Windows + Office)

Думал, раз у меня в программе много где используется CreateObject("ADODB.Stream"), и это на всех компах работает, и CreateObject("ADODB.Connection") должен везде работать.
Но сейчас погуглил, - да, много где пишут про ошибку Provider cannot be found. It may not be properly installed.

Мне не настолько скорость уж и важна, - пусть даже только 100 запросов в секунду будет обрабатываться.
Лишь бы это инициализировалось быстро (а не 20 секунд переноса текстового файла в dictionary), и работало на всех компах без доп установки чего-либо.

Может, еще какие идеи есть? Что-нибудь есть встроенное в винду / Office для подобного?
 
Игорь, Думаю Вы уже даже не балансируете, а занесли ногу над пропастью. При всем уважении к Excel, все ж его возможности ограниченны и приходит пора, когда надо отказываться от решений основанных на нем. Думаю Андрей тоже согласится с этим, мы не раз это обсуждали. Если еще и прибавить Version ZOO
Цитата
Игорь написал:
любой версии Excel (2003-2019) под любой версией windows (XP-10) любой разрядности (32/64)
то это мрак.
А, почему именно текстовый файл?
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
А, почему именно текстовый файл?
Михаил, ну а собственно как хранить 2000000 строк ключ/значение в книге формата Excel 2003.
Попробовал с текстовым файлом на этих двух миллионах строк.
Строка формировалась стандартным Write. Пример строки
"key000000001","value000000000000001it is a value of key when we will use in our work"
Чтение стандартным Input заняло чуть более 3 секунд в двумерный массив с запасом на 6000000 строк и переменной, отслеживающей заполненный конец массива. Диск SSD мог оказать влияние. Может есть и более шустрые методы.
Далее стандартная сортировка Quick Sort по первому столбцу ключа, но я не делал :)  У меня данные и так были в csv упорядоченными.
Далее функцией бинарного поиска на упорядоченном массиве по ключу. Использовалась как Udf-функция рабочего листа. Для 2000 ключей время вычисления на листе около 5 секунд.
Код
Public Function GetValue(ByVal KeyValue As String) As Variant
    Dim vLeft As Long, vRight As Long, vMiddle As Long
    If FRowCount = 0 Then InitDataArray
    vLeft = 1: vRight = FRowCount + 1
    Do Until (vRight - vLeft) = 1
        vMiddle = (vRight + vLeft) \ 2
        If StrComp(FDataArray(vMiddle, 1), KeyValue, vbBinaryCompare) > 0 Then
            vRight = vMiddle
        Else
            vLeft = vMiddle
        End If
    Loop
    If FDataArray(vLeft, 1) = KeyValue Then
        GetValue = FDataArray(vLeft, 2)
    Else
        GetValue = CVErr(XlCVError.xlErrNull)
    End If
End Function

Как любит писать тёзка - Ну, а чё?! Зато как требовалось - только стандартный феншуй :)
 
Андрей VG,  Да я сперва подумал о старых добрых DBF или  MDB, но это не решит проблему отсутствия драйверов или движка.

Что-то мне подсказывает что XP+2003 <> SSD а наоборот мееедленное железо.
Изменено: БМВ - 29.05.2020 20:30:23
По вопросам из тем форума, личку не читаю.
 
Может быть разбить файл данных (сортированный) на несколько (десятков, сотен), к этой куче создать небольшой индексный (с данными начало-конец), который можно заранее считать например в коллекцию/массив, поиском по которому быстро определяется нужный файл данных.
С чтением особых проблем думаю не будет, а вот пополнять/изменять сложнее...
Если есть быстрый способ чтения текстового файла от/до указанных строк - тогда можно не разбивать. Но как дополнять?
 
Добрый день! Лучше всего, на мой взгляд, вариант с SQLite - будет использоваться один физический файл с космическим быстродействием. Учитывая ограниченность структур хранимой информации было бы замечательно, если бы Виталий (Bedvit) разработал dll по этому поводу (С - родной интерфейс для SQLite).

Альтернатива - IndexedDB (мы с ней не работали).
Владимир
 
Андрей, спасибо, думаю такой вариант мне подойдет
Я как-то и забыл про поиск по упорядоченному массиву (привык искать перебором всех в цикле)
Учитывая, что чаще всего записей будет меньше 200-500 тысяч, должно работать быстро.
(а считывать и записывать в массив попробую через split / join, они вроде тоже быстро работают. Хотя, возможно, построчный Input и быстрее окажется)

БМВ, что мне пора отказываться от Excel, я давно уже понимаю (особенно когда речь про сложный код из 30 тысяч строк)
Но я из языков программирования знаю только VBA, потому и делаю в том в чём могу
(есть в планах заказать разработку аналогичного функционала в нормальной среде разработки, или в качестве веб-сервиса, но это все обойдется в семизначную сумму, и только ТЗ мне писать надо несколько месяцев. Потому и откладываю до поры до времени, стараясь выжать из VBA максимум возможного)
 
Игорь, если все сетуют за SQLite, но вы не хотите таскать доп. библы с файлом, может встроить их в книгу вашей же функцией? 3 dll, что в архиве Андрея весят 2,8 МБ.

Потом сохранять на диске где нибудь в AppData пользовательской папки и проверять периодически при запуске.
Как вариант.
 
Цитата
Игорь написал:
БМВ, что мне пора отказываться от Excel, я давно уже понимаю
я не совсем к этому призываю, хотя в идеале да. Оставить Excel  как интерфейс, а вот данные все ж переносить в зрелые и централизованные хранилища.
VBA - ну как вариант это в Access работает а решение не требует при этом лицензии. Только RunTime , хотя шоустопером может оказаться устаревшие ОС, я не проверял, но допущу что последние RT туда не встанут.
По вопросам из тем форума, личку не читаю.
 
Цитата
sokol92 написал:
было бы замечательно, если бы Виталий (Bedvit) разработал dll по этому поводу
Добрый вечер, Владимир.
Не уловил. По ссылке на проект SQLiteForExcel как раз и реализована такая библиотека. Может запросы несколько низко уровневые... Ну, или вы про то, что там две библиотеки: одна собственно SQLite, другая wrapper to stdcall? Не думаю, что Виталий возьмётся рекиомплировать SQLite так, чтобы была возможность использовать stdcall (по другому VBA не умеет работать с функциями в dll).
Прогнал без вывода на лист обе функции: для загруженного в массив набора и бинарным поиском и запросами к SQLite. Бинарный выиграл 0,01 секунды против 0,1 секунды у SQLite. Хотя почему, в общем-то понятно.
Изменено: Андрей VG - 29.05.2020 22:01:24
 
Всем привет! Хорошая тема. Не хотел заранее открывать карты, но немного преоткрою. Сейчас дописываю функции (предполагаю одни из быстрейших, почему отпишусь уже в той теме) по быстрому поиску строки по индексу или входящей подстроке, а также подсчет строк в файле csv/txt.
Файл читается блоками, по 16МБ для макс скорости (равными обычно кешу hdd), поэтому 4е условие условие Игоря выполняется.
Могу сделать и быструю запись в файл строки, но..давайте матчасть поднимем, вы уже знаете, я без нее никак :)
Самая быстрая обработка данных в ОЗУ. Каждый раз дергать txt файл это минимум секунды (размером в 1ГБ, я тестировал на таком), поэтому или грузим все в оперативку или тратим время на чтение и запись в файл, но оперативку экономим. Далее если  записи ключ/значение будут разного размера, нужно будет сдвигать байты файла (а не перезаписать сверху старую пару), что сложнее и в разы дольше. Если кеширование не частое можно и подождать. Мои функции не требуют сторонних продуктов, работают на win api. Можно даже без Excel. Не смотрел исходники SQLiteForExcel, не могу по ней что-то сказать определенное, но правило одно или файл медленный или быстрая ОЗУ (считываем весь файл в нее). Вообщем нужно более подробнее от Игоря, прямо по шагам, что делаем, с какими данными.
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
Игорь написал:
Этот код будет встроен в мою надстройку для Excel, которую и без того людям непросто установить и запустить (это ж надо разблокировать файл, макросы включить),
dll туда залить и запускать не проблема, сам так делаю.
Андрей VG, Андрей, а SQLiteForExcel, свою базу не в оперативке держит? Даже, если есть индексы, по ним надо или читать с файла или из ОЗУ. При последовательном чтении можно подгружать заранее часть файла, но если произвольная выборка из файла?
«Бритва Оккама» или «Принцип Калашникова»?
 
Вообще интересно поближе познакомится с SQLiteForExcel.
Цитата
Игорь написал:
всё это должно работать на любой версии Excel (2003-2019) под любой версией windows (XP-10) любой разрядности (32/64), причем работать это будет на тысячах разных компов, т.е. нужно что-то такое, что будет одинаково стабильно работать на любом компе.
нужно писать свою библиотеку. Она не будет зависеть от сторонних продуктов, кроме ОС. Можно и без ОС, но сложнее (все зависит от задачи, обычно чем проще тем лучше)
Изменено: bedvit - 30.05.2020 13:08:54
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
bedvit написал:
а SQLiteForExcel, свою базу не в оперативке держит?
Привет, Виталий.
SQLiteForExcel - это SQLite3_StdCall.dll обёртка над стандартной sqlite3.dll (собственно движок SQLite), чтобы можно было использовать функции dll. В движке sqlite3.dll используется подход к вызовам, отличный от stdcall.
Соответственно, можно работать с базами SQLite ровно так, как определено в движке: с базой в файле, или с несколькими базами в файлах, можно и с базой только в памяти, а можно и в смешанном режиме - и с базой в памяти и с базой в файле.
Изменено: Андрей VG - 30.05.2020 14:26:29
 
Цитата
Игорь: нельзя использовать дополнительные библиотеки / файлы.Этот код будет встроен в мою надстройку для Excel,
как вариант, можно рассмотреть файловый менеджер от того же Виталия, то есть "вшить" все необходимые библиотеки в файл надстройки
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Андрей VG написал:
Может запросы несколько низко уровневые...
Добрый день, Андрей! Да, я об этом (коряво) старался написать. По крайней мере, в большинстве случаев в качестве ответа на Select-запрос из VBA логично получить ответ в виде привычного двумерного массива типа Variant (плюс метаданные, если необходимо). Так что тут есть простор для творчества в части расширения возможностей dll.

Для тех, кто будет использовать тесты SQLiteForExcel: необходимо изменить "1 Jan 2000" на, например, "2000-01-01".
Хорошее объяснение, почему лучше работать с SQLite, а не с файлами, здесь.
Изменено: sokol92 - 30.05.2020 16:09:42
Владимир
 
Цитата:
Цитата
Лучшая производительность
Чтение и запись из базы данных SQLite часто происходит быстрее, чем чтение и запись отдельных файлов с диска. См. 35% быстрее, чем файловая система и внутренние и внешние BLOB .
Приложение должно загружать только те данные, которые ему нужны, а не читать весь файл и проводить полный анализ в памяти.
Небольшие изменения перезаписывают только те части файла, которые меняются, сокращая время записи и износ на дисках SSD.
sokol92, Владимир, вообщем-то, делаю тоже самое. Было бы интересно сравнить, производительность, к примеру поиска в файле при произвольной выборке и последовательной.
Андрей, спасибо, интересно, жаль времени нет этот инструмент потестировать.
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
bedvit написал:
Вообщем нужно более подробнее от Игоря, прямо по шагам, что делаем, с какими данными.
Спасибо, но в данном вопросе я воспользуюсь вариантом от Андрея (с бинарным поиском по файлу)
Конкретно этот функционал будет использоваться очень редко в надстройке, поэтому усложнять всё использованием отдельной dll не хочу.

PS: DLL пригодилась бы для других функций, которые проблематично / невозможно реализовать в VBA ввиду низкой скорости / утечек памяти, таких как многопоточная загрузка, преобразование JSON в XML, декодирование GZIP, и т.п.
Виталий, если можете написать под заказ такую DLL (с многопоточной загрузкой), пишите в личку, обсудим.
А с этими парами Ключ|Значение, будем считать, что вопрос решён (я получил ответы на все свои вопросы)
Изменено: Игорь - 30.05.2020 18:08:52
Страницы: 1
Наверх