Почему один и тот же запрос SQLite выполняется в 30 раз медленнее, если извлекает только в два раза больше результатов?
Я работаю над ускорением запроса, который использую уже около недели, и задал несколько вопросов по этому поводу здесь(Как ускорить получение результатов после выполнения запроса sqlite? , Это нормально, что sqlite.fetchall ()работает так медленно? , Как эффективно использовать min ()и max ()?).
С очень полезной помощью из ответов, данных там, мне удалось сократить время до запроса sqlite, занимающего 100.95секунд, и fetchall, занимающего:1485.43. Этого по-прежнему было недостаточно, поэтому, опробовав несколько разных индексов, мне удалось сократить время запроса до 0.08секунд для одной выборки, а время выборки — до 54.97секунд. Поэтому я подумал, что мне наконец удалось достаточно ускорить процесс.
Затем выполняется запрос для следующей выборки, что занимает 0.58секунд, а fetchall занимает 3952.80секунд. В третьем примере запрос занял 1.01секунд, а fetchall — 1970.67секунд.
Первая выборка извлекла 12951 строку, вторая выборка — 24972 строки, а третья — 6470 строк. Мне очень любопытно, почему первый образец извлекал строки намного быстрее, когда у него было примерно вдвое меньше, чем во втором примере.
Код(spectrumFeature_inputValuesсоставляет (1, ), (2, )и (3, )из 3 использованных выборок.):
self.cursor.execute('begin')
self.cursor.execute("EXPLAIN QUERY PLAN "+
"SELECT precursor_id, feature_table_id "+
"FROM `MSMS_precursor` "+
"INNER JOIN `spectrum` ON spectrum.spectrum_id = MSMS_precursor.spectrum_spectrum_id "+
"INNER JOIN `feature` ON feature.msrun_msrun_id = spectrum.msrun_msrun_id "+
"WHERE spectrum.scan_start_time BETWEEN feature.rtMin AND feature.rtMax "+
"AND MSMS_precursor.ion_mz BETWEEN feature.mzMin AND feature.mzMax "+
"AND feature.msrun_msrun_id = ?", spectrumFeature_InputValues)
print 'EXPLAIN QUERY PLAN: '
print self.cursor.fetchall()
import time
time0 = time.time()
self.cursor.execute("SELECT precursor_id, feature_table_id "+
"FROM `MSMS_precursor` "+
"INNER JOIN `spectrum` ON spectrum.spectrum_id = MSMS_precursor.spectrum_spectrum_id "+
"INNER JOIN `feature` ON feature.msrun_msrun_id = spectrum.msrun_msrun_id "+
"WHERE spectrum.scan_start_time BETWEEN feature.rtMin AND feature.rtMax "+
"AND MSMS_precursor.ion_mz BETWEEN feature.mzMin AND feature.mzMax "+
"AND feature.msrun_msrun_id = ?", spectrumFeature_InputValues)
print 'query took:',time.time()-time0,'seconds'
time0 = time.time()
precursorFeatureIds = self.cursor.fetchall()
print 'it fetched:',len(precursorFeatureIds),'rows'
print 'fetchall took',time.time()-time0,'seconds'
time0 = time.time()
for precursorAndFeatureID in precursorFeatureIds:
feature_has_MSMS_precursor_inputValues = (precursorAndFeatureID[0], precursorAndFeatureID[1])
self.cursor.execute("INSERT INTO `feature_has_MSMS_precursor` VALUES(?,?)", feature_has_MSMS_precursor_inputValues)
print 'inserting took',time.time()-time0,'seconds'
self.connection.commit()
и результаты:
EXPLAIN QUERY PLAN:
[(0, 0, 2, u'SCAN TABLE feature (~100000 rows)'), (0, 1, 1, u'SEARCH TABLE spectrum USING INDEX fk_spectrum_scahn_start_time_1 (scan_start_time>? AND scan_start_time? AND scan_start_time? AND scan_start_timeОператоры создания SQLite:
-- -----------------------------------------------------
-- Table `feature`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `feature` (
`feature_table_id` INT PRIMARY KEY NOT NULL,
`feature_id` VARCHAR(40) NOT NULL,
`intensity` DOUBLE NOT NULL,
`overallquality` DOUBLE NOT NULL,
`charge` INT NOT NULL,
`content` VARCHAR(45) NOT NULL,
`intensity_cutoff` DOUBLE NOT NULL,
`mzMin` DOUBLE NULL,
`mzMax` DOUBLE NULL,
`rtMin` DOUBLE NULL,
`rtMax` DOUBLE NULL,
`msrun_msrun_id` INT NOT NULL,
CONSTRAINT `fk_feature_msrun1`
FOREIGN KEY (`msrun_msrun_id` )
REFERENCES `msrun` (`msrun_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE INDEX `fk_mzMin_feature` ON `feature` (`mzMin` ASC);
CREATE INDEX `fk_mzMax_feature` ON `feature` (`mzMax` ASC);
CREATE INDEX `fk_rtMin_feature` ON `feature` (`rtMin` ASC);
CREATE INDEX `fk_rtMax_feature` ON `feature` (`rtMax` ASC);
DROP TABLE IF EXISTS `spectrum`;
-- -----------------------------------------------------
-- Table `spectrum`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `spectrum` (
`spectrum_id` INT PRIMARY KEY NOT NULL,
`spectrum_index` INT NOT NULL,
`ms_level` INT NOT NULL,
`base_peak_mz` DOUBLE NOT NULL,
`base_peak_intensity` DOUBLE NOT NULL,
`total_ion_current` DOUBLE NOT NULL,
`lowest_observes_mz` DOUBLE NOT NULL,
`highest_observed_mz` DOUBLE NOT NULL,
`scan_start_time` DOUBLE NOT NULL,
`ion_injection_time` DOUBLE,
`binary_data_mz` BLOB NOT NULL,
`binary_data_rt` BLOB NOT NULL,
`msrun_msrun_id` INT NOT NULL,
CONSTRAINT `fk_spectrum_msrun1`
FOREIGN KEY (`msrun_msrun_id` )
REFERENCES `msrun` (`msrun_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE INDEX `fk_spectrum_spectrum_id_1` ON `spectrum` (`spectrum_id` ASC);
CREATE INDEX `fk_spectrum_scahn_start_time_1` ON `spectrum` (`scan_start_time` ASC);
DROP TABLE IF EXISTS `feature_has_MSMS_precursor`;
-- -----------------------------------------------------
-- Table `spectrum_has_feature`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `feature_has_MSMS_precursor` (
`MSMS_precursor_precursor_id` INT NOT NULL,
`feature_feature_table_id` INT NOT NULL,
CONSTRAINT `fk_spectrum_has_feature_spectrum1`
FOREIGN KEY (`MSMS_precursor_precursor_id` )
REFERENCES `MSMS_precursor` (`precursor_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_spectrum_has_feature_feature1`
FOREIGN KEY (`feature_feature_table_id` )
REFERENCES `feature` (`feature_table_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE INDEX `fk_feature_has_MSMS_precursor_feature1` ON `feature_has_MSMS_precursor` (`feature_feature_table_id` ASC);
CREATE INDEX `fk_feature_has_MSMS_precursor_precursor1` ON `feature_has_MSMS_precursor` (`MSMS_precursor_precursor_id` ASC);
Как вы можете видеть, я сделал индексы из значений mzи rtкак в спектре, так и в признаках, потому что я полагал, что большая часть времени тратится на сравнение этих чисел вместе.
Так почему же первый образец намного быстрее второго и третьего?И как время запроса связано со временем выборки? Самое главное, есть ли способ ускорить это?
Обновление 1:
После разговора с коллегой это, вероятно, потому, что сравнение точки с двумерным измерением (rtMin, rtMax, mzMin, mzMax )займет n^2 времени. Это примерно соответствует тому, что второму сборщику потребовалось чуть больше 60 ^ 2 секунд (приблизительное время, которое потребовалось первому сборщику ), и он извлек немногим менее чем в два раза больше строк. Это не отвечает ни на один из моих вопросов.
Обновление 2:
Я попытался использовать дерево R *, как указано в комментариях. Я сделал новую таблицу:
CREATE VIRTUAL TABLE convexhull_edges USING rtree(
feature_feature_table_id,
rtMin, rtMax,
mzMin, mzMax,
);
и измените мой запрос на:
self.cursor.execute("SELECT precursor_id, feature_table_id "+
"FROM `MSMS_precursor` "+
"INNER JOIN `spectrum` ON spectrum.spectrum_id = MSMS_precursor.spectrum_spectrum_id "+
"INNER JOIN `feature` ON feature.msrun_msrun_id = spectrum.msrun_msrun_id "+
"INNER JOIN `convexhull_edges` ON convexhull_edges.feature_feature_table_id = feature.feature_table_id "
"WHERE spectrum.scan_start_time BETWEEN convexhull_edges.rtMin AND convexhull_edges.rtMax "+
"AND MSMS_precursor.ion_mz BETWEEN convexhull_edges.mzMin AND convexhull_edges.mzMax "+
"AND feature.msrun_msrun_id = ?", spectrumFeature_InputValues)
Это дало следующие результаты:
EXPLAIN QUERY PLAN:
[(0, 0, 3, u'SCAN TABLE convexhull_edges VIRTUAL TABLE INDEX 2: (~0 rows)'), (0, 1, 2, u'SEARCH TABLE feature USING INDEX sqlite_autoindex_feature_1 (feature_table_id=?) (~1 rows)'), (0, 2, 1, u'SEARCH TABLE spectrum USING INDEX fk_spectrum_scahn_start_time_1 (scan_start_time>? AND scan_start_time? AND scan_start_time? AND scan_start_timeТак что немного быстрее, чем мой предыдущий способ, но все же недостаточно быстро. Далее я собираюсь попробовать решение web _bod.
Обновление 3
Используя веб-решение _bod, я получил следующие результаты:
EXPLAIN QUERY PLAN:
[(0, 0, 2, u'SCAN TABLE feature (~100000 rows)'), (0, 1, 1, u'SEARCH TABLE spectrum USING INDEX fk_spectrum_scahn_start_time_1 (scan_start_time>? AND scan_start_time? AND scan_start_timeТретий, к сожалению, не закончился из-за перезагрузки. Так что это немного быстрее, чем мое первое решение, но медленнее, чем использование R *Tree
Обновление 4
Работая над другим запросом, который выполнялся невероятно медленно, я увидел, что он переходит в непрерывный сон (см. этот вопрос). Поэтому я проверил top при выполнении этого запроса, и он переключается между состояниями R и D, снижая использование ЦП со 100 до 50%. Возможно, поэтому он работает так медленно со всеми предоставленными решениями.
Обновление 5
Я перешел на MySQL, но получаю те же результаты.