Multi User Database Issue

[pweisbrot]

Good afternoon everyone,

We have some xBase++ code that we inherited that has an issue sharing a database file on a server. When more than one user is using the program at a time the database access time is very slow. We tried turning on read only access (even though we need read/write), and changing the op lock settings, but nothing seemed to make work faster. We are using xBase 1.9 and Azure hosted Windows Server 2016. Any reasons this could be happening, and any way to speed it up?

Thank you,
Preston

[Auge_Ohr]

hi,

do you use Anti-Virus Software
if YES are Data Folder exclude

---

xBase DBF was never design for Network so it need some Tricks like Offset Locking at 1000000 +1
other are share Files using SMB (Server-Message-Block) which have different Level.

when one 1 User work which share Files in Network you got "Opportunistic Locking exclusive"
when second User use same DBF it will switch to "Opportunistic Locking Share" where Performance slow down.

---

start with Windows NT we got SMB1 which work with Lanmanager and "Drive" Letter.
there was a lot of "Registry-Hacks" for SMB1

since Windows Vista SMB2 was introduce which work with UNC-Path and "Folder"
while SMB1 was still active, until Window 10, "Drive" Letter work ... but you got delay, slow down and other Problem.

also SMB2 had a new "Local Cache" which make Problem if "Local" Folder is not shared.
you will find a "Hot-fix" at Alaska called "SMB2-Patch" which contain 3 Registry Keys

http://technet.microsoft.com/en-us/libr ... 10%29.aspx

Code: Select all

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Lanmanworkstation\Parameters
DirectoryCacheLifetime
FileNotFoundCacheLifetime
FileInfoCacheLifetime

the "SMB2-Patch" will set those Registry Key to 0 (zero)

Note : the "SMB2-Patch" is for Lanmanworkstation

---

if you still have Trouble it might be Xbase++ "Cache" with xxxDBE_LIFETIME
like in Thread set xxxDBE_LIFETIME to 0 (zero)

---

1.) do NOT use SMB1 ( Windows 10 will disable it by default )
2.) do NOT use SMB1 Registry-Hacks
3.) use UNC-Path (Folder) instead of "Drive" Letter
4.) use xxxDBE_LIFETIME = 0
5.) use Alaska "SMB2-Patch" if Local Folder is not shared

[Victorio]

Preston, how database you use ?

I working with old foxpro and Visual foxpro databases, and when Lan is slow or work on remote server, classic acces with USE... is slow when more than one user open database.
I solved it with MS VFP ODBC driver and open with SQL command , openning need more time but after open browsing, appending, deleting and all work better. This is not ideal, because ODBC driver is old only version 6 but works.

[dbroder@nycourts.gov]

hi,

do you use Anti-Virus Software
if YES are Data Folder exclude

---

xBase DBF was never design for Network so it need some Tricks like Offset Locking at 1000000 +1
other are share Files using SMB (Server-Message-Block) which have different Level.

when one 1 User work which share Files in Network you got "Opportunistic Locking exclusive"
when second User use same DBF it will switch to "Opportunistic Locking Share" where Performance slow down.

---

start with Windows NT we got SMB1 which work with Lanmanager and "Drive" Letter.
there was a lot of "Registry-Hacks" for SMB1

since Windows Vista SMB2 was introduce which work with UNC-Path and "Folder"
while SMB1 was still active, until Window 10, "Drive" Letter work ... but you got delay, slow down and other Problem.

also SMB2 had a new "Local Cache" which make Problem if "Local" Folder is not shared.
you will find a "Hot-fix" at Alaska called "SMB2-Patch" which contain 3 Registry Keys

http://technet.microsoft.com/en-us/libr ... 10%29.aspx

Code: Select all

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Lanmanworkstation\Parameters
DirectoryCacheLifetime
FileNotFoundCacheLifetime
FileInfoCacheLifetime

the "SMB2-Patch" will set those Registry Key to 0 (zero)

Note : the "SMB2-Patch" is for Lanmanworkstation

---

if you still have Trouble it might be Xbase++ "Cache" with xxxDBE_LIFETIME
like in Thread set xxxDBE_LIFETIME to 0 (zero)

---

1.) do NOT use SMB1 ( Windows 10 will disable it by default )
2.) do NOT use SMB1 Registry-Hacks
3.) use UNC-Path (Folder) instead of "Drive" Letter
4.) use xxxDBE_LIFETIME = 0
5.) use Alaska "SMB2-Patch" if Local Folder is not shared

Hi Jimmy -
Thank you for your response. My name is D. I work with Preston.

We are following the guidelines you mentioned:
1.) do NOT use SMB1 ( Windows 10 will disable it by default )
2.) do NOT use SMB1 Registry-Hacks
3.) use UNC-Path (Folder) instead of "Drive" Letter
4.) use xxxDBE_LIFETIME = 0
5.) use Alaska "SMB2-Patch" if Local Folder is not shared[/quote]

In addition, we added excluded the data folder from the anti-virus.

Unfortunately there was no change in the performance.

Can you please explain more on the Offset Locking at 1000000 +1 trick? We are not sure how to implement this.

Thank you -
D

[Auge_Ohr]

hi,

sorry it was not 1000000 but 1000000000 +1
look at DBFDBE (DATA-Komponente) DBFDBE_LOCKOFFSET

did you use NTX or CDX

---

if it had work before it most are Anti-Virus Updates that make Problem.
also Windows Update are know as Problem but i haven't News about Problem with Server 2016

---

please descript WHEN your Apps slow down.

Code: Select all

Open DBF
APPEND
REPLACE
Browse

Errorsys Logfile
Windows Client Logfile

Windows Server Logfile

[Tom]

Add: Exclude the database folder from antivirus-checks, use CodeSigning to except your app from being scanned locally.

[dbroder@nycourts.gov]

hi,

sorry it was not 1000000 but 1000000000 +1
look at DBFDBE (DATA-Komponente) DBFDBE_LOCKOFFSET

did you use NTX or CDX

---

if it had work before it most are Anti-Virus Updates that make Problem.
also Windows Update are know as Problem but i haven't News about Problem with Server 2016

---

please descript WHEN your Apps slow down.

Code: Select all

Open DBF
APPEND
REPLACE
Browse

Errorsys Logfile
Windows Client Logfile

Windows Server Logfile

Hello Again -

After running procmon it became apparent that there was an issue with how many reads were being done to get the data. We tried pruning the database and that helped some. However, we found that if we sorted the database (used dbfview), the program ran without any performance issues.

Couple more questions for you:
1. Do you know if there is a way to insert a new record into the database at a specific location rather than appending at the end?
2. I tried running DBSort() within the application but lost the memo field after the sort. Do you know how to run DBSort w/o losing the memo field?

Thank you for your input.
D

[skiman]

Hi,

Are you using indexes? It looks as you don't? You really need indexes!

[dbroder@nycourts.gov]

Hi,

Are you using indexes? It looks as you don't? You really need indexes!

Thanks for your response. We are using indexes.

[Auge_Ohr]

Hi,
Are you using indexes? It looks as you don't? You really need indexes!

Thanks for your response. We are using indexes.

did you use NTX or CDX