[firebird] Оптимизация запроса

← →
Kerk © (2006-02-18 19:44) [0]

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

SELECT F.ID_FILE, COUNT(d1.id_file) FROM DOWNLOADS d1, FILES F WHERE d1.remote_ip in (select remote_ip from DOWNLOADS where id_file = :IDFILE) AND F.id_file=d1.id_file AND F.id_file <> :IDFILE GROUP BY F.ID_FILE

Таблицы:
CREATE TABLE FILES ( ID_FILE INTEGER NOT NULL, NAME VARCHAR(255) NOT NULL ); ALTER TABLE FILES ADD CONSTRAINT PK_FILES PRIMARY KEY (ID_FILE);

CREATE TABLE DOWNLOADS ( ID_DOWNLOAD INTEGER NOT NULL, ID_FILE INTEGER NOT NULL, REMOTE_IP CHAR(15) NOT NULL ); ALTER TABLE DOWNLOADS ADD CONSTRAINT PK_DOWNLOADS PRIMARY KEY (ID_DOWNLOAD); ALTER TABLE DOWNLOADS ADD CONSTRAINT FK_DOWNLOADS FOREIGN KEY (ID_FILE) REFERENCES FILES (ID_FILE) ON DELETE CASCADE ON UPDATE CASCADE;

FILES - 191 строка
DOWNLOADS - 43054 строки

Запрос выполняется недопустимо долго. Есть идеи как оптимизировать?

← →
Desdechado © (2006-02-18 19:48) [1]

что он должен делать?

Ps почему не кофу "базы"?

← →
Kerk © (2006-02-18 19:51) [2]

Desdechado © (18.02.06 19:48) [1]
что он должен делать?

Должен выдавать список айди файлов, которые скачивались с тех же айпи, что и данный файл (:IDFILE).

> Ps почему не кофу "базы"?

Хм.. привычка.. сорри. :)

← →
jack128 © (2006-02-18 20:12) [3]

Kerk © (18.02.06 19:44)
Не знаю, поможет или нет, но почитать в любом случае стоит http://ibase.ru/devinfo/joins.htm

← →
Desdechado © (2006-02-18 20:16) [4]

думаю, можно обойтись без подзапроса, еще раз соединив с DOWNLOADS
версия FB не указана, некоторые из них плохо работают с подзапросами

← →
Kerk © (2006-02-18 20:24) [5]

Desdechado © (18.02.06 20:16) [4]

Такой вариант выполняется на несколько секунд дольше:
SELECT F.ID_FILE, COUNT(d1.id_file) FROM DOWNLOADS d1, DOWNLOADS d2, FILES F WHERE d1.remote_ip = d2.remote_ip AND d2.id_file = :IDFILE AND d1.ID_DOWNLOAD <> d2.ID_DOWNLOAD AND d1.id_file <> :IDFILE AND F.id_file=d1.id_file GROUP BY F.ID_FILE
Performance Analysis говорит, что производится на 3 млн больше Indexed Reads

← →
Kerk © (2006-02-18 20:48) [6]

Добавил индекс на поле REMOTE_IP
Execute time = 3s 78ms

лучше.. . а еще можно быстрее?

← →
Kerk © (2006-02-18 20:50) [7]

Вариант без подзапроса
Execute time = 250ms

Вопрос снят. Всем спасибо. :)

← →
Igorek © (2006-02-18 21:08) [8]

> Вопрос снят. Всем спасибо. :)

На здоровье. :)

← →
wicked © (2006-02-18 22:01) [9]

возможно, ляпну не в тему, но я для себя усвоил одно правило - избегать использования конструкции "something in (select...)" где только возможно..... альтернативная конструкция с exists работает быстрее, иногда на порядки.....
почему не в тему - это было с mssql..... но, думаю, общие принципы те же....

буду рад, если кто-то укажет на ошибочность моих логических построений, ибо експертом в IB/FB не являюсь (хоть и хотел бы)....

← →
Igorek © (2006-02-18 22:13) [10]

> wicked © (18.02.06 22:01) [9]

