Page 1 of 1
OrdKey CDX-NTX
Posted: Mon Jan 14, 2008 1:14 pm
by ask
Try this
Code: Select all
#include "fivewin.ch"
request DBFcdx
FUNCTION MAIN()
SET DELETED On
DBCREATE( "TMPTEST", { { "TEST", "C", 10, 0 } } )
USE TMPTEST via "DBFcdx"
INDEX ON FIELD -> test TO TMPTEST
APPEND BLANK
REPLACE FIELD -> test WITH "Test 1"
APPEND BLANK
REPLACE FIELD -> test WITH "Test 2"
DELETE
APPEND BLANK
REPLACE FIELD -> test WITH "Test 2"
DELETE
APPEND BLANK
REPLACE FIELD -> test WITH "Test 3"
GO TOP
do while !eof()
? RECNO(), ORDKEYNO()
SKIP
enddo
CLOSE
RETURN NIL
The result is 1 1 and 4 2 which is the right one because why delete 2 records
Now try with ntx (remove REQUEST DBFcdx , remove via "DBFcdx")
The result is 1 1 and 4 4 which is the wrong . Using dbfntx the ordkeyno counts deleted records too.
Can someone please give me a clue how i can use dbfntx and ordkeyno to work ?
regards,
A.S.K
Posted: Mon Jan 14, 2008 1:39 pm
by nageswaragunupudi
Please create the table, index and close.
Reopen and then do the test. Both RDDs count deleted records.
Posted: Mon Jan 14, 2008 1:53 pm
by ask
Yes but WHY? If i use set deleted on WHY does it counts the records? This is a BIG problem because i cannot move within my indexed records. Any solution?
Thank you
A.S.K
Posted: Mon Jan 14, 2008 2:12 pm
by nageswaragunupudi
Create index with for !deleted() condition.
Posted: Mon Jan 14, 2008 2:30 pm
by ask
Thank you NageswaraRao
regards,
A.S.K
Posted: Mon Jan 14, 2008 2:54 pm
by nageswaragunupudi
There is another way if you are using DBFCDX only.
Create the index on normal way. But when you open the table, set filter to !deleted(). OrdKeyCount() and OrdKeyNo() respect filters and scopes. So you get only 2 keys.
On large tables if you find the filter to make the program slow, you can always create one more index tag 'INDEX ON DELETED() TAG DELETED'
Also it is desirable not to create indexes with for clause, but create separate tag for deleted(), because filter optimizations use only indexes that do not have for clauses.
With DBFNTX only way is the create every index you need with FOR !deleted() clause.
Posted: Mon Jan 14, 2008 3:01 pm
by ask
On large tables if you find the filter to make the program slow, you can always create one more index tag 'INDEX ON DELETED() TAG DELETED'
Can you explain it because i don't understand it
Thank you
A.S.K
Posted: Tue Jan 15, 2008 2:27 am
by nageswaragunupudi
Mr ask
In the olden days of ndx, ntx and initial cdx, filters used to crawl. We used to avoid them like plague. After foxpro introduced Rushmore technology for optimization of filters, Comix and SIX provided similar optimisations for Clipper. Later on Clipper's DBFCDX used comix for optiimization of filters. Now, except DBFNTX all RDDs provide optimized filters. But there is one condition for the RDD to be able to optimize the filters. We need to provide indexes on all the columns we use in the filter expressions. If not, the RDD optimizes the filter either patially or not at all depending on the availability of filters. When we SET DELETED ON, the RDD's require index on the expression 'DELETED()' also to optimize. Then, if we set filter like 'FLD1 = 'SOMETHING' .AND. FLD2 = 'SOMETHING' .AND. !DELETED()' the filter will be fully optimized. Some RDDs may not need us to specify !DELETED() in the filter clause, but they do need an index. I have not used comix and six for ages. You may check their documentation. But explicitly using !DELETED() in the filter clause and having an index on DELETED() will help every RDD ( other than NTX) to optimize the filters fully.
Posted: Tue Jan 15, 2008 7:56 am
by ask
Thank you for your answer.I did not know all this things .As i can understand being you seem to know allot about databases and indexes . Can you please tell me which of the 2 RDDs (DBFntx , DBFcdx) is faster and more reliable ?
Thank you very much
A.S.K
Posted: Tue Jan 15, 2008 8:21 am
by James Bott
NageswaraRao,
Thanks for that detailed explanation. It was very helpful. I'm sure a lot of us did not know all that.
Regards,
James
Posted: Tue Jan 15, 2008 10:09 am
by nageswaragunupudi
ask wrote:Thank you for your answer.I did not know all this things .As i can understand being you seem to know allot about databases and indexes . Can you please tell me which of the 2 RDDs (DBFntx , DBFcdx) is faster and more reliable ?
Thank you very much
A.S.K
Between the two, DBFCDX.
I read in some posts that now there is a new RDD which is even better. I havent tried.
I am using ADS.