Ahh, I didn't notice the 2nd part of Bob's question. I never do the "where salesid = (int(.vsalesid))". I remember being told long ago that as it's doing its search it would need to evaluate the (int(.vsalesid)) for every row it is looking at so it slows it down. Most frequently I see this when I'm doing a "where date < (.#DATE - 30)" or something like that. It makes sense that RBase is searching each row and saying "what is today - 30, and is the row < that value"? That would be slower than making the calc up from and saying "is the row < that value".
Karen -----Original Message----- From: ttc.inc <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thu, Mar 12, 2015 10:22 am Subject: [RBASE-L] - Re: Update / Where best practice Thanks to all who replied. No speed issues and I understand about the index / expression relationship. I was just wanting to confirm a best practice concerning data integrity. -Bob From: "Bill Downall" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Thursday, March 12, 2015 8:52:11 AM Subject: [RBASE-L] - Re: Update / Where best practice Bob, On your second question, any expression on the right side of the operator in a WHERE condition will guarantee that the index for that column will not be used, and that R:BASE will read sequentially through all rows looking for the ones that match that WHERE condition. However, the query may have already excluded a lot of rows if there is another condition in your WHERE clause that is indexed and is not using an expression. On the first question, it probably doesn't matter. If you have a very large table, or poor performance, then try both ways and time them. Bill On Thu, Mar 12, 2015 at 9:47 AM, <[email protected]> wrote: I have a question as to the Update table Set column = and Where clause. Is it a good practice to have expressions in either? Example: I want to increase a value in an integer column in a table, so : Update Sales_Tab set Qty_Sold = (Qty_Sold + .vNewQty) Where Sales_ID = .vSalesID Or should I obtain the value of Qty_Sold for the record first, increase the value and then update the the table. Select Qty_Sold into vPrevQty Indicator iV1 from Sales_Tab where Sales_ID = .vSalesID Set var vUpdateQty = (.vPrevQty + .vNewQty) Update Sales_Tab set Qty_Sold = .vUpdateQty Where Sales_ID = .vSalesID The first option should be faster, but is it a good practice? Secondly concerning WHERE clauses, is it a good practice to use expressions in them. Example: Variable vSalesID is a text value of "1234" Column Sales_ID is an integer column Update Sales_Tab set Qty_Sold = (Qty_Sold + .vNewQty) + Where Sales_ID = (INT(.vSalesID)) OR should I convert the vSalesID into an integer variable first and not have the expression in the WHERE clause? In the past, to keep the number of lines of code to a minimum, I have used the expressions in the command or where clauses. Note, that I often may update 3 or 4 columns in a table with one Update command, using expressions similar to the above and have done so for many years. But I am wondering if this is a good practice? The help files show examples of the Update command using an expression, but only on one column. Would multiple column updates with expressions be a good practice? Help also shows expressions in the WHERE clause as well. Thanks, Bob
