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

← →
Aristarh © (2003-07-24 19:39) [0]

Неужели все сортировки настолько(!) тормозят запрос?
Запросы:

SELECT SA.SALDODATE, S.NAME, A.HOUSE, A.LITERA, A.FLAT, A.FIO, SALDOIN, NACH, PAY, SALDOOUT, SA.ID FROM STREETS S, ABONENTS A, SALDO SA WHERE ( (SA.SALDODATE = "01.07.2003") and (SA.ID = A.ID) and (S.CODE = A.STREET) ) order by Streets.Name, House, Litera, Flat

Либо:

SELECT SA.SALDODATE, S.NAME, A.HOUSE, A.LITERA, A.FLAT, A.FIO, SALDOIN, NACH, PAY, SALDOOUT, SA.ID FROM SALDO SA INNER JOIN ABONENTS A ON (SA.ID = A.ID) INNER JOIN STREETS S ON (A.STREET = S.CODE) WHERE ( (SA.SALDODATE = "01.07.2003") ) order by Streets.Name, House, Litera, Flat

Выполняются 5 секунд каждый. Это запросы, которые возвращают одну и ту же выборку.

Стоит только убрать сортировку, т.е. строку
order by Streets.Name, House, Litera, Flat
как запросы начинают выполняться за 50 милисекунд (!!) В сто раз быстрее. Причем неважно какая сортировка, т.е. даже при сортировке по простому индексированому Saldo.ID все равно наблюдается замедление в 100 раз. (5 сек)

Как можно уменьшить время сортировки? Или все безнадежно?

Структура таблиц:

SALDO:
ID INTEGER NOT NULL, (индекс)
SALDOIN DOUBLE PRECISION,
NACH DOUBLE PRECISION,
PAY DOUBLE PRECISION,
SALDOOUT DOUBLE PRECISION,
SALDODATE DATE NOT NULL (индекс)

ABONENTS:
ID INTEGER NOT NULL, (primary key)
FIO CHAR(30) NOT NULL, (индекс)
STREET INTEGER, (индекс)
HOUSE SMALLINT,
LITERA CHAR(3),
FLAT SMALLINT,

STREET:
CODE INTEGER NOT NULL, (primary key)
( 50) Неужели все сортировки настолько(!) тормозят запрос?
Запросы:

SELECT SA.SALDODATE, S.NAME, A.HOUSE, A.LITERA, A.FLAT, A.FIO, SALDOIN, NACH, PAY, SALDOOUT, SA.ID FROM STREETS S, ABONENTS A, SALDO SA WHERE ( (SA.SALDODATE = "01.07.2003") and (SA.ID = A.ID) and (S.CODE = A.STREET) ) order by Streets.Name, House, Litera, Flat

Либо:

SELECT SA.SALDODATE, S.NAME, A.HOUSE, A.LITERA, A.FLAT, A.FIO, SALDOIN, NACH, PAY, SALDOOUT, SA.ID FROM SALDO SA INNER JOIN ABONENTS A ON (SA.ID = A.ID) INNER JOIN STREETS S ON (A.STREET = S.CODE) WHERE ( (SA.SALDODATE = "01.07.2003") ) order by Streets.Name, House, Litera, Flat

Выполняются 5 секунд каждый. Это запросы, которые возвращают одну и ту же выборку.

Стоит только убрать сортировку, т.е. строку
order by Streets.Name, House, Litera, Flat
как запросы начинают выполняться за 50 милисекунд (!!) В сто раз быстрее. Причем неважно какая сортировка, т.е. даже при сортировке по простому индексированому Saldo.ID все равно наблюдается замедление в 100 раз. (5 сек)

Как можно уменьшить время сортировки? Или все безнадежно?

Структура таблиц:

SALDO:
ID INTEGER NOT NULL, (индекс)
SALDOIN DOUBLE PRECISION,
NACH DOUBLE PRECISION,
PAY DOUBLE PRECISION,
SALDOOUT DOUBLE PRECISION,
SALDODATE DATE NOT NULL (индекс)

ABONENTS:
ID INTEGER NOT NULL, (primary key)
FIO CHAR(30) NOT NULL, (индекс)
STREET INTEGER, (индекс)
HOUSE SMALLINT,
LITERA CHAR(3),
FLAT SMALLINT,

STREET:
CODE INTEGER NOT NULL, (primary key)
NAME VARCHAR(50) NOT NULL

← →
Sergey13 © (2003-07-25 08:23) [1]

>Неужели все сортировки настолько(!) тормозят запрос?
Не все. Вернее все, но не все "настолько(!)".
А что тебя так удивляет? Сколько записей возвращает запрос? Тем паче что первое сортируемое поле VARCHAR(50).

← →
Johnmen © (2003-07-25 09:14) [2]

