Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Firebird, InterBase Новый топик    Ответить
 Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
Есть 2 таблицы:
Service (14 253 068 записей) - в таблице по обращениям человека собран набор услуг(разновидностей) которые человек использует.
+

CREATE TABLE SERVICE (
    BD       SMALLINT NOT NULL, - номер БД (БД для анализа может быть несколько)
    APP_NUM  BIGINT NOT NULL, - номер дела человека
    CAL_NUM  SMALLINT NOT NULL, - номер обращения
    COD_ORG  SMALLINT NOT NULL, - код обслуживающей организации
    TYP      SMALLINT, - тип услуг (жилищные услуги или дрова/уголь)
    DATA_E   DATE, - дата окончания (выбирается по связке BD, APP_NUM, CAL_NUM из другой таблицы)
    POSL1    SMALLINT, - код разновидности услуги квартплата
    POSL2    SMALLINT, - код разновидности услуги отопление
    POSL3    SMALLINT, - код разновидности услуги горячая вода
    POSL4    SMALLINT, - код разновидности услуги холодная вода
    POSL5    SMALLINT, - код разновидности услуги газ
    POSL6    SMALLINT, - код разновидности услуги електрика
    POSL7    SMALLINT, - код разновидности услуги вывоз мусора
    POSL8    SMALLINT - код разновидности услуги стоки
);
ALTER TABLE SERVICE ADD CONSTRAINT PK_SERVICE PRIMARY KEY (BD, APP_NUM, CAL_NUM, COD_ORG);


Таблица разновидностей по всем услугам

+
[SRC sq
RIZNOVID_JKP (2160 записей) - Таблица разновидностеl]CREATE TABLE RIZNOVID_JKP (
BD SMALLINT NOT NULL, - номер БД
DICT SMALLINT NOT NULL, - номер справочника в БД
CODE SMALLINT NOT NULL, - код разновидности
CODE1 SMALLINT NOT NULL,
CONFG VARCHAR(1) default '',
NAME VARCHAR(50), - название разновидности
NAME_NEW VARCHAR(50),
PAR SMALLINT default 1,
PAR1 SMALLINT default 0,
COUNT_USE INTEGER, - количество использований (сколько раз встречается в БД)
LAST_USE DATE - максимальная дата_е из таблицы сервис
);
[/SRC]


Для заполнения таблицы разновидностей использую хранимку.
В хранимке идет сначала заполнение разновидностями - это операция проходит за 2-3 секунды.
А потом идет заполнение 2 полей:
COUNT_USE - количество использований в таблице сервис
LAST_USE - максимальная DATА_E каждой разновидности по таблице сервис.

Вот как я это делаю:
update RIZNOVID_JKP
set
    COUNT_USE = (select count(posl1) from service where posl1=RIZNOVID_JKP.CODE AND bd=RIZNOVID_JKP.BD and typ=0),
    LAST_USE = (select max(data_e) from service where posl1=RIZNOVID_JKP.CODE AND bd=RIZNOVID_JKP.BD and typ=0)
where dict=41;

dict=41 - это услуга квартплата.
таких update 8 штук, на каждую услугу. При разных услугах меняется столбец posl. Для отопления dict=15 и posl2

В чем у меня проблема:
Каждый select count, в отдельности, выполняется 10-15 секунд. что при размере таблицы в 2160 записей получаем около 9 часов на выполнение.
Вот план запроса
select count(posl1), max(data_e) from service where posl1=5 AND bd=1 and typ=0

План
PLAN (SERVICE INDEX (PK_SERVICE))

------ Информация о производительности ------
Время подготовки запроса = 31ms
Время выполнения запроса = 12s 547ms
Среднее время на получение одной записи = 12 547.00 ms
Current memory = 437 161 904
Max memory = 443 031 472
Memory buffers = 50 000
Reads from disk to cache = 134 682
Writes from cache to disk = 1
Чтений из кэша = 14 387 685


Можно ли как-то ускорить процесс прописывания в каждую разновидность COUNT_USE и LAST_USE
Тут конечно проблема еще связана с таблицей Service. Если бы в ней было меньше записей - все бы работало чуть быстрее. Но уменьшить ее увы никак.

П.С. Пока пиал пришла мысль - а ускорится ли процесс, если каждую разновидность по услугам в Service расписать не в ширину, а в высоту. Что скажите. Правда записей могу получить в 8 раз больше, т.е. 14млн*8.

