Clive,

<< Yes.

1 Record in the Memory Table references 1 record in the disk table, >>

That's probably the issue.  DBISAM has the most trouble with performance
when it has to execute a *lot* of joins, such as is the case with a 1-to-1
join.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks chris, unfortunately the set value = 1 is actually
coalesce(value,0.0) + coalesce(sed.value,0.0)

I just removed the clause to simply things for the post.


"Chris Erdal" <chris@No-Spam-erdal.net> wrote in message
news:Xns97867199BC9BF14torcatis@64.65.248.118...
> Just an idea out of nowhere, but perhaps things'll go faster one at a
> time:
> (assuming value is 0 everywhere before you start)
>
> update "\memory\exp1"
> set value = 1000
> from "\memory\exp1"
> inner join scenario_expense_detail sed on  sed.expenses_code =
> exp1.expenses_code;
>
> update "\memory\exp1"
> set value = value / 10
> from "\memory\exp1"
> inner join scenario_expense_detail sed on    sed.scenario_code =
> exp1.scenario_code;
>
> update "\memory\exp1"
> set value = value / 10
> from "\memory\exp1"
> inner join scenario_expense_detail sed on sed.project_code =
> exp1.project_code;
>
> update "\memory\exp1"
> set value = value / 10
> from "\memory\exp1"
> inner join scenario_expense_detail sed on sed.period = exp1.period;
>
> (and lastly, if necessary)
>
> update "\memory\exp1"
> set value = 0
> WHERE value > 1;
>
> --
> Chris

"Clive" <dd@dddd.com> wrote in news:4A0A0ED7-49F4-41B4-AD25-21FE64A237C1
@news.elevatesoft.com:

> Thanks chris, unfortunately the set value = 1 is actually
> coalesce(value,0.0) + coalesce(sed.value,0.0)
>
> I just removed the clause to simply things for the post.
>
>

Well, how about :

SELECT exp1.RecordID memID, COALESCE(sed.value,0.0) SedValue
INTO memory\exp2
FROM memory\exp1, scenario_expense_detail sed
WHERE exp1.expenses_code = sed.expenses_code
AND exp1.scenario_code = sed.scenario_code
AND exp1.project_code = sed.project_code
AND exp1.period = sed.period
;

UPDATE memory\exp1
SET value = COALESCE(value,0.0) + SedValue
FROM memory\exp1
INNER JOIN memory\exp2
ON exp1.RecordID = memID
;

I'm sort of hoping that the complex join will go faster if it's not also
updating one of the tables involved at the same time.

and its corollary, that updating with a join will go disproportionately
faster the less complex is the join.

and also that a single coalesce in each script will be more efficient than
2 in the same one.

I'm often reckoned to be an optimist...
--
Chris

Will test it out and let you know
Cheers
clive.

"Chris Erdal" <chris@No-Spam-erdal.net> wrote in message
news:Xns97875AA73583614torcatis@64.65.248.118...
> "Clive" <dd@dddd.com> wrote in news:4A0A0ED7-49F4-41B4-AD25-21FE64A237C1
> @news.elevatesoft.com:
>
>> Thanks chris, unfortunately the set value = 1 is actually
>> coalesce(value,0.0) + coalesce(sed.value,0.0)
>>
>> I just removed the clause to simply things for the post.
>>
>>
>
> Well, how about :
>
> SELECT exp1.RecordID memID, COALESCE(sed.value,0.0) SedValue
> INTO memory\exp2
> FROM memory\exp1, scenario_expense_detail sed
> WHERE exp1.expenses_code = sed.expenses_code
> AND exp1.scenario_code = sed.scenario_code
> AND exp1.project_code = sed.project_code
> AND exp1.period = sed.period
> ;
>
> UPDATE memory\exp1
> SET value = COALESCE(value,0.0) + SedValue
> FROM memory\exp1
> INNER JOIN memory\exp2
> ON exp1.RecordID = memID
> ;
>
> I'm sort of hoping that the complex join will go faster if it's not also
> updating one of the tables involved at the same time.
>
> and its corollary, that updating with a join will go disproportionately
> faster the less complex is the join.
>
> and also that a single coalesce in each script will be more efficient than
> 2 in the same one.
>
> I'm often reckoned to be an optimist...
> --
> Chris

