Multi User Database Issue

Xbase++ 2.0 Build 554 or later
Message
Author
pweisbrot
Posts: 4
Joined: Thu Aug 30, 2018 11:21 am

Multi User Database Issue

#1 Post by 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

User avatar
Auge_Ohr
Posts: 1405
Joined: Wed Feb 24, 2010 3:44 pm

Re: Multi User Database Issue

#2 Post by 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
greetings by OHR
Jimmy

Victorio
Posts: 620
Joined: Sun Jan 18, 2015 11:43 am
Location: Slovakia

Re: Multi User Database Issue

#3 Post by 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
Posts: 10
Joined: Thu May 10, 2018 9:47 am

Re: Multi User Database Issue

#4 Post by dbroder@nycourts.gov »

Auge_Ohr wrote: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

User avatar
Auge_Ohr
Posts: 1405
Joined: Wed Feb 24, 2010 3:44 pm

Re: Multi User Database Issue

#5 Post by 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 :?:
greetings by OHR
Jimmy

User avatar
Tom
Posts: 1165
Joined: Thu Jan 28, 2010 12:59 am
Location: Berlin, Germany

Re: Multi User Database Issue

#6 Post by Tom »

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

"Did I offend you?"
"No."
"Okay, give me a second chance."

dbroder@nycourts.gov
Posts: 10
Joined: Thu May 10, 2018 9:47 am

Re: Multi User Database Issue

#7 Post by dbroder@nycourts.gov »

Auge_Ohr wrote: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
Posts: 1183
Joined: Thu Jan 28, 2010 1:22 am
Location: Sijsele, Belgium
Contact:

Re: Multi User Database Issue

#8 Post by skiman »

Hi,

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

Chris.
www.aboservice.be

dbroder@nycourts.gov
Posts: 10
Joined: Thu May 10, 2018 9:47 am

Re: Multi User Database Issue

#9 Post by dbroder@nycourts.gov »

skiman wrote:Hi,

Are you using indexes? It looks as you don't? You really need indexes!
Thanks for your response. We are using indexes.

User avatar
Auge_Ohr
Posts: 1405
Joined: Wed Feb 24, 2010 3:44 pm

Re: Multi User Database Issue

#10 Post by Auge_Ohr »

dbroder@nycourts.gov wrote:
skiman wrote: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 :?:
greetings by OHR
Jimmy

Post Reply