Index file creation

[stecosta66]

Hi All,
I'm having trouble building an index file (.CDX) from a DBF in XSharp. It got created but with the wrong file size (with VO it is about 19Mb, in XSharp 46Mb) and I can see only one (1) order.
If I run this from VO the .CDX file get created regularly with all the orders ok.
What I'm missing?

If anyone can give me any insight it will be helpful.
Thanks.

Stefano

I've attached the project and the .DBF file

[Jamal]

Hi Stefano,

Definitely, something is wrong with the order creation in X#. I don't know if dev team is aware of this and if it is fixed internally in the next build.

Here is what I tried: I reduced the size of the DBF to only 488 records and did a PACK, ran your code and the orders showed up in cmVOdbx32 (DBF Utility).

My observations:

1. Checked your order expressions and nothing seemed out of norm.
2. With your DBF file has thousands of records, the orders were not created correctly, except the first one.
3. When the DBF size is reduced to a few hundreds records, the orders were created.
4. The size of the CDX was 76KB. When I did a reindex in cmVOdbx32, the size dropped to only 18KB.
5. I don't know if it something in your DBF records that is triggering the issue or the settings in your start method.
6. If this worked in VO, then it must work in X#.

Jamal

[stecosta66]

Hi Jamal,

thanks for your reply. This is a copy of a production DBF, this is why has so many records and I've noted the same behaviour as you.
With small DBF and a limited number of records the orders are created, but with big files and many records something weird is happening.
Index file size is always bigger when created in X# then from VO app, even on small .dbf.
I use dBALite to check files.

I hope that someone from the dev team chime in to clarify whats is going wrong here

Thanks again
Stefano

[robert]

Stefano
I will have a look at this.

Robert

[stecosta66]

Thanks Robert,

I've modfied my code so the dbf is created on runtime:

Code: Select all

