Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
UPDATE Speed |
Wed, Feb 21 2007 3:43 AM | Permanent Link |
Pierre Roux | 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. |
Wed, Feb 21 2007 11:07 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Feb 21 2007 10:33 PM | Permanent Link |
"Donat Hebert \(Worldsoft\)" | 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. |
Thu, Feb 22 2007 1:57 AM | Permanent Link |
Pierre Roux | "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 |
Thu, Feb 22 2007 1:57 AM | Permanent Link |
Pierre Roux | "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 |
Thu, Feb 22 2007 6:48 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Feb 22 2007 10:12 AM | Permanent Link |
Pierre | "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 |
This web page was last updated on Friday, May 3, 2024 at 08:07 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |