Thanks Ted, I never really understood the usefulness of a primary key before. Now I do.
I check with and without the the indexes and saw little difference - about 1/5 of a second instead of 15! Thanks again, Joe On Sun, May 15, 2016 at 4:50 PM, Ted Roche <tedro...@gmail.com> wrote: > Yeah, that's just wrong. The query should take 1 second or less. > > Index matches only work if the expression of the index is IDENTICAL to > the expression used in the left side of a WHERE clause. > > ALLTRIM does not work in indexes, as VFP indexes have to have the same > length. > > It's a BEST Practice to not create complex expressions, like stringing > fields together, in order to match records in two tables, as it's > harder to ensure all of those expressions are Rushmore-optimizable. > > So, assuming you have separate index tags for each field (dDate, > cName, cType, etc.) on both of the tables, and also a separate tag for > DELETED() on each table, and none of the indexes have "FOR" clauses, > since they won't be used in Rushmore optimization. > > Assuming you have a primary key on the receipts table the query is > easy. If you don't,... well, it's a lot tougher. > > SELECT *; > FROM Receipts; > Where Receipts.PrimaryKey NOT IN > (SELECT Receipts.PrimaryKey > FROM Receipts JOIN Master > ON > Receipts.dDate = Master.dDate AND > Receipts.cName = Master.cName AND > Receipts.cType = Master.cType AND > Receipts.nAmount = Master.nAmount AND > Receipts.cAccount = Master.cAccount AND > Receipts.cClass = Master.cClass AND > Receipts.cJobCust = Master.cJobCust AND > Receipts.cNumber = Master.cNumber AND > Receipts.mMemo = Master.mMemo AND > Receipts.cSplit_id = Master.cSplit_id > ) > INTO CURSOR Batch READWRITE > > should work in subsecond time with 1350 records. > > On Sun, May 15, 2016 at 4:05 PM, Joe Yoder <j...@wheypower.com> wrote: > > I have a process that starts out with a table of about 4300 records. An > > SQL select yields a cursor of only the records not already in a master > > table for processing and gradual addition to the master table. When the > > master table was empty my SQL results were sub second. Now that the > master > > table has about 1350 records in it the SQL process takes between 15 and > 16 > > seconds. My initial thought was that indexing each field in the master > > table should speed things up but that seems to have no effect. > > > > Here is the code: > > * Build a cursor of all records from the Receipts table that are not in > the > > Master table > > SELECT *; > > FROM Receipts; > > WHERE (DTOC(dDate); > > + ALLTRIM(cName); > > + ALLTRIM(cType); > > + ALLTRIM(STR(nAmount,10,2)); > > + ALLTRIM(cAccount); > > + ALLTRIM(cClass); > > + ALLTRIM(cJobCust); > > + ALLTRIM(cNumber); > > + ALLTRIMm(Memo); > > + cSplit_id; > > NOT in; > > (select DTOC(dDate); > > + ALLTRIM(cName); > > + ALLTRIM(cType); > > + ALLTRIM(STR(nAmount,10,2)); > > + ALLTRIM(cAccount); > > + ALLTRIM(cClass); > > + ALLTRIM(cJobCust); > > + ALLTRIM(cNumber); > > + ALLTRIM(mMemo); > > + cSplit_id; > > FROM Master)); > > INTO CURSOR Batch READWRITE > > > > Suggestions welcome! > > > > Thanks in advance, > > > > Joe > > > > > > --- StripMime Report -- processed MIME parts --- > > multipart/alternative > > text/plain (text body -- kept) > > text/html > > --- > > [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cabqednvwbdd+fh48x62fac027qjdrgugzmwhnkyzucobu4n...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