METHOD BuildIndex( ) 
	// Description: 
	// Parameters : 
	// Returns    :
	LOCAL auStruct		AS ARRAY
	LOCAL aDir 			AS ARRAY
	 
	LOCAL cAppPath		AS STRING
	LOCAL cOrdExt 		AS STRING
	LOCAL cWorkDir		AS STRING
	LOCAL cCurDrive		AS STRING
	LOCAL cDBFile		AS STRING
	LOCAL cIDXFile		AS STRING
    LOCAL cVal1			AS STRING
    
    LOCAL iVal2			AS INT
	
	LOCAL dToday		AS DATE 
		
	LOCAL x 			AS DWORD
    
    LOCAL cStart        AS STRING
    LOCAL cEnd          AS STRING
    LOCAL cElapsed      AS STRING

	LOCAL lSuccess		AS LOGIC
	LOCAL lVal3 		AS LOGIC
	
	LOCAL odbServer		AS DBServer

    cStart := Time( )
    
	RddSetDefault( "DBFCDX" )               // Comix 3 ( CDX )
	SetDeleted( .T. )
	SetAnsi( TRUE )

  	RDDINFO( _SET_MEMOBLOCKSIZE, 1 )		
  	RDDINFO( _SET_MEMOEXT, ".DBV" ) 		
	RDDINFO( _SET_OPTIMIZE, TRUE )   		
	RDDINFO( _SET_STRICTREAD, TRUE )		

	SetExclusive( FALSE )            		
	SetSoftSeek( TRUE )              		
	SetCollation( #CLIPPER )         		
	SetInternational( #CLIPPER )     		
	SetPath( cAppPath )              		
	SetDefault( cAppPath )					

	SetNatDLL( "ITALIAN.DLL" )       		
	SetCentury( TRUE )               		
	SetEpoch( 1990 )                 		
	SetDateCountry( ITALIAN )				
	SetDateFormat( "dd-mm-yyyy" )    		
	SetDecimalSep( Asc( "," ) )      		
	SetThousandSep( Asc( "." ) )     		
	SetDecimal( 2 )							
	SetFloatDelta( 0.001 )
 
 	dToday		:= Today( )
	cOrdExt    	:= ORDBAGEXT( )   							// index extension ( .CDX )
	cCurDrive	:= CurDrive( )
	cWorkDir	:= WorkDir( )
	cAppPath	:= cWorkDir
	cDBFile		:= cAppPath + "TestDBF"  

	SELF:oDCFixedText2:TextValue := "Running..."
	
	SELF:oCCPBCancel:Disable( )
	SELF:oCCPBOk:Disable( )

	// delete existing .dbf and .cdx files
	aDir := Directory( cAppPath + "TestDBF.*" )
	AEval( aDir, { |aFile| FErase( cAppPath + aFile[ F_NAME ] ) } )
	
	// create DBF file
	auStruct := ArrayCreate( 4 )
	auStruct[ 1 ]	:= { "Field1",	"C",	20, 0 }
	auStruct[ 2 ]	:= { "Field2",	"N",	9,  0 }
	auStruct[ 3 ]	:= { "Field3",	"D",	8,	0 }
	auStruct[ 4 ]	:= { "Field4",	"L",	1,	0 }

	lSuccess := DBCREATE( cDBFile, auStruct, "DBFCDX" )
	
	IF lSuccess
		// dbf creation successful, populate with random records
	 	odbServer := DbServer{ cDBFile, .F., .F. }

		FOR x := 1 UPTO 500000
			cVal1 := "Test" + StrZero( x, 6 )
			iVal2 := x
			lVal3 := !lVal3
			
			odbServer:Append( .T. )
			odbServer:FIELDPUT( #Field1, cVal1 )
			odbServer:FIELDPUT( #Field2, iVal2 )
			odbServer:FIELDPUT( #Field3, dToday )
			odbServer:FIELDPUT( #Field4, lVal3 )
			
		NEXT
		
		odbServer:Commit( )
		odbServer:Unlock( )
			
		cIDXFile	:= cDBFile + cOrdExt
	 	IF !File( cIDXFile )
			lSuccess := odbServer:SetOrderCondition( "Deleted() == .F.   .And. Field4 == .F.", { || DELETED() == .F.   .AND. odbServer:FIELDGET( #Field4 ) == .F. },,,,,,,,,,,,, )
			IF lSuccess
				lSuccess := odbServer:CreateOrder( "Order1", cIDXFile, "Field1 + '~' + DToS( Field3 )",, )
				IF lSuccess
					odbServer:Commit( )
				ENDIF
			ENDIF
			

			lSuccess := odbServer:SetOrderCondition( "Deleted() == .F.   .And. Field4 == .T.", { || DELETED() == .F.   .AND. odbServer:FIELDGET( #Field4 ) == .T. },,,,,,,,,,,,, )
			IF lSuccess
				lSuccess := odbServer:CreateOrder( "Order2", cIDXFile, "Field1 + '~' + DToS( Field3 )",, )
				IF lSuccess
					odbServer:Commit( )
				ENDIF
			ENDIF

			lSuccess := odbServer:SetOrderCondition( "Deleted() == .F.", { || DELETED() == .F. },,,,,,,,,,,,, )
			IF lSuccess
				lSuccess := odbServer:CreateOrder( "Order3", cIDXFile, "Field2",, )
				IF lSuccess
					odbServer:Commit( )
				ENDIF
			ENDIF

			lSuccess := odbServer:SetOrderCondition( "Deleted() == .F.  .And. Field4 == .F. ", { || DELETED() == .F.  .AND. odbServer:FIELDGET( #Field4 ) == .F. },,,,,,,,,,,,, )
			IF lSuccess
				lSuccess := odbServer:CreateOrder( "Order4", cIDXFile, "Str( Field2, 7 ) + '~' + Field1 + '~' + DToS( Field3 )",, )
				IF lSuccess
					odbServer:Commit( )
				ENDIF
			ENDIF

			lSuccess := odbServer:SetOrderCondition( "Deleted() == .F.  .And. Field4 == .T. ", { || DELETED() == .F.  .AND. odbServer:FIELDGET( #Field4 ) == .T. },,,,,,,,,,,,, )
			IF lSuccess
				lSuccess := odbServer:CreateOrder( "Order5", cIDXFile, "Str( Field2, 7 ) + '~' + Field1 + '~' + DToS( Field3 )",, )
				IF lSuccess
					odbServer:Commit( )
				ENDIF
			ENDIF

			odbServer:Close( )	
		ENDIF
	 ENDIF

    cEnd := Time( )
    cElapsed := ElapTime( cStart, cEnd )
    
	SELF:oDCFixedText2:TextValue := "Finished!"
	SELF:oCCPBCancel:Enable( )
	SELF:oCCPBOk:Enable( )

	TextBox{ SELF, "TestDbf", "Elapsed: " + cElapsed + CRLF + "Build Index done!" }:Show( )
	
	_Quit( )
			
RETURN NIL

on further investigation I've found the following:
1- in VO the time to create the dbf and the index file is much much shorter then in X#
2- in VO the index file size is way smaller then the one created in X#
3- in X#, with big numbers of records, the CDX file orders aren't created

below some screenshot attached

thanks
Stefano

[robert]

Stefan
Thanks for the example. However, there was no need to post it. I got the message from yesterday and did see the problem.
I am already investigating the problem.

Some remarks before hand:
1) When creating the CDX driver we did not optimize for speed (yet). Or first priority was to create correct indexes. Once we are sure that the files are correct then we'll revisit the code and add some optimizations.

2) When creating a new CDX file we have to make decisions about what you could call "the fill factor". In other words, we can fill up all index pages completely to create a compact file. This is what the VO CDX driver does. The advantage is a smaller file. The disadvantage is that when a new key has to be inserted in a page that is already full, then that insertion will trigger a page split, and quite often also a page page split in one or more levels of parent pages. We decided to not completely fill up the pages. That makes the CDX bigger, but an insert of a key will be much easier (and faster).
In fact, some indexes (such as date indexes or indexes on a customer or invoice number) usually grow only in one direction. For these indexes full pages should not be a problem. Other indexes, such as on names, get key inserts all over the index. In that case a full page may be an issue.

3) Having said this, the file creation should still work. And apparently there is a problem with big numbers of records. We'll look into that.


Robert

[robert]

Stefano,
I found and fixed a problem. The index is now about the same size as in VO and the index creation is faster. Not as fast as in VO but definitely faster than before.
We plan to release a new build later this week. This fix will be included.

Robert

[stecosta66]

Thanks Robert

Over the years I've developed a very big accounting software and in the transition from VO to X# I have to deal with DBF until I move all DB to SQL.
I will test the fix on DBFs with even bigger numbers

Thanks again
Stefano