Как оптимизировать запрос с условием в сортировке?

[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...
Как-то можно это оптимизировать?

[Gage]

Код: Выделить всё

SELECT MsgID, Max(AddDate)
FROM Table1
GROUP BY MsgID
ORDER BY Max(AddDate) DESC

А теперь подарок:
создай индексы на все поля, которые включаешь в WHERE
и будет тебе 100-кратное увеличение производительности.

[Merlin]

Код: Выделить всё

SELECT MsgID, Max(AddDate)
FROM Table1
GROUP BY MsgID
ORDER BY Max(AddDate) DESC

Долго пытался установить в голове связь между этим техстом и тем, что спрашивали. Не сумел.

А теперь подарок:
создай индексы на все поля, которые включаешь в WHERE
и будет тебе 100-кратное увеличение производительности.

Или 100-кратное замедление, как повезёт с полями.

[Merlin]

В форумной таблице все записи имеют 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

[Gage]

Да уж, сморозил так уж сморозил. [/b]

[Aleksandr.]

Констатирую очередного партизана на допросе, старательно скрывающего версию сервера. Сей запрос не только дублирует новые по количеству ответов, но и пропускает безответные. Если я правильно понял вопрос, то на 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" вычитал, что при выборке данных больше некоего процента от общего числа употребление индексов может замедлить, а не ускорить, запрос.

[Merlin]

А такой запрос не может быть быстрым в принципе. Таблица А перебирается натуралом, В - если нет индекса на TopMsgID - тоже. Использование индекса A.MsgID для группировки действительно только ухудшает ситуацию на полном фетче, а он (фетч) необходим для сортировки результата выборки по выражению, где никакие индексы ни при чём. Если количество записей удовлетворяющих условию WHERE A.topmsgid IS NULL мало по отношению к общему в А, то замена нулла на 0 в таблице, условия на WHERE A.topmsgid=0 и создание индекса по topmsgid улучшит ситуацию, если таких большинство - ухудшит. Если индекса на B.topmsgid нет, то его создание может ускорить объединение таблиц. Ваще-та не видя структуры индексов со статистикой и плана выполнения запроса заниматься оптимизацией - гадание на кофейной гуще.

[Merlin]

Упс, тока щас заметил, что таблица одна и та же Однозначно надо попробовать с индексом на topmsgid и ликвидацией нулла.

[Aleksandr.]

Упс, тока щас заметил, что таблица одна и та же Однозначно надо попробовать с индексом на 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 - так они что, тоже все могут быть быстрее, если какой-то индекс истчо добавить?

[Merlin]

Истчо бы мне понять, тупому, что к чему...

Читайте книги - источник знаний (С).

А то какие-то смутные разговоры о Natural, от которого надо избавляться.

Не всегда. В двух словах. Индекс может быть применён только в случае наложения на индексированные поля какого-либо условия. Нет условия - никуда от натурала (тупого перебора таблицы) не денешься. Есть условие - всё зависит от усечения выборки этим условием. Применение индекса тем эффективнее, чем в таблице меньше данных, этому условию отвечающих. Если же условию соответствует бОльшая часть записей в таблице, то от него можно получить и тормоза и очень даже нефиговые, тут перебор предпочтительнее. Что со своими запросами делать - дальше сам думай и экспериментируй.

[Aleksandr.]

Спасибо, что просветили.