dbfntx very slow with 2+ users

[don lowenstein]

My applications that utilize DBFNTX run really fast with one user and really slow with 2 or more users logged in at the same time.

Has anyone experienced this?

Harbour 3.2.0
BCC 5.82
Fivewin version 13.02

Thanks in Advance.

don

[lucasdebeltran]

Yes I do.

How many indexes do you have?.

Since when are you suffering this?.

[driessen]

Lucas,

It is quite normal that ntx is becoming slower if the number of users is increasing.

For each index, a ntx-file is created. If you open a DBF-file with its ntx-files, every file takes a file handle.

In my application, I open 38 different files with at least 7 indexes per file. This means (1 x 38) + (7 x 38) = 304 file handles per user.
If you have 5 users, that means 1520 file handles on your server.

I experienced too that my application was becoming slower and slower.

I changed to CDX indexing. For 1 DBF-file, only 1 CDX-file is opened. In my excample that would mean 74 file handles per user.
If you have 5 users, that means 370 file handles on your server.

After having made the change, my application ran fine again.

Your antivirus can also be the cause of slowing down your application. Try it out with the antivirus disabled. I had this problem with some old versions of Norton. The current versions are OK.

Hope to have helped you.

[lucasdebeltran]

Hello,

Thank you very much. Yes, I switched to DBFCDX many years ago.

Now I use CDX with various Tags and only one .cdx file per dbf, but in Network, with 3 or more users it is quite slow.

We are now moving to ADO and the problem has gone away .

With ADS there was an increase on speed, but not very very much.

I did not find what was the problem with > 3 users and DBF to be such slow. With ADO and Access, for example, same network and a database of 8.000 records the speed is almost like the local PC.

Harbour leaders are not very much interested on Windows, so they don´t pay many attention on those issues, and it is hard to trace the problem.

[Otto]

Lucas,
search for SMB on the forum.
What OS do you use.

Best regards,
Otto

[lucasdebeltran]

Otto,

Windows XP and Windows 7 mostly.

I do not use mapped drives, just \\server\folder, so I taought SMB was not the cause:
viewtopic.php?f=3&t=21740&p=115743&hilit=smb#p115743

Interesting post also:
https://en.wikipedia.org/wiki/Server_Message_Block


Do you know if Harbour function OS_NetRegOK() disables SMB?.

Thank you.

[lucasdebeltran]

Hello,

xHarbour created this function, later adopted by Harbour, that disables SMB 2.0.

I tried some time ago with Little Luck.

Here it is. It may help you:

Code: Select all  Expand view  RUN

#include "directry.ch"
#include "hbwin.ch"

/* NOTE: To change any of these registry settings
         Administrator rights are required by default in Windows. [vszakats] */

FUNCTION win_osNetRegOk( lSetIt, lDoVista )

   LOCAL bRetVal := .T.
   LOCAL cKeySrv
   LOCAL cKeyWks

   hb_default( @lSetIt, .F. )
   hb_default( @lDoVista, .T. )

   IF ! lDoVista .AND. hb_osIsWinVista()
      /* do nothing */
   ELSEIF hb_osIsWin9x()
      bRetVal := win_regQuery( WIN_HKEY_LOCAL_MACHINE, "System\CurrentControlSet\Services\VxD\VREDIR", "DiscardCacheOnOpen", 1, lSetIt )
   ELSE
      cKeySrv := "System\CurrentControlSet\Services\LanmanServer\Parameters"
      cKeyWks := "System\CurrentControlSet\Services\LanmanWorkStation\Parameters"

      IF lSetIt
         lSetIt := ! hb_osIsWinNT() .OR. wapi_IsUserAnAdmin()
      ENDIF

      /* Server settings */
      bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeySrv, "CachedOpenLimit", 0, lSetIt )
      bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeySrv, "EnableOpLocks", 0, lSetIt ) /* Q124916 */
      bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeySrv, "EnableOpLockForceClose", 1, lSetIt )
      bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeySrv, "SharingViolationDelay", 0, lSetIt )
      bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeySrv, "SharingViolationRetries", 0, lSetIt )

      IF hb_osIsWinVista()
         /* If SMB2 is enabled turning off oplocks does not work, so SMB2 is required to be turned off on Server. */
         bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeySrv, "SMB2", 0, lSetIt )
      ENDIF

      /* Workstation settings */
      bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeyWks, "UseOpportunisticLocking", 0, lSetIt )
      bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeyWks, "EnableOpLocks", 0, lSetIt )
      bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeyWks, "EnableOpLockForceClose", 1, lSetIt )
      bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeyWks, "UtilizeNtCaching", 0, lSetIt )
      bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeyWks, "UseLockReadUnlock", 0, lSetIt )

      IF hb_osIsWinVista()
         bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeyWks, "FileInfoCacheLifetime", 0, lSetIt )
         bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeyWks, "FileNotFoundCacheLifetime", 0, lSetIt )
         bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, cKeyWks, "DirectoryCacheLifetime", 0, lSetIt )
      ENDIF

      IF hb_osIsWin2K()
         bRetVal := bRetVal .AND. win_regQuery( WIN_HKEY_LOCAL_MACHINE, "System\CurrentControlSet\Services\MRXSmb\Parameters", "OpLocksDisabled", 1, lSetIt )
      ENDIF
   ENDIF

   RETURN bRetVal
 

