John, The SUB-SELECT is so much faster because in getting the custno list it only has to process the index file to provide a list to update. The correlated version has to do a lot more work involving the RB2 and RB3 files and joining the two tables.
Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ----- Original Message ---- From: John Engwer <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tue, November 10, 2009 2:20:50 PM Subject: [RBASE-L] - Re: Update taking a long time I am writing a routine that has not been deployed yet so the symptoms manifest themselves on a standalone PC. When I use the update with a select in the where clause it is fast (a second or two verses 3 minutes). I plan to do some more testing when I get a chance. I would expect table correlation to add overhead but not that much overhead. John -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Gary Wendike Sent: Tuesday, November 10, 2009 2:41 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Update taking a long time John, one other thought...are you working across a network or on a standalone machine. I have found, sometimes, it is advantageous to use permanent tables in scenarios where the data is being transferred across the network. Just a thought. Gary -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of John Engwer Sent: Tuesday, November 10, 2009 10:01 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Update taking a long time Thanks to all for your responses. Tony - The update with sub select is very fast and that is what I will use. I never realized that an update using table correlation is so slow. Bob - My qualcol setting is 10. Dennis - Not many rows with the same customer number. There is no index on the RECEIPT column. Marc - It would be a many - many relationship. The tables are so small, I expected it to be very fast. John -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of A.G. IJntema Sent: Tuesday, November 10, 2009 2:33 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Update taking a long time Hi John, Looking at your statement it sounds a little strange to me. If I understand your statement correctly your intention is to update the column Receipt to 'Y' where the Custno is also available in T2007 If this is correct I think you better use a statement like: Update T2008 set Receipt= 'Y' where custno in (select custno from T2007) Kind Regards, Tony IJntema -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of John Engwer Sent: dinsdag 10 november 2009 6:13 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Update taking a long time Razzak, I create the tables and indexes just before I perform the update. However, per your suggestion, I did pack index and the results are the same. John -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of A. Razzak Memon Sent: Monday, November 09, 2009 11:55 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Update taking a long time At 11:07 PM 11/9/2009, John Engwer wrote: >UPDATE t2008 SET Receipt ='Y' FROM t2008 t1,t2007 t2 WHERE >t1.CUST_NO = t2.CUST_NO >Using V8, 11/02/2009 build John, Have you PACKed the INDEXes since you have applied the last update? Using the latest update of R:BASE Turbo V-8 (Build: 8.0.22.31102 or higher), try the following: 01. Start RBG8 and CONNect dbname 02. At the R> prompt: SET FEEDBACK ON PACK INDEX CLS SET FEEDBACK OFF 03. Now CREATE the TEMPORARY table accordingly and do the UPDATE as outlined above. See what you get. Very Best R:egards, Razzak. __________ Informatie van ESET NOD32 Antivirus, versie van database viruskenmerken 4590 (20091109) __________ Het bericht is gecontroleerd door ESET NOD32 Antivirus. http://www.eset.com __________ Informatie van ESET NOD32 Antivirus, versie van database viruskenmerken 4590 (20091109) __________ Het bericht is gecontroleerd door ESET NOD32 Antivirus. http://www.eset.com
