Страница 1 из 1
Как оптимизировать запрос с условием в сортировке?
Добавлено: 28 ноя 2005, 20:37
Aleksandr.
В форумной таблице все записи имеют MsgID - ИД записи, TopMsgID - код записи, ответом на которую является сообщение (NULL для новых), AddDate - отметка времени, когда запись была добавлена.
Мне надо получить все записи из таблицы с TopMsgID=NULL, отсортированные, в случае, если ответов на сообщение нет, то по своим AddDate, иначе по AddDate ответов на сообщение (у которых TopMsgID=MsgID этой записи).
Запрос такого образца:
Код: Выделить всё
SELECT A.MsgID, A.AddDate
FROM Table1 A, Table1 B
WHERE A.topmsgid IS NULL AND B.TopMsgID=A.MsgID
ORDER BY (CASE WHEN B.TopMsgID IS NULL THEN A.AddDate ELSE B.AddDate END) DESC
работает быстро, но по понятным причинам дублирует результирующие записи по числу ответов на них.
А такой запрос:
Код: Выделить всё
SELECT A.MsgID, A.AddDate
FROM Table1 A
WHERE A.topmsgid IS NULL
ORDER BY COALESCE((SELECT Max(AddDate) FROM Table1 B WHERE B.TopMsgID=A.MsgID), A.AddDate) DESC
показывает то, что надо, но скорость его работы вообще просто жуткая по тормознутости: таблица на 770 записей фетчится за
3s 750ms, а 8150 записей - 8m 27s 281ms
. Не понимаю я такой разницы между MS SQL, который что один что другой моментально выполняет, и FireBird...
Как-то можно это оптимизировать?
Добавлено: 28 ноя 2005, 22:24
Gage
Код: Выделить всё
SELECT MsgID, Max(AddDate)
FROM Table1
GROUP BY MsgID
ORDER BY Max(AddDate) DESC
А теперь подарок:
создай индексы на все поля, которые включаешь в WHERE
и будет тебе 100-кратное увеличение производительности.
Добавлено: 28 ноя 2005, 22:47
Merlin
Gage писал(а):Код: Выделить всё
SELECT MsgID, Max(AddDate)
FROM Table1
GROUP BY MsgID
ORDER BY Max(AddDate) DESC
Долго пытался установить в голове связь между этим техстом и тем, что спрашивали. Не сумел.
Gage писал(а):
А теперь подарок:
создай индексы на все поля, которые включаешь в WHERE
и будет тебе 100-кратное увеличение производительности.
Или 100-кратное замедление, как повезёт с полями.
Re: Как оптимизировать запрос с условием в сортировке?
Добавлено: 28 ноя 2005, 23:06
Merlin
Aleksandr. писал(а):В форумной таблице все записи имеют MsgID - ИД записи, TopMsgID - код записи, ответом на которую является сообщение (NULL для новых), AddDate - отметка времени, когда запись была добавлена.
Мне надо получить все записи из таблицы с TopMsgID=NULL, отсортированные, в случае, если ответов на сообщение нет, то по своим AddDate, иначе по AddDate ответов на сообщение (у которых TopMsgID=MsgID этой записи).
Запрос такого образца:
Код: Выделить всё
SELECT A.MsgID, A.AddDate
FROM Table1 A, Table1 B
WHERE A.topmsgid IS NULL AND B.TopMsgID=A.MsgID
ORDER BY (CASE WHEN B.TopMsgID IS NULL THEN A.AddDate ELSE B.AddDate END) DESC
работает быстро, но по понятным причинам дублирует результирующие записи по числу ответов на них.
Констатирую очередного партизана на допросе, старательно скрывающего версию сервера. Сей запрос не только дублирует новые по количеству ответов, но и пропускает безответные. Если я правильно понял вопрос, то на FB 1.5 можно попробовать
Код: Выделить всё
SELECT A.MsgID,
Max(CASE WHEN B.TopMsgID IS NULL THEN A.AddDate ELSE B.AddDate END)
FROM Table1 A Left Join Table1 B On B.TopMsgID=A.MsgID
WHERE A.topmsgid IS NULL
Group By A.MsgID
ORDER BY 2 DESC
Добавлено: 28 ноя 2005, 23:19
Gage
Да уж, сморозил так уж сморозил.
[/b]
Re: Как оптимизировать запрос с условием в сортировке?
Добавлено: 29 ноя 2005, 15:03
Aleksandr.
Merlin писал(а):
Констатирую очередного партизана на допросе, старательно скрывающего версию сервера. Сей запрос не только дублирует новые по количеству ответов, но и пропускает безответные. Если я правильно понял вопрос, то на FB 1.5 можно попробовать
Код: Выделить всё
SELECT A.MsgID,
Max(CASE WHEN B.TopMsgID IS NULL THEN A.AddDate ELSE B.AddDate END)
FROM Table1 A Left Join Table1 B On B.TopMsgID=A.MsgID
WHERE A.topmsgid IS NULL
Group By A.MsgID
ORDER BY 2 DESC
Извините. FB 1.5.
Вопрос Вы поняли, судя по результатам, правильно, только Ваш вариант запроса дает истчо больше тормоза. Не понимаю, почему.
Что до индексов, их два на таблице: один по MsgID, а второй AddDate DESC. Вообще я не особо смотрел по индексам, потому как в книжке "Мир Interbase" вычитал, что при выборке данных больше некоего процента от общего числа употребление индексов может замедлить, а не ускорить, запрос.
Добавлено: 29 ноя 2005, 15:58
Merlin
А такой запрос не может быть быстрым в принципе. Таблица А перебирается натуралом, В - если нет индекса на TopMsgID - тоже. Использование индекса A.MsgID для группировки действительно только ухудшает ситуацию на полном фетче, а он (фетч) необходим для сортировки результата выборки по выражению, где никакие индексы ни при чём. Если количество записей удовлетворяющих условию WHERE A.topmsgid IS NULL мало по отношению к общему в А, то замена нулла на 0 в таблице, условия на WHERE A.topmsgid=0 и создание индекса по topmsgid улучшит ситуацию, если таких большинство - ухудшит. Если индекса на B.topmsgid нет, то его создание может ускорить объединение таблиц. Ваще-та не видя структуры индексов со статистикой и плана выполнения запроса заниматься оптимизацией - гадание на кофейной гуще.
Добавлено: 29 ноя 2005, 16:02
Merlin
Упс, тока щас заметил, что таблица одна и та же
Однозначно надо попробовать с индексом на topmsgid и ликвидацией нулла.
Добавлено: 29 ноя 2005, 17:02
Aleksandr.
Merlin писал(а):Упс, тока щас заметил, что таблица одна и та же
Однозначно надо попробовать с индексом на topmsgid и ликвидацией нулла.
Млин. Потрясающе.
Добавил индекс на TopMsgID, NULL перевел на 0, и запрос на 8000 записей ушел в доли секунды! Истчо бы мне понять, тупому, что к чему... А то какие-то смутные разговоры о Natural, от которого надо избавляться. У меня масса запросов наподобие
Код: Выделить всё
SELECT Table1.ID, Table1.Name, Table1.JoinID, Table2.Name, Table1.AddDate FROM Table1
LEFT JOIN Table2 ON Table2.ID=Table1.JoinID
ORDER BY AddDate DESC
индексы и них у всех есть на поле ID и на AddDate DESC, но у всех IBExpert рисует план с join natural - так они что, тоже все могут быть быстрее, если какой-то индекс истчо добавить?
Добавлено: 29 ноя 2005, 20:01
Merlin
Aleksandr. писал(а): Истчо бы мне понять, тупому, что к чему...
Читайте книги - источник знаний (С).
Aleksandr. писал(а):
А то какие-то смутные разговоры о Natural, от которого надо избавляться.
Не всегда. В двух словах. Индекс может быть применён только в случае наложения на индексированные поля какого-либо условия. Нет условия - никуда от натурала (тупого перебора таблицы) не денешься. Есть условие - всё зависит от усечения выборки этим условием. Применение индекса тем эффективнее, чем в таблице меньше данных, этому условию отвечающих. Если же условию соответствует бОльшая часть записей в таблице, то от него можно получить и тормоза и очень даже нефиговые, тут перебор предпочтительнее. Что со своими запросами делать - дальше сам думай и экспериментируй.
Добавлено: 30 ноя 2005, 17:11
Aleksandr.
Спасибо, что просветили.