When i set a filter to a dbf, the vert-scroll range from the xBrose is not updated. How to make this?
Board index FiveWin for Harbour/xHarbour Question about XBROWSE
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
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
James, thanks very much! I will test this.
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.
Gale,
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.
Regards,
James
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.
nCount:=0
go top
do while ! eof()
nCount++
skip
enddoRegards,
James
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
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.
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.
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
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
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
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.
I pulled out my Six ng file and found this information on TurboRead. My memory is coming back now.
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"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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
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
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
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
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
// 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 nilFWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
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
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
byte-one wrote: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.
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
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)
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
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