Если мсскл2005, то не страшен даже select where in, если внутренний запрос не зависит от внешнего - оптимайзер точно его перекроит в join. А если зависит - то неизвестно. Вообще любой запрос select where in можно переписать с join - и это будет методологически правильно.

← →
Desdechado © (2006-02-18 22:16) [11]

Слишком надеяться на оптимизатор не стоит:
1. Он часто делает ошибки (из-за плохой статистики индексов или просто глюк).
2. У него может быть включен режим разбора по правилам.

PS не у всех серверов есть, но лучше все-таки самому делать, чем на "дядю" полагаться

← →
Igorek © (2006-02-18 22:21) [12]

> PS не у всех серверов есть, но лучше все-таки самому делать,
> чем на "дядю" полагаться

100% согласен

← →
wicked © (2006-02-18 22:24) [13]

> Igorek © (18.02.06 22:13) [10]

> > wicked © (18.02.06 22:01) [9]
>
> Если мсскл2005, то не страшен даже select where in, если
> внутренний запрос не зависит от внешнего - оптимайзер точно
> его перекроит в join. А если зависит - то неизвестно. Вообще
> любой запрос select where in можно переписать с join - и
> это будет методологически правильно.

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

← →
тень отца Гамлета © (2006-02-19 01:53) [14]

join (по крайней мере в FB) всегда быстрее, чем подзапрос с in.

← →
Kerk © (2006-02-28 20:54) [15]

Сорри, но дубль два :)

SELECT f.id_file, d1.id_file, count(*) FROM DOWNLOADS d1, DOWNLOADS d2, FILES f WHERE d1.remote_ip = d2.remote_ip AND d2.id_file = f.id_file AND d1.id_file > f.id_file AND d1.remote_ip IN ( SELECT d.remote_ip FROM DOWNLOADS d GROUP BY d.remote_ip HAVING (count(*) > :user_min) AND (count(*) < :user_max)) GROUP BY f.id_file, d1.id_file HAVING count(*) > :DOWNLOADS_MIN

Выделенный фрагмент страшно замедлил выполнение.
Смысл - должны учитываться только айпи, количество скачиваний с который в диапазоне (:user_min,:user_max);

Либо лыжи не едут.. либо я..

← →
Igorek © (2006-02-28 21:12) [16]

Ты посмотри насчет подзапросов в файрбирде - что-такое должно быть.
А то вечно через з-цу разруливать запросы замаешься.
У меня сразу руки зачесались написать что-то типа:
left join ( select count(*) as rc, d.remote_ip as rip from DOWNLOADS d GROUP BY d.remote_ip ) c on d1.remote_ip = c.rip where ((c.rc > :user_min) AND (c.rc < :user_max))
Кроме того настоятельно советую рефакторить схему базы. Напр. ввести словарик уд. айпи с полем в колл. скачиваний.

← →
Kerk © (2006-02-28 21:26) [17]

Igorek © (28.02.06 21:12) [16]
Напр. ввести словарик уд. айпи с полем в колл. скачиваний.

И поддерживать актуальность триггерами на изменение DOWNLOADS? ИМХО криво.. зачем хранить одну и ту же инфу два раза? А вот если создавать словарик прямо в процедуре как временную таблицу (в FB есть такое надеюсь?)... попробовать стоит, думаю.

← →
Igorek © (2006-02-28 21:35) [18]

> И поддерживать актуальность триггерами на изменение DOWNLOADS?
> ИМХО криво.. зачем хранить одну и ту же инфу два раза?

Ничего кривого не вижу. У тебя классическая дилемма "хранить-вычислять". На сей раз в БД. Выбор в пользу "хранить" оптимальнее, так как быстрее обновить счетчик один раз при выгрузке (они ж нечасто) чем каждый раз перевычислять все счетчики. А база будет расти... надеюсь. :)

← →
Desdechado © (2006-02-28 22:16) [19]

> создавать словарик прямо в процедуре как временную таблицу (в FB есть такое надеюсь?)
временных таблиц нету, но зато есть мощное средство, именуемое FOR SELECT в процедуре, которая может возвращать набор данных через SUSPEND
в такой процедуре можно переколбасить всю БД и вернуть пару строк нужного тебе формата

