Preface: We have been running DBISAM as part of our solution for many years (large customer and large datastes (let’s say 5GB-10GB tables with 5m-10m+ records) which we manage through a variety of strategies)
and, I must admit, it has proven to stand the test of time 😊

Now, the problem:

We know that SQL execution and data filters perform slower when using the File Server mode (typically a local session and over Mapped Drives). We also understand that different networks have different performance considerations and results and that unoptimized WHERE statements may have performance penalties.

We have a scenario where we open up a dataset (linked to a fast performing grid (InfoPower) – so no problems here). The dataset has roughly 20 000 records in it and probably around 250 fields (yes too many).
General size of this table is +- 70Mb included any blob data.

We apply a LIKE filter (e.g. description like ‘%screw%’). The filter executes in roughly 5 seconds. We are perfectly happy with this performance (considering it's a LIKE with % operators on both sides)

W use DBSYS, start a transaction, change a record, commit the transaction.

We go back to our application WITHOUT refreshing of the table manually (via the .REFRESH method).
We cancel the filter and then re-apply the SAME filter.

The filter execution takes up to 25 seconds (i.e. much slower – here lies our problem / question)

The TABLE CHANGE / UPDATE seems to trigger some action that affects performance (my best initial guess was refreshing caches).

Should I however close the module (prior to re-applying the filter) – i.e. close the table, re-open it and then apply the filter – it’s as fast as the first attempt (no problems).

Some basic investigation yielded the point of interest being

TDBISAMFilter.BuildNonIndexedBitmap

and

FCursor.NavigateNonIndexedRecords

I have not delved any deeper into this and was hoping you might be able to enlighten me or perhaps direct me towards any optimizations / steps we need to perform or alternatively how to manipulate the table and / or cursor to prevent the problem.

Danie

<<

Danie van Eeden wrote:

...

The filter execution takes up to 25 seconds (i.e. much slower – here lies our problem / question)

...

The TABLE CHANGE / UPDATE seems to trigger some action that affects performance (my best initial guess was refreshing caches).

Should I however close the module (prior to re-applying the filter) – i.e. close the table, re-open it and then apply the filter – it’s as fast as the first attempt (no problems).

...

>>


Danie,

While i cannot comment directly couple of general notes to consider

1. Type of change detection being used - do you have StrictChangeDetection=true ?   Sounds like it's true which will trigger a table reload as you set filters or even navigate beyond current read size.

2. What is the LocalReadSize for the table set to (you indicated it's non C/S access hence the LocalReadSize)  - this determines how many records dbisam reads for each batch?

3. Did you scroll the grid before settings the new filter and was it still on 1st record - trying to figure out if grid might be doing multiple fetches since its on record further down - vs close/reopen which will always reset to 1st record

4. Can you detach the grid from dataset and see if dbisam table has same timings by itself

Raul

Hi Raul,

thank you so much for the quick reply.

Some answers:

1. "Type of change detection being used": StrictChangeDetection is currently DISABLED.
2. "What is the LocalReadSize": This is currently set to 1 (one). I believe this is the DEFAULT.
3. "Did you scroll the grid before settings the new filter and was it still on 1st record:" - I did not scroll intentionally.
I will retest to confirm.
4. "Can you detach the grid from dataset" - retesting to confirm. Feedback will follow.

Danie

<<
1. "Type of change detection being used": StrictChangeDetection is currently DISABLED.
2. "What is the LocalReadSize": This is currently set to 1 (one). I believe this is the DEFAULT.
>>

Thanks Danie

Since these settings are same whether you open or set filter i'm still not sure why there would be a difference in timings.

#1 this being false means lazy change detection is used which would be the fastest option since dbisam would basically use locally cached data (even if it has changed in table). Since LocalReadSize is 1 it might cause cached data to be dumped as record one moves thru records.

For #2 i would experiment with a slightly higher number - since result is loaded into a grid i assume more than 1 row is visible so there will be multiple remote read requests as grid populates row 1,2,3... . - and each one will be a a new read request since we only get 1 record at a time (though engine level caching might cover for some of that).

Raul

As luck would have it may replication steps seem to have become obsolete. I cannot seem to replicate it using the same assumptions / steps anymore. However, with multiple users accessing the same table, it does still occur (the triggers are just somewhat more vague). So far, multiple users, running the same "like" filter (e.g. upper(descript) like '%ABC%'), somewhere in the process the table is also updated within a database transaction (start & commit), and then the filters start to slow to a halt (2 seconds, slow to 20 seconds). These speeds remain until the software process is restarted (program killed and restarted).

I have (I believe) eliminated our own software to some extent, because once we are in this "slow" state, DBSYS does the same (slow filters). Once you restart DBSYS, the filters are fast again.

Really odd..

Some extra information (given this is over a map drive / to a file server and using a local session): The map/network drive traffic (MBps) in windows performance monitor seems to pick up to roughly 10-15 MB/s when the filter is applied. Once the filter is completed (after say 10 seconds), the network traffic remains at this level for roughly another 30 seconds (maybe more, maybe less).. It almost as if there is still some data read taking place (possibly caching? - either by dbisam or perhaps windows?).

Not sure, but would appreciate any assistance in either optimizing our dbisam configuration OR the windows configuration. Currently looking at the Microsoft File Server (map drive) configuration options (like throttling etc).

Appreciate any advice here

More testing yields:

Single user - everything is fine doing "reads" and filters.
2 Users - all ok if just doing "Reads".
The moment the table is amended through a post/data change, the speed / performance starts dropping.

This sounds suspiciously like opportunistic locking.

Research and tests show that SMB v1 is disabled on all machines (and im guessing v2 or 3 is running). From my reading it seems Oplocks cannot be disabled for these versions?

Enabling v1 (and disabling v2 and v3) seems to slow things down by default.

I believe this is related to opportunistic locking and the SMB versions (or some combination).. Still trying to figure it out. (or some way to disable oplocks on v2 and v3)

Found something allows me to turn off OpLocks for SMB v3. It's called LeasingMode and has the following property values for a particular fileshare:

Full = default SMB3
Shared = grant read-caching lease but not write or handle-caching
None = no oplocks or leases, like modified SMB1

Usage: set-smbshare -name "MyShare" -LeasingMode Full

It, however seems to result in the same effect that I get when turning SMBv3 and v3 off (and turning on V1). When setting LeasingMode to NONE, Everything immediately (even for a SINGLE user) slows down (slow performance).

So. no solution just yet.

PS: running Windows 10 machines (server and clients)

mmmmmm.. just came across this...
don't know how I missed it.. going to give it a try

https://www.elevatesoft.com/bulletins?action=view&category=dbisam&bulletin=shared_access_to_dbisam_databases_under_windows_10_2016

Ran setting changes on both server and clients (x2) and restarted.
Issue still persisting.

1 user = OK
2 User - READING = OK
2 User - READ AND WRITE = poor performance after write