We are very happy with ADO, as it offers to use MSSQL, MySQL, Access, etc with same code.

In the mean time, we are still suffering with DBFS.

[don lowenstein]

I have a Harbour / Fivewin application that uses MSSQL tables via Mediator (3rd party RDD from OTS software).

Does ADO provide MSSQL and MySQL seamlessly using harbour RDD database "regular" commands, like USE, SKIP, SEEK, DELETE, PACK, COPY, etc. and regular function calls like dbskip, dbdelete, dbpack, dbappend, etc. ??

if yes, is the conversion path relatively seamless (within reason of course)?

[lucasdebeltran]

Don,

ADO does not work like a RDD, please take a look at samples\adoxbr01.prg.

I did not get Mediator working not a reply from OTC, so that´s why I choose SQLRDD, but it did not work as expected.

Your problem happens with DBF stand alone or with Mediator?.

[don lowenstein]

That's an odd topic.

for reading files either individually or especially en masse Mediator is faster than DBF, especially with 2+ users.
If I use sql commands like SELECT * etc, Mediator is very fast.
the coding is 99% harbour compatiable using conventional programming syntax.
i've had good luck with Mediator for about 5 years now.

But, Mediator's updating of tables is much slower than dbf updating. especially when cycling thru a large file. almost 2 times longer.
I suspect tons of logging on the server side slows it down. in these cases, I found it was better to rewrite the code from a do while loop to selecting a temporary result set and working with sql commands.

so, for processes that have lots of database updates it's much faster to run in DBF mode than Mediator with one user at a time. as more users get added my guess is the difference gap would close but I can't be certain of this as I've not tested it.

we have applications that run on mediator RDD and and others that use dbfntx RDD.
There are always multi user configurations, many times across a wan. in the wan cases, we require terminal server or citrix metaframe. this actually makes the .dbf system run better because everything is housed on a huge virtual server with lot's of horsepower. the users see better performance in these cases.

I was just wondering if it might be time to investigate other routes to MSSQL.

[Rick Lipkin]

Don

I was just wondering if it might be time to investigate other routes to MSSQL.

The short answer to your statement above is YES! .. I started using ADO ( sqloledb, or Ms Jet ) many years ago and I never looked back. With recent versions of FiveWin they have created a set of FW_Ado .. functions that simplify the connection strings and recordsets data fetches.

For those that have used the tData class .. using ADO is a very similar class... here is the FW Wiki

http://wiki.fivetechsoft.com/doku.php?i ... ted_stuffs

I know you are probably not looking forward to the re-writing of your MS Sql \Mediator database code.. however, once you do, you will find the code is very portable between Ms Sql Server ( and express ), MS Access and Oracle... just re-compile with a different connection string and ( in most cases ) your code stays the same.

As far as dbfcdx, dbfntx .. the day of the DOS file handle is sunsetting.

Rick Lipkin

[anserkk]

I am also experiencing the same issue.

