Handling a Long Loop Procedure 25000 iterations!

  • 15 years ago
    I have two problems which are probably related.
    My app has a loop which can take some time to complete as it is verifying file details on a computer and updating a database list as necessary. (One installation has 25000 files to check so the loop takes some time to run).

    Prob 1. During the looping windows task manager indicates the program is not responding, but the loop is actually still going. This doesn't look good to the user who is liely terminate the app based on task managers information. What causes this and how do I change it?

    Prob 2. The form which follows the progress of the loop with progress bar and the names of the files processed as a richtext box. Everything works great as you F8 your way through the code while debugging. When I leave it to run, the form blanks out part of its details so you don't see the progress bar. I have tried changing the form's ClipControl setting but still the problem. I want the form to be 'complete' while the app is running as well as if the user leaves it running in tyhe background and alt-tabs between other applications to check progress. Help?

    Will the DoEvents solve the problem? I found a reference to this in another discussion topic which allows the user to add text to a text box while a For Next loop.
  • 15 years ago
    Bradley,

    VB is an event based language in that when an event is triggered e.g. a button click, the processor executes all the relevant code within the event routine (sub) until it exits.
    On exiting the sub, the processor is yielded to whatever other programs may require it.
    If you have a long loop, the processor is not yielded so activities like repaint events are not called and this is why your program seems to freeze.
    It is for this reason that Task Manager gets no response either.

    The DoEvents function temporarily yields the processor so that other events can be processed (progress bar repaints etc.) although even this may not free up processor cpu usage noticably as code execution continues immediately after the other events have been processed.

    As a suggestion, you could try replacing your loop code with a timer event, with say a 10ms interval, so that after each interation of the timer event, the processor is yielded until 10ms later.

    Hope this explanation makes sense

    Trevor
  • 15 years ago

    Thanks TrevorG,


    It took quite a while to get any response to my query but you have given me some direction.
    I will try working on my code tonight.


    You said:

    Quote:
    As a suggestion, you could try replacing your loop code with a timer event, with say a 10ms interval, so that after each interation of the timer event, the processor is yielded until 10ms later.


    I'm not quite sure if the timer will work for me as the loop(s) get quite complicated with filtering recordsets. I may use timer within timer. Just to understand as an example:


    TimerEvent (every 10ms):
    carry out recordset operation here
    counter=counter+1
    progressBar.value=counter
    End of TimerEvent


    Only once the entire TimerEvent has finished is there a 10ms delay before the next firing.
    How do I stop the timer or keep it firing based on recordset position? If recordset.EOF =true the timer should stop.



  • 15 years ago

    Bradley,


    To set this up, add a timer control to your form and set its Enabled property to False and its Interval property to 10.


    When your query has completed, set Timer1.Enabled = True.


    I would experiment with Timer1.Interval and number of records processed for each timer event firing (e.g. 10, 100, 500) to achieve acceptable results.


    Along the lines of your assumption, before exiting the timer event, set Timer1.Enabled = Not recordset.EOF.


    Trevor

  • 15 years ago

    Thanks, I will try it this weekend.

  • 15 years ago
    I used the DO EVENTS and it worked.
    My only problem now is how to make the loop run faster and if anyone has some clever ideas please let me know.

    I have a recordset created from a database which lists files previously accessed.
    The recordset consists of almost 20,000 records.
    I have an array of file name and path values curently being accessed.
    The program moves through the array and filters the recordset for its path and filename. This is where the delay is.
    The program runs quickly for the first 400 or so filter operations and then it slows down.
    I timed the process for 200 operations when it has slowed down 12sec to 34 sec to just over 40secs for the operations towards the end.

    (I also replaced the rs.filter operation with a sql query for the specific file entry to return a completely new recordset). This did not run any quicker.

    As I F8 my way through the code the only delay is when it hits the filter.
    Why does the same filter operation take longer later on in the loop?
  • 15 years ago
    Bradley,

    Can you post an example of your recordset and array structures?

    As a suggestion, and depending on the size of your array, you could insert the array contents into a temporary table and then execute a join query to associate your array items with the actual database records.

    Trevor
  • 15 years ago

    This is being used in a backup routine and I have grouped certain folders for backups on ceratin days.


    I have used the array to capture all the folders and subfolders in the users selected path.
    The user's selected path would be previously classified by a Type eg 'Monday'.
    All files would get this Type associated to it when the record is created in the database.


    eg C:\Documents and Settings\All Users*.*  as a 'Monday' Type


    The program adds each folder path to the 1 dimensional array FolderArray with a redim  procedure with preserve on each new folder found.


    The recordset is a normal ADODC type recordset created from an Access database with a SQL query and vb connection.
    eg "Select * from tFiles where Type = 'Monday' "


    I then loop through the array (The one in question is 1x9600 in size) and check files against those in the database by filtering the above recordset with the filepath and name.


    As mentioned before the only delay is during the filter operation and only when it has performed say 400 filter operations. The array is created upfront and does not change. The recordset is created once and is also upfront.


    I also mentioned that I tried to recreate the recordset based on the file in question
    eg "Select * from tFiles where Type = 'Monday' and FileName='C:\Documents and Settings\All Users\text1.txt ' "
    This did not make the process any quicker.


  • 15 years ago

    Bradley,


    I've tried this solution and it checks 5000 records within 1 sec.


    THIS DEMO ONLY WORKS WHEN EACH PATH IN RECORDSET IS UNIQUE


    You will need to adapt to your requirements.


    Create a class module "RsStore" and add code

    Code:
    Option Explicit


    Private mstrData As String


    Public Property Let DbData(ByVal RsData As String)


    mstrData = RsData


    End Property


    Public Property Get DbData()As String


    DbData = mstrData


    End Property



    Then in your Main form's declaration section add

    Code:
    Dim DbRecords As New Collection


    In the procedure in which you populate your recordset

    Code:
    Dim DbRecord as RsStore


    ' For each record in recordset
    Set DbRecord = New RsStore
    DbRecord.DbData = rs(0) & vbTab & rs(1): 'Repeated for each field
    DbRecords.Add DbRecord, rs(x): ' Where x = Path field
    ' Next record



    To check for existence of a Path

    Code:
    Public Function IsPath(PathName As String) As Boolean


    Dim DbRecord As RsStore


    On Error Resume Next


    Set DbRecord = DbRecords(PathName)


    IsPath = Not (DbRecord Is Nothing)


    Exit Function



    I hope this makes sense


    Trevor

  • 15 years ago

    Thanks TrevorG,


    I'm preparing for meetings which will keep me occupied for the rest of the day so haven't had a chance to look at it properly.
    I've printed a hardcopy so will try and give it a look during a gap in the meetings and try this evening if I understand what to do, otherwise I'll post another query.


    You've been a great help, much appreciated.

  • 15 years ago

    Fantastic, the operation that previously took 1.5 hrs took only 20 minutes.
    But...       there is always a but....  I still need to check the date value stored in the huge recordset and update it if it is older then the current file being checked.


    What I tried was:
    Added the date field from the recordset [rsCompare(2)] and used the File Path [rsCompare(1)] as the key for the collection [DbRecords] entry.


    Code:
    If Not (rsCompare.EOF) Then
    Do While Not (rsCompare.EOF)
       DBRecord.DbData = rsCompare(2)  'the file date
       DbRecords.add DBRecord, rsCompare(1) 'the path is used as the key
       rsCompare.MoveNext
    Loop
    rsCompare.MoveFirst
    End If


    What I need is when the IsPath function returns true ie a match is found, then I need to extract the corresponding date value in the matched entry so I can use it for comparison.


    Code:
    Public Function IsPath(ByVal PathNam As String) As Boolean
    Dim DBRecord As New RsStore
    On Error Resume Next
    IsPath = False
    Set DBRecord = DbRecords(PathNam)
    MsgBox DBRecord.DbData   '<----------------------


    IsPath = Not (DBRecord Is Nothing)
    End Function



    I thought the MsgBox would return the matching date value to be from the DBRecord.Dbdata Class but this only returns the last date value captured.


    How do I get the date value of the matched key entry in the collection assigned to a date variable?


    The next step I have is to compare this value to the fso file object's last modified date value. If it is less than the fso dat then I must update the huge recordset otherwise carry on to next file. This all curently works with the rsCompare.Filter operation as it moves the recordset to the entry in question. However it is the time taken for the filter operation that is a problem. As mentioned previously it starts off quickly and then slows down after 400 or so filter operations. This I don't understand as the recordset stays the same size.

  • 15 years ago

    Bradley,


    You need to edit your code as follows

    Code:
    If Not (rsCompare.EOF) Then
    Do While Not (rsCompare.EOF)
      ' Create a new instance of the RsStore class for each record in recordset
      ' such that each collection item is pointing at a unique DBRecord
      ' Otherwise all collection items (although having unique keys)
      ' will point to the same DBRecord which,
      ' by the end of the loop will be the last record in the recordset
      Set DBRecord = New RsStore


      DBRecord.DbData = rsCompare(2)  'the file date
      DbRecords.add DBRecord, rsCompare(1) 'the path is used as the key
      rsCompare.MoveNext
    Loop
    rsCompare.MoveFirst
    End If



    Hope this makes sense


    Trevor

  • 15 years ago

    You're a genius, or perhaps I just need to properly read the instructions you sent previously.


    I ran a short test here at the office and it "filters" perfectly. Now I can correct the version I have at home and run a test on the big recordset.
    Thanks for all the help. I'm most grateful, hopefully I'll be able to help somebody else...

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.” - Brian Kernighan