Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 11 to 18 of 18 total |
Query optomisation help |
Tue, Mar 14 2006 1:43 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Mar 14 2006 1:54 PM | Permanent Link |
"Clive" | 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 |
Wed, Mar 15 2006 2:55 AM | Permanent Link |
Chris Erdal | "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 |
Wed, Mar 15 2006 4:12 PM | Permanent Link |
"Clive" | 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 |
Wed, Mar 15 2006 7:15 PM | Permanent Link |
"Clive" | 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 |
Thu, Mar 16 2006 2:28 AM | Permanent Link |
Chris Erdal | "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 |
Fri, Mar 17 2006 11:19 PM | Permanent Link |
"Clive" | 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 |
Sat, Mar 18 2006 4:13 AM | Permanent Link |
Chris Erdal | "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 |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |