Page 1 of 3
DBF Append Speed in X# vs VO - Huge Difference
Posted: Tue Sep 15, 2020 5:11 am
by Jamal
Hi Dev Team!
I created a small app that collects data from a webservice and adds 100 records every 3 minutes. The file reached millions of records and the .NET app started to slow down (I thought it was freezing since there was no indication what is going behind the scene), then after extensive CPU profiling and debugging, I isolated the issue to a single function in the DBFCDX RDD.
The X# DBF DBAppend function (or dbSever Append method - also tested) is very, very slow compared the blazing speed in VO. Also, when I used the Advantage AXDBFCDX, it was very fast!
To test I created a dummy dbf with one field ( Char(20) ) and added 1 million records.
On my system, X# took
63.27 minutes while VO only took
3.86 seconds. Simpy call the DBAppend function in a loop.
Using X# public build: 2.5b
VO 2.8 - 2838
Windows 10 Pro, SSD drive.
Here is the code:
Code: Select all
FUNCTION Start() AS VOID
LOCAL cDBF AS STRING
LOCAL aFields AS ARRAY
LOCAL i AS DWORD
LOCAL nStart, nElapsed AS FLOAT
RddSetDefault ( "DBFCDX" )
cDbf := "C:testmytest" // change the dbf to "C:testmytestVO" to test in VO 2.8.
FErase ( cDbf + ".cdx" )
aFields := { { "LAST" , "C" , 20 , 0 } }
DbCreate( cDBF , AFields)
DbUseArea( ,,cDBF )
nStart := Seconds()
? "start : " + ntrim(nStart)
FOR i := 1 UPTO 1000000
DbAppend()
NEXT
nElapsed := Seconds() - nStart
? "Elapsed: " + NTrim(nElapsed) + " seconds"
DbCloseArea()
wait
RETURN
DBF Append Speed in X# vs VO - Huge Difference
Posted: Tue Sep 15, 2020 6:33 am
by robert
Jamal,
We are aware of this and we are working on it:
There is a glitch in the page splitting algorithm .
The resulting index is correct but contains many pages with only one key per page.
This seems to be related to the large number of duplicate key values (in your case 1000000 blank keys).
Robert
DBF Append Speed in X# vs VO - Huge Difference
Posted: Tue Sep 15, 2020 6:57 am
by Chris
Robert,
I think this is a different issue, because there is no index file involved. Something seems to make appending records much slower in the .Net version.
DBF Append Speed in X# vs VO - Huge Difference
Posted: Tue Sep 15, 2020 5:16 pm
by Jamal
Robert,
In the sample I provided I am just appending simple records;
they are not duplicates. If I create an index and use FIELDPUT( ) in the LAST field, the process in X# is extremely slow.
Please see the modified sample code below.It took a whopping 57 minutes in X#, while VO only took 3.49 seconds!
So there are two issues, Append and the page splitting algorithm you mentioned:
Code: Select all
FUNCTION Start() AS VOID
LOCAL cDBF AS STRING
LOCAL aFields AS ARRAY
LOCAL i AS DWORD
LOCAL nStart, nElapsed AS FLOAT
RddSetDefault ( "DBFCDX" )
cDbf := "C:testmytest"
FErase ( cDbf + ".cdx" )
aFields := { { "LAST" , "C" , 20 , 0 } }
DbCreate( cDBF , AFields)
DbUseArea( ,,cDBF )
DBCreateOrder("last", "c:testmytest", "last")
nStart := Seconds()
? "start : " + ntrim(nStart)
FOR i := 1 UPTO 1000000
DbAppend()
fieldput(1, asstring(i))
NEXT
nElapsed := Seconds() - nStart
? "Elapsed: " + NTrim(nElapsed) + " seconds"
DbCloseArea()
wait
RETURN
DBF Append Speed in X# vs VO - Huge Difference
Posted: Wed Sep 16, 2020 8:10 am
by robert
Jamal,
We'll look at this asap. Not for the 2.6 build that is coming in a few days, but in the build after that.
I know that VO used very aggressive caching for files opened exclusively, like in your example.
We'll try to build something similar in X#.
Robert
DBF Append Speed in X# vs VO - Huge Difference
Posted: Wed Sep 16, 2020 11:20 pm
by Jamal
Robert,
Will test again when you issue the fix and I'll report back.
Thanks!
DBF Append Speed in X# vs VO - Huge Difference
Posted: Thu Sep 17, 2020 11:35 am
by ic2
This seems to be related to the large number of duplicate key values (in your case 1000000 blank keys).
We were actually the X# users having that issue that many duplicate values corrupted the CDX after mostly 1 or 2 days of use in a CGI webprogram we fully converted to X#. So I am surprised that this issue still exists as I thought it was solved.
However, as we had another CDX corruption issue and as much as we would have preferfed using the native X# ADS/DBF drivers, we couldn't let the client keep functioning as guinea pig
we eventually reverted the DBFCDX system back to the Vulcan method. Since then the X# webprogram works fine.
So I was wondering Jamal: do you have the same speed issue if you use the Vulcan DLL/RDD's (only for the DBFCDX functionality)?
Dick
DBF Append Speed in X# vs VO - Huge Difference
Posted: Thu Sep 17, 2020 4:17 pm
by Jamal
Dick,
I don't have Vulcan anymore and so I did not test it.
Jamal
DBF Append Speed in X# vs VO - Huge Difference
Posted: Tue Sep 22, 2020 8:07 am
by Kees Bouw
I think all the changes and improvements in 2.6 are less important than a reliable DBF/CDX system. I can't use X# because indexes get corrupted even when using only basic functionality. Yet more and more new features are added while this is just not working properly.
DBF Append Speed in X# vs VO - Huge Difference
Posted: Tue Sep 22, 2020 8:40 am
by robert
Kees,
We have already made some changes in this area, so most likely the problem is fixed.
Did you test the new version?
Robert