one of the differences between your and my query was "select distinct" . This is why yours was so amazing fast ...





Tiberiu




From: Svein Erling Tysvær
Sent: Tuesday, February 17, 2009 6:49 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] speed-up query


Hi Tiberiu!

As I see it, your main problem is that you lack any constants that can be applied directly in your where clause (well, except document.data <= '31.12.2008', which doesn't sound all too selective). Hence, the 800000 records in 'document' has to use NATURAL. Having said that, it doesn't seem like a query that ought to be very slow, just a bit slow. Try the below query and see if it is quicker (I assume that id_cont_debit/credit = 752 is somewhat selective). Excepting you seem to want Valoare_Debit and Valoare_Credit on separate lines rather than on the same line, it ought to be possible to avoid using UNION.

HTH,
Set

select d.id_document, d.id_tip_document, 752 as id_cont , d.id_agent_economic,
'411 ' asSimbolCont,
RTRIM(d.numar) as NumarDocument, d.data as DataDocument,
coalesce((select sum(coalesce(a2.valoare,0))
from articol_doc a2
where a2.id_document=d.id_document and
a2.id_cont_debit+0=752), 0)
as Valoare_Debit,
cast(0 as decimal(18,2)) as Valoare_Credit,
coalesce((select sum(a2.valoare)
from articol_doc a2
where a2.id_document=d.id_document and
a2.id_cont_debit+0=752 and
a2.valoare <> 0), 0) as sold
from document d
join articol_doc a on a.id_document = did_document and
join tip_document td on d.id_tip_document=td.id_tip_document
where (a.id_cont_debit = 752 or
a.id_cont_credit = 752) and
td.nota_contabila_suplimentara = 2 and
d.data <= '31.12.2008' and
(select sum(a2.valoare)
from articol_doc a2
where
a2.id_document=d.id_document and
a2.id_cont_debit+0=752 and
a2.valoare <> 0)
<> 0
union
select d.id_document, d.id_tip_document, 752 as id_cont , d.id_agent_economic,
'411 ' as SimbolCont,
RTRIM(d.numar) as NumarDocument, d.data as DataDocument,
cast(0 as decimal(18,2)) as Valoare_Debit,
coalesce(
(select sum(a2.valoare)
from articol_doc a2
where a2.id_document=document.id_document and
a2.id_cont_credit+0=752 and
a2.valoare <> 0
)
,0) as Valoare_Credit,
0 as sold
from document d
join articol_doc a on a.id_document=d.id_document
join tip_document td on d.id_tip_document=td.id_tip_document
where td.nota_contabila_suplimentara = 2 and
d.data <= '31.12.2008' and
a.id_cont_credit=752

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Tiberiu Horvath
Sent: 17. februar 2009 10:01
To: firebird-support@yahoogroups.com
Subject: [firebird-support] speed-up query

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))
<skipped a few repetitions>
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))
<skipped a few repetitions>
PLAN SORT ((DOCUMENT NATURAL))
PLAN SORT ((DOCUMENT NATURAL))





[Non-text portions of this message have been removed]