--- In firebird-support@yahoogroups.com, "davissammybr" wrote:

> I have two tables. One contains a list of orders and other items with each order. I created a stored procedure that returns me the total value of items per order number and the key field (orderno), the order number. To return all order data and total items use this:
>
> Select
> Ordl.OrderNo,
> Ordl.ClientName,
> Ordl.Age,
>
> Itens.TotalValue
>
> from
> OrderList Ordl
> left join ( Select OrderNo, TotalValue from MyStoredProcedure ) Itens
> on ( Itens.OrderNo=Ordl.OrderNo )
>
> This is very slow. I don't know if I do something wrong.
> How to improve performance?
>
> Thanks.

Why do you calculate the entire stored procedure for each row in Ordl? When only joining on the result, you calculate lots of irrelevant result for each row in Ordl! Better modify your procedure to take OrderNo as an input argument that you put in the WHERE clause of your stored procedure and change your call to something like:

Select Ordl.OrderNo, Ordl.ClientName, Ordl.Age,
Itens.TotalValue
from
OrderList Ordl
left join MyStoredProcedure(OrderNo) Itens on (1=1)

This way, you'll only calculate a small fraction of the amount of records calculated by the stored procedure and greatly improve the performance.

HTH,
Set