Nested ASCII File Process is extremely slow

[HappyJoyAwesome]

I've created some macros to keep contact lists up-to-date. Unfortunately the list of contacts is derived from software that doesn't allow email addresses to be associated with each contact (it's old). So I've generate a tab delimited list from the software's database and I want MEP to compare this with another tab delimited list which has the same fields, except email addresses associated with each record/line.

 

So with each macro run we have a newlyGeneratedContactListWithoutEmails (aka "NEW") file which we need to compare to TheLastListWithEmails (aka "OLD") file. This is because new contacts continually get added, some get edited, and the staff need a way to manually add an email for each CONTACT/LOCATION combination, and this can be done with a nifty CSV viewer/editor. Once the comparison is done and the emails are matched to the correct row, the row is save/appended to a newListFile.

 

The logic of the macro is something like the following:

 

ASCII File Begin on NEW

ASCII File Begin on OLD

IF %CONTACTfromNEW% = %CONTACTfromOLD%

AND

IF %LOCATIONfromNEW%= %LOCATIONfromOLD%

IF %OLDLISTARRAY[x]% Contains "@"

SET STRING %NEWLISTARRAY[x]% TO %OLDLISTARRAY[x]%

GoTo UpdateRecord

END IF

END IF

ASCII File End

Label: UpdateRecord

SET STRING %newContactListWithEmails% to %NEWLISTARRAY[1]%<TAB>%NEWLISTARRAY[2]%<TAB>...etc.

IF Not File Exists "newListFile.txt"

Modify String: Save %newContactListWithEmails% to "newListFile.txt"

ELSE

Modify String: Append %newContactListWithEmails% to "newListFile.txt"

END IF

ASCII File End

 

The above works correctly but is horrendously slow. We're talking about lists with 900 rows in the text file to compare and it outputs a new line/record to the new file about once every minute.

 

How can I make this faster?

 

I would normally use SQL for something like this but I don't have bulk insert permissions.

[Samrae]

It may not make a lot of difference but you should replace the line:

GoTo UpdateRecord

 

with

Break

 

Jumping to a label outside of a loop may cause performance issues.

[Samrae]

Are the OLD and NEW files the same except for the email addresses? Are the records in the same order? Your macro gets the first record from file NEW and compares it with all the records in file OLD. Then it gets the second record from NEW and compares it with all records in OLD. And so on. If each file contains 900 records that would be 810,000 searches.

 

If the records in OLD and NEW match (same number of records, same order) then you could do something like this:

 

- Read file OLD into a variable (OldVar)

- Split OldVar into an array variable (OldArray) at end of line (CRLF)

- Read file New into a variable (NewVar)

- Split NewVar into an array variable (NewArray) at end of line (CRLF)

 

- Set up a repeat loop to process from the beginning to the end of OldArray

- Parse one record from OldArray[idx] and compare with the record from NewArray[idx] and make your adjustments as needed and write the output to a file.

 

I will create a more specific example if you can confirm that records in the file OLD align with the records in file NEW.

[Cory]

I don't have a lot of time but I don't see a sort and like Samrae asked this is important. If not sorted I would first sort. Then I would make everything arrays and not use ASCII File process except to load the arrays. I think I posted here once my sort routine. Then I would keep track of the indexes of your high and low marks. EG if you know you're already in the C's no need searching A's and B's. And of course when you find one of course do not continue searching.

 

Another thing you can do with sorted arrays is a binary search. It sounds like a techy word but it's not. You have probably done it before when organizing files. Instead of starting from the first and plowing thru every one start in the middle and compare if it's greater or less than. Or equal of course:-) Then repeat this for each subset. EG for 1000 records compare it first to record 500. If it's less than copare to 250. If it's more than compare to 375 and so on. This decreases the iterations by an order of magnitude.