FB 2.1.1.

document has 800000+ records
articol_doc has 3000000+ records
tip_document has 10 records

on a smaller database, this query works fine, but with this customer it is
very slow.

Does anybody have any idea how could I speed up this one ?


Thank you,

Tiberiu


select distinct
document.id_document,
document.id_tip_document,
752 as id_cont ,
document.id_agent_economic,
'411 ' as
SimbolCont,
RTRIM(document.numar) as NumarDocument,
document.data as DataDocument,
coalesce(
(select sum(coalesce(articol_doc.valoare,0))
from articol_doc
where
articol_doc.id_document=document.id_document and
articol_doc.id_cont_debit+0=752
)
,0)
as Valoare_Debit,
cast(0 as decimal(18,2)) as Valoare_Credit,
coalesce(
(select sum(coalesce(articol_doc.valoare,0))
from articol_doc
where
articol_doc.id_document=document.id_document and
articol_doc.id_cont_debit+0=752
)
,0)
-
0
as sold
from document
where
exists
(select id_articol_doc
from articol_doc
where
articol_doc.id_document = document.id_document and
(
(articol_doc.id_cont_debit + 0 = 752 ) or
(articol_doc.id_cont_credit + 0 = 752 )
)
) and
exists
(select id_tip_document
from tip_document
where
document.id_tip_document=tip_document.id_tip_document and
tip_document.nota_contabila_suplimentara = 2
) and
document.data <= '31.12.2008' and
coalesce(
(select sum(coalesce(articol_doc.valoare,0))
from articol_doc
where
articol_doc.id_document=document.id_document and
articol_doc.id_cont_debit+0=752
)
,0)
<> 0
union
select distinct
document.id_document,
document.id_tip_document,
752 as id_cont ,
document.id_agent_economic,
'411 ' as
SimbolCont,
RTRIM(document.numar) as NumarDocument,
document.data as DataDocument,
cast(0 as decimal(18,2)) as Valoare_Debit,
coalesce(
(select sum(coalesce(articol_doc.valoare,0))
from articol_doc
where
articol_doc.id_document=document.id_document and
articol_doc.id_cont_credit+0=752
)
,0)
as Valoare_Credit,
0 as sold
from document
where
exists
(select id_tip_document
from tip_document
where
document.id_tip_document=tip_document.id_tip_document and
tip_document.nota_contabila_suplimentara = 2
) and
document.data <= '31.12.2008' and
exists (select articol_doc.id_articol_doc
from articol_doc
where
articol_doc.id_document=document.id_document and
articol_doc.id_cont_credit+0=752
)



PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (TIP_DOCUMENT INDEX (PK_TIP_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (TIP_DOCUMENT INDEX (PK_TIP_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN SORT ((DOCUMENT NATURAL))
PLAN SORT ((DOCUMENT NATURAL)):
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (TIP_DOCUMENT INDEX (PK_TIP_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (TIP_DOCUMENT INDEX (PK_TIP_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_DOCUMENT))
PLAN SORT ((DOCUMENT NATURAL))
PLAN SORT ((DOCUMENT NATURAL))