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

← →
Andrey (2004-05-31 10:13) [0]

Проблема в следующем: при попытке выполнить запрос к таблице, состоящей из 6,000,000 записей, комп выполняет его где-то секунд 30. Открытие той же таблицы через TTable (с установленным индексом) происходит почти сразу (через пару секунд). Отсюда и вопрос, что делать? (без запроса не обойтись)

← →
mutabor (2004-05-31 10:15) [1]

база какая?

← →
Vlad © (2004-05-31 10:16) [2]

> Andrey (31.05.04 10:13)

Приведи запрос

← →
Andrey (2004-05-31 10:17) [3]

Написано же в заголовке: D6, MySQL. Версию MySQL не знаю, но одна из последних

← →
Andrey (2004-05-31 10:17) [4]

select * from contract,client,fund
where contract.fundid=fund.fundid and contract.clt_auto=client.clt_auto
and (contract.ctp_auto=6478102 or contract.ctp_auto=684954 or contract.ctp_auto=409368)

← →
Полный дурак (2004-05-31 10:17) [5]

> Написано же в заголовке: D6, MySQL. Версию MySQL не знаю,
> но одна из последних

Это что, - запрос что-ли?

← →
Andrey (2004-05-31 10:21) [6]

Ну так что, есть какие-то варианты?

← →
Vlad © (2004-05-31 10:23) [7]

> Andrey (31.05.04 10:17) [4]

Индексы по всем полям cвязки есть ?
и еще, возможно or тормозит запрос, попробуй оставить только contract.ctp_auto=6478102, остальное убрать (ради эксперимента)

← →
Sergey13 © (2004-05-31 10:25) [8]

2Andrey (31.05.04 10:17) [4]
Индексы есть?

← →
DenK_vrtz © (2004-05-31 10:27) [9]

при выборе поля всех трех таблиц нужны (SELECT * ...)?

← →
Andrey (2004-05-31 10:29) [10]

Индексы есть по всем полям.

Запрос модифицировал:

select * from contract,client,fund
where contract.fundid=fund.fundid and contract.clt_auto=client.clt_auto
and (contract.ctp_auto IN (6478102,684954,409368));

Тормозит все равно.

А когда в Table ставлю Filter=ctp_auto=6478102 or и т.д. , нормально работает!

← →
Vlad © (2004-05-31 10:32) [11]

> Andrey (31.05.04 10:29) [10]

Я же сказал, попробуй оставить только одно значение contract.ctp_auto = 6478102 без всяких OR или IN (что одно и тоже)

← →
DenK_vrtz © (2004-05-31 10:32) [12]

СТОП.
Так результирующий курсор будет иметь ограничения по 3-м условиям?
исходя из - (contract.ctp_auto=6478102 or contract.ctp_auto=684954 or contract.ctp_auto=409368)

Если так, то сначала ограничь набор contract, а потом сделай объединение с client,fund.

← →
Andrey (2004-05-31 10:32) [13]

Еще особенность: в таблице contract примерно 15 полей, в таблице client около 30. Нужны в запросе почти все

← →
sniknik © (2004-05-31 10:34) [14]

не надо вводить в заблуждение (это мягко говоря)
вот это
select * from contract,client,fund
....
TTable не откроет ни через пару секунд ни вообще, запрос с обьеденением трех таблиц всегда медленнее чем выборка из одной (случай с TTable), поборотся за скорость конечно можно но это другая песня.
к примеру я бы (хотя я не работаю с MySQL) поставил бы условия с явным сравнением на первое место, на случай если MySQL выполняет условия поочередно, а обьеденение потом.

← →
Andrey (2004-05-31 10:34) [15]

"СТОП.
Так результирующий курсор будет иметь ограничения по 3-м условиям?
исходя из - (contract.ctp_auto=6478102 or contract.ctp_auto=684954 or contract.ctp_auto=409368)

Если так, то сначала ограничь набор contract, а потом сделай объединение с client,fund."

А ты примерчик не мог бы привести?

← →
Andrey (2004-05-31 10:38) [16]

