Вход | Регистрация
    1  2  3  4
1С:Предприятие :: 1С:Предприятие 7.7 и ранее

v7: Необходим быстрый поиск информации в больших CSV файлах

v7: Необходим быстрый поиск информации в больших CSV файлах
Я
   evgpinsk_
 
01.05.21 - 13:07
Есть прайс в формате CSV площадки onliner.by
В нём 700 тыс позиций.
Раз в сутки прайс обновляется.

Стоит задача в 1с получать быстрый доступ к цене определённого товара из данного прайса (скорость желательна в пределах нескольких секунд).
В прайсе есть столбец "IDтовара" по которому будет осуществляться поиск.

Вопрос: Каким образом осуществлять поиск, через какие инструменты?
   Кирпич
 
301 - 14.05.21 - 18:01
Если извращаться со всякими дедупликациями, то для этого питон есть. Надо попробовать чо получится по времени.
   Djelf
 
302 - 14.05.21 - 18:02
(300) Это же только для себя, любимого забесплатно! Тут нужно недели две на согласование API и ABI... А это совсем не бесплатно ;)
   Кирпич
 
303 - 14.05.21 - 18:09
Гы. Сколько параметров должно быть у csv2dbf

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html
   Garykom
 
304 - 14.05.21 - 18:20
(303) поэтому и сразу написал выноси в файл настроек операции и только один параметр у csv2dbf - имя файла настроек
   Djelf
 
305 - 14.05.21 - 18:23
(303) Мне кажется и этого не хватит, на все креативные файлы....
   evgpinsk_
 
306 - 16.05.21 - 13:33
(287) Уж ты, упустил ветку. Да, очень не плохие вещи )
Больше для себя, что бы не пропало:
https://drive.google.com/drive/folders/1fndNmavzk9KQJ9DUcQPOzdgQXwa02Mcm?usp=sharing

красивая БД:
https://prnt.sc/12zs75d
   evgpinsk_
 
307 - 16.05.21 - 13:45
(265) Тоже круто - exe для чтения CSV в DBF
И этот способ попроще для многих чем 1sqlite использовать )
   ДедМорроз
 
308 - 16.05.21 - 17:42
База sqlite сама по себе не быстрая,т.к.в отличие от dbf имеет страничную структуру.
Лучше сунуть нос в сторону ms sql express и в нем bulk insert.
   Кирпич
 
309 - 16.05.21 - 18:05
(308) Глупости говорите. Sqlite Побыстрее ms sql express будет. А страничная структура к быстродействию вообще не имеет никакого отношения. Sqlite не предназначена для многопользовательской работы и клиент-сервера. Если её использовать только для чтения (как и нужно автору), то она быстрая как чёрт.
   ДедМорроз
 
310 - 16.05.21 - 23:12
Журнал регистрации 1с в некоторых версиях на sqlite,и что-то быстроты там не видно.
   trdm
 
311 - 17.05.21 - 06:50
(308) да, она временами не быстрая. Транзакцию надо использовать.
   trdm
 
312 - 17.05.21 - 06:53
(0) > Необходим быстрый поиск информации в больших CSV файлах
Надо напомнить, то csv файлы - текстовые, и доступ там последовательный. Т.е. что-бы что-то найти надо пробежать практически весь файл. Тогда как реляционки с индексами - имеют произвольный сопоб доступа.
   Кирпич
 
313 - 17.05.21 - 07:59
(310) ну вот как раз пример как не надо использовать sqlite
   Garykom
 
314 - 17.05.21 - 08:00
(312) Хочу напомнить что зависит от размера csv файла
На размере как у ТС можно весь закинуть в память и даже полным сканированием достаточно быстро "в пределах нескольких секунд" находить
   Garykom
 
315 - 17.05.21 - 08:01
(314)+ В смысле сейчас когда объем памяти даже офисных машинок от 8 гигов это норма
Раньше согласен приходилось извращаться с какими то базами и индексами, сейчас можно решать задачу тупо в лоб
   evgpinsk_
 
316 - 17.05.21 - 08:57
(314) Вот как-раз следующий вопрос частично касается этого.
Я замарочился (пришлось вспоминать синтаксис SQL) и привёл db3 базу исходного CSV файла к нормализованному виду:
вместо одной таблицы, в которой цены магазинов в столбцах одной тоблицы, использую 5 табличек.
https://prnt.sc/130xbq3
Таблица Прайс имеет вид:
идТовара   (создан индекс по полю)
идМагазина
Цена

