performance problem in saving/retrieving huge blob from oracle DB using Java

Question

I am saving huge blob(500 MB) into oracle DB using JDBC. It takes a lot of time in insertion and later on retrieval.

Please suggest, if any of you have encountered this problem.

Of course, the BLOB will take a lot of time to insert and retrieve. It's 500 MB after all. You might want to specify how much time it is taking, and why it shouldn't take so long. Then, somebody might be able to help you. — Vineet Reynolds
– Vineet Reynolds, Commented Jun 12, 2011 at 11:38
one solution would be not to insert or retrieve BLOBs that large. — unbeli
– unbeli, Commented Jun 12, 2011 at 11:51
or compress the data, perhaps. But all such answers are presumptuous. — Vineet Reynolds
– Vineet Reynolds, Commented Jun 12, 2011 at 11:55
How long does it take to save the BLOB to the database? How long does it take to move a 500 MB file from the web server to the database server? — Justin Cave
– Justin Cave, Commented Jun 12, 2011 at 12:03

Gary Myers · Accepted Answer · 2011-06-12 22:32:29Z

5

Lots of non-database people are really scared of sticking BLOBs and CLOBs in databases. They shouldn't be. Oracle manages them very well. Also bear in mind that Oracle also develops file systems (including BTRFS) so know about storing all kinds of data. Data in the database can be better protected against media or system failure, secured against unauthorised access and audited for improper use.

You should be using 11g and SecureFile LOBS. This document on SecureFile performance gives guidelines to achieving performance that is as good or better than regular filesystem storage.

Of course it is worth checking what the bottleneck is first. If it is the network between the app server and the DB server then no amount of database tuning will bypass the issue.

answered Jun 12, 2011 at 22:32

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlie Martin · Accepted Answer · 2011-06-12 11:59:45Z

0

500 MB blob? Oh my God.

Look, seriously, the answer is "don't do that!" Relational databases aren't intended for that or optimized for it; the way the tables and filesystem have to be organized for indexing and searching aren't suited for that kind of big indigestable lump.

Consider, instead, creating a separate filesystem for this big lumps, and storing a pathname in the database.

answered Jun 12, 2011 at 11:59

Charlie Martin

113k27 gold badges198 silver badges268 bronze badges

6 Comments

Vineet Reynolds Over a year ago

I agree with this in principle. There's only problem though with this approach - accessing the blob is no longer a transactional activity. If the OP is fine with that, he should consider it.

Bagmita Over a year ago

@Vineet...this is out of context, I know that OP refers to the person asking the question, but what does it actually stand for?

Vineet Reynolds Over a year ago

@Bagmita, OP = Original Poster.

Charlie Martin Over a year ago

@Vineet I really really doubt that Oracle can maintain transactional integrity across a 500MB blob. But you can, using JDBC, start a unit of work, do your BAB ("big ass blob"), update your pathname, and end the UOW. You have to deal with error handling during the BAB, but the DB can handle integrity.

Gary Myers Over a year ago

Of course Oracle can maintain transactional integrity with a 500Mb BLOB. It is just data. Databases are REALLY good at handling data.

|

Collectives™ on Stack Overflow

performance problem in saving/retrieving huge blob from oracle DB using Java

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related