> не надо вводить в заблуждение (это мягко говоря)
> вот это
> select * from contract,client,fund
> ....
> TTable не откроет ни через пару секунд ни вообще, запрос
> с обьеденением трех таблиц всегда медленнее чем выборка
> из одной (случай с TTable), поборотся за скорость конечно
> можно но это другая песня.

Ну это понятно. Без запроса не обойтись. В TTable я делал lookup-поля, но все равно потом нужен будет другой запрос (во время выполнения исходный запрос изменяется)

← →
DenK_vrtz © (2004-05-31 10:39) [17]

если нигде не наврал, то так :)

SELECT * FROM
(SELECT * FROM contract WHERE ctp_auto IN (6478102,684954,409368)) contract, client, fund
WHERE contract.fundid=fund.fundid
AND contract.clt_auto=client.clt_auto

← →
Vlad © (2004-05-31 10:41) [18]

sniknik © (31.05.04 10:34) [14]

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

Как показали тесты на IB, при ~6 млн записях практически не заметно, три таблицы там объединяются или одна. Если есть необходимые индексы, то запрос срабатывает пулей.
Но как только в запрос добавляешь OR или IN, это серьезно сказывается на быстродействии.

← →
Andrey (2004-05-31 10:42) [19]

> если нигде не наврал, то так :)
>
> SELECT * FROM
> (SELECT * FROM contract WHERE ctp_auto IN (6478102,684954,409368))
> contract, client, fund
> WHERE contract.fundid=fund.fundid
> AND contract.clt_auto=client.clt_auto

Блин. По-моему, select из select-a не работает. Но сейчас еще раз проверю

← →
Andrey (2004-05-31 10:46) [20]

Ага. По ctp_auto нету индекса, видимо в этом проблема?

← →
Vlad © (2004-05-31 10:47) [21]

> Andrey (31.05.04 10:46) [20]
> Ага. По ctp_auto нету индекса, видимо в этом проблема?

Естественно, индексы д.б. по всем полям, по к-рым происходит отбор.

← →
DenK_vrtz © (2004-05-31 10:48) [22]

Andrey (31.05.04 10:46) [20]

Так тебя ж сразу спросили про индексы!!!

← →
Sandman25+1 (2004-05-31 10:49) [23]

Индексы по fund.fundid и client.clt_auto есть?

← →
sniknik © (2004-05-31 10:50) [24]

Vlad © (31.05.04 10:41) [18]
т.е. хочеш сказать что декартово обьеденение трех таблиц по 6млн. записей такойже по скорости как открытие одной без обьеденений (как делает табле)?
позволь не поверить
а вот если есть ограничивающеее условие в силу которого вибирается малая часть то тут будет зависеть от того что было вначале обьеденение всего или выборка и последующее обьеденение малой части.
как сработает конкретный движок, вопрос, если есть анализатор запроса пофигу что где ставить. но на всякий случай... и вообще начал бы с тестов и доков что и как в конкретном движке работает (имхо).

← →
Andrey (2004-05-31 10:51) [25]

> Так тебя ж сразу спросили про индексы!!!

Ну, не заметил. Зато поговорили.
Кстати, селект из селекта все-таки не работает. Можно продолжить тему об этом.

← →
Andrey (2004-05-31 10:54) [26]

> Индексы по fund.fundid и client.clt_auto есть?

По ним есть - это первичные ключи

← →
DenK_vrtz © (2004-05-31 10:57) [27]

Если не работает select из select"a, то SELECT * FROM contract WHERE ctp_auto IN (6478102,684954,409368) можно загнать во view

:) "но это уже совсем другая история"

← →
Vlad © (2004-05-31 11:01) [28]

> sniknik © (31.05.04 10:50) [24]

> т.е. хочеш сказать что декартово обьеденение трех таблиц
> по 6млн. записей такойже по скорости как открытие одной
> без обьеденений (как делает табле)?

Есть основная таблица (6 млн записей) и есть 2 справочника, получаем декартово произведение трех таблиц, затем естественно связываем таблицы по ключевым полям и ограничиваем выборку по нек-му критерию (индексированному полю). Так вот - на глаз никакой разницы нет, что мы выбираем из одной, что из трех таблиц. Результат не заставляет ждать и секунды (Так на IB во всяком случае)

