LINUX.ORG.RU

Python, сильнейшая регрессия SELECT при fetchall()

 ,


1

1

Всем доброго времени суток!

Имеется небольшой код который создаёт базу и туда закидывает тестовые данные, а затем делает обращение к базе SELECT запросом:

@profile # декоратор для профайлера
def do():
    import os
    import sqlite3

    # смотрим есть ли уже база на дсике
    is_db_exist = os.path.isfile('./123.db')
    print('is_db_exist: {}'.format(is_db_exist))
    # return

    db = sqlite3.connect('123.db')

    # базы на диске нет, значит создаём структуру и добавляем данные
    if not is_db_exist:
        # создаём базу
        db.execute('''
        CREATE TABLE task_list (
        id INTEGER PRIMARY KEY AUTOINCREMENT
        );
        ''')
        db.execute('''
        CREATE TABLE dict (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        [key]   TEXT,
        value   TEXT,
        task_id   REFERENCES task_list (id)
        );
        ''')

        db.execute("INSERT INTO task_list (id) VALUES (NULL);")
        db.commit()

        # добавляем тестовые записи
        for i in range(300000):
            db.execute('''
            INSERT INTO 'dict' ('task_id', 'key', 'value') VALUES
                          ('{0}', 'key_1', 'data_1'),
                          ('{0}', 'key_2', 'data_2'),
                          ('{0}', 'key_3', 'data_3'),
                          ('{0}', 'key_4', 'data_4')
            '''.format(i))

        db.commit()

        # проверяем что всё нормально добавилось
        total_added = db.execute('SELECT count(*) from dict;').fetchall()[0][0]
        print('добавлено записей: {}'.format(total_added))

    got = db.execute('select [key], value from dict where task_id like 1')
    fetched = got.fetchall() # вот этот кусок выполняется целых 0.5 секунд, вместо 0.001 секунд

    db.close()

do()

Обратите внимание на этот кусок кода:

    fetched = got.fetchall() # вот этот кусок выполняется целых 0.5 секунд, вместо 0.001 секунд

На одной машине (windows 7) он выполняется целых 0.5 секунд, а на другой (windows 8) 0.001 секунд.

Откуда-то взялась колоссальная регрессия. Я пробовал обновлять питон до последней версии 3.5.3 (в пределах ветки 3.5 эта версия последняя), но толку нету.

На стэке пишут надо добавить индексы и будет всё хорошо, но что-то мне они не помогли.

Коллеги, куда копать, что делать?


На одной машине (windows 7) он выполняется целых 0.5 секунд, а на другой (windows 8) 0.001 секунд.

Коллеги, куда копать, что делать?

В сторону винфака.

Deleted ()

Копать в сторону «руками выполнить «тормозящий запрос» в базе». То есть, без питона.

kawaii_neko ★★★ ()
Ответ на: комментарий от kawaii_neko

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

rubro ()
Ответ на: комментарий от rubro

косяк именно со стороны питона

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

полагаю, ты сам себе противоречишь.

прислушайся ко второму данному тебе совету, и уже после этого продолжай гадать дальше.

Deleted ()
Последнее исправление: Deleted (всего исправлений: 2)

integer сравнивать like-ом --- это фича такая? зачем?

Rastafarra ★★★★ ()
Ответ на: комментарий от Rastafarra

Почему-то такой запрос не работает:

select [key], value from dict where task_id = 1
Ничего не находит так

rubro ()
Ответ на: комментарий от Deleted

Не могли бы вы пожалуйста подсказать как это выглядеть может? Я сейчас представляю себе вызов в консоли бинарника sqlite с передачей ему параметров и потом обработка вручную полученного результата (вывода)

rubro ()
Ответ на: комментарий от rubro

но зачем ты вставляешь строки там, где объявлен integer? и потом строки же сравниваешь?

Rastafarra ★★★★ ()
Ответ на: комментарий от Rastafarra

Невнимательность! Исправил, но главная проблема всё равно не исчезла

rubro ()
Ответ на: комментарий от Rastafarra

Коллега, огромное вам спасибо! В этом была суть проблемы, теперь всё идеально шустро работает! Эта ошибка по невнимательности и была корнем всей проблемы. Посылаю вам лучи света и благодарности!

rubro ()
Ответ на: комментарий от rubro

да нет же чувак, тут проблема ИМЕННО в ПИТОНЕ, именно поробуй обновиться, написать на пару форумов, выдвинуть десяток теорий, вместо чтоб еще раз перечитать свой говнокодец-. вся суть лоровца.

anonymous ()
Ответ на: комментарий от Rastafarra

Рано я обрадовался... Вот код текущий исправленный:

