--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> Which Firebird version are you using?

oh yeah. I forget that. using 2.0.4. I do not upgraded my DB yet.

> I think Firebird 2.1 allows you to
> do things like:
>
> WITH CP as
> (Select First 12 S2.CODPRODUCT
> From SALES S2
> where S2.DATE BETWEEN :DATE_INI AND :DATE_END
> group by S2.CODPRODUCT
> ORDER BY Sum(S2.VALUE) DESC)
>
> Select Sum(S.VALUE) As VALUE,
> Coalesce(SUBSTRING(P.NAME FROM 1 FOR 20), 'OTHERS') AS NAME
> From SALES S
> Left Join CP on S.CODPRODUCT = CP.CODPRODUCT
> Left Join PRODUCTS P On CP.CODPRODUCT = P.CODPRODUCT
> where S.DATE BETWEEN :DATE_INI AND :DATE_END
> Group By NAME
>
> WITH is an addition to Firebird that I think can make many statements
> look considerably simpler. Note that I do not actually use Firebird 2.1
> yet, so there may be mistakes in my syntax.
>

Ok. I will try this...

> As for your original question, Firebird used to evaluate IN (subselect)
> for every potential row, but I think that between version 1.0 and

1.5 it

> started translating such queries into EXISTS queries when possible.
> Though I have no clue whether subselects with aggregation can or are
> optimized in any way.
>
> HTH,
> Set
>

May be that. Firebird couldn´t optimize subselect like my. well it
couldn´t optmize with WHERE, because the first part of the SQL is slow
too, even withtout the subquery GROUP BY.
But, in this case, like Firebird, I don´t know how to translate the
IN it to EXISTS (thanks ADAM may be you are right too). The FIRST and
GROUP BY clauses in subquery made me mad. May be I lost something?
Using (Select ...From (Select..)) takes 6-10 seconds. But Select
.... IN (select...) 6 minutes or more.
Sorry may be I´m not "all clear" in my words, so if any questions,
please ask ok?

> emb_blaster wrote:
> > hi to all,
> > I=B4m making a pie chart (like MS Excel) of X most sold products and
> > group the others. But every time I execute the SQL the firebird became
> > slow...
> > I don=B4t think, but maybe my SQL is too complex :( , or

there=B4s a bug

> > :? . can anyone help?
> > take a look, lets say that X is 12 then:
> >
> > Select
> > Sum(SALES.VALUE) As VALUE,
> > SUBSTRING(PRODUCTS.NAME FROM 1 FOR 20) AS NAME
> > From SALES
> > Inner Join PRODUCTS On SALES.CODPRODUCT =3D PRODUCTS.CODPRODUCT
> > where (SALES.DATE BETWEEN :DATE_INI AND :DATE_END) AND
> > (SALES.CODPRODUCT IN (Select First 12
> > SALES.CODPRODUCT
> > From SALES
> > where (SALES.DATE BETWEEN :DATE_INI AND
> > :DATE_END)
> > group by SALES.CODPRODUCT
> > ORDER BY Sum(SALES.VALUE) DESC))
> > Group By NAME
> > UNION
> > Select
> > Sum(SALES.VALUE) As VALUE,
> > 'OTHERS' AS NAME
> > From SALES
> > where (SALES.DATE BETWEEN :DATE_INI AND :DATE_END) AND
> > (SALES.CODPRODUCT NOT IN (Select First 12
> > SALES.CODPRODUCT
> > From SALES
> > where (SALES.DATE BETWEEN :DATE_INI AND
> > :DATE_END)
> > group by SALES.CODPRODUCT
> > ORDER BY Sum(SALES.VALUE) DESC))
> > Group By NAME
> >
> > the first part of Union slow too, maybe it execute the IN (SELECT ...)
> > for every row. But with a "static IN" it runs good. See:
> >
> > Select
> > Sum(SALES.VALUE) As VALUE,
> > SUBSTRING(PRODUCTS.NAME FROM 1 FOR 20) AS NAME
> > From SALES
> > Inner Join PRODUCTS On SALES.CODPRODUCT =3D PRODUCTS.CODPRODUCT
> > where (SALES.DATA BETWEEN :DATA_INI AND :DATA_END) AND
> > (SALES.CODPRODUCT IN (100,358,24,227,359,360,94,1712,395,6,450,357))
> > Group By NAME
> >
> > the way to correct I found is making a SELECT ... FROM SELECT see

below:

> >
> > SELECT
> > VALUE,
> > NAME
> > FROM (SELECT FIRST 12
> > SUM(SX.VALUE) AS VALUE,
> > SUBSTRING(PRODUCTS.NAME FROM 1 FOR 20) AS NAME
> > FROM SALES SX
> > INNER JOIN PRODUCTS ON SX.CODPRODUCT =3D PRODUCTS.CODPRODUCT
> > WHERE (SX.DATA BETWEEN :DATA_INI AND :DATA_END)
> > GROUP BY NAME
> > ORDER BY SUM(SX.VALUE) DESC)
> >
> >
> > I don=B4t know if this is a standard behavior of firebird. Is it? I
> > think my first SQL is more "beauty" and clean to understand. There=B4s
> > anothers ways? I searched in the messages but don=B4t found any light.
> > thanks.
>