Super, Doug! TxnHist is listed first in my example so RBASE will run through all the records, looking up values in the temp table with an extremely efficient index.
Dennis From: [email protected] [mailto:[email protected]] On Behalf Of Doug Hamilton Sent: Tuesday, February 03, 2015 5:12 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: WHERE Clause, indices and parens questions Thanks Dennis! That's exactly what I was looking for. The column I'm updating is indexed and I can understand why it would take long to update. I'll double check to see if the index is really needed. However, I did go ahead and implement your other suggestions and tested it on my system which is a recent copy of the actual db. Holy carp! Processing time went from about 30 seconds down to about 1 second! I have one question on your point #2: I assume TxnHist should be listed first in the FROM clause because it is the table being updated and not because it is the larger (more rows) of the two tables. Thanks again and best wishes for continued recovery from your auto accident. Doug On 2/3/2015 9:48 AM, Dennis McGrath wrote: Doug, First of all, is the column you are updating indexed? That would slow updating it tremendously on a table this long. If that is not the case I would do this: 1. Make a multi column index on your temp table for the 3 columns in the order that is used in your joining where clause. 2. Make the temp table the second table, not the first. 3. Set manopt on to make sure R:BASE follows your optimization. 4. Use this syntax (no parenthesis around the where clause): UPDATE TxnHist + SET ChryInvNbr = INV.ChryInvNbr + FROM TxnHist TXN, ChryInvDtlTmp INV + WHERE + TXN.VPlNmbr = INV.VPlNmbr AND + TXN.CusPnbr = INV.CusPnbr AND + TXN.TxDate = INV.InvoiceDate This will avoid trying to use any of the single indexes in TxnHist, and use a very efficient multi-column index to get the update value from the temp table. Further optimization can be done by changing the where clause (and temp index) clause so the most unique column is first. I suspect InvoiceDate would be the most unique, but only you can answer that question. Dennis McGrath -----Original Message----- From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Doug Hamilton Sent: Monday, February 02, 2015 8:15 PM To: RBASE-L Mailing List Subject: [RBASE-L] - WHERE Clause, indices and parens questions Hi All - I'm updating a column in a 1.2 megarow table (TxnHist) with values from a column in a temp table (ChryPaid) that may have a few thousand rows, based on three columns matching between the two tables. I remember previous posts about use of parenthesis to force (or ignore?) use of indexed columns and the best order in which to list the columns. Unfortunately I can't find that post 'cause I'm asking those questions again: How should I use parenthesis to optimize the WHERE clause? Does the order of the columns matter? i.e. most unique listed first or last? Are these even a concern if MANOPT is OFF? The reason I'm asking is that it took about an hour run the following command: UPDATE TxnHist + SET ChryInvNbr = INV.ChryInvNbr + FROM ChryInvDtlTmp INV, TxnHist TXN + WHERE + (INV.VPlNmbr = TXN.VPlNmbr AND + INV.CusPnbr = TXN.CusPnbr AND + INV.InvoiceDate = TXN.TxDate) Column CusPnbr in table TxnHist is an FK, the other five columns in the WHERE clause are single-column indices. The most unique column in TxnHist is VPlNmbr. Settings in the RBASE.DAT file are: SET STATICDB ON SET FASTLOCK ON SET QUALCOLS 2 MANOPT is not set in RBASE.DAT or in code, I believe, so it is OFF by default. Per Taskmanager, Network utilization is 17% on a 100 mbps connection (high?). CPU usage is about 10% to 15%. This is on an XP machine with 2 Gb RAM. R:Base is version 9.5.5.20116. Thanks much for your help and my apologies for the repeat questions. Doug --- This email has been checked for viruses by Avast antivirus software. http://www.avast.com ________________________________ [http://static.avast.com/emails/avast-mail-stamp.png]<http://www.avast.com/> This email has been checked for viruses by Avast antivirus software. www.avast.com<http://www.avast.com/>
