Оптимизация запросов к базе данных Oracle /fetchall

Question

Дизайн базы данных далек от оптимального, но я должен с этим справиться, и теперь я действительно застрял.

Редактировать: я использую cx_Oracle

ОК, так что это мой запрос:

query="select degree, spectraldev.event.eventnumber \
       from spectraldev.degree \
       join spectraldev.alignment on \
            (spectraldev.version_id = alignment.version_id) \
       join spectraldev.event on \
            (alignment.timestamp between event.eventstart and event.eventstop) \
       join spectraldev.eventsetup on \
            (spectraldev.event.eventsetup = spectraldev.eventsetup.oid) \
       where spectraldev.event.eventnumber>=" + options.start + " AND spectraldev.event.eventnumber<=" + options.stop + " AND \
            HITS>=" + str(options.minimum_hits)+" \
       order by spectraldev.event.eventnumber"

db_cursor.execute(query)

Который возвращает кучу degrees (12.34 и т. д.) для многих событий, которые идентифицируются уникальным номером (eventnumber как 346554).

Таким образом, я получаю таблицу, как это:

454544    45.2
454544    12.56
454544    41.1
454544    45.4
454600    22.3
454600    24.13
454600    21.32
454600    22.53
454600    54.51
454600    33.87
454610    32.7
454610    12.99

И так далее…

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

Я думаю, что это можно сделать в SQL, но я просто не могу заставить его работать. Сейчас я использую для этого python, но команде fetch требуется около 1-2 часов для завершения около 2000 событий, что слишком медленно, так как мне нужно обработать около 1000000 событий.

Это моя часть, которая занимает так много времени:

_degrees = []
for degree, eventNumber in cursor.fetchall():
    _degrees.append([eventNumber, degree])

а затем сортировка (что действительно быстро, < 1сек) и вычисление средних (также очень быстро):

_d={}
for eventNumber, degree in _degrees:
    _d.setdefault(eventNumber, []).append(degree)

for event in events:
    _curDegree = _degrees[int(event)]
    _meanDegree = sum(_curDegree) / float(len(_curDegree))
    meanDegrees.append(_meanDegree)

Есть ли способ сделать часть Python в SQL?

Answer 1

Это в стороне, но важный. Вы широко открыты для SQL-инъекций. Это может неиметь значения в вашем конкретном случае, но лучше всегда кодировать для худшего.

Вы не упоминаете, какой модуль вы используете, но предполагая, что он совместим с PEP 249(вы, вероятно, используете cx_Oracle), тогда вы можете передать словарь с именованными параметрами связывания. Типичный запрос может выглядеть так:

query = """select column1 from my_table where id = :my_id"""
bind_vars = {'my_id' : 1}

db_cursor.execute(query, bind_vars)

По вашему фактическому запросу вы конвертируете некоторые переменные (options.startнапример) в строку в Python, но не в кавычках в SQL, что означает, что они неявно преобразуются обратно в число. Это почти наверняка не нужно.

---

По отношению к вашей актуальной задаче 1-2 часа на завершение 2000 событий, вы правы, смешны. Вы не опубликовали схему, но я думаю, что вам не хватает индексов.

Чтобы получить среднее количество градусов на номер события, вы должны использоватьavg() функция. Это сделает ваш запрос:

select spectraldev.event.eventnumber, avg(degree) as degree
  from spectraldev.degree
  join spectraldev.alignment 
        -- I think this is wrong on your query
    on (degree.version_id = alignment.version_id)
  join spectraldev.event 
    on (alignment.timestamp between event.eventstart and event.eventstop)
  join spectraldev.eventsetup 
    on (spectraldev.event.eventsetup = spectraldev.eventsetup.oid)
 where spectraldev.event.eventnumber >= :start
   and spectraldev.event.eventnumber <= :stop
   and hits >= :minimum_hits
 group by spectraldev.event.eventnumber
 order by spectraldev.event.eventnumber

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

Судя по этому, вам нужен индекс для следующих таблиц и столбцов;

• СОБЫТИЕ - eventnumber, eventstart, eventstop, eventsetup
• СТЕПЕНЬ - version_id
• ВЫРАВНИВАНИЕ - version_id, tstamp
• EVENTSETUP - oid

и где угодно hits возможно.

Сказав все, что ваша проблема может быть указателями. Вы не предоставили свой план объяснения или схему, или количество строк, так что это будет предположение. Однако, если вы выбираете значительную часть строк в таблице, CBO может использовать индексы, когда это не следует. Принудительное полное сканирование таблицы с использованием полной подсказки, /*+ full(event) */ например, может решить вашу проблему.

Удаление order by, если это не требуется, может также значительно ускорить ваш запрос.