DBF/CDX or SQL

DBF/CDX or SQL

Postby Marc Vanzegbroeck » Fri Oct 23, 2009 2: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
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: DBF/CDX or SQL

Postby driessen » Fri Oct 23, 2009 2: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. 24.07 - Harbour 3.2.0 (February 2024) - xHarbour Builder (January 2020) - Bcc773
User avatar
driessen
 
Posts: 1422
Joined: Mon Oct 10, 2005 11:26 am
Location: Genk, Belgium

Re: DBF/CDX or SQL

Postby Marc Vanzegbroeck » Fri Oct 23, 2009 2: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
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: DBF/CDX or SQL

Postby James Bott » Fri Oct 23, 2009 4: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
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: DBF/CDX or SQL

Postby Marc Vanzegbroeck » Fri Oct 23, 2009 8: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
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: DBF/CDX or SQL

Postby fraxzi » 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
User avatar
fraxzi
 
Posts: 811
Joined: Tue May 06, 2008 4:28 am
Location: Philippines

Re: DBF/CDX or SQL

Postby James Bott » 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
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: DBF/CDX or SQL

Postby Marc Vanzegbroeck » Sat Oct 24, 2009 7: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: Select all  Expand view
#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  Expand view
#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
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: DBF/CDX or SQL

Postby Richard Chidiak » Sat Oct 24, 2009 8: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
User avatar
Richard Chidiak
 
Posts: 946
Joined: Thu Oct 06, 2005 7:05 pm
Location: France

Re: DBF/CDX or SQL

Postby Marc Vanzegbroeck » Sat Oct 24, 2009 8: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
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: DBF/CDX or SQL

Postby Enrico Maria Giordano » 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
User avatar
Enrico Maria Giordano
 
Posts: 8701
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: DBF/CDX or SQL

Postby Otto » Sat Oct 24, 2009 2: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:

Image
Best regards,
Otto
********************************************************************
mod harbour - Vamos a la conquista de la Web
modharbour.org
https://www.facebook.com/groups/modharbour.club
********************************************************************
User avatar
Otto
 
Posts: 6304
Joined: Fri Oct 07, 2005 7:07 pm

Re: DBF/CDX or SQL

Postby James Bott » Sat Oct 24, 2009 2: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
User avatar
James Bott
 
Posts: 4840
Joined: Fri Nov 18, 2005 4:52 pm
Location: San Diego, California, USA

Re: DBF/CDX or SQL

Postby Marc Vanzegbroeck » Sat Oct 24, 2009 3: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
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: DBF/CDX or SQL

Postby Marc Vanzegbroeck » Sat Oct 24, 2009 4: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
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Next

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 44 guests