--- In firebird-support@yahoogroups.com, "emb_blaster"
<EMB_Blaster@...> wrote:

>
> hi to all,
> I´m 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´t think, but maybe my SQL is too complex :( , or there´s 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 = 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 = 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 = PRODUCTS.CODPRODUCT
> WHERE (SX.DATA BETWEEN :DATA_INI AND :DATA_END)
> GROUP BY NAME
> ORDER BY SUM(SX.VALUE) DESC)
>
>
> I don´t know if this is a standard behavior of firebird. Is it? I
> think my first SQL is more "beauty" and clean to understand. There´s
> anothers ways? I searched in the messages but don´t found any light.
> thanks.
>

Neither is as elegant as it would look inside a stored procedure, but
I don't see why your first option shouldn't work.

I believe what is probably letting you down is the not in which can
not use an index due to null handling issue. Change your sub-queries
to exists and not exists respectively and see whether the performance
improves.

The select from select behaviour is new in Firebird 2.1.

Adam