Bill, your grocery store example is great.
To summarize (and make sure I understand it correctly), here's an
example using the UPDATE command with the following:
TxnHist is a 1.6 megarow table
InvDtlTmp is a few hundred row temp table.
Columns VPlNmbr, CusPnbr and TxDate in table TxnHist are indexed.
Columns VPlNmbr, CusPnbr and InvoiceDate in table InvDtlTmp are indexed.
UPDATE InvDtlTmp +
SET TxnHistSeqNbr = TXN.TxnHistSeqNbr +
FROM InvDtlTmp INV, TxnHist TXN +
WHERE +
INV.VPlNmbr = TXN.VPlNmbr AND +
INV.CusPnbr = (TXN.CusPnbr) AND +
INV.InvoiceDate= (TXN.TxDate)
InvDtlTmp is listed first in the FROM clause because it is the table
being updated.
In the compound WHERE clause, the parenthesis around (TXN.CusPnbr) and
(TXN.TxDate) prevent their indices from being used, forcing VPlNmbr
index to be used, assuming it is the most unique of the three columns
(has the fewest duplicate values).
Does the order of the columns in the WHERE clause have any significance?
MANOPT is irrelevant in this case because it addresses multi-table
ordering, not indexing.
Thanks,
Doug
On 6/2/2016 5:52 PM, Bill Downall wrote:
Mike,
An expression inside parentheses on the right sign of the operator in
the condition is -- by design, I think -- not used for indexed
retrieval, but for row-by-row comparisions. (On the other hand "WHERE
(columnname > .vdate)" would be able to use an index.
This is a very useful thing to know, because it enables you to trick
R:BASE into using exactly the index you want it to use in the cases
where it guesses wrong. (R:BASE only uses one index in compound
conditions.) All you have to do is put parens around the item to the
right of the = sign (or > sign, or whatever.)
Bill
On Thu, Jun 2, 2016 at 6:48 PM, Michael J. Sinclair
<[email protected] <mailto:[email protected]>> wrote:
Hi all,
I was doing a search of a two table view with about 1 million rows.
The column I am using has an index.
This search was very slow.....
BROWSE ALL FROM viewname WHERE columname > (.#date - 365)
This search was almost instant
SET VAR vdate = (.#date - 365)
BROWSE ALL FROM viewname WHERE columname > .vdate
Why is there such a big difference?
Mike
--
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to [email protected]
<mailto:[email protected]>.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
For more options, visit https://groups.google.com/d/optout.
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
--
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
