ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Помогите с оптимизацией
24 сообщений из 24, страница 1 из 1
Помогите с оптимизацией
    #39855065
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
Есть 2 таблицы:
Service (14 253 068 записей) - в таблице по обращениям человека собран набор услуг(разновидностей) которые человек использует.
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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 каждой разновидности по таблице сервис.

Вот как я это делаю:
Код: sql
1.
2.
3.
4.
5.
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 часов на выполнение.
Вот план запроса
Код: sql
1.
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
...
Рейтинг: 0 / 0
29.08.2019, 12:41
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855140
Dmitriy_3206
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dmitriy_3206
Гость
akrush,
не рассматривали вариант разнести разные виды работ в разные таблицы?
Учитывая что данные есть - вместо громоздкой текущей таблицы сделать view что бы не сразу перелопачивать софт.
...
Рейтинг: 0 / 0
29.08.2019, 13:40
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855155
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
Dmitriy_3206,
Идея интересная. Сейчас попробую сделать 8 вьюх и из них выбирать, если я правильно понял направление :)
...
Рейтинг: 0 / 0
29.08.2019, 13:58
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855165
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
Dmitriy_3206,
Спасибо за подсказку.
Не долго думаю соорудил запрос:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
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



Теперь создам таблицу и результат в таблицу. Тогда выборка в хранимке будет идти уже просто по таблице.
План запроса и время работы:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
План
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 часов.
...
Рейтинг: 0 / 0
29.08.2019, 14:11
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855168
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
а подправить хранимку для работы с другой таблицей - это не проблема :)
...
Рейтинг: 0 / 0
29.08.2019, 14:12
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855210
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
Все получилось.
Спасибо за "ускорительный пинок" в нужном направлении.
отработало все максимум минуты за 3
...
Рейтинг: 0 / 0
29.08.2019, 15:05
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855313
KreatorXXI
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KreatorXXI
Участник
akrush,

а у Вас таблица service индексы какие-нибудь имеет? Вижу что запросы идут по натуралу. Тогда это не дело.
...
Рейтинг: 0 / 0
29.08.2019, 18:08
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855343
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
akrush,

попробуй переписать на merge
...
Рейтинг: 0 / 0
29.08.2019, 19:36
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855372
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
KreatorXXIakrush,

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

Значит плохо смотрите.
В первом посте есть ДДЛ таблицы с праймари кеем
Дальше по тексту есть запрос с планом и там видно что он выполняется как раз по ключу.
По поводу перебора натуралом - заметил что если использовать count или max, то идет натуралом. Но может ошибаюсь, тогда подскажите по какому полю желательно достроить дополнительный индекс.
...
Рейтинг: 0 / 0
29.08.2019, 21:44
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855373
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
Симонов Денисakrush,

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

Денис, подскажите как, хотя бы условным примером по моим таблицам
...
Рейтинг: 0 / 0
29.08.2019, 21:45
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855378
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
akrush,

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
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 буферов тут маловато
...
Рейтинг: 0 / 0
29.08.2019, 22:04
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855584
KreatorXXI
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KreatorXXI
Участник
akrush,

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


