Should I use BLOB or Tables for storing large data?

Question

Problem

Currently investigating a solution to improve the performance of a web application. The application works well for small projects, but faces performance issues in the UI when working with large projects.

The use case is the following:

A user has to submit an excel document that contains 10000 line items. Each line item contains approximately 50 terms and each term can have one or more attributes. The system should support a project that can handle 200 users uploading such documents. A max of 10 users can concurrently be active. There can be multiple such large projects.

The database currently used is Oracle. We also need to ensure that the chosen solution will work well with an in-memory columnar RDBMS.

The existing functionality works well for small projects that has both a web UI and an excel interface. But the web UI has performance issues with large projects and we will solely rely on an excel interface.

The operations on the data involve upload/import, download/export, editing and reports.

All actions have to be transactional, since there are other updates within the RDBMS that occur as part of the upload. So this cannot be put in a non-transactional data source. There is at least one main operation where we need to load all the data. This operation can be done asynchronously.

Existing solution

Our existing solution which runs on tomcat and Oracle uses wide tables. This solution works well upto 1000 line items and then has performance issues on the application server. The performance issues relate to java object hydration and causes memory issues on the application server. This is because the wide table has a large number of null columns and the java objects that are created are large due to a large number of empty fields.

Options

In order to handle a larger number of line items we need to reduce the memory footprint of the existing solution. We are trying to decide between the following approaches:

1. BLOB
2. Narrow table
3. Redesigned Java object (New)

BLOB solution

One way to avoid the null values is to transform the excel document into a concise key value format that can be compressed and stored in the database as a BLOB per user. The advantage of this approach is:

1. Use considerably less space in the DB.

The drawbacks are:

1. We are limited in what we can do, since there are some operations that will need to process data across all users.
2. A small edit will cause the whole BLOB to be rewritten and thus causing redo log growth.
3. Will be difficult to retrofit the existing UI against this model in the future
4. Maintain a new model for large projects

Narrow table

This approach solves the null values by having a few fields with a row for each term. The number of null columns is reduced drastically. The java objects hydrated from these rows do not have empty fields and can be small in size. So the memory issue is alleviated. The advantages are:

1. A narrow table is well suited for an in-memory columnar approach
2. Keeps open the possibility of reworking the UI to work against the new table structure

The drawbacks are:

1. Order of magnitude increase in the number of rows. A single project will end up having 10000x50x200 rows, i.e., 100 millions rows.
2. Maintain a new model since the UI will not be touched and that will go off the old model.

Redesigned java class

Had not initially considered this approach but it looks like a good option. We use the existing data model, but revamp our java class backed by a map. Only the populated fields are held in this map. This avoids having a class with a large number of fields and hence reduces the memory footprint for a sparsely populated object.

The advantage

1. Solves the application memory issue with the least impact of all 3 options
2. Uses the existing data model

Drawbacks

1. Does not get rid of empty columns in the DB. But I think we can live with this for now.
2. May not be the best format for a columnar in-memory RDBMS

Question

What is the best approach to take?

---

Update As I was clearing up the description, a potential third option (Redesigned java class) dawned on me. So I am going to investigate it further as it looks promising with no model impact. Let me know if this is not a good option based on the use case and if you see any issues with it.

Answer 1

The challenge is how should this information be stored efficiently in an RDBMS?

The question should be why should this information be stored in a RDBMS at all?

What are you going to do with it once it's there?

If all you're going to do is "save" a spreadsheet into the database and then pull it back out again, then I'd suggest you're wasting your time. It's a file; put it in a file system where it belongs and from where you can [far more] easily retrieve it.

However ...

If you want to interrogate the "uploaded" data and "slice and dice" it, drawing summaries across the data uploaded by many users, then the database is most definitely the way to go.

OK, 100M rows is a lot but with proper indexing (and partitioning, if you have the option), your database will cope with it.

Answer 2

Yes, the big question is what do you want to do with these excel documents once they're in the DB. You can store them as BLOBS quite happily, but then you can store them as files on the filesystem too, and the latter allows you to manipulate the documents in various ways (eg running code to change them).

If you're just storing them for later retrieval, then store them as blobs. You can store additional metadata about the contents alongside the blob and this is the approach I'd use if you needed to run queries about the documents.

Note that SQL Server 2012 has the ability to index files that are stored in 'filetables' which are hybrid file/DBs so you get the benefit of both.

Answer 3

Perhaps consider a hybrid approach. Fetching and storing documents is the purview of document-centric or "NoSQL" databases. Perhaps store the actual spreadsheets in (e.g.) Cassandra and keep your metadata (and copies of any working data, if you only really care about a subset of the data in the spreadsheet) in Oracle.

As to your memory pressure in Tomcat, take a look at the Flyweight design pattern. It basically recommends that you don't create objects for every bit of data; instead only instantiate an object when you need the data. For example, instead of creating an object with 10K rows consisting of 50 items, only create as many rows as you need for the current operation, likewise with items. This will require keeping the backing data in a raw form (the Excel spreadsheet) and only instantiating the individual values when required.

Answer 4

It depends on what you intend to do with the content of the files.

If you ever need to make queries based on the content of the sheets (and I am pretty sure you will have to), I really think you should consider the table solution. I think there are some ways to improve your performance issues (batch insert...).

Answer 5

As I was clearing up the description, a potential third option (Redesigned java class) dawned on me. So I am going to investigate it further as it looks promising with no model impact. Let me know if this is not a good option based on the use case and if you see any issues with it.

Answer 6

When working with large amounts of data, it is generally recommended to use tables rather than BLOB (Binary Large Object) data types for storing the data. This is because tables are more flexible and efficient for managing and querying large amounts of data, compared to BLOB data types.

BLOB data types are designed for storing binary data, such as images, videos, and audio files. While they can be used for storing large amounts of data, they are not well suited for querying or manipulating the data. For example, you cannot use SQL statements to filter or search the data in a BLOB, and you cannot easily update or delete specific parts of the data.

On the other hand, tables are designed specifically for storing and managing data in a structured and organized way. Tables allow you to use SQL statements to query, filter, and manipulate the data, and they provide efficient ways of storing and indexing the data to improve performance. Tables also support transactions, which allow you to manage concurrent access to the data and ensure data consistency.

Overall, while BLOB data types can be used for storing large amounts of data, tables are generally a better choice for managing and querying large datasets. Therefore, if you have large amounts of data that you need to store and manage in a database, it is recommended to use tables rather than BLOB data types.