--- In firebird-support@yahoogroups.com, Daniel Rail <daniel@a...> wrote:

> 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)
>

This usage is for use with Delphi? I get error when I try to pass a
parameter to a procedure that returns a result set instead of one record.

> > 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.
>

Thats sounds interesting, so the idea is to avoid complex querys and
to use Stored Procedures to use simple querys,

I´ ll tell you if I get to reduce my times as you did from minutes to
seconds :-))

Greetings

Emiliano Sutil