@profile # для профайлера
def do():
    import os
    import sqlite3

    # смотрим есть ли уже база на дсике
    is_db_exist = os.path.isfile('./123.db')
    print('is_db_exist: {}'.format(is_db_exist))
    # return

    db = sqlite3.connect('123.db')

    # базы на диске нет, значит создаём структуру и добавляем данные
    if not is_db_exist:
        # создаём базу
        db.execute('''
        CREATE TABLE task_list (
        id INTEGER PRIMARY KEY AUTOINCREMENT
        );
        ''')
        db.execute('''
        CREATE TABLE dict (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        [key]   TEXT,
        value   TEXT,
        task_id   REFERENCES task_list (id)
        );
        ''')

        db.execute("INSERT INTO task_list (id) VALUES (NULL);")
        db.commit()

        # добавляем тестовые записи
        for i in range(300000):
            db.execute('''
            INSERT INTO 'dict' (task_id, 'key', 'value') VALUES
                          ({0}, 'key_1', 'data_1'),
                          ({0}, 'key_2', 'data_2'),
                          ({0}, 'key_3', 'data_3'),
                          ({0}, 'key_4', 'data_4')
            '''.format(i))

        db.commit()

        # проверяем что всё нормально добавилось
        total_added = db.execute('SELECT count(*) from dict;').fetchall()[0][0]
        print('добавлено записей: {}'.format(total_added))

    got = db.execute('select [key], value from dict where task_id = 1')  # выполняется целых 0.5 секунд, вместо 0.001
    fetched = got.fetchall()
    print(fetched)

    db.close()

do()

Теперь не 0.5 секунд выполняется, а 0.4 примерно, это лучше, но всё равно очень медленно

rubro ()
Ответ на: комментарий от rubro

Комментарий тут неверно написал, он должен быть для fetchall(), именно этот участок долго выполняется

rubro ()
Ответ на: комментарий от rubro

fetchall(), именно этот участок долго выполняется

ну... я так понимаю у тебя там 1 200 000 (лямдвести) записей? хз как тут sqlite себя ведет. это не так чтобы мало.

Rastafarra ★★★★ ()
Ответ на: комментарий от Rastafarra

Добавил индексы, правда я в них совсем не разбираюсь, теперь код вот так выглядит:

@profile # для профайлера
def do():
    import os
    import sqlite3

    # смотрим есть ли уже база на дсике
    is_db_exist = os.path.isfile('./123.db')
    print('is_db_exist: {}'.format(is_db_exist))
    # return

    db = sqlite3.connect('123.db')

    # базы на диске нет, значит создаём структуру и добавляем данные
    if not is_db_exist:
        # создаём базу
        db.execute('''
        CREATE TABLE task_list (
        id INTEGER PRIMARY KEY AUTOINCREMENT
        );
        ''')
        db.execute('''
        CREATE TABLE dict (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        [key]   TEXT,
        value   TEXT,
        task_id   REFERENCES task_list (id)
        );
        ''')

        db.execute('''
        CREATE INDEX idx_dict ON dict (
        id,
        "key",
        value,
        task_id
        );
        ''')

        db.execute('''
        CREATE INDEX tl_idx ON task_list (
        id
        );
        ''')

        db.execute("INSERT INTO task_list (id) VALUES (NULL);")
        db.commit()

        # добавляем тестовые записи
        for i in range(300000):
            db.execute('''
            INSERT INTO 'dict' (task_id, key, value) VALUES
                          ({0}, 'key_1', 'data_1'),
                          ({0}, 'key_2', 'data_2'),
                          ({0}, 'key_3', 'data_3'),
                          ({0}, 'key_4', 'data_4')
            '''.format(i))

        db.commit()

        # проверяем что всё нормально добавилось
        total_added = db.execute('SELECT count(*) from dict;').fetchall()[0][0]
        print('добавлено записей: {}'.format(total_added))

    got = db.execute('select [key], value from dict where task_id = 1')  # выполняется целых 0.5 секунд, вместо 0.001
    fetched = got.fetchall()
    print(fetched)

    db.close()

do()
И всё равно та же самая регрессия на выходе. Да и не ясно почему на одной машины всё летает, а на другой тормозит, можете на своей проверить пожалуйста сколько строка с fetchall() выполняться будет?

rubro ()
Ответ на: комментарий от Rastafarra

Да, лям-двести, но тот же самый запрос на другой машине, или даже на этой же самой машине но выполненный в SQLiteStudio отрабатывает за 0.001 сек

rubro ()
Ответ на: комментарий от rubro

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

anonymous ()
Ответ на: комментарий от anonymous

Без питона в SQLiteStudio как выше указал выполняется за 0.001 сек, то есть проблема не в том что запрос тяжёлый, а в том что питон почему-то при fetchall() тупит нещадно. То есть проблема не в SQLite а в питоне как мне сейчас кажется.

rubro ()
Ответ на: комментарий от anonymous

Бесполезный комментарий полный жопного пламени - вся суть лоровского анона.

anonymous ()
Ответ на: комментарий от rubro

А ты в студии только селект делаешь или всю последовательность запросов выполняешь?

anonymous ()
Ответ на: комментарий от rubro

