Juan Jose Ochoa wrote:

> try each of these procedures and use the following with the faster,
> (I mean, the less slowly).
> then
> select sum(a.cznetto*a.ilosc),t.KODKRESKO,m.OPIS,...
> from sp_X a left join T
> on a.idtow=t.id
> left join M
> on a.idmag=m.id
> group by t.KODKRESKO,m.OPIS...
>
>
> CREATE PROCEDURE sp_X RETURNS
> (Z_ID BIGINT,
> IDMAG BIGINT,
> IDTOW BIGINT,
> ILOSC DECIMAL(18,3),
> CZNETTO DECIMAL(18,4),
> CZNETTO DECIMAL(18,4),
> CSBRUTTO DECIMAL(18,4)
> )
> AS
> BEGIN
> FOR SELECT Z.ID, Z.IDMAG
> FROM SPRZEDAZ Z
> WHERE Z.DATASPRZEDAZY BETWEEN '2005-02-01' AND '2005-02-01'
> AND ((Z.GODZINA BETWEEN 8 AND 8) OR Z.GODZINA=0))
> INTO :Z_ID, :IDMAG
> DO BEGIN
> FOR SELECT P.IDTOW,P.ILOSC,P.CZNETTO,P.CZNETTO,P.CSBRUTTO
> FROM POZSPRZEDAZ P
> WHERE P.IDSPR /*fk NOT NULL*/= Z.ID/*PK*/
> /* tell me if this apply and/or helps taken from the having
> clause*/
> and P.ILOSC<>0 and P.CSNETTO<>0
> /* - - - */
> INTO :IDTOW,:ILOSC,:CZNETTO,:CZNETTO,:CSBRUTTO
> DO BEGIN
> SUSPEND;
> END
> END
> END

It was fast when using select * from sp_X but the same is plain inner
join. Only select sum(*) from sp_x Inner join T ...
is slow exactly as sum from inner join (well, maybe a little faster then
inner join) where a date range is larger then one day.

The problem is bad query which returns big resultset for summarization
(unfortunately that query was not my idea) (309 products from TOWARY
table X 40 records from MAGAZYNY joined with a couple of thousands
records from resultset).When I limit to one record from MAGAZYNY and one
from TOWARY it take only few seconds (but I'm sure that in fact should
take a few milliseconds if data was normalized)


Your help is very valuable.Thank You.However I doubt if I can do
anything to speed it up.

Regards
Boguslaw Brandys

> CREATE PROCEDURE sp_X RETURNS
> (
> IDSPR BIGINT,
> Z_ID BIGINT,
> IDMAG BIGINT,
> IDTOW BIGINT,
> ILOSC DECIMAL(18,3),
> CZNETTO DECIMAL(18,4),
> CZNETTO DECIMAL(18,4),
> CSBRUTTO DECIMAL(18,4)
> )
> AS
> BEGIN
> /*Natural scan (8mill records) :(*/
> FOR SELECT P.IDSPR,P.IDTOW,P.ILOSC,P.CZNETTO,P.CZNETTO,P.CSBRUTTO
> FROM POZSPRZEDAZ P
> /* tell me if this apply and/or helps taken from the having
> clause*/
> where P.ILOSC <>0 and P.CSNETTO<>0
> /* - - - */
> INTO :IDSPR,:IDTOW,:ILOSC,:CZNETTO,:CZNETTO,:CSBRUTTO
> DO BEGIN
> /*PK scan*/
> FOR SELECT Z.ID, Z.IDMAG
> FROM SPRZEDAZ Z
> WHERE Z.ID = :P_IDSPR AND
> Z.DATASPRZEDAZY BETWEEN '2005-02-01' AND '2005-02-28'
> AND
> ((Z.GODZINA BETWEEN 8 AND 22) OR Z.GODZINA=0))
> INTO :Z_ID, :IDMAG
> DO BEGIN
> SUSPEND;
> END
> END
> END
>
> HTH
>
> Juan Jose