Question about XBROWSE

[byte-one]

When i set a filter to a dbf, the vert-scroll range from the xBrose is not updated. How to make this?

[James Bott]

Since there is no easy way to know the total number of records in the filter, you would have to count them then post the count to the bKeycount var of the browse object.

oBrw:bKeyCount := {|| nCount }

This is probably not feasible except for databases with very few records. Remember that a filter requires reading of every record in the database, not just the filtered ones. It would be much better to use a conditional index if you can.

Regards,
James

[byte-one]

James, thanks very much! I will test this.

[Gale FORd]

It seems to depend on the database driver you use. xHarbour has functions to return the number of records in a filter similar to Six and Comix drivers. I think Advantage can do this also. Some ADO/ODBC drivers supply the recordcount property in their recordlist objects.

[James Bott]

Gale,

xHarbour has functions to return the number of records in a filter similar to Six and Comix drivers.

I'm curious, are these any faster than counting them yourself? It would seem that they would also have to skip through all the records.

Code: Select all  Expand view  RUN

nCount:=0
go top
do while ! eof()
   nCount++
   skip
enddo

Regards,
James

[Gale FORd]

If the filter can be resolved with the indexes using Comix's Clipmore, Six's Mach Six, or Advantage Optimized Filters (AOF) then yes it is very fast.
The important part is the level of optimization.
If the filter can be completely optimized using the indexes, if Comix's rlOptLevel() returns 2, then cmkeycount() and cmkeyno() will be very fast to determine total records and relative position.
If the filter is only partially optimized then the maybe's have to be resolved to get an accurate count. I think cmkeycount() and cmkeyno() will still be faster than skipping through the records because it only has to visit the records that are questionable. Also dbskip() can take longer because it sets a lock every time it moves the record pointer.

Here is info on rlOptLevel() from Comix's Clipmore which is supported in xHarbour.com

rlOptLevel() Get optimization level rlNewQuery() achieved
------------------------------------------------------------------------------
Synopsis
rlOptLevel() -> nOptLevel
Arguments
None.
Returns
A numeric result which is one of the following values:
2: Fully Optimizable
1: Partially Optimizable
0: Not Optimizable

Description

rlOptLevel() returns the level of optimization which was achieved by the
last call to rlNewQuery(). Since cmFilter() calls rlNewQuery(), this
function will also return the level of optimization achieved by the last
call to cmFilter().

The three levels of optimization possible are (see examples below for
more information):

Fully Optimizable which means that the condition was fully resolved
by looking at the indexes (i.e., all records meeting the condition were
determined solely by looking at the indexes).

Partially Optimizable which means that part of the condition was
resolved by looking at indexes, but part of the condition could not be
resolved.

Not Optimizable which means that no part of the condition could be
resolved by looking at indexes.

[James Bott]

Gale,

Now some of it is coming back to me--I rarely use filters. So, it could be much faster or maybe just a tiny bit faster.

Also dbskip() can take longer because it sets a lock every time it moves the record pointer.

Wow, I never heard that before. Why would it need to lock the record just to move the pointer? Nothing is being written. Which record is it locking, the one it is leaving or the one it is moving to? Or, is it locking the file? Strange.

Something I like to keep in mind regarding speed. If something takes less than a second then most users won't notice even if it takes twice as long. Sometimes speed is not important. I wish it was not important more often.

Regards,
James

[Gale FORd]

It has been a while but my understanding is that the lock is to make sure the index is stable as it finds the next key in the index. If the index is updated while the RDD is finding the correct node in the tree, it could go to the wrong record.

I pulled out my Six ng file and found this information on TurboRead. My memory is coming back now.

