Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 18 total
Thread Query optomisation help
Mon, Mar 13 2006 1:02 AMPermanent Link

"Clive"
Hi,

I am updating a memory table and the update is taking over 90 seconds on a
2.4ghz 1GB windows XP machine V4.22 B4

All columns are indexed and I have tried the NOJOINOPTIMIZE command and
theres not much difference between using that and not.

The memory table has 12672 rows and the disk table has 23328 rows.

Would 90 seconds to run this seem usual?. or is there something I can do to
speed this up

Cheers
Clive.

================================================================================
SQL statement
================================================================================

update "\memory\exp1"

set value = 1

from "\memory\exp1"

inner join scenario_expense_detail sed on  sed.expenses_code =
exp1.expenses_code and         sed.scenario_code = exp1.scenario_code and



sed.project_code = exp1.project_code and   sed.period = exp1.period

Tables Involved
---------------

exp1 (exp1) table opened shared, has 12672 rows
scenario_expense_detail (sed) table opened shared, has 23328 rows

Join Ordering
-------------

The driver table is the exp1 table (exp1)



The exp1 table (exp1) is joined to the scenario_expense_detail table (sed)
with
the INNER JOIN expression:



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

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

The expression:

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

has been rewritten and is OPTIMIZED

================================================================================




Mon, Mar 13 2006 2:29 AMPermanent Link

Abdulaziz Jasser
Do you have indexes for "expenses_code", "scenario_code", and "project_code" in both memory table and the physical table?  If not, create ones.  
It should speed up the query.
Mon, Mar 13 2006 4:38 AMPermanent Link

"Clive"
Yep and yep.
The posted query plan indicates that all is optimised.

"Abdulaziz Jasser" <jasser@cyberia.net.sa> wrote in message
news:BDD3C954-8F2F-4CB2-B306-DCE92755B4F0@news.elevatesoft.com...
> Do you have indexes for "expenses_code", "scenario_code", and
> "project_code" in both memory table and the physical table?  If not,
> create ones.
> It should speed up the query.
>

Mon, Mar 13 2006 12:09 PMPermanent Link

Abdulaziz Jasser
Hummm, I don't think I can help you more on this due to the fact I am still using 3.30, but let me tell you
something from what I have learned over the years, db transaction really depends on how fast are BOTH your
CPU+HRAD DRIVE (Not to forget memory).  The other thing, are you testing this query over a LAN or a single
PC?
Mon, Mar 13 2006 12:26 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Clive,

<< Would 90 seconds to run this seem usual?. or is there something I can do
to speed this up >>

If the join condition is a 1-to-1 link, then it could take a while since
DBISAM has to filter the joined disk table 12672 times.  Also, what is the
record size of the in-memory table ?  If it is fairly large, then you might
want to specify a COMMIT INTERVAL around 1000 or so in the SQL statement to
allow for more rows to be updated in each internal transaction.

--
Tim Young
Elevate Software
www.elevatesoft.com


Mon, Mar 13 2006 2:49 PMPermanent Link

"Clive"
Its all local.

"Abdulaziz Jasser" <jasser@cyberia.net.sa> wrote in message
news:3CBF7CD8-4FA6-4E1F-AD22-A35557827387@news.elevatesoft.com...
> Hummm, I don't think I can help you more on this due to the fact I am
> still using 3.30, but let me tell you
> something from what I have learned over the years, db transaction really
> depends on how fast are BOTH your
> CPU+HRAD DRIVE (Not to forget memory).  The other thing, are you testing
> this query over a LAN or a single
> PC?
>

Mon, Mar 13 2006 3:00 PMPermanent Link

"Clive"
The structure of the memory table is

Integer,
Integer,
Integer,
Float,
Date,
String 255
Float

I tried the commit interval 1000, but timings remained about the same.

Guess thats as fast as this query can go.

Cheers
Clive.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:8CB24898-72EF-48F8-9A3B-0B03C9CF973B@news.elevatesoft.com...
> Clive,
>
> << Would 90 seconds to run this seem usual?. or is there something I can
> do to speed this up >>
>
> If the join condition is a 1-to-1 link, then it could take a while since
> DBISAM has to filter the joined disk table 12672 times.  Also, what is the
> record size of the in-memory table ?  If it is fairly large, then you
> might want to specify a COMMIT INTERVAL around 1000 or so in the SQL
> statement to allow for more rows to be updated in each internal
> transaction.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>
>

Mon, Mar 13 2006 5:19 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Clive,

<< Guess thats as fast as this query can go. >>

Is the join a 1-to-1 relationship ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Mon, Mar 13 2006 5:55 PMPermanent Link

"Clive"
Yes.

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

..


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:D0476368-BD6D-4BD0-BD2E-707D1F24C6E8@news.elevatesoft.com...
> Clive,
>
> << Guess thats as fast as this query can go. >>
>
> Is the join a 1-to-1 relationship ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Tue, Mar 14 2006 5:11 AMPermanent Link

Chris Erdal
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
Page 1 of 2Next Page »
Jump to Page:  1 2
Image