ты сделал составной индекс, а выбираешь по одному полю. твой индекс бесполезен :(

например так:

db.execute("'CREATE INDEX idx_dict ON dict (task_id);"')

Rastafarra ★★★★ ()
Ответ на: комментарий от anonymous

Я клоню в сторону коммита перед селектом.

anonymous ()
Ответ на: комментарий от anonymous

Большое спасибо! Вот теперь всё отлично работает, за 0.001 сек отрабатывает селект

rubro ()
Ответ на: комментарий от anonymous

Только селект, сначала скрипт выше базу создаёт, далее на ней веду тесты

rubro ()
Ответ на: комментарий от Rastafarra

Да вот так гладко, большое спасибо! Теперь всё реактивно летает!

rubro ()

Ребят всем огромное спасибо за помощь и советы! Спасли отца русской демократии, я безмерно вам благодарен и очень ценю вашу помощь!

rubro ()
Ответ на: комментарий от rubro

Да, лям-двести, но тот же самый запрос на другой машине, или даже на этой же самой машине но выполненный в SQLiteStudio отрабатывает за 0.001 сек

И SQLiteStudio тоже выбирает «лям-двести»? Может ты поставишь limit 999999999 и посмотришь разницу. Не верю в результаты SQLiteStudio.

kachan ★★ ()
Ответ на: комментарий от rubro

Хорошо, конечно, что ты решил проблему, но различий это не объясняет. Я бы еще сделал селект из питона на существующем файле (без предварительной вставки) и выполнил бы скриптом в студии аналог того, что ты делаешь в питоне. Я склоняюсь к различиям работы коммита (или че-то рядом, возможно, вставки или работы с буфером) на разных версиях винды.

anonymous ()
Ответ на: комментарий от kachan

Я же выбираю всего лишь 4 строки по task_id из этих ляма-двести, а не селект всего ляма-двести как вам возможно показалось

rubro ()
Ответ на: комментарий от anonymous

Если вы посмотрите скрипт то обнаружите что первичный запуск создаёт базу, а все последующие к ней лишь обращаются, к уже имеющейся базе. То есть здесь мне кажется коммит не влияет. Тот же самый селект в студии работает за 0.001 сек чего я и пытался добиться в питоне.

rubro ()
Ответ на: комментарий от rubro

Но без индекса БД просматривает _все_ записи, чтобы определить удовлетворяют ли они запросу. После того, как ты создал индекс, БД смотрит в него (а его структура оптимизирована для такого поиска) и выбирает только то, что удовлетворяет запросу (твои 4 записи). Ранее ты создавал индекс, который не отличается по структуре от таблицы, поэтому для БД в использовании такого индекса нет смысла, она его может вообще осознанно не использовать (не знаю как с этим конкретно в sqlite).

anonymous ()
Ответ на: комментарий от rubro

Чувак, 0.001 сек, это дичь, это нереально, я на мощных серваках так данные не могу выбрать. Поставь limit 9999999999, и увидишь разницу.

kachan ★★ ()
Ответ на: комментарий от anonymous

Спасибо за пояснения, буду знать теперь, я вообще слаб в sqlite

rubro ()
Ответ на: комментарий от kachan

Так что ли?

select [key], value from dict where task_id like 1 limit 9999999999;
Выполняет за 0.001 сек.

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

Возможно дело в том что стоят диски SSD на обоих машинах и поэтому так работает, не знаю.

rubro ()
Ответ на: комментарий от rubro

А стоп. Я тупанул, там же один таск всего выбирается. Сори, с лимитом чет тупанул.

kachan ★★ ()
Ответ на: комментарий от kachan

Бывает :) Но на один таск там выдаёт 4 записи сейчас, потом их будет в районе примерно до 100шт

rubro ()
Ответ на: комментарий от rubro

а зачем тебе лям фетчолных объектов, ты понимаешь суть бд ваще? зачем ты создал этот тред, хуй знает что у тебя С МАШЭиНОЙ НЕ ТаК

anonymous ()
Ответ на: комментарий от anonymous

Вот и вся суть русских форумов, обосрать ТС потому что он делает хрень.

kachan ★★ ()
Последнее исправление: kachan (всего исправлений: 1)
Ответ на: комментарий от kachan

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

rubro ()

перепиши на rust

anonymous ()
Ответ на: комментарий от anonymous

Это было бы бесплодным расточением ресурсов, всё проще и легче обычно решается.

rubro ()
Ответ на: комментарий от rubro

РЕГРЕССИ В ПИСТоН ... КАЛЕКИ ПРАСТИТЕ МАЮ ОПЕЧАТКУ... ТУТ ДЕЛО В САМОМ ПЕТон ... СКЛАЙ.т МИЛЛИОН ИНСТАНСАВ ФЕТЧАЛ ТАРМАЗИТ НА МАСДАЕ ПАЧИМУ. суть задачи нет, тормозит рандомный вызов в рандомном окружении на рандомной машине, ну давайте все прогоним у себя эти тесты, у кого восприизведется РЕГРЕССИЯ? дайте угадаю, ниукого ?

anonymous ()
Ответ на: комментарий от anonymous

МИЛЛИОН ИНСТАНСАВ ФЕТЧАЛ
where task_id = 1

Вызвал пожарных на твой адрес.

anonymous ()
Ответ на: комментарий от rubro

Невнимательность была на уровне чтения документации к python sqlite3, там в первых же абзацах сказано, что делать в запросе format - не надо, а надо использовать placeholders, у них есть приведение типов питона в sqlite-овские типы :)

pod ★★ ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.