DBF Append Speed in X# vs VO - Huge Difference

This forum is meant for questions and discussions about the X# language and tools
Jamal
Posts: 315
Joined: Mon Jul 03, 2017 7:02 pm

DBF Append Speed in X# vs VO - Huge Difference

Post 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
User avatar
robert
Posts: 4290
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

DBF Append Speed in X# vs VO - Huge Difference

Post 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
XSharp Development Team
The Netherlands
robert@xsharp.eu
User avatar
Chris
Posts: 4599
Joined: Thu Oct 08, 2015 7:48 am
Location: Greece

DBF Append Speed in X# vs VO - Huge Difference

Post 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.
Chris Pyrgas

XSharp Development Team test
chris(at)xsharp.eu
Jamal
Posts: 315
Joined: Mon Jul 03, 2017 7:02 pm

DBF Append Speed in X# vs VO - Huge Difference

Post 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
User avatar
robert
Posts: 4290
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

DBF Append Speed in X# vs VO - Huge Difference

Post 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
XSharp Development Team
The Netherlands
robert@xsharp.eu
Jamal
Posts: 315
Joined: Mon Jul 03, 2017 7:02 pm

DBF Append Speed in X# vs VO - Huge Difference

Post by Jamal »

Robert,

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

Thanks!
ic2
Posts: 1813
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

DBF Append Speed in X# vs VO - Huge Difference

Post 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 :P 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
Posts: 315
Joined: Mon Jul 03, 2017 7:02 pm

DBF Append Speed in X# vs VO - Huge Difference

Post by Jamal »

Dick,

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

Jamal
User avatar
Kees Bouw
Posts: 99
Joined: Wed Nov 06, 2019 11:35 am
Location: Netherlands

DBF Append Speed in X# vs VO - Huge Difference

Post 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.
User avatar
robert
Posts: 4290
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

DBF Append Speed in X# vs VO - Huge Difference

Post 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
XSharp Development Team
The Netherlands
robert@xsharp.eu
Post Reply