Но не пойму механизм использования индекса. Почемуто этот запрос:
    ТекстЗапроса="SELECT Магазины.Наименование, Прайс.Цена, Прайс.СрокДоставки
    |FROM (Прайс INNER JOIN Товары ON Прайс.Товар = Товары.Код) INNER JOIN Магазины ON Прайс.Магазин = Магазины.Код
    |WHERE (((Товары.СуперКлюч)='"+СокрЛП(ТМЦ.МодельНаОнлайнере)+"'))";

не использует индекс, т.к. время его отработки у меня составляет 370 милиСек
что в 20 раз больше чем обычный запрос 
Select 
к ненормализованной исходной таблице price.db3  - около 15 милиСек

Вопрос : что я сделал не так?
   evgpinsk_
 
317 - 17.05.21 - 09:05
(314) В моём случаем поиск "несколько секунд" - это многовато. Пользователь находится в счёте, и нажимает кнопку на товарах, чтобы быстро посмотреть цены конкурентов.
Здесь отклик желателен в милисекунды
   Ёпрст
 
318 - 17.05.21 - 09:16
(316) код, канечна адок.
Хотя бы where перенеси в join, для начала
   Djelf
 
319 - 17.05.21 - 09:21
(316) Для начала запусти analyze, затем, если не сработает explain query plan select...
Ну и лучше не использовать inner join, а использовать left join, т.е. сначала отобрать Товары по ключу, а потом уже к таблице товары клеить Прайс.
   evgpinsk_
 
320 - 17.05.21 - 09:27
(318) Не силён в составлении sql запросов. Понимаю, но с нуля сложновато их писать. Беру код, который строит msAccess, и он именно так написал код
Графическое представление
https://prnt.sc/130yoc1
я не вижу здесь ошибки, запрос построен помоему верно.
и вот текстом этот же запрос:

SELECT Прайс.Товар, Прайс.Магазин, Прайс.Цена
FROM (Прайс INNER JOIN Магазины ON Прайс.Магазин = Магазины.Код) INNER JOIN Товары ON Прайс.Товар = Товары.Код
WHERE (((Товары.КодОнлайнера)="Экземпляр"));
   Garykom
 
321 - 17.05.21 - 09:30
(316) Нафик нормализовывать если это не требуется?
   evgpinsk_
 
322 - 17.05.21 - 09:32
(321) Согласен, просто нравится когда красиво, да и не забыл ещё немного уроки института )
Тут вопрос больше теоретический, каким образом работаем механизм использования индексов СУБД "SQLiteBase"

В простейшем случае когда выборка из одной таблицы - он используется. Но когда в запросе несколько таблиц, почемуто нет.
Ведь явно его както в коде 1с не нужно задавать?

Если ответ с ходу не виден - тогда можно забить
   acanta
 
323 - 17.05.21 - 09:35
А индекс может отвалиться из за вот этих вот сокрлп() и т.п.?
   evgpinsk_
 
324 - 17.05.21 - 09:37
(323) Разве сокрЛП() имеет отношение к индексу?. Функция просто в текст запроса передаёт верно параметр
   evgpinsk_
 
325 - 17.05.21 - 09:39
(319) Поменял на left join, стало быстрее, 
    ТекстЗапроса="SELECT Магазины.Наименование, Прайс.Цена, Прайс.СрокДоставки
    |FROM (Прайс LEFT JOIN Товары ON Прайс.Товар = Товары.Код) LEFT JOIN Магазины ON Прайс.Магазин = Магазины.Код
    |WHERE (((Товары.СуперКлюч)='"+СокрЛП(ТМЦ.МодельНаОнлайнере)+"'))";

но индексы всё равно не используются.
Сейчас Результат возвращается за 2800 милиСЕк
Напомню что из ненормализоваенной таблицы по ключу Товары.СуперКлюч
результат возвращается за 28 милисекунд
   Djelf
 
326 - 17.05.21 - 09:46
(325) Что по твоему делает этот код? FROM (Прайс LEFT JOIN Товары ON Прайс.Товар = Товары.Код)
Он перебирает весь прайс и ко всему прайсу клеит товары. И только потом ставит фильтр на Товары.СуперКлюч
Наоборот пиши: FROM Товары LEFT JOIN Прайс ON Прайс.Товар = Товары.Код
   evgpinsk_
 
