Speed up my loop!

[davesmith202]

I have some code that parses up to a million lines of data and its pretty slow. So, using the genius minds on Access Word forums, could some bright spark tell me if my code is optimum for speed or could I change it to be faster? Below is the main batch of code in my loop. Last time I ran it, it took 10 hours to parse the data!

Thanks,

Dave

        If InStr(var(i), "[Event ") > 0 Then rs!Event = var(i)

        If InStr(var(i), "[Site ") > 0 Then
            rs!Site = var(i)
            rs!SiteClean = Replace(Mid(var(i), 8), """]", "")
        End If

        If InStr(var(i), "[Date ") > 0 Then rs!DateField = var(i)
        If InStr(var(i), "[Round ") > 0 Then rs!Round = var(i)
        
        If InStr(var(i), "[White ") > 0 Then
            rs!White = var(i)
            rs!WhiteClean = Replace(Mid(var(i), 9), """]", "")
        End If

        If InStr(var(i), "[Black ") > 0 Then
            rs!Black = var(i)
            rs!BlackClean = Replace(Mid(var(i), 9), """]", "")
        End If

        If InStr(var(i), "[Result") > 0 Then
            rs!result = var(i)
            Select Case var(i)
            Case "[Result ""1-0""]"
                rs!ResultClean = "1-0"
            Case "[Result ""0-1""]"
                rs!ResultClean = "0-1"
            Case "[Result ""1/2-1/2""]"
                rs!ResultClean = "1/2-1/2"
            Case "[Result ""1/2""]"
                rs!ResultClean = "1/2-1/2"
            End Select
            
        If InStr(var(i), "[WhiteELO ") > 0 Then rs!WhiteELO = var(i)
        If InStr(var(i), "[BlackELO ") > 0 Then rs!BlackELO = var(i)
        End If

        If InStr(var(i), "[ECO") > 0 Then
            rs!ECO = var(i)
            rs!ECOclean = Mid(var(i), 7, 3)
        End If

        If InStr(var(i), "[PlyCount") > 0 Then rs!PlyCount = var(i)
        If InStr(var(i), "[EventDate") > 0 Then rs!EventDate = var(i)

 

[DCrake]

The problem with your code is that every if statement is being evaluated even if the preceeding statement was true, therefore adopt your code accordingly

        If InStr(var(i), "[Event ") > 0 Then rs!Event = var(i)

        ElseIf InStr(var(i), "[Site ") > 0 Then
            rs!Site = var(i)
            rs!SiteClean = Replace(Mid(var(i), 8), """]", "")


        ElseIf InStr(var(i), "[Date ") > 0 Then rs!DateField = var(i)
        
        ElseIf InStr(var(i), "[Round ") > 0 Then rs!Round = var(i)
        
        ElseIf InStr(var(i), "[White ") > 0 Then
            rs!White = var(i)
            rs!WhiteClean = Replace(Mid(var(i), 9), """]", "")
  

        ElseIf InStr(var(i), "[Black ") > 0 Then
            rs!Black = var(i)
            rs!BlackClean = Replace(Mid(var(i), 9), """]", "")
 

        ElseIf InStr(var(i), "[Result") > 0 Then
            rs!result = var(i)
            Select Case var(i)
            Case "[Result ""1-0""]"
                rs!ResultClean = "1-0"
            Case "[Result ""0-1""]"
                rs!ResultClean = "0-1"
            Case "[Result ""1/2-1/2""]"
                rs!ResultClean = "1/2-1/2"
            Case "[Result ""1/2""]"
                rs!ResultClean = "1/2-1/2"
            End Select
            
        ElseIf InStr(var(i), "[WhiteELO ") > 0 Then rs!WhiteELO = var(i)
        ElseIf InStr(var(i), "[BlackELO ") > 0 Then rs!BlackELO = var(i)

        ElseIf InStr(var(i), "[ECO") > 0 Then
            rs!ECO = var(i)
            rs!ECOclean = Mid(var(i), 7, 3)
   

        ElseIf InStr(var(i), "[PlyCount") > 0 Then rs!PlyCount = var(i)
        ElseIf InStr(var(i), "[EventDate") > 0 Then rs!EventDate = var(i)

        Else

        End If

