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:
http://forums.fivetechsupport.com/viewt ... mb#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

#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