327 - 17.05.21 - 09:48
А не может быть медленный поиск связан с тем что в нормализованной таблице 'прайсы' строк в пять раз больше чем в не нормализованной? Маловероятно но всё же.
   evgpinsk_
 
328 - 17.05.21 - 09:48
(326) понял через час приеду и попробую исправить
   Кирпич
 
329 - 17.05.21 - 10:01
(327) Конечно может. Особенно если индексов нет.
   Djelf
 
330 - 17.05.21 - 10:14
Да есть у него индекс по товару. Это оптимизатор sqlite дурит...
Что-то я помню такого странного поведения. Решается плюсом перед Товары.Код.
FROM Товары LEFT JOIN Прайс ON Прайс.Товар=+Товары.Код
 
 
   trdm
 
331 - 17.05.21 - 10:32
(321) > Нафик нормализовывать если это не требуется?
Бест практих под хайлоад
   Garykom
 
332 - 17.05.21 - 10:40
(331) хайлоад это отказ от sql, использование in-memory database и баз типа ключ-значение
причем делают всевозможные значения на которые навешивают нужное явно отказываясь от нормализации в угоду скорости ответа
   Garykom
 
333 - 17.05.21 - 10:42
(332)+ в случае 1С 7.7 это написание ВК, которая грузит CSV в оперативку и затем мгновенно отвечает
   trdm
 
334 - 17.05.21 - 11:05
(332) нет, это набор правил для разруливания высоких нагрузок.
Отказ от скуля тут не принципиален.
   trdm
 
335 - 17.05.21 - 11:06
+ ты же в курсе, что скуль сам загоняет наиболее юзабельные части данных в оперативку?
   Кирпич
 
336 - 17.05.21 - 11:14
(333) Для этого и ВК не надо. Есть же WSH. Да и грузить всё в память несчастной семерки это жестоко.
   evgpinsk_
 
337 - 17.05.21 - 12:42
(326) (330) 
Мне предполагается что действительно "дурит оптимизатор sqlite  SQL кода "
перенёс в MSAccess эти таблицы (прайс 2,1млн строк, товары 700тыс строк)
и этот же запрос в нём отрабатывается мгновенно, десятые доли секунды
SELECT Товары1.СуперКлюч, Магазины1.Наименование AS Магаз, Товары1.Наименование AS Товар, Прайс1.Цена, Прайс1.СрокДоставки
FROM (Прайс1 INNER JOIN Товары1 ON Прайс1.Товар = Товары1.Код) INNER JOIN Магазины1 ON Прайс1.Магазин = Магазины1.Код
WHERE (((Товары1.СуперКлюч)="usb08087805"));

он же в графической форме:
https://prnt.sc/1315k2x
   evgpinsk_
 
338 - 17.05.21 - 13:06
(318) > Хотя бы where перенеси в join, для начала

   ТекстЗапроса="SELECT Магазины.Наименование, Прайс.Цена, Прайс.СрокДоставки
    |FROM (Прайс INNER JOIN Товары ON Прайс.Товар = Товары.Код) INNER JOIN Магазины ON Прайс.Магазин = Магазины.Код
    |WHERE (((Товары.СуперКлюч)='"+СокрЛП(ТМЦ.МодельНаОнлайнере)+"'))";

было бы интересно попробовать, но не знаю как )
нужно решать через Запрос в Запросе?
   Djelf
 
339 - 17.05.21 - 13:07
(337) Еще раз повторяю - поставь + перед Товары1.Код
Т.е. на так  ON Прайс1.Товар = Товары1.Код, а вот так  ON Прайс1.Товар = +Товары1.Код
   Ёпрст
 
340 - 17.05.21 - 13:11
(338) че как ? выкинь where и в своём inner join воткни and Товары.СуперКлюч =
   Ёпрст
 
341 - 17.05.21 - 13:15
или так, хотя бы:


|SELECT Магазины.Наименование, Прайс.Цена, Прайс.СрокДоставки
|from Товары
|left join Прайс on Прайс.Товар = Товары.Код
|left join Магазины on Прайс.Магазин = Магазины.Код
|WHERE Товары.СуперКлюч ='"+СокрЛП(ТМЦ.МодельНаОнлайнере)+"'";
   evgpinsk_
 