← →
Andrey (2004-05-31 11:04) [29]

> Индексы по fund.fundid и client.clt_auto есть?

Нет индексов по contract.fundid и contract.clt_auto. Может, из-за этого он вешается?

← →
Nikolay M. © (2004-05-31 11:06) [30]

> Есть основная таблица (6 млн записей) и есть 2 справочника,
> получаем декартово произведение трех таблиц, затем естественно
> связываем таблицы по ключевым полям и ограничиваем выборку
> по нек-му критерию (индексированному полю). Так вот - на
> глаз никакой разницы нет, что мы выбираем из одной, что
> из трех таблиц. Результат не заставляет ждать и секунды
> (Так на IB во всяком случае)

А что, в IB нет оптимизатора запросов, который сам оптимальным образом расставляет порядок условий в WHERE? Даже в мускуле оптимизатор есть.

← →
DenK_vrtz © (2004-05-31 11:06) [31]

Andrey (31.05.04 11:04) [29]

в ромашку играем? Пробуй!

← →
Andrey (2004-05-31 11:07) [32]

> Есть основная таблица (6 млн записей) и есть 2 справочника,
> получаем декартово произведение трех таблиц, затем естественно
> связываем таблицы по ключевым полям и ограничиваем выборку
> по нек-му критерию (индексированному полю). Так вот - на
> глаз никакой разницы нет, что мы выбираем из одной, что
> из трех таблиц. Результат не заставляет ждать и секунды
> (Так на IB во всяком случае)

Хе. Не совсем. Я сейчас убрал условие по ctp_auto и он один хрен тормозит.

← →
Vlad © (2004-05-31 11:12) [33]

> Andrey (31.05.04 11:07) [32]

> Я сейчас убрал условие по ctp_auto

Да не надо совсем его убирать. Естественно будет тормозить, ты же выбираешь теперь произведение всех записей !
Говорю, сделай индекс по этому полю, и ограничивай без всяких OR или IN то есть просто ctp_auto = XXX

← →
sniknik © (2004-05-31 11:13) [34]

Vlad © (31.05.04 11:01) [28]
вот!
> и ограничиваем выборку по нек-му критерию (индексированному полю).
это на твой взгляд делается после, на самом деле движок обычно сначала ограничивает, а обьеденение уже по 100записям к примеру (что в выборку вошло) делает, это большого времени не составляет,
убери условие ограничения (чтобы получить полное декартово произведение таблиц) и посмотри сколько времени займет.

> Нет индексов по contract.fundid и contract.clt_auto. Может, из-за этого он вешается?
одна из причин. для присоеденения по неиндексированному полу обычно строится временный индекс.

← →
sniknik © (2004-05-31 11:16) [35]

полу = полю
;)
впрочем к какому нибудь неиндексированному(и противоположному) полу я бы тоже присоеденился... ;о))

← →
Vlad © (2004-05-31 11:20) [36]

> sniknik © (31.05.04 11:13) [34]

а я разве с этим спорю, это делает оптимизатор практически во всех СУБД.
Речь о том, что если в ограничивающее условие впихнуть OR то будут заметные тормоза.

← →
Andrey (2004-05-31 11:26) [37]

Ну а как без OR обойтись?

Надо ограничивать по всем трем значениям!

← →
Vlad © (2004-05-31 11:28) [38]

> Andrey (31.05.04 11:26) [37]

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

← →
sniknik © (2004-05-31 11:31) [39]

может оптимизатор с OR не справляется? на access/MSSQL такого не замечал.

← →
Andrey (2004-05-31 11:32) [40]

Да я попробовал - один хер не пашет.

Может, MySQL плохо делает декартово произведение или вообще тупит при работе с большими таблицами?

> sniknik © (31.05.04 11:31) [39]

На небольших объемах (100-200 тыс) это не заметно, зато начинает здорово сказываться при объеме свыше 1 млн.
Тоже самое относится и к between - также серьезно затормаживает выборку, причем никакие индексы не спасают

