DjangoBB LoFi version

Полная версия: Слишком медленный update колонки в sqlite3

Начало » Базы данных » Слишком медленный update колонки в sqlite3

1 2

vanvanov

Фев. 5, 2017 00:54:01

Добрый день! Я не так давно начал работу с sqlite3. У меня python3.4 и sqlite 2.6.0 (sqlite3.sqlite_version показывает ‘3.6.20’, не знаю, в чем разница).

Проблема в следующем. Есть таблица в 12 колонок и почти 32.000 строк. В качестве идентификатора использую колонку NO, она содержит номера строк. ID Autoincrement, как я понял, мне не подходит, потому что там везде уникальный ID для всех строк, в том числе, удаленных, а мне нужно, чтобы было строго +1 для всей нумерации.

Когда я удаляю строку, мне нужно перенумеровать все строки в колонке NO. Для этого использую (условно):

 			lst = [(x,) for x in range(31889)]
			dbc.executemany('update TEST set NO=?',lst)

На колонке NO есть индекс, но при этом update выполняется долго (как минимум, на минуты идет отсчет). В чем проблема?

py.user.next

Фев. 5, 2017 02:18:01

vanvanov
Когда я удаляю строку, мне нужно перенумеровать все строки в колонке NO.

Это неправильный способ. В таблице должен храниться только порядок записей, а номера должны назначаться при выборке в соответствии с порядком записей. Поэтому ничего не надо перенумеровывать; главное, чтобы порядок записей сохранялся. И id и порядок должны быть разными полями, так как id не обязан быть в каком-то порядке, он только за уникальность отвечает. А дальше, когда ты выбрал все записи, ты их нумеруешь.

vanvanov

Фев. 5, 2017 03:19:28

py.user.next
А по какому критерию sqlite хранит порядок записей - по времени добавления? А если, например, есть записи 0-500, и нужно будет вставить запись как раз между 495 и 496? А если нужно сделать SELECT для записей 300-350, как будет выглядеть запрос? Но даже если все это как-то можно утрясти, почему в принципе update такой адски медленный?

ZerG

Фев. 5, 2017 10:40:16

У вас какойто извращенный маниакальный метод садомазохизма закрученный на никрофилии!

Предположим вам нужно удалить строку таким методом что бы не нарушился порядок?
Ну так тем же апдейтом просто очистите дынные в етой строке - пусть останеться пустой но только с ижентификатором!
Просто пока у вас 32к записей - ето ничто! а вот когда вы решите каждый раз переиндексировать 3 лимона это уже будет интереснее!

В целом я не до конца верно вам ответил - но совершенно точно могу сказать что у вас ошибка на уровне структуру/запросов в бд!
Если вы более детально опишите структуру и задачу - возможно вам дадут совет как сделать “правильно”

з.ы. проблема совершенно точно не в sqlite !

py.user.next

Фев. 5, 2017 10:51:13

vanvanov
А по какому критерию sqlite хранит порядок записей - по времени добавления?

Это твоя задача - сделать такое поле, отвечающее за порядок. Через него записи будут понимать, какая идёт раньше, а какая позже.

vanvanov
А если, например, есть записи 0-500, и нужно будет вставить запись как раз между 495 и 496?

495-ая может иметь номер 12345, 496-ая может иметь номер 56789, тогда запись между ними будет иметь номер (12345 + 56789) / 2.

vanvanov
А если нужно сделать SELECT для записей 300-350, как будет выглядеть запрос?

Отсортируешь по полю порядка и возьмёшь записи с 300 по 350.

vanvanov
почему в принципе update такой адски медленный?

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

PooH

Фев. 5, 2017 10:52:41

Давайте начнем с того, какой смысл имеет номер записи в вашей задаче?

vanvanov

Фев. 5, 2017 13:19:10

py.user.next
Это твоя задача - сделать такое поле, отвечающее за порядок.

Autoincrement? Или несистемное?

py.user.next
Отсортируешь по полю порядка и возьмёшь записи с 300 по 350.

А именно? Каждый раз делать fetchall() и дальше производить манипуляции со списком? У меня это получается медленнее, чем средствами самой БД. И больше нужно кода.

PooH
Давайте начнем с того, какой смысл имеет номер записи в вашей задаче?

БД представляет собой словарь. Для него написана обертка, позволяющая делать поиск (кстати, LIKE не поддерживает кириллицу, но, видимо, это отдельная тема), а также навигацию по номеру записи. Естественно, если в БД 10 записей, то GUI должен отображать, например, “Записи 0-9” или “Записи 1-10” и предлагать переход между ними. Т.е. все завязано на номере.

py.user.next
Потому что при каждом добавлении каждой записи ты переписываешь вообще все записи. То есть если ты добавляешь 10 новых записей, то ты 10 раз переписываешь всю таблицу базы данных. Быстро это или медленно, в любом случае это глупо и неэффективно.

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

py.user.next

Фев. 5, 2017 14:42:57

vanvanov
Autoincrement? Или несистемное?

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

vanvanov
Каждый раз делать fetchall()

Сортируешь и берёшь 350 записей, из них отбрасываешь 300. Да и sqlite допускает указание “limit 299,50”, так что даже ничего отбрасывать не надо.

