Оптимизация таблицы

[Лысый]

Всем привет!
FB 1.5.1
Имеется таблица населенных пунктов (163 982 записей):

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

CREATE TABLE TOWN (
    ID_TOWN       INTEGER NOT NULL,
    ID_REGION     INTEGER NOT NULL,
    ID_DISTRICT   INTEGER,
    ID_TOWN_TYPE  INTEGER NOT NULL,
    NAME          VARCHAR(40) NOT NULL,
    ZIP_CODE      VARCHAR(6)
);

ALTER TABLE TOWN ADD CONSTRAINT PK_TOWN PRIMARY KEY (ID_TOWN);

ALTER TABLE TOWN ADD CONSTRAINT FK_DISTRICT__TOWN FOREIGN KEY (ID_DISTRICT) REFERENCES DISTRICT (ID_DISTRICT) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE TOWN ADD CONSTRAINT FK_REGION__TOWN FOREIGN KEY (ID_REGION) REFERENCES REGION (ID_REGION) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE TOWN ADD CONSTRAINT FK_TOWN_TYPE__TOWN FOREIGN KEY (ID_TOWN_TYPE) REFERENCES TOWN_TYPE (ID_TOWN_TYPE);

CREATE INDEX IDX_TOWN_NAME ON TOWN (NAME);

Имеется выборка:

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

SELECT
    T.ID_TOWN
  , R.ID_REGION
  , D.ID_DISTRICT
  , TT.SHORT_NAME
  , T.ID_TOWN_TYPE
  , T.NAME
  , T.ZIP_CODE
  , R.NAME AS REGION_NAME
  , COALESCE(D.NAME, '-') AS DISTRICT_NAME
FROM TOWN T LEFT JOIN DISTRICT D ON T.ID_DISTRICT = D.ID_DISTRICT
            LEFT JOIN REGION R ON T.ID_REGION = R.ID_REGION +0
            LEFT JOIN TOWN_TYPE TT ON T.ID_TOWN_TYPE = TT.ID_TOWN_TYPE +0
WHERE UPPER(T.NAME) LIKE UPPER(:pWord) || '%'

Идет интенсивный ввод данных с выборкой по населенным пунктам. Юзвери при выборе задают условия поиска весьма безсистемно, т.е. могут набрать букву "М" и получить полный список нас. пунктов на букву "М". При этом загрузка проца весьма велика (~80%). При выборе "Москва" где то ~35-40%, что на мой взгляд тоже многовато.
Железо не специализированно: PIV 2Г ОЗУ 800Мб.
Вопрос как можно снизить нагрузку? Читал что индексы с больших таблиц на маленькие снижают производительность. Попытка деактивировать индексы, которые создаются автоматом (IBExpert) для FK_TOWN_TYPE__TOWN и FK_REGION__TOWN успехом не увенчались... Сложилось впечатление, что деактивировать индекс и оставить FK нельзя. Что посоветуете?

[kdv]

эх, по-моему не с той стороны ты зашел. нельзя при явных outer join скармливать серверу сначала большие таблицы, а потом маленькие.
Надо наоборот.

поменяй порядок таблиц на обратный и напиши right join вместо left.

[Лысый]

поменяй порядок таблиц на обратный и напиши right join вместо left.

Сделал так:

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

SELECT
       T.ID_TOWN
     , TT.SHORT_NAME
     , T.ID_TOWN_TYPE
     , T.NAME
     , T.ZIP_CODE
  FROM TOWN_TYPE TT RIGHT JOIN TOWN T ON T.ID_TOWN_TYPE = TT.ID_TOWN_TYPE
 WHERE UPPER(T.NAME) LIKE UPPER(:pWord) || '%'

Разница с left join в пару ms, причем в пользу последнего.

[sag]

Предположу, что запрос выполняется по таблице TOWN перебором NATURAL, а индекс, который хотелось бы использовать (IDX_TOWN_NAME) игнорируется. Так?
Попробуй для начала поэкспериментировать без джойнов, сравни планы и время выполнения запросов:

SELECT T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE
FROM TOWN T WHERE UPPER(T.NAME) LIKE UPPER('М') || '%'

SELECT T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE
FROM TOWN T WHERE T.NAME LIKE 'М' || '%'

SELECT T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE
FROM TOWN T WHERE T.NAME LIKE 'М%'

SELECT T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE
FROM TOWN T WHERE T.NAME STARTING 'М'

[Лысый]

Предположу, что запрос выполняется по таблице TOWN перебором NATURAL, а индекс, который хотелось бы использовать (IDX_TOWN_NAME) игнорируется. Так?

Совершенно верно

Результат эксперимента:
PLAN (T NATURAL) для:

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

SELECT  T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE 
  FROM TOWN T WHERE UPPER(T.NAME) LIKE UPPER('М') || '%'

SELECT T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE 
FROM TOWN T WHERE UPPER(T.NAME) LIKE 'М%'

SELECT  T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE 
FROM TOWN T WHERE T.NAME LIKE 'М' || '%'

SELECT T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE 
FROM TOWN T WHERE UPPER(T.NAME) STARTING 'М'

PLAN (T INDEX (IDX_TOWN_NAME)) для:

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

SELECT  T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE 
FROM TOWN T WHERE T.NAME LIKE 'М%'

SELECT  T.ID_TOWN, T.ID_TOWN_TYPE, T.ZIP_CODE 
FROM TOWN T WHERE T.NAME STARTING 'М'

Чтож можно делать выводы... Напрашивается поле в таблицу с заглавным названием.

[sag]

> Чтож можно делать выводы... Напрашивается поле
> в таблицу с заглавным названием.

На это я и намекал. А лефтджойны в твоем случае - вторичны, этож, насколько я понял, "допривязка" дополнительных "небольших" справочников.

[Лысый]

> Чтож можно делать выводы... Напрашивается поле
> в таблицу с заглавным названием.

На это я и намекал. А лефтджойны в твоем случае - вторичны, этож, насколько я понял, "допривязка" дополнительных "небольших" справочников.

Все верно полностью Спасибо.
Завтра добавлю поле.

[Лысый]

Ну и для полного счастья... что все таки с индексами привязанными к FK? Можно их удалить/деактивировать без удаления самого FK?

[Мозгокрут 13-ый]

можно. установив в rdb$index_inactive значение 3. а смысл? FK без индекса не сможет проверяться. не умеем мы пока такого. Оракл - умеет без индексов и ПК и ФК проверять, тупым перебором таблицы. Это надо?

[Лысый]

можно. установив в rdb$index_inactive значение 3. а смысл? FK без индекса не сможет проверяться. не умеем мы пока такого. Оракл - умеет без индексов и ПК и ФК проверять, тупым перебором таблицы. Это надо?

Ну да, я по аналогии с ораклом и рассуждал.. ну нет так нет.

[Мозгокрут 13-ый]

я думал у тебя проблема другая - много fk на одну таблицу, в результате по одному столбцу больше 1 индекса. По идее это должно быть "исправлено" в FB 2.0, т.е. возможность создания FK на существующий индекс, пусть даже и созданный вручную.

[Лысый]

Добавил поле, создал по нему индекс - летает! Всем спасибо!