342 - 17.05.21 - 13:54
(339) Забыл отписать, плюсики не меняют ситуацию, индекс не задействуется
(340) (341)  сейчас попробую
   evgpinsk_
 
343 - 17.05.21 - 14:16
(341) Это помогло, вместо 2000 мсек стало 300мсек
не уверен  что это правильный результат, т.к. напомню в не оптимизированном сплошном прайсе из одной таблицы поиск длится 15 милисек
   evgpinsk_
 
344 - 17.05.21 - 14:21
п.с. Сейчас заметил свою ошибку, я в таблице Прайс забыл сделать индекс по полю Магазин
а оно используется в join

Хотя странно, добавление индекса по полю Магазин не повлияло на скорость - сейчас теже 300 милисек

Итого имеем , что поиск в базе из 3х оптимизированных таблиц в 20 раз медленнее (0,3сек) чем в одной сплошной таблице (0,015сек).
п.с.с. Вопрос имеет чисто теоретический интерес, на него можно смело забить )
   Djelf
 
345 - 17.05.21 - 14:45
(342) Та какую версию 1sqlite то используешь? Небось какую-то древнюю? Возьми мою сборку посвежее https://cloud.mail.ru/public/9znr/ZJ6ULE9aR
В старых версиях довольно плохой планировщик, он очень плохо переваривает inner join.
Более новая должна давать результат примерно соответствующий (343). Вот теперь плисик туда добавь и станет 1-2 микросекунды.
Но ты же посмотрел explain query plan select... как я советовал.
У тебя план запроса примерно такой
id    parent    notused    detail
3    0    0    SEARCH TABLE Товары USING INTEGER PRIMARY KEY (rowid=?)
6    0    0    SCAN TABLE Прайс
Индекс по товару в Прайсе не используется. А не используется потому что во-первых это не очень хороший индекс (много дублей), а во вторых без плюсика, который подавляет использование ключевого индекса в Товарах sqlite дуркует.

Но можно не только плюсиком заставить sqlite использовать этот индекс, вот так вот должно работать, см. план ниже
LEFT JOIN Прайс INDEXED BY Товар ON Прайс.Товар = Товары.Код

id    parent    notused    detail
4    0    0    SEARCH TABLE Товары USING INTEGER PRIMARY KEY (rowid=?)
7    0    0    SCAN TABLE Прайс USING INDEX Товар
   Djelf
 
346 - 17.05.21 - 14:49
(344) При запросе используется только один индекс! Индекс по Магазину никак не мог изменить скорость выборки.
Ну и чему ты в (344) удивляешься? Ясное дело, что запрос к плоской таблице по уникальному индексу будет быстрее.
   pechkin
 
347 - 17.05.21 - 14:56
(346) 2 разных индекса никак не могут использоваться для поиска записи
   evgpinsk_
 
348 - 17.05.21 - 14:57
(345) Чудеса бывают, плюсик помог на свежей версии 1sqlite )
    ТекстЗапроса="SELECT Магазины.Наименование, Прайс.Цена, Прайс.СрокДоставки
    |FROM Товары    
    |LEFT JOIN Прайс ON Прайс.Товар = +Товары.Код 
    |LEFT JOIN Магазины ON Прайс.Магазин = +Магазины.Код
    |WHERE Товары.СуперКлюч ='"+СокрЛП(ТМЦ.МодельНаОнлайнере)+"'";

поиск стал вместо 300 мСек 30 мСек 
а когда добавил индекс и на Магазин то 8 мСек 
:)
   evgpinsk_
 
349 - 17.05.21 - 15:01
(347) Верно. просто значит первая попытка без кеша (поэтому более медленная)
Добавление индекса по полю Магазин - не увеличивает скорость
сейчас она стала 8мСек

помогла свежая версия 1sqLite и плюсики в тексте запроса
   Djelf
 
350 - 17.05.21 - 15:07
(349) Базу то скинь, с которой работаешь. 30мс в (348) ИМХО слишком долго! Должно быть 0-1мс.
   evgpinsk_
 
