DBF/CDX or SQL

[Marc Vanzegbroeck]

Hi,

I have a planning program written in clipper5.2 and FW195.
Now I have moved this to xHarbour and FWH.
My customers are complaining that the new program is much slower than the old one.

I tested it on my network. I installed it on my server. Then I open the program on one PC and the program is very fast.
Than I open it olso on another PC and the program is much slower...
If I close the program agean on the second PC, the one on the first stays slow. If I restart the program on the first PC again, the program is faster again.
Does someone have the same problem?
Should it be faster with SQL instead of DBF/CDX?

Thanks,
Marc

[driessen]

Hello,

To my opinion the reason for some PC's to be slow is not caused by DBF/CDX.

I have a very big application, written in FWH 9.09 and xHarbour. My biggest installation is on a network with more than 40 users, all using my application together. All the data are in DBF-files. All DBF-files together contain more than 2 GB of data.

Everything is running just fine.

Over the years I have experienced often that the Antivirus software used on some PC's is the cause of a system to be slow. Maybe you should search in that direction.

Good luck.

[Marc Vanzegbroeck]

Michel,

Thanks for your answer.
The strangest thing is that on the same network/server/PC it runs very fast until somone else olso open the program from another PC.

My 16bit program is now olso running on the same network/server/PC and keeps running fast.

Regards,
Marc

[James Bott]

Marc,

I remember Otto had a similar experience. Try searching the forum for "slow network" or something similar. If I remember correctly it was when the server was running XP.

Anti-virus programs can slow database apps down. They normally scan each file when it is saved. For DBFs this means the entire DBF is scanned for each record update. You should set the AV software to exclude DBF's, memo files, and indexes.

James

[Marc Vanzegbroeck]

Hi,

I just found that the CDX-driver cause the slow-acces via network if more than 1 person open the file.

For my test I open my program and call a detail of a planning of 1 day (search for 2400 records in a database)
this take 0.4 seconds

Then I create a small test program that open my database without CDX.
Result : program runs still the same speed


Then I create a small test program that open my database with CDX.

Result : calling a detail take more 8 seconds!!!!

The test program just open the database and wait for close...

Is there a better index than CDX that I can use?

Regards,
Marc

[fraxzi]

Hello,

It must be caused by local process done in user's PC not from server. Look for client/server solution for this is the path.
With my experience with DBF/CDX, It is better to use SQL but not all the time and still navigate my DBF's.

I found a solution with Advantage Database Server. Undeniably the best solution for [x]Harbour + FWH.. Or its just me.


Regards,

[James Bott]

Marc,

>Then I create a small test program that open my database with CDX.

Can we see your test program?

James

[Marc Vanzegbroeck]

Hi,

Here is an example.
If run test1, it take less then 0.2 seconds.

If an other PC runs test and you run test1 again, it take more then 10 seconds.

TEST1.EXE

Code: Select all

#INCLUDE "FiveWin.ch"
REQUEST DBFCDX
FUNCTION test1()
   local oWnd, i
   local t := seconds()
   local vstruct:={}
   RDDSETDEFAULT("DBFCDX")

   IF !file('planning.dbf')
      aadd(vstruct,{'NR','N',10,0})
      dbcreate('planning',vstruct)
      use planning new shared
      FOR i=1 to 1000000
          appe blank
          repl nr with i
      NEXT
      index on str(nr) tag test
   ELSE
      use planning new shared
   ENDIF
   ordSetFocus( 'TEST' )
   FOR i = 1 to 1000
      seek str(92*i,10)
   NEXT i
   msginfo(seconds()-t)

RETURN nil
 

TEST.EXE

Code: Select all

#INCLUDE "FiveWin.ch"
REQUEST DBFCDX
FUNCTION test()
   local  oWnd
   RDDSETDEFAULT("DBFCDX")
   use planning new shared

   DEFINE WINDOW oWnd FROM 1, 1 TO 22, 75 TITLE "Test"

   ACTIVATE WINDOW oWnd MAXIMIZED
RETURN nil
 

Regards,
Marc

[Richard Chidiak]

Marc,

It is a fact that fwh (xharbour) applications in network environment work slow, especially in peer to peer environment .

This is something we noticed long time ago, and it gets worst when Vista is used. Our experience is based on many many network environments using our different software .

Tuning Opslock help a bit but still slow when application is ran from a terminal, this can be acheived using OS_NETREGOK( .T. )

Anti virus has to be tuned, it can be a nightmare also

One thing that may help is to tune the DBFLOCKSCHEME , this has improved performance in our case

SET DBFLOCKSCHEME TO 2 is what we use, you can find on xharbour's doc all the different parameters for dbflockscheme

PS : The only solution we found so far to make our application run in a decent way on a network is to install a TSE (Microsoft licensing of course) , in this case response time is excellent from the terminal.

HTH

Richard

[Marc Vanzegbroeck]

Richard,

Thanks for you info.
But as you can see in the test-program, the programs slows-down even without locking records, so I think DBFLOCKSCHEME will not make it faster.
Its strange that it runs very fast on the network if only 1 person use the program. So I think it's not because a virus-scanner or other software on the PC. And it stays slow oven when the other close the program. It runs fast again after restarting the program.

Regards,
Marc

[Enrico Maria Giordano]

I never noticed that behavior. I think it could be something wrong in the network (a faulty network card?).

EMG

[Otto]

Hello Marc,
I did some tests with your exe files. But I can’t reproduce the time lag you talk about.
This is what I do:


Best regards,
Otto

[James Bott]

Marc,

I think you have some problem specific to the PCs or network you are using for the test.

I ran your test programs here on two XP Pc's as peer-to-peer. Test1.exe running on the PC acting as the server took 0.03 seconds. Then when running Test.exe on the other PC and running Test1.exe on the server again it took 0.06 seconds. Granted it is slower--twice as slow, however it is only 3/100s of a second slower not the 10 seconds that you are seeing.

Neither of the PCs has had the OPLOCKs adjusted. Both PC's have AV software configured to not scan DBFs or CDXs.

Regards,
James

[Marc Vanzegbroeck]

James, Otto,

Can you test it with my exe-file?
Maybe it has something to with my version of xHarbour/FWH/BCC
I use FWH7.10, Xharbour 0.99.71; BCC5.5

http://www.vms.be/FWTest/test.exe
http://www.vms.be/FWTest/test1.exe

Thanks,
Marc

[Marc Vanzegbroeck]

Enrico,

I don't think there is something wrong with the network-card.
Why is it running fast if only 1 peron is using it. Then it's olso running via the network and server.

James,

It's the same for antivirus-program becauwe it's running fast on the network/server when only one person is using it..

Regards,
Marc