Shell Programming and Scripting

I created a file1.txt with 500,000 keys and did some timing tests on my laptop (2.6GHz i3 M 350): awk takes about 1.08 secs to check a zip record against the 500K keys. I loaded the keys into a linked list in C and did a substring match an this took 0.842 secs per zip record (so with a custom C program you might expect a 15%-20% imporvement in speed).

The real issue here is the substring match of each key, if you could determine the key of a zip datafile record and do and database or B-tree lookup in would only take a few miliseconds per zip datafile record. For example if you could say the key for data record:

Code:

KKKKK 1454545345 842011011920025500000001287009909427909 03156456456546 KKKKKK AAA MMMMMMM034535345345345345

Was "56456456546" and indexed search could be done against your 500K keys and the matching key ticked off as found very quickly. Without this ability you are could really only expect to check in the order of 10 ziprecords per second.

can you plz provide the code that u used so that I can check it on the unix system.

Yesterday, I tried loading the keys into a c++ B-Tree and then pulling a key from your zip-string (11 chars from position 60) and doing a lookup. This managed to check 20,000 zip records in approx 0.7 sec. This highlights the power of indexed lookups, more work should probably be done in parsing your data records and determining the key from the record.


From the example zip record in my previous post we could lookup:

Code:

"56456456546"
"6456456546 "
"456456546  K"
"56456546 KK"
"6456546 KKK"
...
"MMMM0345353"
"MMM03453534"

Depending on the region where data appears and what limitation you have on key characters (eg are spaces or letters allowed in the keys), up to 27 lookups would be required for each record, this should still be able to process close to 1000 records per second.

As you can see it's important to find out as much about your data as possible, Ideally the processing program should look at a record and identify it's type and extract the proper key exactly and report errors on badly formatted records. This avoids false positives, ensures you are looking up what is intended and can react to format changes within the data records.

As methyl pointed out earlier getting a Systems Analyst involved is your best bet as we can only advise on information you supply here and there may be even better ways to get the result you require.

@vsachan
What Database Engine and version do you have?
Did the flat file data come from an extract from you main database(s)?
Have you now developed code to match your requirements?