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.