Page 1 of 2
DBF/CDX or SQL
Posted: Fri Oct 23, 2009 2:15 pm
by 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
Re: DBF/CDX or SQL
Posted: Fri Oct 23, 2009 2:21 pm
by 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.
Re: DBF/CDX or SQL
Posted: Fri Oct 23, 2009 2:39 pm
by 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
Re: DBF/CDX or SQL
Posted: Fri Oct 23, 2009 4:37 pm
by 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
Re: DBF/CDX or SQL
Posted: Fri Oct 23, 2009 8:37 pm
by 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
Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 12:27 am
by 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,
Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 12:36 am
by James Bott
Marc,
>Then I create a small test program that open my database with CDX.
Can we see your test program?
James
Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 7:24 am
by 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
Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 8:06 am
by 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
Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 8:18 am
by 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
Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 11:55 am
by Enrico Maria Giordano
I never noticed that behavior. I think it could be something wrong in the network (a faulty network card?).
EMG
Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 2:22 pm
by 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
Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 2:53 pm
by 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
Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 3:47 pm
by 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
Re: DBF/CDX or SQL
Posted: Sat Oct 24, 2009 4:03 pm
by 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