We have a problem with step 9 of the following query, the update statement causes a timeout on the dbsrvr.  We have tried these both with where conditions and joins, without any
improvement.

--------------
*Prerequisites
--------------
1)Run Mailware & Arev Purage Updates
2)Export Arev (Advanced Revelation) Purage table to mailware as "Lifestyle_Arev_AllPurages"

----------------------
*Combine Purage Update
----------------------

3)DROP TABLE Lifestyle_com_allpurages


4) Creates a copy of mailware purage table as CombinePurageTable*
-----------------------------------------------------------------
select Custno,Type,YearlyOrders
into Lifestyle_Com_AllPurages
from lifestyle_allpurages

5) Adds customers from Arev_Purage who are not in MailwarePurage*
---------------------------------------------------------------
Insert into Lifestyle_Com_AllPurages
(CustNo,Type,YearlyOrders)
select Custno,Type,YearlyOrders
from lifestyle_arev_allpurages
where custno not in (select custno from Lifestyle_AllPurages)


6) Update Original Mailware_Purage table if arev recency is later then update*
----------------------------------------------------------------------------
update Lifestyle_AllPurages
set  Lifestyle_AllPurages.type = Lifestyle_Arev_AllPurages.type
from Lifestyle_AllPurages,Lifestyle_Arev_AllPurages
where Lifestyle_AllPurages.custno = Lifestyle_Arev_AllPurages.custno
and Lifestyle_Arev_AllPurages.type < Lifestyle_AllPurages.type

7) Update Combine Purage table from Original Mailware_Purage*
-------------------------------------------------------------
update Lifestyle_Com_AllPurages
set Lifestyle_Com_AllPurages.type = lifestyle_allpurages.type
from Lifestyle_Com_AllPurages,lifestyle_allpurages
where Lifestyle_Com_AllPurages.custno = lifestyle_allpurages.custno


8) Combine Purage(Orders: arev+mailware) and place in "Temp Table"
-------------------------------------------------------------------
select lifestyle_allpurages.custno,(lifestyle_arev_allpurages.yearlyorders + lifestyle_allpurages.yearlyorders) comorders
into lifestyle_purage_comorders
from lifestyle_arev_allpurages,lifestyle_allpurages
where lifestyle_arev_allpurages.custno = lifestyle_allpurages.custno


9) Update the Combine Purage(Orders) set equal to (arev+mailware)
------------------------------------------------------------------
update Lifestyle_Com_AllPurages
set Lifestyle_Com_AllPurages.YearlyOrders = lifestyle_purage_comorders.comorders
from Lifestyle_Com_AllPurages,lifestyle_purage_comorders
where Lifestyle_Com_AllPurages.custno = lifestyle_purage_comorders.custno


10) DROP TABLE Lifestyle_purage_comorders

Table Layouts:

Lifestyle_com_AllPurages - 461 391 Records
CustNo - FLOAT
Type - INTEGER
YearlyOrders - INTEGER

Lifestyle_AllPurages - 6488 Records
CustNo - FLOAT
Type - INTEGER
YearlyOrders - INTEGER
TypeN - INTEGER

Lifestyle_Purage_comorders - 6174 Records
CustNo - FLOAT
Type - INTEGER

Lifestyle_AREV_AllPurages - 460900 Records
CustNo - FLOAT
Type - INTEGER
YearlyOrders - INTEGER

This query take Recency and Frequency from on old Advanced revelation Database and creates a cumulative total for both the Advanced revelation and DBISAM Data in DBISAM.

Indexes also do not speed up the update process, significantly.

Any help would be much appreciated.

Pierre,

<< We have a problem with step 9 of the following query, the update
statement causes a timeout on the dbsrvr.  We have tried these both with
where conditions and joins, without any improvement. >>

What is the exact error message that you are getting ?  Also, what version
of DBISAM are you using ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Assuming 4.25b3

Suggest:

1) Index Join fields for sure
2) Use a Commit Interval 500 to allow engine to breath (makes it faster too)
3) You should not put tablename or alias on a set variable  (surprised it
let you do it)

Example:

Update LifeStyle_Com_AllPurages m
Set YearlyOrders = lu.ComOrders
from LifeStyle_Com_AllPurages m join lifestyle_purage_comorders lu
on lu.Custno = m.Custno
Commit Interval 500;

hth  Donat.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Pierre,

<< We have a problem with step 9 of the following query, the update
statement causes a timeout on the dbsrvr.  We have tried these both with
where conditions and joins, without any improvement. >>

What is the exact error message that you are getting ?  Also, what version
of DBISAM are you using ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

No error message just times out.  Asks whether you would like to reconnect to the database.  We are using version 4.21

Pierre

"Donat Hebert \(Worldsoft\)" <donat.hebert@worldsoft.ca> wrote:

Assuming 4.25b3

Suggest:

1) Index Join fields for sure
2) Use a Commit Interval 500 to allow engine to breath (makes it faster too)
3) You should not put tablename or alias on a set variable  (surprised it
let you do it)

Example:

Update LifeStyle_Com_AllPurages m
Set YearlyOrders = lu.ComOrders
from LifeStyle_Com_AllPurages m join lifestyle_purage_comorders lu
on lu.Custno = m.Custno
Commit Interval 500;

hth  Donat.

Hi Donat,

Thanks for this will give it a try and post should we have any further queries.

Thanks
Pierre

Pierre,

<< No error message just times out.  Asks whether you would like to
reconnect to the database.  We are using version 4.21 >>

Are you using DBSYS, or is this your own application ?  You will probably
need to increase the TDBISAMSession.RemoteTimeout property a bit in order to
prevent a timeout on the client side during a lengthy operation like this.
In DBYS, the option is in File/Options/Remote/Timeout.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Pierre,
Hi Tim,

Sorted it out with the correct indexing!

Thnks

<< No error message just times out.  Asks whether you would like to
reconnect to the database.  We are using version 4.21 >>

Are you using DBSYS, or is this your own application ?  You will probably
need to increase the TDBISAMSession.RemoteTimeout property a bit in order to
prevent a timeout on the client side during a lengthy operation like this.
In DBYS, the option is in File/Options/Remote/Timeout.

--
Tim Young
Elevate Software
www.elevatesoft.com