There are 2 PC's ie PC1 and PC2.
The application and data reside on PC1. For eg on D Drive ie D:\MyApp
The operating system used on both the PC's are Windows 10

The problem occurs ONLY when the second user uses the Application irrespective of whether it is used from the PC1 or PC2

The D Drive of PC1 is shared and accessed from PC2

If the application is NOT open in PC1 then there is NO performance ISSUE on PC2
If the application is OPEN in PC1 then the performance is too poor on PC2 ie extremely slow.

Already tried the following:-
Disabled Antivirus, Excluded the App Folder from Antivirus Scan
Applied the command netsh interface tcp set global autotuninglevel=highlyrestricted
Disabled Remote Differential Compression API support
Tried disabling SMB2.
Instead of using the Mapped drive, I tried using the UNC path too ie \\PcName\SharedDrive\MyApp\MyDBF.DBF, unfortunately, didn't make any noticeable difference
On the main PC, tried tweaking the performance options and changed "Adjust for best performance of Background Services"

Searched the forum, did not find a solution that could solve this issue.

I am wondering what solutions are used by others ? Is there anything that I missed to try ?
It is a very old app, for the time being, unable to move to SQL from DBF

[hmpaquito]

Perhaps https://groups.google.com/g/comp.lang.x ... Pt-VaADDdw

[Jimmy]

hi,

i saw Comments about SMB ...

many of these Comment are "outdate", while for SMB1, and not valid when using Windows 10 / 11
Note : it is NOT recommend to use "older" OS than Windows 10 any more

only some older NAS will still use SMB1. change your NAS

---

SMB1 should be DISABLE and SMB2 ENABLE which is default under Windows 10 / 11 !

to check it run Rowershell and type

Code: Select all  Expand view  RUN

Get-SmbServerConfiguration

now look for

EnableSMB1Protocol : False
EnableSMB2Protocol : True

---

are you using "Drive-Letter" or UNC-Path

when using "Drive-Letter" it work with LANmanager and UDP Port 135-139
have a look at Server / Network / Ports if there is Traffic on this Ports

if YES than you still working with OLD SMB1 and all it´s Problem
do NOT use OLD SMB1 "Hack" on Windows 10 / 11

---

since SMB2 we have a "Local Cache" which can made Problem when use "Local" Action
this is e.g. with "temporary" Action like INDEX ON or FERASE(localfile)

btw.
Question : does FiveWin "lock" Index like Xbase++ do

read here what is recommend for SMB2 when work with "temporary" Local Action
https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-7/ff686200(v=ws.10)?redirectedfrom=MSDN

DISABLE under HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\LanmanWorkstation\Parameters

Code: Select all  Expand view  RUN

FileInfoCacheLifetime
FileNotFoundCacheLifetime
DirectoryCacheLifetime
 

and set all 3 to 0 ( Zero ) on Workstation ( on Server only when using RDP )

---

General "File-Based" System will slow down when SMB switch from "Single Exclusive" to "Opportunistic lock".

Programmer like to "open" as much as possible and "leave" all "open" ...
that you can do with Single User App but it is not recommend in Network

under SQL you do not have "direct" Access to Data, Wifi can "lost Connection".
in older Network it was slow to open a DBF, read/write and close it. but that have change

very slow are TBrowse & Co which use a "Skipper" to navigate.
Spezial when using FILTER without SCOPE / Index it can very slow on big Database and make much Traffic

btw. same with SQL when not using LIMIT

so do use Resource only when need and avoid "Stand-by" of open Database when not need

[anserkk]

i saw Comments about SMB ...

many of these Comment are "outdate", while for SMB1, and not valid when using Windows 10 / 11
Note : it is NOT recommended to use "older" OS than Windows 10 any more

True

are you using "Drive-Letter" or UNC-Path

I was using a drive letter, now, I changed to UNC Path. Unfortunately, I did not see any noticeable difference in the speed

Programmer like to "open" as much as possible and "leave" all "open" ...
that you can do with Single User App but it is not recommend in Network

I open DBF files ONLY when it is necessary and CLOSE after usage, never kept open the whole time

do NOT use OLD SMB1 "Hack" on Windows 10 / 11

Noted

Thank you