Смотри планы выполнения запросов. Возможно, удастся оптимизировать...

← →
Zacho © (2003-07-25 09:42) [3]

Какая версия FB ? Подозреваю, что попробовав выполнить тот же запрос на FB 1.5 или Yaffil ты приятно удивишься.

← →
Aristarh © (2003-07-25 11:52) [4]

>Sergey13 © (25.07.03 08:23)

Запрос возвращает порядка 5 тыс. записей из таблицы Saldo, в
которой содержится всего ~150 тыс. записей.

>Тем паче что первое сортируемое поле VARCHAR(50).

Даже по целому Saldo.ID время выполнения точно такое
же: 5 сек. Так что VARCHAR ни при чем.

>Johnmen © (25.07.03 09:14)

Возможно и удастся... Где можно план посмотреть?

>Zacho © (25.07.03 09:42)

Версия Firebird-1.0.0.796-Win32

← →
Sergey13 © (2003-07-25 11:56) [5]

А че - нормально. 5000 записей отсортировать по 4 полям - это что просто что ли? Уменьшай количество - выиграешь в скорости. 5000 сразу все равно никому не нужно (как правило).

← →
Aristarh © (2003-07-25 12:01) [6]

>Sergey13 © (25.07.03 11:56)

А если не по четырем полям? А по Saldo.ID?
Да и нужна такая сортировка, это отчетность в бухгалтерию, вразнобой никак.

← →
Zacho © (2003-07-25 12:07) [7]

> Aristarh © (25.07.03 11:52)

Если я все правильно путаю :), то использования временных файлов в памяти для сортировки есть только в Yaffil (и может быть в FB 1.5). А в IB - FB 1.0 для этого используются временные файлы на диске, отсюда и тормоза даже при наличии индекса.
А посмотреть план запроса можно в любом инструменте для работы с IB - IBConsole, IBExpert и т.д., да хоть в isql.exe

← →
Zacho © (2003-07-25 12:09) [8]

> Aristarh © (25.07.03 12:01)

А для отчетов это не существенно, все равно основное время уйдет на фетч такого резалтсета на клиента.

← →
Aristarh © (2003-07-25 12:17) [9]

Plan:
PLAN SORT (JOIN (A NATURAL,S INDEX (RDB$PRIMARY11),SA INDEX (IXSALDOID,IXSALDODATE)))

Adapted plan:
PLAN SORT (JOIN (A NATURAL,S INDEX (INTEG_72),SA INDEX (IXSALDOID,IXSALDODATE)))

>А для отчетов это не существенно, все равно основное время
>уйдет на фетч такого резалтсета на клиента.

Если убрать сортировку, то резалтсет ведь не изменится, и время его доставки тоже. Происходит мгновенно.

← →
Zacho © (2003-07-25 12:22) [10]

> Aristarh © (25.07.03 12:17)

FetchAll 5000 записей мнговенно ? Не верю. Разве что на локальном компе с быстрым винтом и достаточным объемом памяти.

← →
Aristarh © (2003-07-25 12:41) [11]

>Zacho © (25.07.03 12:22)

Нет, IBExpert в SQL builder"e фетчит лишь сколько на экране.
Но все равно, причем здесь такая долгая сортировка?

Кстати, таже база, но по сети. Время увеличилось не на много 60мс. вместо 50мс

← →
Aristarh © (2003-07-25 12:47) [12]

Ставлю Firebird-1.5.0.3744-RC4-Win32
Что лучше выбрать SuperServer или Classic?

Нагрузка:
-две БД по 25 мегабайт каждая.
-5 клиентских машин, которые обращаются к каждой из баз.

← →
Zacho © (2003-07-25 12:55) [13]

> Aristarh © (25.07.03 12:41)

Есть время выполнения запроса и время фетча. Похоже, ты просто путаешь одно с другим. А для построения отчета придется фетчить все записи резалтсета на клиента, это и займет основное время, особенно по сети.
А долгая сортировка потому, что резалтсет не влезает в кэш сервера и происходит сортировка во временных файлах на диске. Если я что-то напутал, то пусть более знающие товарищи поправят и уточнят.
Как вариант решения - запрос без сортировки и сортировка уже полученного резалтсета в клиентском приложении, или действительно посмотри как это работает в Yaffil или FB 1.5

Aristarh © (25.07.03 12:47)

Если сервер многопроцессорный - то Classic

← →
Johnmen © (2003-07-25 13:16) [14]

Разница во времени выполнения запроса без сортировки (или с сортировкой по saldo.id) (1) и с сортировкой по полям не таблицы saldo (2) прекрасно объясняется при просмотре планов выполнения обоих.

← →
Aristarh © (2003-07-25 13:16) [15]

>Zacho © (25.07.03 12:55)

Поставил FB 1.5
Ну мля.... время выполнения стало 1.3 сек вместо 5.5 сек!

