topic-17128

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

DBF/CDX or SQL
Posted: Fri Oct 23, 2009 02:15 PM

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

Posts: 1467

Joined: Mon Oct 10, 2005 11:26 AM

Re: DBF/CDX or SQL
Posted: Fri Oct 23, 2009 02:21 PM

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.

Regards,

Michel D.
Genk (Belgium)

_____________________________________________________________________________________________

I use : FiveWin for (x)Harbour v. 25.12 - Harbour 3.2.0 (May 2025) - xHarbour Builder (January 2020) - Bcc773

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: DBF/CDX or SQL
Posted: Fri Oct 23, 2009 02:39 PM

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

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: DBF/CDX or SQL
Posted: Fri Oct 23, 2009 04:37 PM

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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: DBF/CDX or SQL
Posted: Fri Oct 23, 2009 08:37 PM

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

Posts: 811

Joined: Tue May 06, 2008 04:28 AM

Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 12:27 AM

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. :lol:

Regards,

:idea:

Kind Regards,

Frances

Fivewin for xHarbour v18.07

xHarbour v1.2.3.x

BCC 7.3 + PellesC8 ( Resource Compiler only)

ADS 10.1 / MariaDB

Crystal Reports 8.5/9.23 DE

xMate v1.15

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 12:36 AM

Marc,

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

Can we see your test program?

James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 07:24 AM

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 (fw): Select all Collapse

#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 (fw): Select all Collapse

#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

Posts: 946

Joined: Thu Oct 06, 2005 07:05 PM

Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 08:06 AM

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

http://www.cbati.com

Uestudio
Fwh 13.05 Harbour 3.2 MSVC 2013

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 08:18 AM

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

Posts: 9020

Joined: Thu Oct 06, 2005 08:17 PM

Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 11:55 AM

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

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

Otto

Posts: 6983

Joined: Fri Oct 07, 2005 07:07 PM

Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 02:22 PM

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

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 02:53 PM

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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 03:47 PM

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

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 04:03 PM

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

FiveTech Support Forums