>Hi, people
>
>I have a table named nf with a field dataemissao( date) and a field nfnum and indexed by these fields, and a table named nfitens with foreign Key nfnum from nf table, and two fields vcofins and vpis( numeric values). I need the sum of these fields in a period of time. It's not surprise that the performance of the query that sums these( vPIS and vCOFINS) decrease with the period of time increase.
>I can put the sum of vpis and vcofins for every nfnum in the nf table and the problem is solved. But is there any other idea to speed the query?
>
>
> SELECT
> SUM( Nfitens.vCOFINS) as VCOFINS,
> SUM( Nfitens.vPIS) as VPIS
> FROM
> nf Nf inner join Nfitens on nf.nfnum=nfitens.nfnum
> WHERE nf.dataemissao BETWEEN ?tddatefrom and ?tddateto
>
What are the indexes in both tables? You can create a data warehouse with everything pre-calculated to be used in reports if performance of the query is a concern.
If it's not broken, fix it until it is.
My Blog