Оптимизация Big SELECT

Я использую SQLite в Python для большой системы управления файлами. У меня есть большой плоский файл (100 миллионов строк), который я хочу отсортировать, используя значения трех столбцов (которые являются целыми числами), чтобы я мог выполнять итерацию и выполнять некоторые вычисления.

Я использовал SQLite с большим SELECT ... ORDER BY (с индексом по одному столбцу). Поскольку этот большой SELECT слишком требователен к памяти, мне нужно вызывать его несколько раз (с OFFSET и LIMIT).

Я мог бы использовать сортировку Linux, но я хочу, чтобы она не зависела от платформы. Он работает нормально (пока правильно настроена правильная PRAGMA), но медленно. Как это оптимизировать?

Команды такие:

PRAGMA journal_mode = OFF
PRAGMA synchronous = 0
PRAGMA locking_mode = EXCLUSIVE
PRAGMA count_change = OFF
PRAGMA temp_store = 2
CREATE TABLE tmpTranscripts_arm_3R_transcripts (id INTEGER PRIMARY KEY, name varchar(255), chromosome varchar(255), start int(11), end int(11), direction tinyint(4), tags varchar(1023), bin int(11), exons varchar(10000))
CREATE INDEX 'iTranscript_arm_3R_14943' ON 'tmpTranscripts_arm_3R_transcripts' (start, end, direction)
INSERT INTO tmpTranscripts_arm_3R_transcripts (name, chromosome, start, end, direction, tags, bin, exons) VALUES ('SRR060644.1', 'arm_3R', 11450314, 11450337, -1, 'feature=transcript;bestRegion=(self);nbGaps=0;nbMismatches=0;ID=SRR060644.1;identity=100.0', 300011450, '')
(this, more than 10 millions times)
SELECT * FROM tmpTranscripts_arm_3R_transcripts ORDER BY start, end, direction LIMIT 0, 10000
(this, as much as needed)

sqlite select sql-order-by pysqlite
person unamourdeswann    schedule 05.10.2011    source источник
comment
Вы создали ИНДЕКСЫ для этих трех столбцов? Добавление индексов действительно увеличивает скорость операций сортировки. Но, конечно, у этого есть своя цена - база данных будет больше, а операция вставки будет медленнее.   -  person Zuljin    schedule 05.10.2011
comment
Если 3 отдельных индекса не будут работать хорошо, возможно, будет возможно создать 1 многостолбцовый индекс, если ваш SELECT всегда упорядочен одинаково. Отметьте этот ответ stackoverflow.com/questions/179085 /   -  person Zuljin    schedule 05.10.2011
comment
Что ж, похоже, это сработало не намного лучше ... Потребовалось бы ~ 8 часов, чтобы перебрать всего 10 миллионов строк ... и я попробовал оба решения ... Еще одна идея?   -  person unamourdeswann    schedule 05.10.2011

Ответы (1)


arrow_upward
2
arrow_downward

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

Я проверил 2 базы данных SQLite и MongoDB с 5 миллионами элементов. Для SQLite вставка всех строк заняла ~ 1200 секунд, а их выбор - около 300 секунд. MongoDB был быстрее, и вставка заняла ~ 400 секунд при выборе менее 100 секунд.

Пожалуйста, сверьте свой код с моими примерами и проверьте, похож ли ваш выбор. Я использовал курсор вместо LIMIT / OFFSET. Если это все еще не помогает, я думаю, что стоит попробовать MongoDB. У него есть один недостаток - для поддержки большой базы данных (например, вашей) требуется 64-битная ОС. Если вы раньше использовали его раньше, то вот кратчайшее руководство по установке для Windows:

А вот и мои тестовые скрипты на Python 3.x для SQLite.

import sqlite3
from time import time

conn = sqlite3.connect('test.dbase')

c = conn.cursor()

c.execute("""PRAGMA journal_mode = OFF""")
c.execute("""PRAGMA synchronous = 0""")
c.execute("""PRAGMA locking_mode = EXCLUSIVE""")
c.execute("""PRAGMA count_change = OFF""")
c.execute("""PRAGMA temp_store = 2""")

c.execute("""CREATE TABLE tmpTranscripts_arm_3R_transcripts (id INTEGER PRIMARY KEY, name varchar(255), chromosome varchar(255), start int(11), end int(11), direction tinyint(4), tags varchar(1023), bin int(11), exons varchar(10000))""")
c.execute("""CREATE INDEX 'iTranscript_arm_3R_14943' ON 'tmpTranscripts_arm_3R_transcripts' (start, end, direction)""")

t1 = time()

for i in range(0, 5000000):
    c.execute("""INSERT INTO tmpTranscripts_arm_3R_transcripts (name, chromosome, start, end, direction, tags, bin, exons) VALUES ('SRR060644.1', 'arm_3R', %d, %d, %d, 'feature=transcript;bestRegion=(self);nbGaps=0;nbMismatches=0;ID=SRR060644.1;identity=100.0', 300011450, '')""" % ((i+123)%352, (i+523)%422, (i+866)%536))
    if(not i%10000):
        print("Insert:", i)

t2 = time()
print("Insert time", t2-t1)

conn.commit()

t1 = time()
c.execute("""SELECT * FROM tmpTranscripts_arm_3R_transcripts ORDER BY start, end, direction""")

i = 0
for row in c:
    a = row[0]
    if(not i%10000):
        print("Get:", i, row)
    i+=1

t2 = time()
print("Sort time", t2-t1)

c.close()

и для MongoDB

from pymongo import Connection
from pymongo import ASCENDING, DESCENDING
from time import time

connection = Connection()
connection = Connection('localhost', 27017)
db = connection['test-database']
collection = db['test-collection']
posts = db.posts

posts.create_index([("start", ASCENDING), ("end", ASCENDING), ("direction", ASCENDING)])

t1 = time()

for i in range(0, 5000000):
    post = { "name": 'SRR060644.1',
            "chromosome": 'arm_3R',
            "start": (i+123)%352,
            "end": (i+523)%422,
            "direction": (i+866)%536,
            "tags": 'feature=transcript;bestRegion=(self);nbGaps=0;nbMismatches=0;ID=SRR060644.1;identity=100.0',
            "bin": 300011450,
            "exons": ''}

    posts.insert(post)

    if(not i%10000):
        print("Insert:", i)

t2 = time()
print("Insert time", t2-t1)

t1 = time()

i = 0
for post in posts.find().sort([("start", ASCENDING), ("end", ASCENDING), ("direction", ASCENDING)]):
    if(not i%10000):
        print("Get:", i, post)
    i+=1

t2 = time()
print("Sort time", t2-t1)
person Zuljin    schedule 07.10.2011