4

I apologise in advance for the length of this post, but I want to paint an accurate picture of the problems my app is facing and then pose some questions below;

I am trying to address some self inflicted design pain that is now leading to my application crashing due to out of memory errors.

An abridged description of the problem domain is as follows;

  • The application takes in a “dataset” that consists of numerous text files containing related data
  • An individual text file within the dataset usually contains approx 20 “headers” that contain metadata about the data it contains. It also contains a large tab delimited section containing data that is related to data in one of the other text files contained within the dataset. The number of columns per file is very variable from 2 to 256+ columns.

The original application was written to allow users to load a dataset, map certain columns of each of the files which basically indicating key information on the files to show how they are related as well as identify a few expected column names. Once this is done, a validation process takes place to enforce various rules and ensure that all the relationships between the files are valid. Once that is done, the data is imported into a SQL Server database. The database design is an EAV (Entity-Attribute-Value) model used to cater for the variable columns per file. I know EAV has its detractors, but in this case, I feel it was a reasonable choice given the disparate data and variable number of columns submitted in each dataset.

The memory problem

Given the fact the combined size of all text files was at most about 5 megs, and in an effort to reduce the database transaction time, it was decided to read ALL the data from files into memory and then perform the following;

  • perform all the validation whilst the data was in memory
  • relate it using an object model
  • Start DB transaction and write the key columns row by row, noting the Id of the written row (all tables in the database utilise identity columns), then the Id of the newly written row is applied to all related data
  • Once all related data had been updated with the key information to which it relates, these records are written using SqlBulkCopy. Due to our EAV model, we essentially have; x columns by y rows to write, where x can by 256+ and rows are often into the tens of thousands.
  • Once all the data is written without error (can take several minutes for large datasets), Commit the transaction.

The problem now comes from the fact we are now receiving individual files containing over 30 megs of data. In a dataset, we can receive any number of files. We’ve started seen datasets of around 100 megs coming in and I expect it is only going to get bigger from here on in. With files of this size, data can’t even be read into memory without the app falling over, let alone be validated and imported. I anticipate having to modify large chunks of the code to allow validation to occur by parsing files line by line and am not exactly decided on how to handle the import and transactions.

Potential improvements

  • I’ve wondered about using GUIDs to relate the data rather than relying on identity fields. This would allow data to be related prior to writing to the database. This would certainly increase the storage required though. Especially in an EAV design. Would you think this is a reasonable thing to try, or do I simply persist with identity fields (natural keys can’t be trusted to be unique across all submitters).
  • Use of staging tables to get data into the database and only performing the transaction to copy data from staging area to actual destination tables.

Questions

  • For systems like this that import large quantities of data, how to you go about keeping transactions small. I’ve kept them as small as possible in the current design, but they are still active for several minutes and write hundreds of thousands of records in one transaction. Is there a better solution?
  • The tab delimited data section is read into a DataTable to be viewed in a grid. I don’t need the full functionality of a DataTable, so I suspect it is overkill. Is there anyway to turn off various features of DataTables to make them more lightweight?

Are there any other obvious things you would do in this situation to minimise the memory footprint of the application described above?

Thanks for your kind attention.

3
  • 1
    I think this question is far too localised to your problem domain at the moment and is likely to be closed. But you could rephrase it to focus on the specific problem of data storage. And I suspect your answer would be to look into NoSQL databases.
    – pdr
    Nov 16, 2011 at 8:30
  • I was hesitant to ask the question also. However, I figured that even though the background info is specific, the two questions I ended up asking are pretty general and answerable. (1) How do you keep transactions small in a situation where you are importing a lot of data (or does it even matter)? and (2) Is there any way of disabling features of a Datatable to make it somewhat more lightweight?
    – Mr Moose
    Nov 16, 2011 at 14:20
  • A transaction should be a single logical 'unit-of-work'. The scope of the 'unit' can vary, though - the logical boundaries aren't always fixed - and are defined by business need. So, the question is, what are the files you are receiving? If it's just uploads as general history/knowledge, you may not even need a transaction. If you're uploading sales data, the transaction should be one, well, transaction (how you go about finding that could be interesting, depending on upload process). Nov 16, 2011 at 18:08

2 Answers 2

1

EAV is nearly always a mistake (perhaps not in your case but ....).

The data in each column must have some meaning (probably contained in the header meta data!).

Perhaps you could translate the meta-data headers into a "proper" table definition with usable columns?

Either way I would load each data set directly into an intermediate table 1 row per record. Even if you need to name the columns (col1,col2,col3 .... col256) or just a one very wide column. Then change your validation process to use the intermediate tables rather than load everything into memory.

If you can cross-reference the the various files they must have some meaning full key/identifier already. You could use these as the primary keys rather than letting the database allocate a key, thus saving you the work of tying everything up again.

6
  • It may be more cost effective to buy a bigger machine. A 64 bit machine with 8 gb is not that expensive these days! Nov 16, 2011 at 10:31
  • This does not solve his problem unless his application runs as a x64 process and many applications on a x64 operating system do not. This also does not prevent extreme amounts of memory from being used. While I would admit adding a more powerful machine, based on the fact the application performance is poor is a good idea, its certainly only going to mask the problem.
    – Ramhound
    Nov 16, 2011 at 13:16
  • I can see this evolving into an EAV flamewar akin to Emacs vs. Vi
    – maple_shaft
    Nov 16, 2011 at 14:00
  • No flamewar. EAV isn't really the issue. I just thought I'd mention it as background info. We can't really use any other form of schema as the data in the reports only really has around 2 or 3 columns that you'd expect to find across all datasets. The rest of the columns can be anything else that was considered of interest. The metadata doesn't describe the columns, only some basic info of the report. Thanks for your input though. I do appreciate it.
    – Mr Moose
    Nov 16, 2011 at 14:15
  • @Mr. Moose, just didn't want to be seen recommending EAV as a general solution. Looks like its your only man. I still recommend loading the base files into tables just to get "free" indexing and buffer management to solve your memory issue. Comment on 8gb machine not being that expensive still stands though :-) Nov 17, 2011 at 1:57
1

GUID's can be a good idea for uniquely identifying a single record, especially in cases of EAV or where a record needs to be unique across schemas, databases and environments. They may actually cause performance problems in a typical RDBMS however as comparing a string of characters against other strings of characters is much more expensive than comparing numerical data. This could negatively impact queries but then the effect might be negligible or not applicable for you. So use your best judgement.

Staging tables are a great idea if applications depending on this data do not have explicit requirements for 100% real-time data. You can always offload the heavy processing and memory usage involved with processing this data into your main tables during off-peak hours. They also allow you to perform data validation at a later time as well.

1
  • Hmm, good point. I was considering the impact of GUIDs on storage but not necessarily on query performance.
    – Mr Moose
    Nov 16, 2011 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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