--------------------------
Firebird 3.0.4.33054;
IBExpert 2018.5.14.1;
Windows 10 32-bit;
4Gb RAM;
SSD 512Mb + HDD 1 Tb;
Настройки по умолчанию

Среда разработки:
Delphi XE2 + FIBPlus 7.4
29 авг 19, 12:41    [21959727]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
Dmitriy_3206
Member

Откуда: Киев/Чернигов
Сообщений: 69
akrush,
не рассматривали вариант разнести разные виды работ в разные таблицы?
Учитывая что данные есть - вместо громоздкой текущей таблицы сделать view что бы не сразу перелопачивать софт.
29 авг 19, 13:40    [21959821]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
Dmitriy_3206,
Идея интересная. Сейчас попробую сделать 8 вьюх и из них выбирать, если я правильно понял направление :)
29 авг 19, 13:58    [21959841]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
Dmitriy_3206,
Спасибо за подсказку.
Не долго думаю соорудил запрос:
+
select 1 as cod_posl, posl1 as cod_rizn, count(posl1), max(data_e) from service
where posl1>0
GROUP BY posl1

UNION all

select 2 as cod_posl, posl2 as cod_rizn, count(posl2), max(data_e) from service
where posl2>0
GROUP BY posl2

UNION all

select 3 as cod_posl, posl3 as cod_rizn, count(posl3), max(data_e) from service
where posl3>0
GROUP BY posl3

UNION all

select 4 as cod_posl, posl4 as cod_rizn, count(posl4), max(data_e) from service
where posl4>0
GROUP BY posl4

UNION all

select 5 as cod_posl, posl5 as cod_rizn, count(posl5), max(data_e) from service
where posl5>0
GROUP BY posl5

UNION all

select 6 as cod_posl, posl6 as cod_rizn, count(posl6), max(data_e) from service
where posl6>0
GROUP BY posl6

UNION all

select 7 as cod_posl, posl7 as cod_rizn, count(posl7), max(data_e) from service
where posl7>0
GROUP BY posl7

UNION all

select 8 as cod_posl, posl8 as cod_rizn, count(posl8), max(data_e) from service
where posl8>0
GROUP BY posl8


Теперь создам таблицу и результат в таблицу. Тогда выборка в хранимке будет идти уже просто по таблице.
План запроса и время работы:
План
PLAN (SORT (SERVICE NATURAL), SORT (SERVICE NATURAL), SORT (SERVICE NATURAL), SORT (SERVICE NATURAL), SORT (SERVICE NATURAL), SORT (SERVICE NATURAL), SORT (SERVICE NATURAL), SORT (SERVICE NATURAL))

------ Информация о производительности ------
Время подготовки запроса = 32ms
Время выполнения запроса = 1m 43s 281ms
Среднее время на получение одной записи = 9 389.18 ms
Current memory = 430 872 720
Max memory = 539 928 000
Memory buffers = 50 000
Reads from disk to cache = 969 726
Writes from cache to disk = 1
Чтений из кэша = 117 901 680


Это не 9 часов.
29 авг 19, 14:11    [21959854]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
а подправить хранимку для работы с другой таблицей - это не проблема :)
29 авг 19, 14:12    [21959857]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
Все получилось.
Спасибо за "ускорительный пинок" в нужном направлении.
отработало все максимум минуты за 3
29 авг 19, 15:05    [21959912]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 783
akrush,

а у Вас таблица service индексы какие-нибудь имеет? Вижу что запросы идут по натуралу. Тогда это не дело.
29 авг 19, 18:08    [21960058]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 9831
akrush,

попробуй переписать на merge
29 авг 19, 19:36    [21960108]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
KreatorXXI
akrush,

а у Вас таблица service индексы какие-нибудь имеет? Вижу что запросы идут по натуралу. Тогда это не дело.


Значит плохо смотрите.
В первом посте есть ДДЛ таблицы с праймари кеем
Дальше по тексту есть запрос с планом и там видно что он выполняется как раз по ключу.
По поводу перебора натуралом - заметил что если использовать count или max, то идет натуралом. Но может ошибаюсь, тогда подскажите по какому полю желательно достроить дополнительный индекс.
29 авг 19, 21:44    [21960156]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
Симонов Денис
akrush,

попробуй переписать на merge


Денис, подскажите как, хотя бы условным примером по моим таблицам
29 авг 19, 21:45    [21960157]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 9831
akrush,

