Question about XBROWSE

[Gale FORd]

I just tried the test program using xHarbour.com ("RMDBFCDX") and cmxShared(.f.) to turn off locking mode (dirty read).
It works just like the Clipper with Six test I did earlier.
I am going to use this on some of my history databases and see what happens.

You have to be careful which count functions you use if the database is using a range and filters.
For instance in Comix/Clipmore the cmxkeycount() and the cmkeycount() act a little different. Below is the description of the cmkeycount() function.

Code: Select all | Expand

Description
    cmKeyCount() is similar to cmxKeyCount(), but returns the number of keys
    which meet the filtered condition in the current master index.
    (cmxKeyCount() returns the "raw" count regardless of the filter).
    
    If the index is not conditional (i.e., no FOR clause), this is the same
    as the number of records which meet the condition.

    If the index is conditional, this will be the number of records which
    meet the filter condition and meet the FOR condition of the index.

    cmKeyCount() will force Linear Optimization to be performed so that the
    count returned is fully accurate.

    cmKeyCount() obeys the current index scope (if any).  I.e., it will only
    count the number of keys for the current index which meet the condition
    _and_ fall within the current index scope.
 

I modified tsbrowse to include the rmdbfcdx driver from xHarbour.com
xHarbour.com includes the functions from Comix/Clipmore and Six/MachSix drivers, but I pretty much use the Comix/Clipmore ones.

Code: Select all | Expand

         Elseif cRDDName == "RMDBFCDX"
            ::cDriver := cRDDName
            ::bTagOrder := &( "{|uTag|OrdSetFocus(uTag)}" )
            cType := Type( "cmKeyNo()" )
            ::lClipMore := .t.
            ::bKeyNo    := &( "{|cTag|cmKeyNo(cTag) }" )
            ::bLogicLen := If( ::lFilterMode,;
                               {||(::cAlias)->(Self:RecCount(::uValue1))}, ;
                               &( "{|cTag| cmKeyCount( cTag ) }" ) )
            ::bGoToPos  := &( "{|n| cmKeyGoTo( n ) }" )
 

[James Bott]

Gale,

I tested it on a local drive.

So it does seem that the NTX driver does dirty reads by default. Perhaps that is all it can do.

James

[Gale FORd]

The NTX driver does not do dirty reads. I think that NTX is not compressed and CDX is compressed. So some tests show NTX driver is faster but that is not what you are seeing.
When I tried the same test on a local drive the times were 3.68/1.10 vs network drive 42.97/2.69
So the fact that you are running it on a local drive changes the speed from 42.97 all the way down to 3.68.

[Enrico Maria Giordano]

Can dirty read be activated on a plain DBFCDX driver? I really notice a huge (not expected) slowdown on using shared mode on a network drive and I would like to improve it.

EMG

[Gale FORd]

Using the commercial xHarbour.com I can turn the locking mode off with cmxShared(.F.) using 'DBFCDX' and 'RMDBFCDX'. It did not work with 'DBFNTX'
I tried this with some reports using history database files and the speed is much better. I am reluctant to use this on current or frequently updated files.
I also did some testing with filters with and without temporary indexes. This was pretty surprising.

I wanted to expand the tests. So to make everything even I counted how many records matched a filter and used that number for the tests.
It so happens that my test filter found 83 records out of 100.
So with the default setting I found that it took 34 seconds to perform the skip test for 83 records.
Then I turned shared mode off
With filter only, the time dropped in half to 17.4 seconds
With filter still in effect and a temporary index created from filter with additive clause it dropped just a little more to 14.8 seconds.
If I closed the filter (set filter to) and just used the temporary index (with "for" still making it filtered) it acted just like there was no filter. The time was .2 seconds.

So I found the fastest times were to turn shared mode off (dirty read) and
1. Turn off any filters
or if you need to filter
2. Create temporary index with "for" and make sure no other filter set on dbf

Here is the test results

First let us find out how many records to test
Filter for dr_name <> 'C'
Records = 83 out of 100
-----------------
Skip test - shared. No Filter
Records = 83
............
Elapsed time: 33.97 seconds

-----------------
Skip test - shared with dirty read on and filtered
Records = 83
!!!!!!!!!!!!
Elapsed time: 17.42 seconds
-----------------
Skip test - shared with dirty read on and filtered temp index
Records = 83
!!!!!!!!!!!!
Elapsed time: 14.84 seconds
-----------------
Skip test - shared with dirty read on and filtered temp index then filter off
Records = 83
!!!!!!!!!!!!
Elapsed time: 0.20 seconds

Here is my test code

Code: Select all | Expand

external dbfcdx, rmdbfcdx