351 - 17.05.21 - 15:10
Ну и самый прикол что поиск длиться теже 8 мСек в моём первоначальном запросе из (316)  :)

    ТекстЗапроса="SELECT Магазины.Наименование, Прайс.Цена, Прайс.СрокДоставки
    |FROM (Прайс INNER JOIN Товары ON Прайс.Товар = Товары.Код) INNER JOIN Магазины ON Прайс.Магазин = Магазины.Код
    |WHERE (((Товары.СуперКлюч)='"+СокрЛП(ТМЦ.МодельНаОнлайнере)+"'))";
   evgpinsk_
 
352 - 17.05.21 - 15:12
(350) Сейчас результат на любых вариантах Select  - 8мСек

30мСек в (348) - был глюк первого запуска. Заметил что иногда первый запускает тормозит. Потом все пыпытки стабильно 7-9 мСек
   evgpinsk_
 
353 - 17.05.21 - 15:16
https://dropmefiles.com/ec9WK
оптимизированная база.
в ней же таблица Price - исходная не нормализированная
   Djelf
 
354 - 17.05.21 - 15:18
(351) Это не прикол, в версиях Орефкова движок sqlite 3.7.11, а начиная с движка 3.8.0 поменялся оптимизатор: https://runebook.dev/ru/docs/sqlite/queryplanner-ng
С 1С 7.7 он работал криво, насколько помню в 3.14.0 починили работу нового планировщика с виртуальными таблицами (которыми являются таблицы 1С 7.7).
   evgpinsk_
 
355 - 17.05.21 - 15:19
(346) > Ну и чему ты в (344) удивляешься? Ясное дело, что запрос к плоской таблице по уникальному индексу будет быстрее.
Не знал, тогда действительно в моём частном случае правильней и проще держать плоскую базу.
Найти нужную строку по коду товара, и в ней уже выбрать цены магазинов из столбцов
   Djelf
 
356 - 17.05.21 - 15:31
(355) Тут не понятно что будет быстрее.
Ты же можешь с нормализованной таблицей получать цены в разрезе магазин, а с плоской нет.
Если тупо "товар" -> "строка прайса" -> "список магазинов с ценами", возможно плоская будет быстрее, даже при учете того что разбирать колонки придется в 1С.
   Злопчинский
 
357 - 17.05.21 - 15:38
исходить надо из перспектив среднесрочных. если создаваемый инструмент важен - то следует делать сразу правильно, это в будущем окупится.
   evgpinsk_
 
358 - 17.05.21 - 15:45
(356) > Ты же можешь с нормализованной таблицей получать цены в разрезе магазин, а с плоской нет.

с плоской нет - средствами SQL
но ведь элементарно получив строку из базы, потом ручками вытянуть цены магазина по названию колонк (они формализованы: Магазин1, Магазин2)

(357) согласен, но в моём  случае врядли будет усложнение именно этой задачи в будущем (хотя кто его знает).

п.с. Вынашиваю очень долго не простую задачу по импорту прайсов поставщиков в базу 1с.
Есть не совсем простые вопросы по теоретической части. /уже както подымал тему ранее/
Соберусь с мыслями и попробую поднять новую тему ещё раз )
   Djelf
 
359 - 17.05.21 - 15:53
(358) А ты попробуй, без нормализации, вытянуть цены по одному из магазинов.
Но есть же не только магазины, но и срок доставки! Вот тебе еще и один повод для нормализации базы.
   evgpinsk_
 
360 - 17.05.21 - 16:10
(359) Да, по магазину очень не просто. В моём частном случае эта задача не стоит. Нужен просто анализ рынка для конкретному товару
 
 
   evgpinsk_
 
361 - 17.05.21 - 16:33
(359) срок доставки - не мешает использовать плоскую таблицу. Это ведь такой же параметр как и цена
   Djelf
 
362 - 17.05.21 - 16:38
(361) Ты де уже добился приемлемой скорости? Даже 300мс это ерунда, стало 30мс, это уже более чем приемлемо. Забудь (на время).
   evgpinsk_
 
363 - 17.05.21 - 18:16
(362) Да, всё ок. Уже 8 милисекунд. Для этой задачи это с большим запасом
  1  2  3  4

Список тем форума
 
ВНИМАНИЕ! Если вы потеряли окно ввода сообщения, нажмите Ctrl-F5 или Ctrl-R или кнопку "Обновить" в браузере.