I have some problem that need to know about CMP Entity Bean.
Now my project has one table contains about 150 columns that cannot be normalized. This table need to be updated about 100 records every 5 minutes all day.
This table contains critical data in factory.
So, please give me the solution to handle this table.
For other tables I use CMP Entity Bean to handle, map one-to-one.
Thank you so much.
In theory, yes. In practice, it depends on your server, your database and many other factors.
I suggest you build a CMP entity and run it through some tests to see if it performs well enough. If it does, good. If not, update these tables through straight JDBC (which can be better optimized).
I have heard that there can be problems when mapping entity beans to tables with more than 32 columns. But there is nothing to stop you from having multiple EJB's accessing different columns in the same table. (IIRC, CMP has always allowed this - the problem has been going the other way and mapping the same bean to different tables).
So you could still have a good, decoupled, well encapsulated CMP EJB design even with one table. If you are ever in a position to review the DB architecture in the future, it should only require changing the RDBMS deployment descriptor.
I just check the database design and got big surprise that this table contains about 400 columns!!! not 150....
This project uses Oracle9i database, IBM WebSphere App Server, MQSeries runs on a very big server.
Should I split this CMP Entity Bean to more than one?
Assuming your use CMP Entities, splitting them up may be your best bet. Use the same Primary Key for each CMP, and try to arrange all the other fields into logical groups.
I still suggest you do some experiments before you commit yourself, though. Given the high rate of updates against this table, you may be forced to manage the database interactions yourself via JDBC to get the desired throughput.
A table with 400 columns is nothing more than a clear indication that whoever designed that schema did not know what they were doing. C'mon, 400 columns?!? Is that really necessary? I highly doubt it. I'm sure that the table can be split across several 1-to-1 mappings with new tables to make life easier for DBA's, programmers, etc.
Remember that a good data model is key to a successful software project since many things depend on the database and it's data structure.
Forget about CMP/BMP mappings for the moment and worry about the table/column size issue. I would. The company at which I work made a wrong decision regarding data modeling about 12 months ago, and to this day it causes problems in the database, in our code, and pretty much causes headaches all around for everyone.
Thank you for all advice. This system is very very complex and is a real-time and has high volumn of transaction. Each record in this table contains data for one complicated equipment. I don't know why the old IT team designed like this. The previous team doesn't use database system, they kept data in file system.
According to all advice, I think I should discuss with current db design team about this table and ask them to redesign schema. And I'll design this application and testing to measure performance in many dimension.
Thank you so much,