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.


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


  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


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.

  • 2
    Why do you think 100 million records is a lot for a database? And why do you think properly normalized data will have that many records with the requirements you provided?
    – user22815
    May 20, 2015 at 23:30
  • This is just for a single project with a single response. So yes, it is a lot. I am looking for ways on how this data can efficiently be stored and loaded. Our existing solution faces issues with both storing and and loading this data and that is with less than 1/10 of this data size. We are looking at revamping our existing table structure moving from one having a large number of columns with less number of rows to one with a few columns and more number of rows to see if it addresses the performance issue. But would like feedback on whether it makes more sense to go with a BLOB approach. May 20, 2015 at 23:48
  • If users are just submitting/retrieving documents and there is no DB processing of said data, would Sharepoint (or similar) be a simpler solution?
    – dave
    May 21, 2015 at 0:29
  • 1
    What about your db server performance? Have you check memory configuration or disk performance? May 21, 2015 at 3:38
  • 1
    @ddalton you're not hooking the web UI up directly to the DB and presenting data as Oracle Forms (or whatever) are you? Do you have an application tier in between the DB and the web client? I'm still not sure how putting a BLOB in a DB column will help - you either need the data contained in the excel doc, or its just a opaque object. The latter doesn't need to be part of any display.
    – gbjbaanb
    May 21, 2015 at 15:42

6 Answers 6


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.

  • I Agree, I mean the excel doc will have 100m xml nodes in it
    – Ewan
    May 21, 2015 at 11:39
  • 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 or BLOBs and process it. This operation can be done asynchronously, so a BLOB is seems feasible. But storing it as rows gives us a lot more flexibility with future enhancements that would need to slice and dice it. But our current focus is on improving the performance. May 21, 2015 at 14:00
  • @ddalton: That's easy. Just put your files under version control or a CMS and you have transactionality.
    – Kevin
    May 22, 2015 at 2:42

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.

  • The database we are currently using is Oracle. We plan to also support an in-memory RDBMS. Ideally we need to be able to interact with the data using a web UI, but there are currently some huge performance issues with it and so we need to do all the interaction using excel. We continue to use the web UI for smaller projects. May 21, 2015 at 15:06

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.

  • We have to use our existing RDBMS, and also have to think about the in-memory RDBMS that will soon be introduced. The reason for moving from an existing wide table to a narrow table is to solve the hydration issue, i.e., We avoid hydrating the null values and so our java objects reduce the memory pressure. But the concern here is with the increase in number of rows generated. May 21, 2015 at 16:14

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...).


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.


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.

Your Answer

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

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