ok, thanks for the clarification ...
I'll try to do it myself and see ... but also I would like to do it
in a function/stored procedure, as will be more clear for me ...
and in this way I'll compare the speed difference ...
my initial approach is:
create temporary table test as SELECT
oid,numero,data,concepte,deure,haver,(deure-haver) as saldo FROM
assentaments WHERE clau_compte='0257000000002';
create a cursor,
loop for each row and update the saldo field
and return the select * from test
still working ...
regards and thanks again !
raimon
On 21/05/2007, at 14:46, Richard Broersma Jr wrote:
>
> --- Raimon Fernandez <coder@montx.com> wrote:
>> This returns 3217 rows, and the value_sum is ok, but it takes too
>> long (89.45 sec)
>>
>> Can anyone confirm that it's doing for every row the
>> 'starting_sum' (first select), and if so, how to do it just once ?
>
> Yes, any sub-select in the Select expression list will be executed
> many times. To fix this, you
> will need to reform your query by pushing this sub-select down to
> the from clause. Also you will
> need to add clau_compte to the SELECT expression list of both sub-
> Selects
>
> SELECT <your expression list>, Summed_assentaments.delta_sum +
> Initialvalue.starting_sum
> FROM ( <your first initial sub-select> ) AS
> Summed_Assentaments( oid, concepte,deure, haver,
> delta_sum, clau_compte )
> INNER JOIN ( <your initial value sub-select> ) AS Initialvalue
> ( starting_sum, clau_compte )
> ON Summed_Assentaments.clau_compte = Initialvalue.clau_compte;
>
> I hope this can help to improve query time.
>
> Regards,
> Richard Broersma Jr.
>
