Creating Index (problem ??)

[re_turner_jr]

I am using vb6 to create an index for a dbaseIII table

The table name is 'MVDETAIL.DBF'
Size is 8.34 MB

It is a non-unique index and has been running for about 2.5 hours now.
This seems a little long to me, is it that I'm just impatient or should I kill the execution and check the code?

The SQL statment in code is

SQLst = "CREATE INDEX xdetic ON mvdetail (det_icode)
CN.execute sqlst

[Hack]

2.5 hours does sound a little excessive to me.

[si_the_geek]

It depends how the index is created.. if it is effectively a Clustered index (items re-arranged on disk), then it could take a while to move things around.

Saying that, unless the sorting is particularly badly designed (say a Bubble Sort, directly on disk rather than in memory) it shouldn't take as long as that for an 8 MB file.


I would recommend checking if any other processes are working with the same file, as you may actually be waiting for a lock to clear.

[re_turner_jr]

There are no other programs or processes accessing the file

while the application is running (in debug) I can open the folder containing the files (both .dbf and .ndx) the new index is there and almost immediately shows 2.54 MB of data (no I can't open the file as it is being used by the VB app) it will show the same thing after 1 hr, or 2, or 3...

If I can change the sort method, How??
Any help would be appreciated

I stopped the application; Below is actual code.

Private Sub ChkNDX()
    Dim Tst As Boolean
    Call OpenConnection
    
    Tst = Mod007.CheckFileExist("c:\mv\xmvdetic.dbf")
    If Tst = False Then
        SQLst = "CREATE INDEX xmvdetic " & _
                "ON mvdetail (det_icode)"
        CN.Execute SQLst
    End If

[si_the_geek]

I don't think you could change the method used to sort/create the index file, as that will be part of the internal processes (there may be extra switches for Create Index, but I have no idea what they might be).

Your code means almost nothing to us, as you have not shown the two functions you used (which may well be causing problems), or told us where it is 'stuck' (I presume on line 9, but can't be sure).

[re_turner_jr]

your assumption is correct
line nine is where it is hanging, but no error.

OpenConnection sub is tried and true. It does just that, open my db connection. If you want to see it I'll post it, but I know it works as I have used it in other apps.

ChkFileExist returns True if there is a file by that name and false if it doesn't exist. Again tried, true, and used in other apps.

I do think that the app is working properly, just doing it slowly. If there is a way in which to speed it up, that would be great. However, I'm more than likely going to leave the app running all night and check it in the morning. Hopefully, it will be finished and I'll never have to re-create the whole index just do small updates.

Anyway, I'll post back the results in the morning.

[re_turner_jr]

It ran all night (5:00pm - 7:20am), a little over 14 hours and did not get past line 9.

I have the sub,function, and original code I posted.

'--------------------------------------------------------------------
'Opens the connection to tables
Private Sub OpenConnection()
    Dim Temp As String
    If CN.State <> 0 Then       '-----------------------------------
        CN.Close                    'makes sure connection is closed
    End If                      '-----------------------------------
    
    Temp = "Driver={Microsoft dBASE Driver (*.dbf)};" & _
            "DriverID=21;Fil=(dBase III);defaultdir=" & _
            StorePath & ";readonly=false"
    CN.Open Temp                                            'Opens Connection
End Sub
 
'-------------------------------------------------------------------------
Public Function CheckFileExist(Path As String) As Boolean
    If Dir$(Path) <> vbNullString Then
        CheckFileExist = True
    Else
        CheckFileExist = False
    End If
End Function
 
'--------------------------------------------------------------------
Private Sub ChkNDX()
    Dim Tst As Boolean
    Call OpenConnection
         
    Tst = Mod007.CheckFileExist("c:\mv\xmvdetic.dbf")
     If Tst = False Then
         SQLst = "CREATE INDEX xmvdetic " & _
                     "ON mvdetail (det_icode)"
         CN.Execute SQLst                       '<===== Line getting 'stuck' on
     End If 
end sub

cn.execute sqlst - Is the line getting stuck on