This way as soon as a condition is met and the process has been completed it drops out of the If command\ and ignores all the rest as the correct answer has been derived from the var(i)

 

[davesmith202]

Actually David that is a very clever point. I just didn't see that.

Any pointers in how I might do that? I can't see a way. Some form of Select Case?

 

[davesmith202]

Ooops, I missed that you already suggested. My apologies and thank you!

 

[DCrake]

The code I have given you is correct simply replace your existing code with the revised code. And yes it is simulting a select case statement, the only difference is that with select statements you can only question one value by using the ElseIf statement you can vary the question.

 

[davesmith202]

One other aspect is that do you think I should be using Left instead of Instr?

ElseIf InStr(var(i), "[Date ") > 0 Then rs!DateField = var(i)

The field will always start "[Date " so I wondered if Instr is slower since it has to search the whole line?

 

[davesmith202]

I get a compile error "Else without if", on this line:

ElseIf InStr(var(i), "[Site ") > 0 Then

Do I need to put a carriage return after each Then statement?

Edit: I put a carriage return after teh first Then statement and it worked.

 

[Poppa Smurf]

In your lines of data does each line represent one record or there are more lines of data to make the record? Why I ask is if the field is in the same position for each record on the line of data you could use the MID statement.

Here is an example of code that I use to read text ShiftDate = Trim(Mid(sInputLine, 26, 18)). This code trims the data between position 26 to 43 inclusive to give me the date. I then do evalauation on the date. I may have another field in position 44 so I would have Staff_name=Trim(Mid(sInputLine, 26, 18)), this reduces the need for testing the variable for each field name as per your code.

 

[davesmith202]

One line of data is one field only. A record is spread over several lines.

 

[davesmith202]

Ok, result of a test. For this loop I knocked the time down from 56s to 53s, so not a lot of difference. Shame.

What about instead of using var(i), set myString=var(i) and instead use myString? Is it faster than looking up var(i) each time?

 

[davesmith202]

I am looking at alternative ways to speed up my code. I have this:

    Dim var
    var = Split(strdata, vbCrLf)

Should I set the var as a string? e.g. Dim var as String

 

[DCrake]

What does one line of data look like? (strData) is it coming from a text file?

 

[davesmith202]

Typical text file would contain the following record structure:

[Event "?"]
[Site "London"]
[Date "1794.??.??"]
[Round "?"]
[White "Atwood, George"]
[Black "Philidor, Francois Andre Dani"]
[Result "1-0"]
[ECO "B21"]

1. e4 c5 2. f4 Nc6 3. Nf3 e6 4. c3 d5 5. e5 f5 6. d4 Nh6 7. h3 Qb6 8. b3 Bd7 
9. Be3 Nf7 10. Qd2 O-O-O 11. Qf2 cxd4 12. Nxd4 Nxd4 13. Bxd4 Qc6 14. Nd2 b6 
15. a4 Bc5 16. Bb5 Bxd4 17. Qxd4 Qc5 18. Nf3 Bxb5 19. Qxc5+ bxc5 20. axb5 Kb7 
21. Ke2 Ra8 22. Ra6 Rhe8 23. Rd1 Nd8 24. Ne1 c4 25. bxc4 dxc4 26. Rd7+ Kc8 
27. Rxg7 Rb8 28. Raxa7 Rxb5 29. Rac7+ Kb8 30. Rxc4 Rb7 31. Rb4 Rxb4 32. cxb4 
Nc6 33. Nd3 Re7 34. Rxe7 Nxe7 35. Nc5 Ng6 36. Nxe6 Kc8 37. Ke3 Kd7 38. Nd4 
Ne7 39. g4 Ke8 40. g5 1-0

 

[DCrake]

