Yes, Tiberiu, 110ms really sounds unbelievable. Though you don't need
DISTINCT, duplicates are removed when using UNION, you need to use UNION
ALL to include duplicates. And I do hope that making the indexes for
articol_doc.id_cont_debit and articol_doc.id_cont_credit available
(through using JOIN and removing +0 in the outermost part) is
responsible for part of the speed increase...

Set

Tiberiu Horvath wrote:

> one of the differences between your and my query was "select distinct" . Th=
> is is why yours was so amazing fast ...
>
> Tiberiu
>
> From: Svein Erling Tysv=E6r=20
> Sent: Tuesday, February 17, 2009 6:49 PM
> To: firebird-support@yahoogroups.com=20
> 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 a=
> pplied directly in your where clause (well, except document.data <=3D '31.1=
> 2.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 =3D 752 is somewha=
> t selective). Excepting you seem to want Valoare_Debit and Valoare_Credit o=
> n separate lines rather than on the same line, it ought to be possible to a=
> void using UNION.
>
> HTH,
> Set