DBFNTX - how to improve speed in SKIP by index file ?
Sergy
Direct file IO access (also with HBNETIO used as FILE IO redirector) needs
much more operations, i.e. for SKIP with active index it needs:
LOCK INDEX FILE
READ INDEX HEADER WITH UPDATE COUNTER TO CHECK IF IT WAS CHANGED
IF INDEX WAS CHANGED SEEK CURRENT RECORD
* TAKE NEXT/PREV RECORD NUMBER FROM INDEX PAGE
READ RECORD BODY
IF RECORD IS NOT VISIBLE (DELETED OR FILTERED) GOTO (*)
UNLOCK INDEX FILE
Each (*) operation may cause additional IO request.
SEEK operation has in practice comparable cost to SKIP.
Daniele Campagna
Well, what about to use dtos(sale_date)? Dates will have the
format "yyyymmdd" (char), so a simple seek "201703" will find the
first record for March 2017 sales, then read and skip until dates
match.
Skip with indexes opened is far slower than without them. If you are sure that sales are added so that the older are fist, you can :
seek the first, seek the last (Someone ages ago wrote a
seekbottom() function),
close indexes, go to first, do while recno()<=last, skip...
Dan
--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: http://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Sergy
понедельник, 15 мая 2017 г., 18:33:11 UTC+3 пользователь Daniele Campagna написал:
Francesco Perillo
Unsubscribe: harbour-users+unsubscribe@googlegroups.com
Web: http://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-users+unsubscribe@googlegroups.com.
Sergy
понедельник, 15 мая 2017 г., 22:31:50 UTC+3 пользователь fperillo написал:
Ash
Francesco Perillo
--
Przemyslaw Czerpak
You need RDD with bitmap support.
Using DBRMAP with index on DATE field all what is necessary
is setting filter and switching to natural table order:
request RMDBFCDX
[...]
rddSetDefault( "RMDBFCDX" )
[...]
// filter on indexed fields are automatically optimized
// in RMDBF* RDDs
SET FILTER TO DATE >= STOD( "20170401" ) .AND. ;
DATE <= STOD( "20170430" )
// natural table order, skip does not touch index
ordSetFocus( 0 )
dbGoTop()
WHILE ! eof()
[...]
dbSkip()
ENDDO
In Harbour repository there is contrib RDDBM library which
also supports bitmap filters but they have to be set manually
(there is no automatic filter optimization), the method of
manual setting is rather inefficient and later the skip in
natural order is not as fast as it can be, anyhow you can
try to make:
request BMDBFCDX
[...]
rddSetDefault( "BMDBFCDX" )
[...]
// choice index on DATE field
ordSetFocus( 1 )
// create array for filtered records
aBM := {}
dbSeek( dStart )
dbOrderInfo( DBOI_SKIPEVAL,,, {| key, rec |
IF key > dStop
RETURN .t.
ENDIF
aadd( aBM, rec )
RETURN .f.
} )
bm_dbSetFilterArray( aBM )
ordSetFocus( 0 )
dbGoTop()
WHILE ! eof()
[...]
dbSkip()
ENDDO
You can also lock index manually and make COPY TO
for given range:
// choice index on DATE field
ordSetFocus( 1 )
// lock the index
dbOrderInfo( DBOI_READLOCK, .T. )
// now index is locked, no one else can upodate it
dbSeek( dStart )
// copy records
COPY TO (cDest) WHILE DATA <= dStop
// unlock the index
dbOrderInfo( DBOI_READLOCK, .F. )
the COPY TO will be much faster because index is locked for the
whole time so for internal SKIP inside COPY TO operation instead of:
READ INDEX HEADER WITH UPDATE COUNTER TO CHECK IF IT WAS CHANGED
IF INDEX WAS CHANGED SEEK CURRENT RECORD
* TAKE NEXT/PREV RECORD NUMBER FROM INDEX PAGE
READ RECORD BODY
IF RECORD IS NOT VISIBLE (DELETED OR FILTERED) GOTO (*)
UNLOCK INDEX FILE
READ RECORD BODY
IF RECORD IS NOT VISIBLE (DELETED OR FILTERED) GOTO (*)
index.
best regards,
Przemek
> --
> You received this message because you are subscribed to the Google
> Groups "Harbour Users" group.
>
> ---
> You received this message because you are subscribed to the Google Groups "Harbour Users" group.
Francesco Perillo
request RMDBFCDX
request BMDBFCDX
Sergy
Sergy
вторник, 16 мая 2017 г., 0:51:12 UTC+3 пользователь druzus написал:
ZeTo Fernandes
Sergy
WBR
Przemyslaw Czerpak
Hi Francesco,
> > request RMDBFCDX
> Is this RDD public?
> How does it work internally? Is the RDD that creates an array of records to
> return to the client? Something like BMDBFCDX but done internally... ?
records but BMDBF* RDDs is very basic implementation which supports
only Harbour arrays in operations on low level bit map record set.
It also does not optimize many operations which can be quite well
optimized with bit map filters so speed improvement is only partial.
> > request BMDBFCDX
> I do something similar for a browse in hbQt: I run a new thread that scans
> the file and populate an array with recno()... it is the model in Qt... I
> may try BMDBFCDX...
it should be anyhow when filter is already set it should greatly speed
up browsing records regardless of used user interface.
best regards,
Przemek
Przemyslaw Czerpak
Hi Sergy,
> Your suggestion is more wonder for me than Francesco's NETIO/LetoDBf... ))
simply function which make COPY TO temporary DBF files on the server
side for given range of data and then open this tables remotely to create
final reports. Easy and fast.
> I will do some deep investigation in those RDD, but the first question -
> can I use "bitmap" indexes together with DBFNTX ?
is left untouched in bitmap RDDs.
best regards,
Przemek
elch
> Your suggestion is more wonder for me than Francesco's NETIO/LetoDBf... ))
For sure NETIO is the fastest method, i.e. you can create set of
LetoDBf processes (SKIPs) roughly through 80K-100K records using an indexed table in a *single second*, which can be tuned up 3-5 times.
In comparison NetIO feels like a lame duck, tenths ! of times slower as both technics have to deal with limits in 'package based' networks ...
These not mentioned network limits may have a 'homeopathic' increase of performance by using bitmap index with NetIO ...
IMO
Rolf
Francesco Perillo
--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.
Unsubscribe: harbour-users+unsubscribe@googlegroups.com
Web: http://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-users+unsubscribe@googlegroups.com.
Przemyslaw Czerpak
Hi Rolf,
> > > Your suggestion is more wonder for me than Francesco's NETIO/LetoDBf...
> > ))
> > For sure NETIO is the fastest method, i.e. you can create set of
> LetoDBf processes (SKIPs) roughly through 80K-100K records using an indexed
> table in a *single second*, which can be tuned up 3-5 times.
> In comparison NetIO feels like a lame duck, tenths ! of times slower as
> both technics have to deal with limits in 'package based' networks ...
the server side what with NETIO can be done only using RPC which is
the fastest method. Also much faster then using LETODB.
> These not mentioned network limits may have a 'homeopathic' increase of
> performance by using bitmap index with NetIO ...
said in one of previous messages gives only small improvement in
comparison to standard file server network redirectors and for sure
is much slower then LETODB.
best regards,
Przemek
elch
> > > Your suggestion is more wonder for me than Francesco's NETIO/LetoDBf...
> > ))
> > For sure NETIO is the fastest method, i.e. you can create set of
> LetoDBf processes (SKIPs) roughly through 80K-100K records using an indexed
> table in a *single second*, which can be tuned up 3-5 times.
> In comparison NetIO feels like a lame duck, tenths ! of times slower as
> both technics have to deal with limits in 'package based' networks ...
Read again my message. I was talking about processing operation on
the server side what with NETIO can be done only using RPC which is
the fastest method. Also much faster then using LETODB.
sigh,
even old original LetoDb can call any possible Harbour function at 'server side'.
[Franceso: but no <command> like "SUM" pre-processed by std.ch :-) ]
It can also call 'UDF' [ User Defined Functions] compiled as HRB,
loadable at server start or even when the server is up.
LetoDBf fork will not block other connections during such a RPC/ UDF is executed,
that is somehow un-noticed 'new'.
It also can start such an UDF function 'connection independent',
think of a 'background task' running at server side: started by a user who afterwards log out and went home.
And *maybe* my point is missed:
without any changes, LetoDBf outperforms NetIO multiple times.
RPC is not the solution at all, what is about updating new data to server ?
IMO
Rolf
[ nice place to dedicate very honours to Alexander and Pavel for their work, without would be no LetoDBf ]
ZeTo Fernandes
Daniele Campagna
Sergy, another solution could be using scoped indexes. I have a database where some data are "active" and have a flag "A", while others are "historical" and have a flag "B". Users work the entire month on "active" data, then close the period (records are marked as "B") and load a new batch of records (marked "A"). Using a filter to display/browse only the "A" records is too slow, so I have in place a simple:
index on <date field> for <flagname>="A" to
<indexname>
You could let the dbf unchanged and only create scoped indexes (index01, index02...) with month number.
Of course this approach is fine if users need a single month report, if they want f.e. january AND February you either must create a new temporary index on the fly or extract data to a temp file (first January, then February....) and this complicates things...
Besides, why to use ntx indexes? Switch to cdx.
Dan
--
Sergy
For sure NETIO is the fastest method, i.e. you can create set of
simply function which make COPY TO temporary DBF files on the server
side for given range of data and then open this tables remotely to create
final reports. Easy and fast.
> I will do some deep investigation in those RDD, but the first question -
> can I use "bitmap" indexes together with DBFNTX ?
Yes. They are descendant RDDs of DBF* RDDs so all low level index data
is left untouched in bitmap RDDs.
Sergy
Sergy
elch
Hi Rolf
LetoDBf based on NETIO, but why it is so faster ?
this is your third misperception: it is *not* based on HbNetIO.
It is Russian! based, experienced to work in even bad conditions technics,
just extended by a minor bit German precision :-)
a:)
a RDD ( Replaceable Database Driver ) is a technic invented by Cl*pper some decades ago.
This means you can switch from this to that with 'very few' changes.
So the basic change to test LetoDBf means to add a line with Leto_Connect() to the server
-- and to use the letodb.hbc ...
Sure, there are ways to fix you morely at specific LetoDBf -- not needed at first steps.
b:)
it is highly optimized to work around the network limits in a 'package based' network
-- search in this list for a note from me about the 'delayed error':
This may explain why it is so fast *updating* data to the server.
And it perhaps gives you an impression about network limits, rarely mentioned ...
c:)
the secret behind the skipping speed is a well maintained record data read cache,
valid by default for only a single second at client.
d:)
Ron ! benchmarked LetoDBf to be even (many) much faster as the ADS server
-- not only by some %, but much more -- would have not expected such a gap.
This would make LetoDb[f] fastest possible DBF access over TCP/IP network,
even with default settings ...
e:)
there may be still bugs in there, even the reports about get lesser ...
ah, the link:
https://github.com/elchs/LetoDBf
with great honours to the origin -- Aleander, Pavel: all well at you ?
Rolf
Francesco Perillo
elch
Can you please expand on point C?
c:) read cache
the client (your application) works with a local copy of the record data.
So the next "field" access <right in time> does not lead to a new network
request to server, as you already got the whole record data.
How long this 'cache' is valid, before a new request to server happens, can be
tuned -- by default it is one single second.
If a SKIP is requested, the server responds *not only* with a single record,
but with a *bunch* of records (LETO_SETSKIPBUFFER, default in letodb.ini: 21)
These are the records 'around' the record the client requested to SKIP,
depending on the SKIPing direction before or behind.
Then the next SKIP in application does not lead to a new network request,
but just exchanges the 'record buffer' at client side.
When this buffer, aka 'read cache' must be refreshed, is clearly defined:
# want to skip to a record outside the read cached records
# the timeout for cache is reached ( LetoDBf can set it table specific )
# R-F-lock a record/ file --> request fresh record data from server
Especially the last point is important, as in any network application:
in between the time, the record data is requested
-- but before you lock the record/ file -- data may have changed.
So only after a lock is granted, you are sure to see what is real ;-)
[ think of a SQL result set: working on data meanwhile may have changed ]
---
All is about the limit in amount of requests/ answers,
that can be send to the server in a timespan, as each is a 'whole packet'.
So LetoDB[f] most effort is to optimize the needed amount of packets,
and to send mostly filled packets.
One single packet can be up to ~ 1500 bytes
-- the more bytes less, the more decrease in network performance.
( One question about:
https://groups.google.com/forum/#!topic/harbour-users/jKqFD-UoF4s )
best regards
Rolf
