For Mr. Nages

[lucasdebeltran]

Dear Mr. Nages,

From previous posts from you I understood that SET DELETED ON in a network scenareo and xBrowse causes some delay, so that´s the reason to include the For !Deleted() clause on the index command.

Is it correct?.

Thank you.

[nageswaragunupudi]

Mr Lucas

No.
I advise you NOT to create any conditional ( using for clause ) in the main CDX.
Please note that conditional indexes are not used for filter optimization.

Instead, create one additional TAG like this for every DBF (large and medium)

INDEX ON DELETED() TAG DELETED

When you browse a DBF,
USE <dbf> <other clauses>
SET FILTER TO !DELETED()
GO TOP
<browse>


If you want to set any filter then
SET FILTER TO <yourcondition> .AND. !DELETED()

When you clear filter, instead of fully clearing filter
SET FILTER TO !DELETED()

[James Bott]

Why not just use SET DELETED ON at the beginning of your application?

[nageswaragunupudi]

Why not just use SET DELETED ON at the beginning of your application?

SET DELETED ON should anyway be used at the beginning of the application.

SET DELETED ON acts as a un-optimized filter. RDD reads all records including deleted records and skips deleted records in the memory. When there are many deleted records in a DBF the hit on performance is clearly perceptible. In a networked environment, the sluggishness is more visible.

If my advice is implemented, SET FILTER TO !DELETED() acts as a fully optimized filter because there is an index tag on the expression "DELETED()" and the RDD does not read deleted records from the disk at all.

In addition, without this filter OrdKeyCount() includes deleted records and if this filter is set, OrdKeyCount() excludes the deleted records and we get the correct count.

[James Bott]

Nages,

Thanks again for a clear explanation. I'm sure this will be useful.

James

[Enrico Maria Giordano]

Rao,

sorry but we still haven't a sample demonstrating that DBFCDX do optimize filters. My sample in another thread shows that there is no speed difference between using and not using indexes, so it looks like no optimization occur. Let's hope I'm wrong...

EMG

[lucasdebeltran]

Dear Mr. Nages,

I am refering to xBrowse´s speed, not filtering.

For xBrowse, is it right to not use SET DELETED ON?.

Thank you.

[nageswaragunupudi]

Dear Mr. Nages,

I am refering to xBrowse´s speed, not filtering.

For xBrowse, is it right to not use SET DELETED ON?.

Thank you.

My first advise applies to XBrowse also and XBrowse mostly.
Always keep SET DELETED ON and please follow my advice.
Another point: XBrowse depends more on the accuracy of OrdKeyCount() and OrdKeyNo() and OrdKeyGoTo().
My advice helps this aspect also.

If you still feel sluggish behavior on networks there is something more you can do. We can discuss that later if and when you need.

[mercurial]

We can discuss that later if and when you need.

As Nages said, it all depends on what is inside the DBF. Imagine a 100.000 records dbf, with only 2 records deleted. There should be absolutely no difference between the various methods described in terms of refresh time. If you start to have 10% of records deleted it may make a difference.

SET DELETED ON:
When you only use SET DELETED ON, as previously noticed, all the records are read from the dbf, analyzed and eventually discarded. Almost all browse systems use dbSkip() to move between records. So if you need to display record 150 and the next not deleted record is 850, 600 records are read, checked, discarded. Since DBFs can be shared and someone else may modify the data (unless in exclusive mode) the browse systems can't cache which record are displayed so in case of a dbSkip(-1) it has to transfer, check, discard again that 600 records.
Imagine the worst case, in which just record 1 and record 100000 are active.... the browse must read 999998 records...

INDEX ON ! DELETED():
with this index the records are logically divided in two groups, the active and the deleted. Now record 150 and record 850 are 1 record from each other... so dbSkip() is really quick. Imagine the worst case: there are 2 groups, one composed by 2 active records, the other by 999998 records.

Up to here I completely agree with Nages.
What I don't agree is when he adds SET FILTER TO ! deleted() when the index is active.
I did some tests and with INDEX+SET FILTER active, ordKeyCount() doesn't change: it always reports 100.000, because there are 100.000 records in the index.
It may be that the RDD can be quicker reading the deleted() value from the index, but I'm not sure it really does it.

Only ordScope() actually creates a filter using the index. And it can be proved by test small test I write here.

It may be that xBrowse is smart: it may recognize that a filter condition is also present in a index and uses it to set an ordScope() range. In this way ordKeyCount() returns the number of active records present in the DBF.


There is still another way to handle these cases, but it is a bit more complex: custom indexes.

Code: Select all  Expand view  RUN

#pragma -w0
procedure main
? "Create DBF"
a := {}
aAdd( a, {"TEST","C",30,0} )

dbCreate( "TEST", a )

USE TEST
FOR i := 1 TO 100000
    append blank
NEXT
GO 300
delete
GO 700
delete
go 10000
delete

? "CREATE INDEX"
INDEX ON DELETED() TO TEST

ClOSE DATABASES
? "START TEST"

USE TEST

? "1:", ordKeyCount()
SET FILTER TO !DELETED()
? "2:", ordKeyCount()
SET FILTER TO

SET INDEX TO TEST
? "3:", ordKeyCount()
SET FILTER TO !DELETED()
? "4:", ordKeyCount()

SET FILTER TO

#include "Ord.ch"
OrdScope( TOPSCOPE   , .T. )
OrdScope( BOTTOMSCOPE, .T. )
? "5:", ordKeyCount()