Home » RDBMS Server » Performance Tuning » Reading byte data from BLOB vs File System
Reading byte data from BLOB vs File System [message #621421] Wed, 13 August 2014 04:26 Go to next message
mafoe
Messages: 6
Registered: July 2014
Junior Member

I have the requirement to store raw data (ranging in size from 100 kb to 150 MB per file) and read it quickly when needed (in a Java application deployed on a Tomcat, with an Oracle 11g as backend).

Currently we store those files on an NFS. We thought about putting them into our database to reduce maintenance and have a central storage instead of two. My test reading a 13 MB file from NFS versus reading it from a BLOB however were sobering.

From BLOB: 2-3s
From file system: 200ms

Looking at this massive difference, the file system clearly wins.

Is there anything I am missing here? Any kind of obvious performance boost that I missed?

Here's the trivial DDL for the test table:

CREATE TABLE TEST
(
ID NUMBER
, NET BLOB
);
Re: Reading byte data from BLOB vs File System [message #621433 is a reply to message #621421] Wed, 13 August 2014 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How do you read the file from NFS? Post code.
How do you read the BLOB from database? Post code and used tool.

Re: Reading byte data from BLOB vs File System [message #621436 is a reply to message #621433] Wed, 13 August 2014 05:54 Go to previous messageGo to next message
mafoe
Messages: 6
Registered: July 2014
Junior Member
Michel Cadot wrote on Wed, 13 August 2014 12:41

How do you read the file from NFS? Post code.
How do you read the BLOB from database? Post code and used tool.



BLOB via JPA:

EntityManager em = emf.createEntityManager();
			Query nativeQuery = em.createNativeQuery("SELECT NET FROM TEST");
			Blob result = (Blob) nativeQuery.getSingleResult();
			byte[] bytes = result.getBytes(1, (int) result.length());


NFS:

Can't post code, but it's basic FileReader/BufferedReader java mechanism.

[Updated on: Wed, 13 August 2014 05:55]

Report message to a moderator

Re: Reading byte data from BLOB vs File System [message #621440 is a reply to message #621436] Wed, 13 August 2014 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In the first case, which driver do you use?
Did you investigate in changing the parameters related to LOBs?
Is your database local to the program execution or remote?
How did you compute the posted times?

Re: Reading byte data from BLOB vs File System [message #621444 is a reply to message #621440] Wed, 13 August 2014 06:53 Go to previous messageGo to next message
mafoe
Messages: 6
Registered: July 2014
Junior Member
Michel Cadot wrote on Wed, 13 August 2014 13:20

In the first case, which driver do you use?
Did you investigate in changing the parameters related to LOBs?
Is your database local to the program execution or remote?
How did you compute the posted times?




ojdbc6 : 11.2.0.3.0

I went to Oracle's pages about LOBs, but I didnt spot any significant game changers concerning performance. I didnt test NOLOGGING etc, mostly because I thought it'd be faster and more productive asking here first in case there exists a massive performance boost I am not aware of even after quick-reading that part of the manual.

The database is remote.

I computed the times with Java's System.nanoTime() before and after the code block that I posted.
Re: Reading byte data from BLOB vs File System [message #621455 is a reply to message #621444] Wed, 13 August 2014 08:16 Go to previous message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The database is remote.
I computed the times with Java's System.nanoTime() before and after the code block that I posted.


How do you know the time is not spent in the network?
Did you use a large SDU to minimize network round trips?

Have a look at Tanel Poder presentation Oracle LOB Internals and Performance Tuning

Read Database Application Developer's Guide - Large Objects, Chapter 7 Performance Guidelines. You may be interested in "Example of Retrieving LOB Access Statistics" script.

You could also get profit reading "The Java Tutorial", Using Large Objects and studying oracle.sql Class BLOB fields ad methods, especially MAX_CHUNK_SIZE.

And also in this snippet: Read BLOBs data from database.

Previous Topic: How to calculate ROI on performance gain
Next Topic: cache issue
Goto Forum:
  


Current Time: Sat Nov 17 07:03:38 CST 2018