Code: Select all  Expand view  RUN

  SET TURBOREAD:

  Syntax:

  SET TURBOREAD [ON | OFF]

  This command allows you to turn-off the automatic locking of index
  files during certain read-only processes, ie: SEEK, GO TOP, SKIP, FIND,
  etc.  This powerful feature has one serious side-effect:  IT ALLOWS
  OTHERS TO UPDATE YOUR INDEX WHILE YOU ARE SEARCHING IT!  Basically,
  this means that even though your SEEK may return a FOUND() = .T.
  status, if the index was changed during the SEEK process, it would not
  necessarily be accurate.  That's why we call it a "dirty" read. <g>

  To offer some type of solution to this integrity problem, semaphore
  management functions (Sx_MakeSem(), Sx_KillSem(), etc) have been included
  so that you may have a mechanism through which you can inform other network
  users that you are in the SEEK process.  With this, they can be forced to
  wait until your process is complete before performing the index file
  update.

  NOTE: Using this function, while somewhat risky, will improve your
        network performance by up to 100%, so weigh the options carefully.

    NOTE:  This command is NOT supported under SIXNTX.

  Example:

    /*
        This program demonstrates the speed difference dirty read makes when
        skipping though a shared database.
    */

    #include "SIXNSX.CH"

    LOCAL nStart, nEnd

    SET EXCLUSIVE OFF         // Make SHARED the default

    USE TEST VIA "SIXNSX"     // Open up the TEST database
    INDEX ON last TO last     // Build an index; Since it's exclusive after
    CLOSE DATA                //  just creating it, close the database and
    USE TEST VIA "SIXNSX"     //  reopen it
    SET INDEX TO LAST         // Then set our index active again

    ? "Skip test - shared"
    ?
    nStart := Seconds()       // Save starting time
    FOR nCnt := 1 TO 240      // Cruise through the database for a bit
      ?? "."                  // Print a dot
      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"
    ?

    // Now turn dirty read on
    SET TURBOREAD ON

    ? "Skip test - shared with dirty read on"
    ?
    nStart := Seconds()       // Save starting time
    FOR nCnt := 1 TO 240      // Cruise through the database for a bit
      ?? "!"                  // Print an exclamation point
      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"
 

[Gale FORd]

Wow. I just tried the example on my previous email on a simple test database with 100 records. The locks done on each skip really adds some overhead!

Skip test - shared
................................................................................
................................................................................
................................................................................

Elapsed time: 58.28 seconds

Skip test - shared with dirty read on
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Elapsed time: 0.36 seconds!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[James Bott]

Gale,

Thanks for the explaination--it is very informative. Now it makes sense.

Those pesky other users, are always create problems.

Well, if programming was easy, then anyone could do it!

Regards,
James

[James Bott]

Gale,

I guess the standard NTX index doesn't lock records. My test below took 2.7 seconds.

It took 0.28 seconds without an index.

Regards,
James

Code: Select all  Expand view  RUN

// Purpose: Test speed of skipping

#include "fivewin.ch"

function main()

use customer exclusive
index on last to last

use customer shared
set index to last
go top

    nStart := Seconds()       // Save starting time
    FOR nCnt := 1 TO 240      // Cruise through the database for a bit
      //?? "."                  // Print a dot
      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

msgInfo( nEnd - nStart )

return nil

[James Bott]

Gale,

I guess my test wasn't quite the same as yours since yours was printing "." and "!" and mine wasn't. That probably accounts for the difference.

Would you mind running your test without the printing?

Regards,
James

[nageswaragunupudi]

When i set a filter to a dbf, the vert-scroll range from the xBrose is not updated. How to make this?

If you are using DBFCDX or ADS, the vertical scroll is updated automatically when we set filter, move the record pointer ( say skip 0 ) and refresh the browse.

XBrowse depends on OrdKeyCount() and OrdKeyPos() for this. Any RDD where these functions return correct values work well.

If we use any other RDD like DBFNTX, we need to take care of all this ourselves if we can. Basically this RDD does not indicate the number of filter records and the position relative to the total. Such RDDs do not in general help vertical scroll bars.

[nageswaragunupudi]

On the subject raised about count of records, any RDD that uses bitmap filters counts records on the basis of bits that are on. DBFCDX is one ( Clipper adopted this in 5.3 from Comix)

[Gale FORd]

James,

Did you run your test program on a server (mapped) drive or on a local drive. When I copied the test.exe and test.dbf to local drive, I see the times you noted.

Below is the exe and dbf on local drive
Skip test - shared
.....................................
.....................................
.....................................

Elapsed time: 3.64 seconds

Skip test - shared with dirty read on
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Elapsed time: 2.80 seconds


Below is the same exe and dbf, but run on a mapped network drive
I changed the test program to run with and without the display and the times were not that much different.

--------------------- No Display
Skip test - shared

Elapsed time: 47.67 seconds

Skip test - shared with dirty read on

Elapsed time: 2.49 seconds

--------------------- With Display

Skip test - shared
......................................
......................................
......................................

Elapsed time: 42.97 seconds

Skip test - shared with dirty read on
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Elapsed time: 2.69 seconds