Are you only concerned with lines that are preceeded with a [ bracket? or do you need al the other lines as well.

 

[davesmith202]

Sorry guys, I messed up.

I have 3 loops, the loop I was modifying to speed up was in actual fact loop 3, whereas I thought I was working on loop 1. :S

In other words, I have speeded up the process, but not on the slowest part.

I will check the slowest part and try to apply what I have learnt here to it. No wonder I couldn't see much of a speed increase!

 

[Banana]

Just a interesting piece of fact: If one want to vary conditions using Select Case in much like fashion as If/ElseIf:

Select Cast True
   InStr(var(i), "a") > 0
      ....
   InStr(var(i), "b") > 0
      ....
   Mid(var(i), 5, 1) = "z"
      ....
End Select

But I don't think branching is the real problem as indicated by the test of a slight decrease in performance.


I have to ask... where are you getting the data from? If it comes from say, text file that's width delimited for instance, it may be actually faster to link to it and execute a query that performs the formatting and clean-up. For example:

INSERT INTO myPermanentTable (
   ...
)
SELECT
   Site,
   Replace(Mid(Site, 8), """]", "") As SiteClean,
   DateField,
   [Round],
   ...
   Result,
   Switch(
      Result = "[Result ""1-0""]", "1-0",
      Result = "[Result ""0-1""]", "0-1",
      Result = "[Result ""1/2-1/2""]", "1/2-1/2",
      Result =  "[Result ""1/2""]", "1/2-1/2"
   ) AS ResultClean
...
FROM [C:\foobar.txt].Table1

(Note: I'm missing on the FROM clause - do use graphical builder to help you to select the linked table representing the text file then build the SELECT list in similar fashion to what you did in VBA)

HTH.



EDIT: Just saw the recent posts that got posted since I started writing.

So the text flle isn't going to be easy to use in a query since it's not in a CSV or width delimited format and each "columns" would appear as a row. That would mean some pre-processing may be necessary before it can be used as queried. If the 3rd looping isn't improved, it may be useful to look at the options of doing pre-processing (e.g. transposing the rows into column into a intermediary table then selecting from it into final form). Generally speaking, it's likely to be faster to run a series of queries than doing a loop across a recordset object. Of course this may not be actually true for this scenario but we won't know until we test.

 

[DCrake]

Looking at it further you only need to read the first three characters do decide what to do

[Event "?"]
[Site "London"]
[Date "1794.??.??"]
[Round "?"]
[White "Atwood, George"]
[Black "Philidor, Francois Andre Dani"]
[Result "1-0"]
[ECO "B21"]

Select Case Left(StrData,3)
   Case "[Ev"

   Case "[Si"

   Case "[Da"

   Case etc

   Case Else

End Select

 

[davesmith202]

Good points David, although some data I import can have things like EventDate and Event, so I have to have the full monty.

Here is the errant code:

Public Function GetMyFile(pathToFile As String)
    Dim fileNum As Integer
    Dim myString As String
    Dim myTotalString As String
    Dim a As String
    fileNum = FreeFile

    Open pathToFile For Input As #fileNum
    '       Do Until EOF(fileNum)
    '          Input #fileNum, myString
    '          Debug.Print myString
Dim x As Long
    x = 0
    Do Until EOF(fileNum)
        x = x + 1
        Line Input #fileNum, myString

        myTotalString = myTotalString & myString & vbCrLf

        Me.txtCount1 = CStr(x)
        a = DoEvents()
    Loop


    Close #fileNum
    GetMyFile = myTotalString
End Function

 

[DCrake]

    Do Until EOF(fileNum)
        Line Input #fileNum, myString
       If Left(MyString,1) = "[" Then
            x = x + 1
            myTotalString = myTotalString & myString & vbCrLf
       End If

        Me.txtCount1 = CStr(x)
        a = DoEvents()
    Loop

Doing this would only get you the lines you want

 

[davesmith202]

I do need the Move lines too, not just lines starting with "[".