С первой версией полная совместимость? Можно ли безболезненно ставить на сервер версию 1.5?

За счет чего такой прирост?

← →
KDS © (2003-07-25 13:18) [16]

примечание 1: IB существует в двух архитектурах - Classic (CS) и SuperServer (SS). Архитектура Classic использует отдельные процессы на каждого пользователя, SuperServer - отдельные threads на пользователя в общем процессе. SS обладает общим кэшем, который увеличивает производительность, а CS благодаря разделению пользователей по процессам обладает большей надежностью. UDF для CS выполняются в адресном пространстве пользователя, а для SS - в общем адресном пространстве сервера. Поэтому в UDF для CS можно использовать глобальные константы, а в SS - нельзя. Для Windows IB существует только как SuperServer. Документы по отличиям CS от SS от Borland и IBPhoenix.

(C) http://www.ibase.ru/ib6.htm

← →
Zacho © (2003-07-25 13:19) [17]

> Aristarh © (25.07.03 13:16)
>
> С первой версией полная совместимость? Можно ли безболезненно
> ставить на сервер версию 1.5?

Можно, только стоит сделать backup базы под FB 1.0 и потом restore под FB1.5

>Zacho © (25.07.03 13:19)

А что используется другая дисковая структура?

>KDS © (25.07.03 13:18)

CS уже есть и под виндовс.

>Johnmen © (25.07.03 13:16)
PLAN SORT (JOIN (A NATURAL,S INDEX (INTEG_72),SA INDEX (IXSALDOID)))

PLAN JOIN (A NATURAL,S INDEX (INTEG_72),SA INDEX (IXSALDOID))

разница только в SORT

> Aristarh © (25.07.03 13:24)
> >Zacho © (25.07.03 13:19)
>
> А что используется другая дисковая структура?

Вроде бы ODS та же, но на всякий случай...

>Aristarh © (25.07.03 13:24)

Ну да. Так ведь сортировка в данном случае производится над выходным потоком, без использования индексов. А это не очень быстро...:)
Отсортируй только по saldo.id и посмотри план. Это поможет в понимании.
И ещё см. http://www.krista.ru/ib/ - весьма полезно.

Специально создал структуру похожую на описанную выше. Заполнил небольшим количеством данных и проверил отработку Планов.
Если пользовать запрос where and то в Плане появляется JOIN (A NATURAL это приводит к тому, что на каждую выбранную запись приходится перебирать всю таблицу ABONENTS. В случае использования в запросе соединения JOIN в Плане все проходит по индексам.
Резюме
Ускорить выборку до безобразия при использовании сортировки не удастся, но оптимизировать и сделать так, чтобы с ростом количества записей в таблице ABONENTS время запроса заметно не увеличивалось можно, используя соединения JOIN

Даже при сортировке то по Streets.Name или Streets.ID PLAN выглядит так:

PLAN SORT (JOIN (S NATURAL,A INDEX (ABONENTS_IDX2),SA INDEX (PK_SALDO)))

а при использовании соединения JOIN так:

PLAN SORT (JOIN (SA INDEX (SALDO_IDX1),A INDEX (PK_ABONENTS),S INDEX (PK_STREET)))

Спасибо всем. Разбирусь с планами, ветку возможно подниму.

>IgorRu © (25.07.03 15:08)

Нет, при запросе с JOIN

SELECT SA.SALDODATE, S.NAME, A.HOUSE, A.LITERA, A.FLAT, A.FIO, SALDOIN, NACH, PAY, SALDOOUT, SA.ID FROM SALDO SA INNER JOIN ABONENTS A ON (SA.ID = A.ID) INNER JOIN STREETS S ON (A.STREET = S.CODE) WHERE ( (SA.SALDODATE = "01.07.2003") ( JOIN (A NATURAL,S INDEX (RDB$PRIMARY11) Спасибо всем. Разбирусь с планами, ветку возможно подниму. >IgorRu © (25.07.03 15:08) Нет, при запросе с JOIN SELECT SA.SALDODATE, S.NAME, A.HOUSE, A.LITERA, A.FLAT, A.FIO, SALDOIN, NACH, PAY, SALDOOUT, SA.ID FROM SALDO SA INNER JOIN ABONENTS A ON (SA.ID = A.ID) INNER JOIN STREETS S ON (A.STREET = S.CODE) WHERE ( (SA.SALDODATE = "01.07.2003") ) order by Streets.Name, House, Litera, Flat план выглядит так: Plan: PLAN SORT (JOIN (A NATURAL,S INDEX (RDB$PRIMARY11),SA INDEX (IXSALDOID))) Adapted plan: PLAN SORT (JOIN (A NATURAL,S INDEX (INTEG_72),SA INDEX (IXSALDOID))) Т.е. теже самые натуралы :))

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