procedure test
    LOCAL nStart, nEnd, cDriver, nRecCount
    local aRecList

    cDriver := 'DBFCDX'

    SET EXCLUSIVE OFF         // Make SHARED the default
    * setrdd("SIXCDX")
    wait
    USE test VIA cDriver       // Open up the TEST database
    INDEX ON dr_name TO test   // Build an index; Since it's exclusive after
    CLOSE DATA                 //  just creating it, close the database and
    USE test VIA cDriver       //  reopen it
    SET INDEX TO test          // Then set our index active again

    // Find number of records to match filter
    cFilter := [dr_name <> 'C']
    ? "First let us find out how many records to test"
    ? "Filter for "+cFilter
    cmfilter( cFilter )
    go top
    nCounter := 0
    DO WHILE !eof()         // Skip to the end of the file
      nCounter++
      SKIP
    ENDDO
    nRecCount := nCounter
    set filter to
    ? "Records = ", ltrim( str( nCounter ) ), " out of ", ltrim( str( lastrec() ) )
    // ****************************************************
    // Not start testing not filtered
    // ****************************************************
    ? "-----------------"
    ? "Skip test - shared. No Filter"
    ? "Records = ", ltrim( str( nCounter ) )
    ?
    go top
    nStart := Seconds()       // Save starting time
    FOR nCnt := 1 TO 240      // Cruise through the database for a bit
      if mod( nCnt, 20 ) = 0
         ?? "."                  // Print a dot
      endif
      go top
      FOR nCounter := 1 TO nRecCount
        SKIP
      NEXT
      DO WHILE !bof()         // Skip back to the beginning of the file
        SKIP -1
      ENDDO
    NEXT
    nEnd := Seconds()         // Save ending time
    ? "Elapsed time:", nEnd - nStart, "seconds"
    ?

    // Now turn dirty read on
        //  We know no one will be updating the database, so no sense taking
        //  the hit on multi-user overhead
    cmxShared(.F.)                  && Switch to non-locking mode

    // ****************************************************
    // dirty read on and filtered only
    // ****************************************************
    ? "-----------------"
    ? "Skip test - shared with dirty read on and filtered"
    cmfilter(  cFilter )
    go top
    nStart := Seconds()       // Save starting time
    nCounter := cmkeycount()
    ? "Records = ", ltrim( str( nCounter ) )
    ?
    FOR nCnt := 1 TO 240      // Cruise through the database for a bit
      if mod( nCnt, 20 ) = 0
         ?? "!"                  // Print an exclamation point
      endif
      DO WHILE !eof()         // Skip to the end of the file
        SKIP
      ENDDO
      DO WHILE !bof()         // Skip back to the beginning of the file
        SKIP -1
      ENDDO
    NEXT
    nEnd := Seconds()         // Save ending time
    ? "Elapsed time:", nEnd - nStart, "seconds"

    // ****************************************************
    // dirty read on and filtered and temporary index
    // ****************************************************
    ? "-----------------"
    ? "Skip test - shared with dirty read on and filtered temp index"
    nStart := Seconds()       // Save starting time
    cmfilter(  cFilter )
    index on dr_name to test temporary additive usecurrent for dr_name <> "C"
    go top
    nCounter := cmkeycount()
    ? "Records = ", ltrim( str( nCounter ) )
    ?
    FOR nCnt := 1 TO 240      // Cruise through the database for a bit
      if mod( nCnt, 20 ) = 0
         ?? "!"                  // Print an exclamation point
      endif
      DO WHILE !eof()         // Skip to the end of the file
        SKIP
      ENDDO
      DO WHILE !bof()         // Skip back to the beginning of the file
        SKIP -1
      ENDDO
    NEXT
    nEnd := Seconds()         // Save ending time
    ? "Elapsed time:", nEnd - nStart, "seconds"

    // ****************************************************
    // dirty read on and filtered and temporary index then filter turned off
    // ****************************************************
    ? "-----------------"
    ? "Skip test - shared with dirty read on and filtered temp index then filter off"
    // Clear filter to reduce time
    // I filter still on it is Faster than with dirty read on but still pretty slow
    set filter to
    nStart := Seconds()       // Save starting time
    index on dr_name to test temporary for dr_name <> "C"
    go top
    nCounter := cmkeycount()
    ? "Records = ", ltrim( str( nCounter ) )
    ?
    lFirstTime := .t.
    FOR nCnt := 1 TO 240      // Cruise through the database for a bit
      if mod( nCnt, 20 ) = 0
         ?? "!"                  // Print an exclamation point
      endif
      DO WHILE !eof()         // Skip to the end of the file
        SKIP
      ENDDO
      lFirstTime := .f.
      DO WHILE !bof()         // Skip back to the beginning of the file
        SKIP -1
      ENDDO
    NEXT
    nEnd := Seconds()         // Save ending time
    ? "Elapsed time:", nEnd - nStart, "seconds"

return nil
 

[James Bott]

Gale,

Thanks for the testing. It is helpful.

If I closed the filter (set filter to) and just used the temporary index (with "for" still making it filtered) it acted just like there was no filter. The time was .2 seconds.

