jjochoa75 wrote:

>>SELECT M.OPIS AS MAGAZYN,
>>CAST(T.KODKRESKOWY AS INTEGER) AS KOD,T.NAZWA AS NAZWA,SUM(p.ILOSC)
>
> AS
>
>>ILOSC,
>>CAST(P.CZNETTO AS DECIMAL(18,2)) AS CZNETTO,
>>CAST(SUM(P.CZNETTO*P.ILOSC) AS DECIMAL(18,2)) AS WZNETTO,
>>CAST(P.CSBRUTTO AS DECIMAL(18,2)) AS CSBRUTTO,
>>CAST(SUM(P.CSNETTO*P.ILOSC) AS DECIMAL(18,2)) AS WSNETTO,
>>CAST(SUM(P.CSBRUTTO*P.ILOSC) AS DECIMAL(18,2)) AS WSBRUTTO,
>>CAST((100*(SUM(P.CSNETTO*P.ILOSC) -
>>SUM(P.CZNETTO*P.ILOSC))/(SUM(P.CSNETTO*P.ILOSC))) AS DECIMAL(4,2))
>
> AS
>
>>PROC_MARZY,
>>CAST((SUM(P.CSNETTO*P.ILOSC) - SUM(P.CZNETTO*P.ILOSC)) AS DECIMAL
>
> (18,2))
>
>>AS MARZA
>> FROM POZSPRZEDAZ P
>> INNER JOIN SPRZEDAZ Z ON (P.IDSPR = Z.ID AND
>> Z.DATASPRZEDAZY BETWEEN '2005-02-01' AND '2005-02-01'
>> AND ((Z.GODZINA BETWEEN 8 AND 8) OR Z.GODZINA=0))
>> INNER JOIN TOWARY T ON (P.IDTOW=T.ID)
>> INNER JOIN MAGAZYNY M ON (Z.IDMAG=M.ID)
>> GROUP BY M.OPIS,T.KODKRESKOWY,T.NAZWA,P.CZNETTO,P.CSBRUTTO
>> HAVING SUM(P.ILOSC*P.CSNETTO) <> 0
>> ORDER BY M.OPIS,T.NAZWA
>
>
> Just some questions:
>
> 1. How many reads (indexed and not indexed if any) you get on:
> P:
> Z:
> T:
> M:

P: 609
Z: 13131
T: 609
M: 355


when range is exactly one day

but when I set :

Z.DATASPRZEDAZY BETWEEN '2005-02-01' AND '2005-02-28'
AND ((Z.GODZINA BETWEEN 8 AND 22) OR Z.GODZINA=0))

I have:

P: 590210
Z: 339244
T: 590210
M: 334822


All reads are indexes.maybe I should drop some indexes ?

> 2. How many records you get at the end?

454 when only one day range is selected but in second case I have 14916
records at the end.

> 3. How many records do you get and P INNER JOIN Z?

If you think about full tables join : Z contains 4 milions and P - 8
milions of records.A lot.What I really want is to find a way to first
select a subset based on DATASPRZEDAZY range of dates and then use inner
join but I know that something like:

(...)
from POZSPRZEDAZ P
inner join (select * from SPRZEDAZ Z where Z.DATASPRZEDAZY between
'2005-02-01' and '2005-02-01) on (P.IDSPR=Z.ID)

is not possible , right ?


So what inner join do is select all records from both and then restrict
based on range of dates.Am I right ?

> 4. Is Z.GODZINA=0 an allowed value?
>
> I suppose: ((Z.GODZINA BETWEEN 8 AND 8) OR Z.GODZINA=0)) is a trick
> to use the right indexes.

No. When Z.GODZINA=0 it means that this record contain negative value
(return of goods) and must be also included.

Z.GODZINA >=0 and Z.GODZINA <= 24

> 5. Do you get significant performance improvement if you remove the
> ORDER BY Clause?

I see no big performance improvement.It doesn't matter compared to
overall slowdown.

> Thanks
> Juan Jose

Thank You for help.
Boguslaw Brandys