Hi,

At February 18, 2004, 09:04, emisutil wrote:

> The result I want is a dataset (I work with delphi) so I can show this
> dataset on a report made with FastReport.

I, too, work with Delphi, and I use IBO.

> What do you mean with selectabel storede procedure?

Look in the IB 6.0 Language Reference manual found on Firebird's
website for more info.
http://firebird.sourceforge.net/index.php?op=doc&id=userdoc

Here's an example:

CREATE PROCEDURE ClientBalances(CutoffDate DATE)
RETURNS(Client_Name varchar(30),
AmountDue numeric(18,2))
AS
BEGIN
FOR SELECT ClientName, SUM(Balance_Due)
FROM INVOICES
WHERE (Invoice_Date<=:CutOffDate)
AND (Balance_due<>0)
GROUP BY Client_Name
INTO :Client_Name, :AmountDue
DO
BEGIN
SUSPEND;
END;
END;

Usage:
SELECT * FROM ClientBalances(:CutOffDate)

> How do you access 100.000 invoices quickly?

I simply keep track of the balance due within the master table of the
invoices and filter out those that have balance due of zero, when
doing the aged A/R report. Also, properly indexed tables do help. And,
by using stored procedures, I can use simple queries, instead of
complex queries. And, that's one of the speed technique that I use.

Just to give you an example, I had one big complex query to return the
aged A/R report and that took forever(almost 15 minutes, with 50,000
invoices) to run. After changing to a stored procedure and using
simple queries within the SP, the time for the report generation was
reduced under 2 minutes. Now, those times were with FB 1.0, with FB
1.5 the complex query took under 5 minutes and the SP ran under 30
seconds. And now, I find that there's not much difference with 100,000
invoices.

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)