I have a unique problem which I'm hoping someone can assist with.

I have One big text file, our Production file. The data in the file is delimited in the following format

Reference|Cost Centre|Analytics Base Value|.... 

The average size of this file is about 30MB. with about 120000 rows.

and then I have about 20 Regional files. these files are similar to the Current big file in structure. except that they are smaller. average size 50000 rows.

Now I have to loop through each line in the big Prod file. For each Reference code, I have to search through each of the 'Regional' files to see which ones contain that specific reference code. and then copying some of the data from that line into a report. There is no way of predetermining what files to look into. And each reference can be in multiple Regional files..

As you can imagine, looping through each row in each file, multiple times is a very time consuming process. Due to memory constraints, I can't load the files into memory.

Does anyone have any smart ideas on how I can do this? I don't need code samples. just pointers on ways I could solve this problem.

I'm developing the tool in C#.

  • That's not unique, it's not even particularly rare. The first step in such cases is to see whether your memory can store an index (filename + offset) to each occurrence of a reference in the files. If you can perform random access on them, that is often enough to get the speed-up you want. – Kilian Foth Jul 9 '13 at 10:53
  • 1
    Sure about your memory constraints? If your regional files are similar in structure and row-size as your main file, you will need ~250MB of memory. That dies not seem very much, even on a smart-phone of today. – Doc Brown Jul 9 '13 at 11:21
  • 1
    The reason this is slow is because you are processing text files. This same behavior also happens in a database when there are no indexes on key fields. It's called a full table scan. If UMBY_2288 existed in row 1 in each of the 20 regional files, you would still have to read each file all the way to the end. There is no way for the process to determine it found the last match and stop looking. Therefore, each single read in your central file will do 5 million reads of regional file data (20 X 50,000). I'd use a database with indexes on the "Reference Codes". – Michael Riley - AKA Gunny Jul 9 '13 at 12:29
  • Thanks guys. looks like relational database has the most support. I'll look into that. I'll try the RAM option first. – greenkode Jul 9 '13 at 12:46
  • should be ... will do 1 million reads... – Michael Riley - AKA Gunny Jul 9 '13 at 13:24

The solution is to read each file once, storing the date in memory. Keep an associative array or similar data structure where the key is the reference number. Then, as you process the master file, looking up each reference should take just microseconds.

If the data is too big to fit in memory, you can create a temporary sqlite database.


Those loads seem quite small for memory, so there could be a bug in your application. It doesn't sound like you're using much on what is probably a multi-gigabyte system.

But.. there is a solution to your problem, it's called a 'relational database' and this kind of load is very small fry for many of them. For the kind of load you're looking at it sounds like SQL Express is probably what you want.

You can load in all your regional files into tables and build indexes to handle the references much more efficiently than you do presently.

  • 1
    While a database would work, if the code has to read all the files, write them to the database, then query the database, then it's kindof overkill if they can just be stored in memory in the first place. In the event of needing any kind of long-term persistence, or memory limitations that prevent keeping it all in memory, then a database would be the answer. – Bobson Jul 9 '13 at 14:31
  • @Bobson: It could be overkill or it could make for one much more flexible ad-hoc reporting solution. – Wyatt Barnett Jul 9 '13 at 15:07
  • My main reasoning for suggesting a DB was more that ease of implementation and maintenance were likely to be more valuable to the overall solution than pure in-memory performance. Also it's likely that DB's will have better optimisation than a DIY solution. – James Snell Jul 9 '13 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.