← →
Kerk © (2006-02-28 22:58) [20]

Собственно, вот так оно сейчас выглядит:

CREATE PROCEDURE GET_DOWNLOADS ( USER_MIN INTEGER, USER_MAX INTEGER, DOWNLOADS_MIN INTEGER) RETURNS ( ID_MASTERFILE INTEGER, ID_SLAVEFILE INTEGER, COMMON_DOWNLOADS INTEGER) AS begin FOR SELECT f.id_file, d1.id_file, count(*) FROM DOWNLOADS d1, DOWNLOADS d2, FILES f, DOWNLOAD_COUNT dc WHERE d1.remote_ip = d2.remote_ip AND d2.id_file = f.id_file AND d1.id_file > f.id_file AND d1.remote_ip = dc.dc_remoteip AND (dc.dc_count > :user_min) AND (dc.dc_count < :user_max) GROUP BY f.id_file, d1.id_file HAVING count(*) > :DOWNLOADS_MIN INTO :ID_MASTERFILE, :ID_SLAVEFILE, :COMMON_DOWNLOADS DO SUSPEND; end

DOWNLOAD_COUNT - сделал табличку по совету Игорька, работает удовлетворительно.

P.S. Сумма значений столбца COUNT(*) должна быть меньше количества строк в DOWNLOADS вроде.. нет?

← →
Desdechado © (2006-02-28 23:02) [21]

если сделал таблицу, то ХП на один запрос смысла не имеет
в count поставь алиас

← →
Kerk © (2006-02-28 23:13) [22]

Desdechado © (28.02.06 23:02) [21]
ХП на один запрос смысла не имеет

Не соглашусь

> в count поставь алиас

Да ладно.. мелочь :)

← →
Харько © (2006-03-01 10:54) [23]

> AND (dc.dc_count > :user_min) AND (dc.dc_count < :user_max)

В вашем ПТУ between - табу?

← →
Kerk © (2006-03-01 10:57) [24]

Danilka © (01.03.06 11:16) [25]

> А перевести выделеный кусок на exists и уже без группировки
> не пробовал?

Пробовал. Быстрее или медленее стало трудно сказать. Через минуту где-то прекратил выполнение.

Последний вариант отрабатывает в пределах 2-3 секунд... но мне дополнительная таблица не нравится :(

> но мне дополнительная таблица не нравится :(

Почитай теорию на тему "нормализация-денормализация" - научишься находит оптимум в этом отношении.

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

Тут такое дело.
1) нормализировать можно с фанатизмом
например текстовое поле заменить на: словарик буковок и табличку с id буковки, порядком буковки и id строки с текстовым полем :)
Можете представить тормоза. :)
Словом надо знать меру.
2) у Керка таблица скачиваний - это фактически таблица первичных документов (лог операций, транзакций); а запросы требуют аггрегированной информации - это уже другой, высший слой абстракции; также напр. в бухгалтерии - есть первичные документы, а есть годовой баланс, который храниться и пересчитывается.

Жалко нету Юрия Зотова - он бы думаю дал более фундаментальные рекоммендации.

В принципе, для построения нужного мне графика, запрос из [20] придется выполнить 10-20 тыс раз.. так что развлекусь еще по полной :)

2Kerk © (18.02.06 19:44)

А если заменить
SELECT F.ID_FILE, COUNT(d1.id_file) FROM DOWNLOADS d1, FILES F WHERE d1.remote_ip in (select remote_ip from DOWNLOADS where id_file = :IDFILE) AND F.id_file=d1.id_file AND F.id_file <> :IDFILE GROUP BY F.ID_FILE

судя по

>Должен выдавать список айди файлов, которые скачивались с тех же айпи, что и данный файл (:IDFILE).

на

SELECT distinct ID_FILE FROM DOWNLOADS d1 WHERE d1.remote_ip in (select distinct remote_ip from DOWNLOADS where id_file = :IDFILE) AND d1.id_file <> :IDFILE

Может конечно и напутал чего. Не пробовал.

А IN всегда в IB долго работает

[firebird] Оптимизация запроса Найти похожие ветки