Hi,

Tried and no increase in speed.

Cheers though

Clive.

"Chris Erdal" <chris@No-Spam-erdal.net> wrote in message
news:Xns97875AA73583614torcatis@64.65.248.118...
> "Clive" <dd@dddd.com> wrote in news:4A0A0ED7-49F4-41B4-AD25-21FE64A237C1
> @news.elevatesoft.com:
>
>> Thanks chris, unfortunately the set value = 1 is actually
>> coalesce(value,0.0) + coalesce(sed.value,0.0)
>>
>> I just removed the clause to simply things for the post.
>>
>>
>
> Well, how about :
>
> SELECT exp1.RecordID memID, COALESCE(sed.value,0.0) SedValue
> INTO memory\exp2
> FROM memory\exp1, scenario_expense_detail sed
> WHERE exp1.expenses_code = sed.expenses_code
> AND exp1.scenario_code = sed.scenario_code
> AND exp1.project_code = sed.project_code
> AND exp1.period = sed.period
> ;
>
> UPDATE memory\exp1
> SET value = COALESCE(value,0.0) + SedValue
> FROM memory\exp1
> INNER JOIN memory\exp2
> ON exp1.RecordID = memID
> ;
>
> I'm sort of hoping that the complex join will go faster if it's not also
> updating one of the tables involved at the same time.
>
> and its corollary, that updating with a join will go disproportionately
> faster the less complex is the join.
>
> and also that a single coalesce in each script will be more efficient than
> 2 in the same one.
>
> I'm often reckoned to be an optimist...
> --
> Chris

"Clive" <dd@dddd.com> wrote in news:CF3F6B79-D0A7-4865-AD70-0A9385DA2089
@news.elevatesoft.com:

> Tried and no increase in speed.
>
> Cheers though
>
>

Ah well, too bad.

If you feel like another go, try:

SELECT exp1.RecordID memID, COALESCE(exp1.value,0.0) + COALESCE
(sed.value,0.0) CumValue
INTO memory\exp2
FROM memory\exp1, scenario_expense_detail sed
WHERE exp1.expenses_code = sed.expenses_code
AND exp1.scenario_code = sed.scenario_code
AND exp1.project_code = sed.project_code
AND exp1.period = sed.period
;

CREATE UNIQUE INDEX ixExp2 ON memory\exp2(memID)
;

UPDATE memory\exp1
SET value = (SELECT CumValue FROM memory\exp2 WHERE exp1.RecordID =
memID)
;

--
Chris

Same sort of timings, the drama is the 1 to1 mapping that Tim says DBISAM is
taking its time on.

You are persistent though gotta respect that!.

Cheers
Clive.


"Chris Erdal" <chris@No-Spam-erdal.net> wrote in message
news:Xns978856129F4A714torcatis@64.65.248.118...
> "Clive" <dd@dddd.com> wrote in news:CF3F6B79-D0A7-4865-AD70-0A9385DA2089
> @news.elevatesoft.com:
>
>> Tried and no increase in speed.
>>
>> Cheers though
>>
>>
>
> Ah well, too bad.
>
> If you feel like another go, try:
>
> SELECT exp1.RecordID memID, COALESCE(exp1.value,0.0) + COALESCE
> (sed.value,0.0) CumValue
> INTO memory\exp2
> FROM memory\exp1, scenario_expense_detail sed
> WHERE exp1.expenses_code = sed.expenses_code
> AND exp1.scenario_code = sed.scenario_code
> AND exp1.project_code = sed.project_code
> AND exp1.period = sed.period
> ;
>
> CREATE UNIQUE INDEX ixExp2 ON memory\exp2(memID)
> ;
>
> UPDATE memory\exp1
> SET value = (SELECT CumValue FROM memory\exp2 WHERE exp1.RecordID =
> memID)
> ;
>
> --
> Chris

"Clive" <dd@dddd.com> wrote in
news:FE51034E-D910-45C0-8CB1-7E8CC2CBE3D4@news.elevatesoft.com:

> Same sort of timings, the drama is the 1 to1 mapping that Tim says
> DBISAM is taking its time on.

OK, I'm sorry but I give up :-/

> You are persistent though gotta respect that!.

And you are very patient

--
Chris