сначала неплохо бы проверить сколько будет выполняться вот такой запрос

select
  s.posl1, s.posl1 
  count(s.posl1), max(s.data_e) 
from service s
join  RIZNOVID_JKP r on s.posl1=r.CODE AND s.bd=r.BD
where s.typ=0 and r.dict=41
group by s.posl1, s.posl1


З.Ы. 50K буферов тут маловато
29 авг 19, 22:04    [21960163]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 783
akrush,

я на 100 процентов могу быть неправ. На вот запрос:
select count(posl1), max(data_e) from service where posl1=5 AND bd=1 and typ=0

И я бы попробовал создать индекс с posl1, bd, typ. И ещё бы туда добавил бы дату в обратном порядке (имею ввиду весь индекс сделал бы по убыванию). Именно в этом запросе индекс с датой не поможет. Потому что есть count. А без count можно хорошо сократить время выборки.
30 авг 19, 12:12    [21960451]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
Симонов Денис,
Запустил. Запустил давно, еще в ок. 09:00 по Киевскому времени.
Эксперт ушел в себя и до сих пор не вернулся.
Перезапустил ФБ чтобы оборвать процесс :(
30 авг 19, 12:33    [21960475]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
KreatorXXI
akrush,

я на 100 процентов могу быть неправ. На вот запрос:
select count(posl1), max(data_e) from service where posl1=5 AND bd=1 and typ=0

И я бы попробовал создать индекс с posl1, bd, typ. И ещё бы туда добавил бы дату в обратном порядке (имею ввиду весь индекс сделал бы по убыванию). Именно в этом запросе индекс с датой не поможет. Потому что есть count. А без count можно хорошо сократить время выборки.


В целом и так уже сократили.
с более чем 9 часов на 3 минуты.
Но я понял суть. попробую накидать таких индексов.


П.С. Эта база только моя для анализа и создания таблиц прекодировок, поэтому избыточность не существенна.
Уперся в проблему по сути только на одном клиенте, где большая база сервис. На остальных, даже где было 26 БД, сервис был поменьше - 1.7 млн записей.
30 авг 19, 13:12    [21960510]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
hvlad
Member

Откуда:
Сообщений: 10531
akrush
Эксперт ушел в себя и до сих пор не вернулся.
Перезапустил ФБ чтобы оборвать процесс :(
Надо было весь комп перезапустить. Ресетом.
Но надёжнее - пробки выкрутить.
На подстанции.
30 авг 19, 13:37    [21960536]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
KreatorXXI
И я бы попробовал создать индекс с posl1, bd, typ. И ещё бы туда добавил бы дату в обратном порядке (имею ввиду весь индекс сделал бы по убыванию). Именно в этом запросе индекс с датой не поможет. Потому что есть count. А без count можно хорошо сократить время выборки.


Создал индексы
CREATE INDEX IDX_SERVICE_POSL1 ON SERVICE (POSL1, BD, TYP);
CREATE INDEX IDX_SERVICE_POSL1DATE ON SERVICE (POSL1, BD, TYP, DATA_E);

только на все 8 услуг. размер БД чуток вырос, на 1,2Гб, но это не смертельно.

Обработка сократилась до 1 минуты. :)

Спасибо за подсказку.
30 авг 19, 13:38    [21960538]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
akrush
Симонов Денис,
Запустил. Запустил давно, еще в ок. 09:00 по Киевскому времени.
Эксперт ушел в себя и до сих пор не вернулся.
Перезапустил ФБ чтобы оборвать процесс :(
\

А вот после создания вышеприведенных индексов - все отработало:
План
PLAN SORT (JOIN (R NATURAL, S INDEX (IDX_SERVICE_POSL1)))

------ Информация о производительности ------
Время подготовки запроса = 16ms
Время выполнения запроса = 46s 109ms
Среднее время на получение одной записи = 2 561.61 ms
Current memory = 435 591 824
Max memory = 1 488 397 024
Memory buffers = 50 000
Reads from disk to cache = 250 748
Writes from cache to disk = 1
Чтений из кэша = 14 539 037
30 авг 19, 13:48    [21960549]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 9831
akrush
ALTER TABLE SERVICE ADD CONSTRAINT PK_SERVICE PRIMARY KEY (BD, APP_NUM, CAL_NUM, COD_ORG);



ну ё-маё. Структура у вас полный трындец.
30 авг 19, 13:58    [21960560]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
hvlad
Member

Откуда:
Сообщений: 10531
akrush
Создал индексы
CREATE INDEX IDX_SERVICE_POSL1 ON SERVICE (POSL1, BD, TYP);
CREATE INDEX IDX_SERVICE_POSL1DATE ON SERVICE (POSL1, BD, TYP, DATA_E);
Второй индекс полностью дублирует первый.
30 авг 19, 14:01    [21960563]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 9831
akrush
akrush
Симонов Денис,
Запустил. Запустил давно, еще в ок. 09:00 по Киевскому времени.
Эксперт ушел в себя и до сих пор не вернулся.
Перезапустил ФБ чтобы оборвать процесс :(
\

А вот после создания вышеприведенных индексов - все отработало:
План
PLAN SORT (JOIN (R NATURAL, S INDEX (IDX_SERVICE_POSL1)))

------ Информация о производительности ------
Время подготовки запроса = 16ms
Время выполнения запроса = 46s 109ms
Среднее время на получение одной записи = 2 561.61 ms
Current memory = 435 591 824
Max memory = 1 488 397 024
Memory buffers = 50 000
Reads from disk to cache = 250 748
Writes from cache to disk = 1
Чтений из кэша = 14 539 037


к RIZNOVID_JKP dict ещё индекс сделай
30 авг 19, 14:02    [21960564]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 783
akrush
akrush
Симонов Денис,
Запустил. Запустил давно, еще в ок. 09:00 по Киевскому времени.
Эксперт ушел в себя и до сих пор не вернулся.
Перезапустил ФБ чтобы оборвать процесс :(
\

А вот после создания вышеприведенных индексов - все отработало:
План
PLAN SORT (JOIN (R NATURAL, S INDEX (IDX_SERVICE_POSL1)))

------ Информация о производительности ------
Время подготовки запроса = 16ms
Время выполнения запроса = 46s 109ms
Среднее время на получение одной записи = 2 561.61 ms
Current memory = 435 591 824
Max memory = 1 488 397 024
Memory buffers = 50 000
Reads from disk to cache = 250 748
Writes from cache to disk = 1
Чтений из кэша = 14 539 037

Запрос надо переделать. Зачем два раза s.posl1? И, да, пробовать merge, если нужен update.
30 авг 19, 14:14    [21960573]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
Симонов Денис
akrush
ALTER TABLE SERVICE ADD CONSTRAINT PK_SERVICE PRIMARY KEY (BD, APP_NUM, CAL_NUM, COD_ORG);



ну ё-маё. Структура у вас полный трындец.

Трындец - потому что Кларион 2.1 ДОС.
Я веду конвертацию в ФБ и некоторые выборки потом формирую.
В целом уже была тема "Кларин отфаербердить"
Жаль только что директору это не надо и я это делаю на полном энтузиазме и в свободное, от основной работы, время.
Но уже почти на финише конвертации Clarion 2.1 DOS -> Firebird 3.0
Так куча всего: и битовые поля, где значения могут быть как 1 бит, так и несколько бит. И это все надо правильно разобрать хотябы по текущему, последнему "пониманию" структуры БД.

Ладно, простите за лирическое отступление.
30 авг 19, 14:55    [21960618]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
akrush
Member

Откуда: Киев, Украина
Сообщений: 352
Симонов Денис
akrush
пропущено...
\

А вот после создания вышеприведенных индексов - все отработало:
План
PLAN SORT (JOIN (R NATURAL, S INDEX (IDX_SERVICE_POSL1)))

------ Информация о производительности ------
Время подготовки запроса = 16ms
Время выполнения запроса = 46s 109ms
Среднее время на получение одной записи = 2 561.61 ms
Current memory = 435 591 824
Max memory = 1 488 397 024
Memory buffers = 50 000
Reads from disk to cache = 250 748
Writes from cache to disk = 1
Чтений из кэша = 14 539 037


к RIZNOVID_JKP dict ещё индекс сделай


Я правильно понял, желательно создать индекс:
CREATE INDEX IDX_RIZNOVID_JKP ON RIZNOVID_JKP (BD, DICT, CODE);

или только на поле DICT?
30 авг 19, 14:57    [21960619]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 9831
akrush,

нет. На DICT достаточно
30 авг 19, 15:20    [21960646]     Ответить | Цитировать Сообщить модератору
Все форумы / Firebird, InterBase Ответить