Имеются две идентичные процедуры(текст приведен ниже) ниже. Непонятно почему в
первой стали наблюдаться тормоза(поэтому быстро возникла вторая). Отличаются
только текстом одного запроса(который почему то стал тормозить(не знаю может
удалил какой то индекс из таблицы KLIENT_ACCOUNT_PROVODKI )).
1) SELECT MAX(DATA) FROM KLIENT_ACCOUNT_PROVODKI WHERE (SUMMA>0) AND
(ID_ACCOUNT = :VAR_ID_ACC) into :DATE_LAST_PRIXOD;
Statement:
--------------------------------------------------------------------------------
SELECT MAX(DATA) FROM KLIENT_ACCOUNT_PROVODKI WHERE (SUMMA>0) AND (ID_ACCOUNT =
:VAR_ID_ACC) into :DATE_LAST_PRIXOD;
Plan:
--------------------------------------------------------------------------------
(KLIENT_ACCOUNT_PROVODKI ORDER KLIENT_ACCOUNT_PROVODKI_IDX1 INDEX
(FK_KLIENT_ACCOUNT_PROVODKI_1))
Query
------------------------------------------------
Plan
------------------------------------------------
Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 21 403,00 ms
Avg fetch time: 856,12 ms
2) SELECT ID_ACCOUNT, MAX(DATA) FROM KLIENT_ACCOUNT_PROVODKI WHERE
(SUMMA>0) AND (ID_ACCOUNT = :VAR_ID_ACC)
group by ID_ACCOUNT
into :VAR_ID_ACC, :DATE_LAST_PRIXOD;
Statement:
--------------------------------------------------------------------------------
SELECT ID_ACCOUNT, MAX(DATA) FROM KLIENT_ACCOUNT_PROVODKI WHERE (SUMMA>0) AND
(ID_ACCOUNT = :VAR_ID_ACC)
group by ID_ACCOUNT
into :VAR_ID_ACC, :DATE_LAST_PRIXOD;
Plan:
--------------------------------------------------------------------------------
(KLIENT_ACCOUNT_PROVODKI ORDER UNQ1_KLIENT_ACCOUNT_PROVODKI INDEX
(FK_KLIENT_ACCOUNT_PROVODKI_1))
Query
------------------------------------------------
Plan
------------------------------------------------
Query Time
------------------------------------------------
Prepare : 16,00 ms
Execute : 78,00 ms
Avg fetch time: 3,00 ms
Memory
Вторая выполняется в гораздо быстрее первой Мистика ну в раз 10 точно.
На текущий момент на таблицу KLIENT_ACCOUNT_PROVODKI имеются следующие
индексы(добавлял удалял)
ALTER TABLE KLIENT_ACCOUNT_PROVODKI ADD CONSTRAINT PK_KLIENT_ACCOUNT_PROVODKI
PRIMARY KEY (ID);
ALTER TABLE KLIENT_ACCOUNT_PROVODKI ADD CONSTRAINT FK_KLIENT_ACCOUNT_PROVODKI_1
FOREIGN KEY (ID_ACCOUNT) REFERENCES KLIENT_ACCOUNT (ID) ON DELETE CASCADE ON
UPDATE CASCADE;
CREATE DESCENDING INDEX KLIENT_ACCOUNT_PROVODKI_IDX1 ON KLIENT_ACCOUNT_PROVODKI
(DATA, ID_ACCOUNT);
CREATE INDEX KLIENT_ACCOUNT_PROVODKI_IDX2 ON KLIENT_ACCOUNT_PROVODKI
(KONTRAGENT_INN);
CREATE INDEX KLIENT_ACCOUNT_PROVODKI_IDX3 ON KLIENT_ACCOUNT_PROVODKI (DATA);
CREATE DESCENDING INDEX KLIENT_ACCOUNT_PROVODKI_IDX_DAT ON
KLIENT_ACCOUNT_PROVODKI (DATA);
1) create procedure ACC_BLOC_AREST_SEL
returns (
DATA_ACOUNT type of column KLIENT_ACCOUNT.DATA_ACOUNT,
ACCOUNT type of column KLIENT_ACCOUNT.ACCOUNT,
PRIZNAK type of column KLIENT_ACCOUNT.PRIZNAK,
NO_ANKET type of column KLIENT.NO_ANKET,
NAME_SHORT type of column KLIENT.NAME_SHORT,
MENADGER type of column KLIENT.MENADGER,
PERSON_OPEN_ACCOUNT type of column KLIENT.PERSON_OPEN_ACCOUNT,
FIO_MENEDGER type of column PERSONAL.F,
FIO_OPEN_ACCOUNT type of column PERSONAL.F,
DATE_LAST_PRIXOD type of T_DATE,
OSTATOK type of T_SUMMA)
as
declare variable VAR_ID_ACC type of column KLIENT_ACCOUNT.ID;
BEGIN
FOR
SELECT
KLIENT_ACCOUNT.DATA_ACOUNT,
KLIENT_ACCOUNT.ACCOUNT,
KLIENT_ACCOUNT.PRIZNAK,
KLIENT.NO_ANKET,
KLIENT.NAME_SHORT,
KLIENT.MENADGER,
KLIENT.PERSON_OPEN_ACCOUNT,
KLIENT_ACCOUNT.ID
FROM KLIENT
INNER JOIN KLIENT_ACCOUNT ON (KLIENT.ID = KLIENT_ACCOUNT.ID_KLIENT)
WHERE
(
(KLIENT.no_anket > 0) AND (KLIENT_ACCOUNT.account STARTING WITH '40')
AND (KLIENT_ACCOUNT.PRIZNAK <> 'О') AND (KLIENT_ACCOUNT.PRIZNAK <> 'З')
)
INTO
:DATA_ACOUNT, :ACCOUNT, :PRIZNAK, :NO_ANKET, :NAME_SHORT, :MENADGER,
:PERSON_OPEN_ACCOUNT, :VAR_ID_ACC
DO
BEGIN
SELECT F FROM PERSONAL WHERE PERSONAL.id = :menadger INTO :FIO_MENEDGER;
SELECT F FROM PERSONAL WHERE PERSONAL.id = :PERSON_OPEN_ACCOUNT INTO
:FIO_OPEN_ACCOUNT;
SELECT SUM(summa) FROM KLIENT_ACCOUNT_PROVODKI WHERE (ID_ACCOUNT =
:VAR_ID_ACC) into :OSTATOK;
SELECT MAX(DATA) FROM KLIENT_ACCOUNT_PROVODKI WHERE (SUMMA>0) AND
(ID_ACCOUNT = :VAR_ID_ACC) into :DATE_LAST_PRIXOD;
if( OSTATOK is null) then OSTATOK = 0;
if( DATE_LAST_PRIXOD is null) then DATE_LAST_PRIXOD = '04.08.1994';
SUSPEND;
END
END
Statement:
--------------------------------------------------------------------------------
SELECT MAX(DATA) FROM KLIENT_ACCOUNT_PROVODKI WHERE (SUMMA>0) AND (ID_ACCOUNT =
:VAR_ID_ACC) into :DATE_LAST_PRIXOD;
Plan:
--------------------------------------------------------------------------------
(KLIENT_ACCOUNT_PROVODKI ORDER KLIENT_ACCOUNT_PROVODKI_IDX1 INDEX
(FK_KLIENT_ACCOUNT_PROVODKI_1))
Query
------------------------------------------------
Plan
------------------------------------------------
Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 21 403,00 ms
Avg fetch time: 856,12 ms
Memory
------------------------------------------------
Current: 137 955 384
Max : 199 269 040
Buffers: 8 192
Operations
------------------------------------------------
Read : 13 571
Writes : 0
Fetches: 667 485
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates |
Deletes | Inserts |
| | Total | reads | reads | |
| |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| KLIENT| 0 | 288 | 0 | 0 |
0 | 0 |
| KLIENT_ACCOUNT| 0 | 4846 | 0 | 0 |
0 | 0 |
| KLIENT_ACCOUNT_PROVODKI| 0 | 111363 | 0 | 0 |
0 | 0 |
| PERSONAL| 0 | 575 | 0 | 0 |
0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
2) create procedure ACC_BLOC_AREST_SEL
returns (
DATA_ACOUNT type of column KLIENT_ACCOUNT.DATA_ACOUNT,
ACCOUNT type of column KLIENT_ACCOUNT.ACCOUNT,
PRIZNAK type of column KLIENT_ACCOUNT.PRIZNAK,
NO_ANKET type of column KLIENT.NO_ANKET,
NAME_SHORT type of column KLIENT.NAME_SHORT,
MENADGER type of column KLIENT.MENADGER,
PERSON_OPEN_ACCOUNT type of column KLIENT.PERSON_OPEN_ACCOUNT,
FIO_MENEDGER type of column PERSONAL.F,
FIO_OPEN_ACCOUNT type of column PERSONAL.F,
DATE_LAST_PRIXOD type of T_DATE,
OSTATOK type of T_SUMMA)
as
declare variable VAR_ID_ACC type of column KLIENT_ACCOUNT.ID;
BEGIN
FOR
SELECT
KLIENT_ACCOUNT.DATA_ACOUNT,
KLIENT_ACCOUNT.ACCOUNT,
KLIENT_ACCOUNT.PRIZNAK,
KLIENT.NO_ANKET,
KLIENT.NAME_SHORT,
KLIENT.MENADGER,
KLIENT.PERSON_OPEN_ACCOUNT,
KLIENT_ACCOUNT.ID
FROM KLIENT
INNER JOIN KLIENT_ACCOUNT ON (KLIENT.ID = KLIENT_ACCOUNT.ID_KLIENT)
WHERE
(
(KLIENT.no_anket > 0) AND (KLIENT_ACCOUNT.account STARTING WITH '40')
AND (KLIENT_ACCOUNT.PRIZNAK <> 'О') AND (KLIENT_ACCOUNT.PRIZNAK <> 'З')
)
INTO
:DATA_ACOUNT, :ACCOUNT, :PRIZNAK, :NO_ANKET, :NAME_SHORT, :MENADGER,
:PERSON_OPEN_ACCOUNT, :VAR_ID_ACC
DO
BEGIN
SELECT F FROM PERSONAL WHERE PERSONAL.id = :menadger INTO :FIO_MENEDGER;
SELECT F FROM PERSONAL WHERE PERSONAL.id = :PERSON_OPEN_ACCOUNT INTO
:FIO_OPEN_ACCOUNT;
SELECT SUM(summa) FROM KLIENT_ACCOUNT_PROVODKI WHERE (ID_ACCOUNT =
:VAR_ID_ACC) into :OSTATOK;
SELECT ID_ACCOUNT, MAX(DATA) FROM KLIENT_ACCOUNT_PROVODKI WHERE (SUMMA>0)
AND (ID_ACCOUNT = :VAR_ID_ACC)
group by ID_ACCOUNT
into :VAR_ID_ACC, :DATE_LAST_PRIXOD;
if( OSTATOK is null) then OSTATOK = 0;
if( DATE_LAST_PRIXOD is null) then DATE_LAST_PRIXOD = '04.08.1994';
SUSPEND;
END
END
Statement:
--------------------------------------------------------------------------------
SELECT ID_ACCOUNT, MAX(DATA) FROM KLIENT_ACCOUNT_PROVODKI WHERE (SUMMA>0) AND
(ID_ACCOUNT = :VAR_ID_ACC)
group by ID_ACCOUNT
into :VAR_ID_ACC, :DATE_LAST_PRIXOD;
Plan:
--------------------------------------------------------------------------------
(KLIENT_ACCOUNT_PROVODKI ORDER UNQ1_KLIENT_ACCOUNT_PROVODKI INDEX
(FK_KLIENT_ACCOUNT_PROVODKI_1))
Query
------------------------------------------------
Plan
------------------------------------------------
Query Time
------------------------------------------------
Prepare : 16,00 ms
Execute : 78,00 ms
Avg fetch time: 3,00 ms
Memory
------------------------------------------------
Current: 137 964 352
Max : 199 269 040
Buffers: 8 192
Operations
------------------------------------------------
Read : 11 415
Writes : 0
Fetches: 566 616
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates |
Deletes | Inserts |
| | Total | reads | reads | |
| |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| KLIENT| 0 | 288 | 0 | 0 |
0 | 0 |
| KLIENT_ACCOUNT| 0 | 4846 | 0 | 0 |
0 | 0 |
| KLIENT_ACCOUNT_PROVODKI| 0 | 219618 | 0 | 0 |
0 | 0 |
| PERSONAL| 0 | 575 | 0 | 0 |
0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
