3

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.

  • 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 '15 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. – codedabbler May 20 '15 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 '15 at 0:29
  • 1
    What about your db server performance? Have you check memory configuration or disk performance? – Juan Carlos Oropeza May 21 '15 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 '15 at 15:42
4

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 '15 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. – codedabbler May 21 '15 at 14:00
  • @ddalton: That's easy. Just put your files under version control or a CMS and you have transactionality. – Kevin May 22 '15 at 2:42
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.

  • 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. – codedabbler May 21 '15 at 15:06
0

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. – codedabbler May 21 '15 at 16:14
0

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

0

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.

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.