vanvanov
кстати, LIKE не поддерживает кириллицу

sqlite> select prof from ccpview where prof like '%програ%';
prof
Оператор ПК, программист, системный администратор
Инженер-программист
Оператор, системный администратор, программист, техник ЭВМ
Техник, программист
Инженер-программист
Системный администратор, программист, лаборант-программист, web-программист
Инженер-программист, водитель-экспедитор
IT-инженер, системный администратор, инженер-программист
Программист, web-программист
Web-разработчик, web-мастер, программист
Администратор, программист, оператор
Инженер-программист
Инженер-программист
Системный администратор, IT-специалист, инженер, инженер-программист
Системный администратор, IT-специалист, инженер, инженер-программист, программист 1С
Инженер-программист, системный администратор
Инженер-программист
Системный администратор, программист
Веб-программист, IT-специалист
Инженер-программист
Веб-программист, верстальщик, веб-мастер, веб-дизайнер
Системный администратор, программист, лаборант-программист
Инженер-программист, системный администратор
Электромеханик, инженер-электроник, программист
Помощник эксперта по недвижимости, программист-стажер, разнорабочий
Техник-программист
Сайт-менеджер, системный администратор, IT-специалист, web-программист
IT-специалист, программист
Инженер-программист
Инженер-программист, системный администратор
Техник-программист
Инженер-программист
Лаборант-программист
Инженер-программист
Инженер-программист
Web-программист, инженер-программист, программист
Веб-программист, системный администратор
Специалист ИТ, программист
Экономист-программист
Инженер, техник-программист, проектировщик
Экономист, программист, менеджер
Техник-программист
Инженер-программист
Инженер-программист
Системный администратор, инженер-программист
Менеджер, программист
Инженер-программист
Инженер, программист
Системный администратор, инженер-программист
Инженер-программист
Программист-техник, программист-инженер, системный администратор
IT-специалист, программист, техник ЭВМ
Помощник программиста, электроник
Администратор базы данных, инженер-программист
Системный администратор, программист
Инженер-программист, системный администратор
Официант, программист, помощник
Системный администратор, программист
Системный администратор, программист, инженер-системотехник
Инженер-программист
Программист-разработчик программного обеспечения, программист 1С, системный администратор
Руководитель сектора социальных программ, специалист по работе с надзорными органами
Web-программист, верстальщик, web-дизайнер
Web-дизайнер, web-программист, SEO-оптимизатор
Информатик-экономист, программист, менеджер, системный администратор
Системный администратор, программист, IT-специалист
Инженер-программист
Экономист-программист
Дизайнер, верстальщик, программист
Системный администратор, программист, оператор ПК
Системный администратор, программист
Системный администратор, IT- специалист, программист, водитель
sqlite>

vanvanov
Ну, я так и думал. Но разве это не архитектурная проблема sqlite?

Что перезапись всех записей занимает время? Это проблема проектирования базы данных - когда требуется постоянная перезапись всех записей.

vanvanov
А если все же требуется обновить всю колонку?

Мы тут говорим о чём-то абстрактном, приведи код. Может, там из-за кода тормозит всё, а ты на СУБД валишь. Но по текущим данным, когда при добавлении записи в таблицу все остальные записи должны переписываться, это звучит как-то не так, как будто ты не понимаешь, что делаешь. Так что и код там явно такой же, а значит и ошибки могут быть в нём алгоритмические. Обновить колонку ты можешь, но только раз на тысячу операций (что можно и подождать), а не раз на каждую операцию.

PooH

Фев. 5, 2017 15:01:05

vanvanov
Естественно, если в БД 10 записей, то GUI должен отображать, например, “Записи 0-9” или “Записи 1-10” и предлагать переход между ними. Т.е. все завязано на номере.

Это, обычно, делают не так - задают сортировку и ограничивают вывод через LIMIT и OFFSET.

vanvanov

Фев. 5, 2017 16:12:38

py.user.next
Да и sqlite допускает указание “limit 299,50”, так что даже ничего отбрасывать не надо.

limit по умолчанию не задействован, перекомпилировать надо.

py.user.next
sqlite> select prof from ccpview where prof like ‘%програ%’;
prof
Оператор ПК, программист, системный администратор

ОК, а найдется ли ‘Оператор’ по запросу ‘%оператор%’? Вроде, ICU решает эту проблему, но там тоже могут быть сложности при установке.

PooH
Это, обычно, делают не так - задают сортировку и ограничивают вывод через LIMIT и OFFSET.

Ну вот, смотрите.

Это - средства навигации по БД. В полях указаны реальные номера записей, без учета каких-нибудь потенциальных промежутков. Допустим, пользователь захотел перейти на запись №500. Как мне узнать номер, под которым эта запись идет в БД, и некоторые ее значения?

 select NO from TEST order by NO limit 500
select TRANSLATION from TEST where NO=?

И так каждый раз вместо одиночного запроса

 select TRANSLATION from TEST where NO=?

?
P.S. Впрочем, я могу удалить функциональность, связанную с удалением строк, и заставить пользователя просто редактировать ненужные записи. Наверное, так и сделаю.