И я бы попробовал создать индекс с posl1, bd, typ. И ещё бы туда добавил бы дату в обратном порядке (имею ввиду весь индекс сделал бы по убыванию). Именно в этом запросе индекс с датой не поможет. Потому что есть count. А без count можно хорошо сократить время выборки.
...
Рейтинг: 0 / 0
30.08.2019, 12:12
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855603
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
Симонов Денис,
Запустил. Запустил давно, еще в ок. 09:00 по Киевскому времени.
Эксперт ушел в себя и до сих пор не вернулся.
Перезапустил ФБ чтобы оборвать процесс :(
...
Рейтинг: 0 / 0
30.08.2019, 12:33
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855628
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
KreatorXXIakrush,

я на 100 процентов могу быть неправ. На вот запрос:
Код: sql
1.
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 млн записей.
...
Рейтинг: 0 / 0
30.08.2019, 13:12
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855644
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
akrushЭксперт ушел в себя и до сих пор не вернулся.
Перезапустил ФБ чтобы оборвать процесс :(Надо было весь комп перезапустить. Ресетом.
Но надёжнее - пробки выкрутить.
На подстанции.
...
Рейтинг: 0 / 0
30.08.2019, 13:37
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855645
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
KreatorXXIИ я бы попробовал создать индекс с posl1, bd, typ. И ещё бы туда добавил бы дату в обратном порядке (имею ввиду весь индекс сделал бы по убыванию). Именно в этом запросе индекс с датой не поможет. Потому что есть count. А без count можно хорошо сократить время выборки.

Создал индексы
Код: sql
1.
2.
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 минуты. :)

Спасибо за подсказку.
...
Рейтинг: 0 / 0
30.08.2019, 13:38
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855649
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
akrushСимонов Денис,
Запустил. Запустил давно, еще в ок. 09:00 по Киевскому времени.
Эксперт ушел в себя и до сих пор не вернулся.
Перезапустил ФБ чтобы оборвать процесс :(\

А вот после создания вышеприведенных индексов - все отработало:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
План
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
...
Рейтинг: 0 / 0
30.08.2019, 13:48
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855655
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
akrush
Код: sql
1.
ALTER TABLE SERVICE ADD CONSTRAINT PK_SERVICE PRIMARY KEY (BD, APP_NUM, CAL_NUM, COD_ORG);




ну ё-маё. Структура у вас полный трындец.
...
Рейтинг: 0 / 0
30.08.2019, 13:58
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855657
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
akrushСоздал индексы
Код: sql
1.
2.
CREATE INDEX IDX_SERVICE_POSL1 ON SERVICE (POSL1, BD, TYP);
CREATE INDEX IDX_SERVICE_POSL1DATE ON SERVICE (POSL1, BD, TYP, DATA_E);

Второй индекс полностью дублирует первый.
...
Рейтинг: 0 / 0
30.08.2019, 14:01
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855658
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
akrushakrushСимонов Денис,
Запустил. Запустил давно, еще в ок. 09:00 по Киевскому времени.
Эксперт ушел в себя и до сих пор не вернулся.
Перезапустил ФБ чтобы оборвать процесс :(\

А вот после создания вышеприведенных индексов - все отработало:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
План
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 ещё индекс сделай
...
Рейтинг: 0 / 0
30.08.2019, 14:02
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855663
KreatorXXI
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KreatorXXI
Участник
akrushakrushСимонов Денис,
Запустил. Запустил давно, еще в ок. 09:00 по Киевскому времени.
Эксперт ушел в себя и до сих пор не вернулся.
Перезапустил ФБ чтобы оборвать процесс :(\

А вот после создания вышеприведенных индексов - все отработало:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
План
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.
...
Рейтинг: 0 / 0
30.08.2019, 14:14
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855691
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
Симонов Денисakrush
Код: sql
1.
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 бит, так и несколько бит. И это все надо правильно разобрать хотябы по текущему, последнему "пониманию" структуры БД.

Ладно, простите за лирическое отступление.
...
Рейтинг: 0 / 0
30.08.2019, 14:55
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855692
akrush
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
akrush
Участник
Симонов Денисakrushпропущено...
\

А вот после создания вышеприведенных индексов - все отработало:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
План
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?
...
Рейтинг: 0 / 0
30.08.2019, 14:57
| Ответить | Цитировать | Написать  
Помогите с оптимизацией
    #39855713
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
akrush,

нет. На DICT достаточно
...
Рейтинг: 0 / 0
30.08.2019, 15:20
| Ответить | Цитировать | Написать  
24 сообщений из 24, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Помогите с оптимизацией
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&fpage=20&tid=1560590]:
 0ms
get settings:
 11ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 45ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 76ms
get tp. blocked users:
 2ms
others: 49ms
total:  215ms
созд. / загр.: 215ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]