comparing Huge Files - Performance is very bad

madhukalyan · October 10, 2006, 8:00am

Hi All,

Can you please help me in resolving the following problem?

My requirement is like this:

1) I have two files YESTERDAY_FILE and TODAY_FILE. Each one is having nearly two million data.
2) I need to check each record of TODAY_FILE in YESTERDAY_FILE. If exists we can skip that by treating as duplicate.
3) If does not exists, I need to check the primary key fields, say for example first 3 fields, if match is found i need check the data part, say for example 5th and 6th fields, if datapart is not matching, then i need to write the record in a new file, say OUTPUT_FILE, with a prefix of 'C' (Change). If datapart is matching skip the record.
4) If Primary Key fields match not found then i need to write the record with a prefix of A (Append).
4) After above process, i need to check each record of YESTERDAY_FILE in TODAY_FILE, if does not exists, i need to write the record with a prefix of D (Delete).

I developed the following logic which is taking too much time to execute...in one minute it is creating 100 records. Performance is too bad. Can any one of you please help me out.

My code is:

while read record
do
        primary_key_fields=`echo $record | cut -d "|" -f 1-${fields_to_compare}`
        data_part=`echo $record | cut -d "|" -f ${fields_to_skip}-`

        flag=`grep "${record}" ${yesterday_file_name}`
        if [ -z "${flag}" ]; then
                flag_tmp=`grep "${primary_key_fields}" $yesterday_file_name`
                yesterday_data_part=`echo ${flag_temp} | cut -d "|" -f ${fields_to_skip}-`
                if [ -z "${flag_tmp}" ] ; then
                        current_record="A|"${record}
                elif [ "${yesterday_data_part}" != "${data_part}" ] ; then
                        current_record="C|"${record}|sed "s/|I|/|U|/g"
                fi
                echo "${current_record}" >> $delta_file_name
        fi
done < $file_name

while read record
do
        primary_key_fields=`echo $record | cut -d "|" -f 1-${fields_to_compare}`
        flag=`fgrep "${primary_key_fields}" ${file_name}`
        if [ -z "${flag}" ]; then
                current_record="D|"`echo ${record| sed "s/|I|/|D|/g"`
                echo "${current_record}" >> $delta_file_name
        fi
done < ${yesterday_file_name}

fields_to_compare, fields_to_skip and file_name are the parameters passed to the script. In the following case:

fields_to_compare=1 (primary Key fields: aaa, bbb etc)
fields_to_skip =3 (From 4th field i need to consider as data part)
file_name=today_file

My Input is :

Yesterdays File (yester_file)

Todays File (today_file)

Output File (deltafile)

C|aaa|xxxxxxxxxxxxxxxxxxxxxxxxx|U|vvvvvvvvvvvvvvvvvvv
C|bbb|xxxxxxxxxxxxxxxxxxxxxxxxx|U|kkkkkkkkkkkkkkkk
A|ddd|xxxxxxxxxxxxxxxxxxxxxxxxx|I|zzzzzzzzzzzzzzzzz
D|ccc|xxxxxxxxxxxxxxxxxxxxxxxxx|D|bbbbbbbbbbbbbbb

Corona688 · October 10, 2006, 1:37pm

code tags for code please. that script is unreadable.

Like {code} stuff {/code} except with [ ] instead of { }

BOFH · October 10, 2006, 3:10pm

I've noticed that for simple things, I can shell script something and it works fine. When things start getting complicated or there's a performance issue, I'll break out perl (or python if you like).

I'd take what you have and see if it could be done better in perl. I'm sure it'd be a lot faster and probably easier to write.

Carl

Glenn_Arndt · October 10, 2006, 4:01pm

I won't even try to interpret your code, because it is too difficult to read without code tags, but with that in mind, have you considered using comm, diff, cmp, etc.? Unix apps such as these were designed for problems like this. Why reinvent the wheel?

I'm also in agreement with BOFH that if raw performance is your concern, perl (or even C) might be better.

If I could read your code, I'd probably have some more insight.

jim_mcnamara · October 10, 2006, 5:23pm

Look - tmarikle posted a nice little 5 line awk program that does most of what you want - print all the lines in file2 that do not exist in file1. A sort of "minus" in a result set sense.

This is a modifed version of it, change it as you want:

awk '
    FILENAME=="file1" {
        Keys[$1 $2 $3]++
    }
    FILENAME=="file2" {
        if (Keys[$1 $2 $3] == 0) {
            print $0
        }
    }
' file1 file2 > newfile

The key is the first three fields. Add more fields or just use $0 for the whole record.

ghostdog74 · October 10, 2006, 10:58pm

Python alternative:

#!/usr/bin/python
deltafile = open("delta.txt","a")
yfile = open("yester_file.txt") #open yesterday file
tfile = open("today_file.txt") #open today file

for i in xrange(0,2000000): #loop 2million records
        yesterline = yfile.readline().strip() #strip newline
        todayline = tfile.readline().strip()
        y_primary , y_2nd, y_3rd , y_4th = yesterline.split("|")
        t_primary, t_2nd, t_3rd, t_4th = todayline.split("|")
        if y_primary == t_primary:
                if y_4th != t_4th:         
                        print >> deltafile , "C|%s|%s|%s|%s" %( t_primary , t_2nd ,"U" , t_4th)
        else:
                print >> deltafile, "A|%s|%s|%s|%s" %( t_primary , t_2nd, t_3rd, t_4th )
                print >> deltafile, "D|%s|%s|%s|%s" %( y_primary , y_2nd, "D", y_4th)

deltafile.close() #close output file

Output:
/home > python test.py
C|aaa|xxxxxxxxxxxxxxxxxxxxxxxxx|U|vvvvvvvvvvvvvvvvvvv
C|bbb|xxxxxxxxxxxxxxxxxxxxxxxxx|U|kkkkkkkkkkkkkkkk
A|ddd|xxxxxxxxxxxxxxxxxxxxxxxxx|I|zzzzzzzzzzzzzzzzz
D|ccc|xxxxxxxxxxxxxxxxxxxxxxxxx|D|bbbbbbbbbbbbbbb