Adlen
New Member
Hello people.
I started working with progress a few months ago, and now i have an optimization i have to do in some old report.
This report brings information about sales, and it can be filtered by date,itens and store number.These three fields are needed for the unique index.
At this time, i´m currently creating temp tables for them, one for the itens selected and another for the stores, after that i use a for each in the sales table bringing the records needed.
The problem is that in case this filters aren´t filled, the report goes very slow. Currently we the tables have 300 stores and around 80.000 itens, and when the report is selected to be generated with all stores and/or itens, it takes kind of a long time.
I thought about separating the four cases (Selected store and selected itens,Selected store and all itens,All store and selected itens,All store and all itens) into different queries, but it sounded kind of dumb.
I would like to hear some opinions about how i should handle this as i´m out of ideas...
Here´s the code where i mount the sales temp-table.
Currently using Windows 7 (64bits) and progress 10.2B.
Thanks in advance.
I started working with progress a few months ago, and now i have an optimization i have to do in some old report.
This report brings information about sales, and it can be filtered by date,itens and store number.These three fields are needed for the unique index.
At this time, i´m currently creating temp tables for them, one for the itens selected and another for the stores, after that i use a for each in the sales table bringing the records needed.
The problem is that in case this filters aren´t filled, the report goes very slow. Currently we the tables have 300 stores and around 80.000 itens, and when the report is selected to be generated with all stores and/or itens, it takes kind of a long time.
I thought about separating the four cases (Selected store and selected itens,Selected store and all itens,All store and selected itens,All store and all itens) into different queries, but it sounded kind of dumb.
I would like to hear some opinions about how i should handle this as i´m out of ideas...
Here´s the code where i mount the sales temp-table.
Code:
DO dDataAux = dDateIni TO dDateEnd:
[INDENT]FOR EACH ttStore NO-LOCK,
[INDENT] EACH ttItens NO-LOCK,
EACH sales NO-LOCK
WHERE
sales.coditem = ttItens.coditem AND
sales.coduni = ttStore.coduni AND
sales.datref = dDateAux :
[INDENT] CREATE ttSales.
BUFFER-COPY sales TO ttSales.[/INDENT][/INDENT]
END.[/INDENT]
END.
Currently using Windows 7 (64bits) and progress 10.2B.
Thanks in advance.