DBF Append Speed in X# vs VO - Huge Difference

[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

[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

[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.

[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

[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

[Jamal]

Robert,

Will test again when you issue the fix and I'll report back.

Thanks!

[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

[Jamal]

Dick,

I don't have Vulcan anymore and so I did not test it.

Jamal

[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.

[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