Eating crow!
When I first looked at the data I just randomly browsed the data and I did not see a high number of duplicate CUST_NO. This morning I took some time to look closer at the data in my tables and discovered that there are a lot of duplicate customer numbers in the tables. I am sure that is why the update is taking so much time to complete and that is why the update using the select in the where clause is so much faster. Dennis, you were correct in your assessment. "It may be that you have many rows in each table for the same customer. This could cause the same row to be updated many times." Thanks again for everyone's response. John From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Tuesday, November 10, 2009 8:39 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Update taking a long time It may be that you have many rows in each table for the same customer. This could cause the same row to be updated many times. The key here is to minimize the actual updates taking place. A better syntax would be: UPDATE t2008 SET Receipt ='Y' Where Cust_no in (select distinct Cust_no FROM t2007) The rows in T2008 will be updated only once with this syntax. Dennis McGrath _____ From: [email protected] [mailto:[email protected]] On Behalf Of John Engwer Sent: Monday, November 09, 2009 10:08 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Update taking a long time I am trying to update a column in temporary table t2008 based on data in temporary table t2007. There are about 4000 - 5000 rows in each table and the column CUST_NO is indexed in both tables. I am trying to figure out why the update takes so long, approximately 3 minutes. That seem like an excessive amount of time with only 4000 rows in the tables. UPDATE t2008 SET Receipt ='Y' FROM t2008 t1,t2007 t2 WHERE t1.CUST_NO = t2.CUST_NO Using V8, 11/02/2009 build John
