Добрый день.
была очень медленная скорость обработки запросов.
сейчас количество записей в CARDJOURNALOPER 3727056 шт., CARDUREAD2 396024 шт.
и я внес изменения в БД:
1. добавил индекс CREATE INDEX CARDJOURNALOPER_FILETYPE ON CARDJOURNALOPER (FILETYPE);
- | Показать
- без него долго работал запрос (>2мин)...
UNION
select CARDJOURNALOPER.CODE, CARDJOURNALOPER.FILENAME,
CARDJOURNALOPER.FILETYPE, CARDJOURNALOPER.OPERTYPE,
CARDJOURNALOPER.OPERDATE, CARDJOURNALOPER.RCVEMAIL,
CARDJOURNALOPER.RCVTYPE, CARDJOURNALOPER.RCVCERTSERIAL, CARDJOURNALOPER.EXCHANGEWAY,
case when CARDUREAD2.DOCID is null then null else 1 end as UREAD,
CARDJOURNALOPER.PDEPT, CARDJOURNALOPER.NOTES
from CARDJOURNALOPER
INNER JOIN GOVQRY ON GOVQRY.CODE = CARDJOURNALOPER.CARDCODE
LEFT JOIN CARDUREAD2 ON CARDUREAD2.DOCID = CARDJOURNALOPER.CARDCODE
AND CARDUREAD2.OPERTYPE = CARDJOURNALOPER.OPERTYPE
AND CARDUREAD2.DOCTYPE = 1
AND CARDUREAD2.USR = ?
where GOVQRY.CARDCODE = ? and CARDJOURNALOPER.FILETYPE=?
2. изменил процедуру DELOLDUREADED . при запуске старая отрабатывала за 60-90 минут
- | Показать
- SET TERM ^ ;
CREATE OR ALTER procedure DELOLDUREADED (
DT date)
as
declare variable DOCTYPE smallint;
declare variable DOCID bigint;
declare variable USR integer;
declare variable OPERTYPE smallint;
BEGIN
/* FOR SELECT U.DOCTYPE, U.DOCID, U.USR, U.OPERTYPE
from CARDUREAD2 U
JOIN CARDJOURNALOPER O ON O.CARDCODE = U.DOCID
where O.OPERDATE < 
T
into OCTYPE, OCID, :USR, :OPERTYPE
AS CURSOR CUR
DO
DELETE FROM CARDUREAD2
WHERE DOCTYPE = OCTYPE and DOCID = OCID
and USR = :USR and OPERTYPE = :OPERTYPE; */
FOR SELECT U.DOCTYPE, U.DOCID, U.USR, U.OPERTYPE
from CARDUREAD2 U
where exists(select * from CARDJOURNALOPER O where O.CARDCODE = U.DOCID and O.OPERDATE < T)
into OCTYPE, OCID, :USR, :OPERTYPE
DO
DELETE FROM CARDUREAD2
WHERE DOCTYPE = OCTYPE and DOCID = OCID
and USR = :USR and OPERTYPE = :OPERTYPE;
/* FOR SELECT U.DOCTYPE, U.DOCID, U.USR, U.OPERTYPE
from CARDUREAD2 U
where not exists ( select null from CARDJOURNALOPER O
where O.CARDCODE = U.DOCID )
into OCTYPE, OCID, :USR, :OPERTYPE
AS CURSOR CUR
DO
DELETE FROM CARDUREAD2
WHERE DOCTYPE = OCTYPE and DOCID = OCID
and USR = :USR and OPERTYPE = :OPERTYPE; */
DELETE FROM CARDUREAD2 U
where not exists ( select O.CARDCODE from CARDJOURNALOPER O
where O.CARDCODE = U.DOCID );
END^
SET TERM ; ^
3. изменил представление CARDDOCUREAD. зачем полный скан таблицы если есть индекс
- | Показать
- CREATE OR ALTER VIEW CARDDOCUREAD(
DOCTYPE,
DOCID,
USR)
AS
select DOCTYPE, DOCID, USR from CARDUREAD2 group by DOCTYPE, DOCID, USR
;
4. пожелание: во время установки обновления или др. служебного режима сделайте пересчет статистики всех индексов.