CMP, BLOBS and findAll

[jonmartin]

I have a CMP EJB with a blob field and a few small metadata fields (like filename, mimetype and such). Storing and retrieving works. Now I want to make a list of all the blobs, displaying metadata, like filenames, but not the actual binary data. So I use the findAll and take care not to call the getBlobData method of any of the returned local interfaces in the returned collection. However, the listing is so slow and uses so much memory I suspect binary data is loaded anyway, even if I only call the getFilename, getMimeType etc. methods.

How come? Is this standard behaviour? I'm using default values all along the way. Data is stored in MySQL and I run jboss 3.2.1. How can I make my findAll listing faster? Running a 'select filename, mimetype from blobtable' in mysql is blazing fast compared to findAll, so I'm pretty confident blob data is actually loaded.

[jonmartin]

It seems that

"<read-ahead>none<....." in jbosscmp-jdbc.xml instead of on-load (default) helps a lot, but I'm not really sure what this does. I suppose none of the beans are read in advance when there is no readahead strategy. However, as the findAll method will read all beans anyway, this shouldn't really help much, unless "none" actually works on a per-field basis, so that the binary blob field that I don't read (no calls to getBlobData() in the listing method) is left alone (until later, when the binary value is really needed).

Does anyone know what it means having no read-ahead strategy? Does it work like I think it does? Any other useful tuning parameters I should consider? Can I specify a per bean strategy, so that my other non-blob beans can be read on-load while only the blob EJB will have no read-ahead strategy?

--
Jon Martin Solaas
[e-mail]
www.objectlabs.no

[jonlee]

You should have a look at the SQL generated (by turning DEBUG on in the log4j.xml). You'll probably find that even if you are not materialising the BLOB, if you have the blob column in your select, it will slow down SQL execution on the DBMS and depending on the DBMS strategy, may be pre-cached at the DBMS for the JDBC connection. You'll need to decide on your course of action based on this investigation.

We discovered this performance issue with Postgresql so wouldn't be surprised to see it in MySQL.

[jonmartin]

Thanks, I know have two beans for the same table, one with just metadata attributes and one with the actual blob-attribute as well. Using the light version for my findAll solves the problem, no binary data loaded.

[ebende]

Hi,

I can tell the following on Read-ahead strategies.

By default a find-method returns only the primary keys of the records from the DB. (SELECT PK FROM TABLE T; is carried out). Once you invoke a method of a single CMP-EJB (from the collection you got from the find-method) the remainder of the record is retrieved. (Eg. SELECT * FROM TABLE T WHERE PK=2; is carried out).

By setting <read-ahead>on-find</read-ahead> in combination with a eager-loading group name, the find method returns besides the PK all fields defined in the group-name. (Eg SELECT PK, FLD_A, FLD_B FROM TABLE T; is carried out when the find-method is invoked, where FLD_A and FLD_B are elements of the "load group".)

Below a sample of jbosscmp-jdbc.xml with load-groups.

The read-ahead strategy can be set on bean level, but also on finder-method level. So, if you've used a read-ahead strategy of on-find by default, the "none" read-ahead strategy at finder-level will overwrite other read-ahead strategies.

I hope this will help. I hope you can give me some help in return:

I use JBoss3.2 and MySql4.0 and CMP. I have difficulties with reading BLOBs. Can you tell me what mapping I need to use?
The mapping
<java-type>java.lang.Object</java-type>
<jdbc-type>BLOB</jdbc-type>
<sql-type>LONGBLOB</sql-type>
doesn't work for me. Or do I need to do something with a "serialized object".
Hope you can give me a piece of code.

Thank,

Evert Bende
http://www.wattabout.com







sample:


<jbosscmp-jdbc> <enterprise-beans> <ejb-name>GangsterEJB</ejb-name> <load-groups> <load-group> <load-group-name>basic</load-group-name> <field-name>name</field-name> <field-name>nickName</field-name> <field-name>badness</field-name></load-group> <load-group> <load-group-name>contact info</load-group-name> <field-name>nickName</field-name> <field-name>contactInfo</field-name> <field-name>hangout</field-name> </load-group> </load-groups> </enterprise-beans> </jbosscmp-jdbc>

[ankurdotshah]

Hi,

I would like to know how to store and retrieve blobs from the database using Jboss/CMP/MySQL combination. I have a servlet that is suppose to first write the BLOB to the database and the same servlet later needs to retrieve the blob and send it to the client. The servlet always talk to the CMP to achieve whatever it needs. The question is, what should be my jbosscmp configuration. My default standard jobss configuration maps BLOBS to JAVA_OBJECT. but given this how do i achieve what i need to. what should the setters and getter look like (is it going to store and retrieve a byte array or a searlized object.

Any help will be greatly appreciated.