Efficient way to analyze large amounts of data?

Question

I need to analyze tens of thousands of lines of data. The data is imported from a text file. Each line of data has eight variables. Currently, I use a class to define the data structure. As I read through the text file, I store each line object in a generic list, List.

I am wondering if I should switch to using a relational database (SQL) as I will need to analyze the data in each line of text, trying to relate it to definition terms which I also currently store in generic lists (List).

The goal is to translate a large amount of data using definitions. I want the defined data to be filterable, searchable, etc. Using a database makes more sense the more I think about it, but I would like to confirm with more experienced developers before I make the changes, yet again (I was using structs and arraylists at first).

The only drawback I can think of, is that the data does not need to be retained after it has been translated and viewed by the user. There is no need for permanent storage of data, therefore using a database might be a little overkill.

Answer 1

It sounds like what you want is a database. Sqlite supports in-memory databases (use ":memory:" as the filename). I suspect others may have an in-memory mode as well.

Answer 2

If you do not mind using access, here is what you can do

Attach a blank Access db as a resource When needed, write the db out to file. Run a CREATE TABLE statement that handles the columns of your data Import the data into the new table Use sql to run your calculations OnClose, delete that access db.

You can use a program like Resourcer to load the db into a resx file

  ResourceManager res = new ResourceManager( "MyProject.blank_db", this.GetType().Assembly );
  byte[] b = (byte[])res.GetObject( "access.blank" );

Then use the following code to pull the resource out of the project. Take the byte array and save it to the temp location with the temp filename

"MyProject.blank_db" is the location and name of the resource file "access.blank" is the tab given to the resource to save

Answer 3

It is not absolutely necessary to go a database. It depends on the actual size of the data and the process you need to do. If you are loading the data into a List with a custom class, why not use Linq to do your querying and filtering? Something like:

var query = from foo in List<Foo>
            where foo.Prop = criteriaVar
            select foo;

The real question is whether the data is so large that it cannot be loaded up into memory confortably. If that is the case, then yes, a database would be much simpler.

Answer 4

I was facing the same problem that you faced now while I was working on my previous company.The thing is I was looking a concrete and good solution for a lot of bar code generated files.The bar code generates a text file with thousands of records with in a single file.Manipulating and presenting the data was so difficult for me at first.Based on the records what I programmed was, I create a class that read the file and loads the data to the data table and able to save it in database. The database what I used was SQL server 2005.Then I able to manage the saved data easily and present it which way I like it.The main point is read the data from the file and save to it to the data base.If you do so you will have a lot of options to manipulate and present as the way you like it.

Answer 5

This is not a large amount of data. I don't see any reason to involve a database in your analysis.

There IS a query language built into C# -- LINQ. The original poster currently uses a list of objects, so there is really nothing left to do. It seems to me that a database in this situation would add far more heat than light.

Answer 6

If the only thing you need to do is search and replace, you may consider using sed and awk and you can do searches using grep. Of course on a Unix platform.

Answer 7

From your description, I think linux command line tools can handle your data very well. Using a database may unnecessarily complicate your work. If you are using windows, these tools are also available by different ways. I would recommend cygwin. The following tools may cover your task: sort, grep, cut, awk, sed, join, paste.

These unix/linux command line tools may look scary to a windows person but there are reasons for people who love them. The following are my reasons for loving them:

1. They allow your skill to accumulate - your knowledge to a partially tool can be helpful in different future tasks.
2. They allow your efforts to accumulate - the command line (or scripts) you used to finish the task can be repeated as many times as needed with different data, without human interaction.
3. They usually outperform the same tool you can write. If you don't believe, try to beat sort with your version for terabyte files.