Report very, very slow. How can I speed up?

rbuser

Hi to all.

I made a report based on a single table

Is an account receivable table. In that I have de following fields (I use
Firebird 1.5.3):

Customer Code
Customer Name
Date (The date of the document)
Type (D=Debit; C=Credit)
Total

I've create a group based on the Customer Code field.

What I want is to show, for a given date range, the sum of the credit and
the debit and the difference between both:

Customer Debit Credit Dif.
1-Customer 1 Name 500,00 500,00 0,00
2-Customer 2 Name 1.500,00 500,00 1.000,00

The Debit is a Variable:

if Table['Type'] = 'D' then
Value := Value + Table['Total'];

The Credit is a Variable:

if Table['Type'] = 'C' then
Value := Value + Table['Total'];

The Dif is a Variable:

Value := Debit.asExtended - Credit.asExtended;

The problem is that I have 35.000 records on that table and the report is
too slow to open.
And I tested in a local environment. I can put that report in a
cliente/server enviromnent (tcp/ip). The users will kill me.

What can I do to speed up the report?

Thanks

IMendes

rbuser

Hello Isildo,

If I were in your shoes I would do the following:

- Create a query CustomersQuery with the following sqlscript:
SELECT CustomerCode, CustomerName FROM YourTable GROUP BY CustomerCode

- Create a query TotalQuery with the following sqlscript:

SELECT SUM(total) WHERE CustomerCode = :CustomerCode and Type = :Type

- Create the calculate Debit field using the TotalQuery

TotalQuery.ParamByName['CustomerCode'].AsString :=
Query1['CustomerCode'].AsString;
TotalQuery.ParamByName['Type'].AsString := 'D';
TotalQuery.Execute; // I don't remember if you run the query with execute
CustomersQuery['Debit'].Value := TotalQueryF_1.Value // Sum of Debits


- Create the calculate Credit field using the TotalQuery

TotalQuery.ParamByName['CustomerCode'].AsString :=
Query1['CustomerCode'].AsString;
TotalQuery.ParamByName['Type'].AsString := 'C';
TotalQuery.Execute; // I don't remember if you run the query with execute
CustomersQuery['Credit'].Value := TotalQueryF_1.Value // Sum of Credits

And connect CustomersQuery to the report.

Hope it helps,

Jose Maria Sanmartin

"Isildo Mendes" escribi? en el mensaje

rbuser

The problem is that I don't use a direct connection between my tables and
the reports. The reports are stored in rtm files. I use a function to pass
just the where clause to the specific report. The user is able to filter the
report by choosing the parameters that he wants. If there is something I
could do is inside the report, by RAP or something else.

Anyway, thanks for your response.

rbuser

Hi,

You can do it with RAP.

- In data tab Create the query CustomersQuery with the following sqlscript:
SELECT CustomerCode, CustomerName FROM YourTable GROUP BY CustomerCode

- In rap create function GetTotal( Customer: string; Type: String): Double;

- In your application GetTotal call a function with TotalQuery

TotalQuery.ParamByName['CustomerCode'].AsString := Customer;
TotalQuery.ParamByName['Type'].AsString := Type;
TotalQuery.Execute; // I don't remember if you run the query with execute
TotalByCliente := TotalQueryF_1.Value;

- In the report call the function to get the debits

- In the report call the function to get the credits

Can you do it?

Good luck,

Jose Maria Sanmartin

rbuser

Well, that's na idea, but you see, we don't want to create too many
dependencies from external queries. We will have many reports like that, and
we don't want to populate de Delphi code with that situations. Our report
module is a fine structure where the user choose what the report he wants,
choose the filters he wants, and print it. Like I said, we just pass for the
report the sql where clause and that's it.

Now, as we see it, when we create a group in the menu, the Report Builder
create the necessary bands for that group, but that doesn't create nothing
in sql, right? It's just an internal mechanism to deal with the groups and
the variables associate with it, is that true?

For example, why this doesn't work?:

For the same report we are talking about, we have 3 dataviews:

Customer_Table
- customer_code
- customer_name
order: customer_name

Cust_total_D (customer account table) (to show the total of Debits for each
customer)
- customer_code
- sum(total)
group: customer_code; where (search): type='D': order: customer_code

Cust_total_C (customer account table) (to show the total of Credits for
each customer)
- customer_code
- sum(total)
group: customer_code; where (search): type='C': order: customer_code

The two last tables are linked with the first through the customer_code
field.

We don't have any groups created by report builder, so there isn't group
bands.

It's possible with that dataview stucture (or other similar) to create the
report? We tried but something is missing, because the report don't show the
correct data or none at all.

Thanks for your help.

IMendes

rbuser

"Isildo Mendes" escribi? en el mensaje