It does seem that you would also have to count the time needed to create the temp index in addition to the skipping through the indexed (filtered) file. I am guessing this would be slower than just setting a filter and skipping since creating an index requires skipping through the entire file once, then skipping through the index requires skipping through the filtered records again.

Of course, if you need to skip through the indexed file more than once then there likely would be a speed improvement overall.

Regards,
James

[Gale FORd]

Actually it gets back to the level of optimization.
If the filter or index can be created with rloptlevel() = 2 then it does not need to touch any records to create the filter. The filtering can be resolved completely by the indexes.
So if you have a lot of records and you need to filter out some of them then it might be better to turn off shared mode and create a temporary index with "for" command that can be optimized by rdd. As you skip through the records for the report it will not have to do any file locks on the index and it is fast!!!
For some reason with a filter (even if it is optimizable), there is still some overhead that is not found if you use a temporary index instead.

I will test the difference between range, filter, and temporary index on large history file using 1 pass similar to what happens on a report and see what happens.

[Enrico Maria Giordano]

Code: Select all | Expand

cmxShared(.F.)

I can't find the above function in xHarbour CVS nor in FWH SVN.

EMG

[Gale FORd]

I think this is something that was written by Przemysław for the commercial version of xHarbour.
It supports the extended functions/commands of Comix/Clipmore, Six/MachSix, Fast Text Search, and something else that I can't remember right now.

[Enrico Maria Giordano]

Ok, thank you. Unfortunately it is something that I can't use.

EMG

[Gale FORd]

James,

I have done some testing to compare the different scenarios of scope, filter, and temp index.
I can't remember the last time I did this much testing on core database functions.
I found out there are a lot of variables.
1. Number of people browsing and reporting on the same database at the same time.
2. How long the index has been in use (updated). A new clean built index speeds up the access.
3. The underlying order of the data. If the data is already in the order of the index the times are better.

This test is on a database with no other users and contains a total of 19199 records
I mixed up the order of the raw data so it did not match the index.
The filter and scopes used below returns 12382 records.
The filter would be dr_name <= 'M'
The scope would be cmxsetscope( 0, "") and cmxsetscope( 1, "M")

For each setup I go to the top and skip one time through

Here is the order of speed
00.13 seconds, Dirty read just skipping for 12382 records. No filter or scope.
00.14 seconds. Dirty read using scope
00.77 seconds. Dirty read using temp index for dr_name <= 'M'
04.36 seconds. Dirty read using scope and temp index
07.44 seconds. Dirty read and filter
10.80 seconds. Shared using scope
11.06 seconds. Shared just skipping. No scope or filter.
23.08 seconds. Shared using filter

So the best speed on a networked drive (in this test) was obtained by turning on dirty read and using a scope.
The dirty read with a temp index worked really well also.

You can get the exe, prg, and test data from
http://www.wwrowland.com/downloadfiles/testdirty.zip

One of the reasons I like to use a combination of scope and temporary index, is that you can quickly sort the records in the scope by any field using the temporary index.
You can also further filter the records at the same time if you want.

[Enrico Maria Giordano]

04.36 seconds. Dirty read using scope and temp index

I don't understand this result. Did you add USECURRENT clause to your INDEX ON command? Without this the index will get all the records (if I remember correctly).

EMG

[Gale FORd]

Yes, I used the "usecurrent" option. I also found that I got a slightly better performance if I set a scope, created a temp index, and then cleared the scope.
If I left the scope in effect it slowed down just a bit. I don't know why, since changing the index should clear the scope, unless "usecurrent" changes this behavior.

Here is the section of code I used for a dirty read, scope, temporary index, and then clear scope.

Code: Select all | Expand

    // ****************************************************
    // dirty read on and scoped and temporary index then scope turned off
    // ****************************************************
    CLOSE DATA
    USE test VIA cDriver       //  reopen it
    SET INDEX TO test          // Then set our index active again
    set order to 1
    cmxShared(.F.)                  && Switch to non-locking mode
    ? "-----------------"
    ? "Skip test - dirty read on and scoped with temp index then clear scope"
    nStart := Seconds()       // Save starting time
    cmxsetscope( 0, '')
    cmxsetscope( 1, 'M')
    index on dr_name to testx temporary usecurrent
    // now clear the scope so only the temp index is in play
    cmxclrscope( 0 )
    cmxclrscope( 1 )
    go top
    nCounter := 0
    do while .not. eof()
      nCounter++
      skip
    enddo
    nEnd := Seconds()         // Save ending time
    ? ltrim( str( nCounter ) ), ' Records'
    ? "Elapsed time:", nEnd - nStart, "seconds"


 

[Enrico Maria Giordano]

Check if testx index is really the active one (? OrdSetFocus()).

EMG

[Gale FORd]

Yes, it is the controlling index. It must have something to do with the nature of a temporary index.

I want to use Advantage Database Server so I don't know how much of this will change.
I know that there is a feature in Advantage that lets it take full control of database so the locking issue will not come up. I think that it may still effect it if the databases are used in shared mode with non Advantage programs, like R&R.