еше попробуй делять явное присоеденение (INNER JOIN)
и индексы обязательно сделай.

> Andrey (31.05.04 11:32) [40]
> Да я попробовал - один <censored> не пашет.

Значит однозначно дело в индексах.
То есть у тебя не хватает индекса(ов) по каким-то полям используемым в where

← →
Andrey (2004-05-31 11:38) [44]

Блин, похоже, совсем беда.

Удалил данные (заново импортировал за неделю). Теперь в contract примерно 10000 записей, в client - около 4000. Не пашет. Не может же такого быть! Запрос ведь примитивный совсем.

← →
Andrey (2004-05-31 11:45) [45]

Ладно, идем другим путем. Делаю через Table, добавил lookup-поля из client и contract. В Filter написал ctp_auto= or ...

Далее, во время выполнения, добавляется еще одно ограничение (не по ctp_auto, а по другому полю). Причем строчка длинная (OR повторяется примерно 500 раз), и опять зависает, причем вешается Windows. Можно как-нибудь эту проблему обойти?

> причем вешается Windows. Можно как-нибудь эту проблему обойти?
конечно! переходи на линух, будет вешатся он.

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

← →
Andrey (2004-05-31 11:57) [47]

Напишу минут через 30. Сейчас убегаю на обед :)

← →
Andrey (2004-05-31 14:15) [48]

В общем, принято радикальное решение снести к черту MySQL и использовать MS SQL. Терпение кончилось, когда из таблицы contract исчезли 75% записей.

> Andrey (31.05.04 14:15) [48]

> В общем, принято радикальное решение снести к черту MySQL
> и использовать MS SQL

Без необходимых индексов и там работать не будет.

> Терпение кончилось, когда из таблицы contract исчезли 75%
> записей.

Вот так прям взяли и исчезли ? :-)

← →
Andrey (2004-05-31 15:58) [50]

> Без необходимых индексов и там работать не будет.

В MS SQL работает. Можешь поздравить :)

> Вот так прям взяли и исчезли ? :-)

Да, причем три раза подряд. С базой работаю только я, права у меня только на чтение, т.е. я сам стереть никак не могу. Мистика, блин :)

> Andrey (31.05.04 15:58) [50]

> В MS SQL работает. Можешь поздравить :)

Чтож, поздравляю :-)
Если верить твоим словам, вывод можно сделать только один - в MySQL либо нет оптимизатора запроса либо он просто корявый.

← →
Andrey (2004-05-31 16:10) [52]

> Чтож, поздравляю :-)
> Если верить твоим словам, вывод можно сделать только один
> - в MySQL либо нет оптимизатора запроса либо он просто корявый.

У меня вывод более радикальный: херня это полная. Как говорится, бесплатный сыр только в мышеловке.

Но я все равно не понял, почему не выполняется такой простой запрос. Хотя, фиг с ним.

Странно, выходит что сначала выбрали базу, а потом стали разбираться подходит она или нет. Обычно делают наоборот.

> Открытие той же таблицы через TTable (с установленным индексом)
> происходит почти сразу (через пару секунд).

А Вы уверены, что TTable фетчит все записи? Мне сдается, что она показывает малую часть НД и подгружает по мере скроллинга.

> Курдль © (31.05.04 17:47) [54]

Так и есть, все настройки в BDE алиасе, в т.ч. и курсор (клиентский/серверный), а так же количество записей в одном пакете (ROWSETSIZE)

А о чем тогда сыр-бор? 30 секунд для MySQL и указанного в [4] запроса с выборкой 6 000 000 записей - это невероятно быстро!

> Курдль © (31.05.04 17:52) [56]

Речь о результирующем наборе данных, там же не 6 млн записей, а к примеру 100-200, это должно быстро срабатывать даже при полном фетче.

так если все живет через БДЕ, если мне память не врет, Query сначала стягивает все НД на локальный комп, а потом их обрабатывает, а Table действительно фетчит их при необходимости... Отсюда и мнимое преимущество TTable...

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