one hibernate entity many tables
hi ,
i have a large DB (60gb+) of stock option in 1 minute intervals so it's a very large DB and growing daily 1 month of data generates approx 0.5Mil rows
(480min*50options*20tradingdays) untill lately i've used a file XML DB but it's getting out'a hand so i've decided to move up to mysql and hibernate
before i used to open trough folders
year->month>day->optionId pretty straight forward but now i can't figrue out how to make same hibernate entity point to diffrent tables (even tough they are all the exact same tables just diffrent names) i'm aware of @secondrytable but we're talking about tens and not it won't take long to become hunderds of tables so i need a clever way to make hibernate switch it's reference i've tried indexing it all to one huge table when index is optionId+DateTime as PK but i don't think it's fast enough ....
any ideas how to overcome this issue ? (keep in mind i'm not building something with endless means this is a private trading system)
thanks in advance .
[1067 byte] By [
Kernel_77a] at [2007-11-27 7:56:48]
# 1
> move up to mysql
MySQL has some benefits but also some important limitations particularly with respect to data integrity - please make sure you understand them before proceeding.
> how to make same hibernate entity point to diffrent tables
I don't follow why you need to - why can't you keep the data in a single table with a suitable key used to partition it? It sounds suspiciously like you're trying to optimise a non-existant performance problem.
> i don't think it's fast enough ....
This is the bit that particularly worries me. Either it's fast enough or it isn't - where's the uncertainty coming from?
# 2
i wasn't aware of the partition option i've been reading about it today and it seems like a good solution thanks , tough i think i will have a problem because i need to set fetch=fetchtype.eager since 1 day of data includes a list of 480 samples i hope it will be fast enough , from what i've been reading there's no way to change an entity's refrence after compiling .
it isn't slow with a capital s but it's certinatly not the speed i was hoping it will be , maybe i'ts because i'm not indexing it right tough i can't think of a better index (with the columns i have) of optionId+timedate ...
thanks for your help mate .
# 3
Ok, basic performance recommendations:
Make your entities as narrow as possible. If you have one table with a gazillion columns, you've probably got a bad database design.
DO NOT use fetch type eager until you have proven that lazy isn't working for you.
If you need to do data intensive calculations, consider writing stored procedures. It's what they're for and it removes the need to send all that data over the wire.
I don't understand why you think that the fact that "1 day of data includes a list of 480 samples" requires you to set eager fetching - the vagueness of the explanation leads me to suspect that you've misunderstood something.
The statement that "there's no way to change an entity's refrence after compiling" makes absolutely no sense. I have no idea what you're talking about here.
Again, if you're not performance profiling it you have no idea what the speed limitations are and where the bottlenecks are. Don't optimise until you know what you're optimising - guessing usually creates poorer performance in either the short or the long term.
# 4
class optionSampleIntrDay {
int paperId
java.sql.date time
String paperName
double price
double volume
double totalTransActions
}
class currentOption{
java.sql.date begin
java.sql.date end
list<optionSampleIntrDay> dailyTrades
}
those are basicly the classes , how can i not set fetchtpe to eager when i try to do that without it i get an exception
i realy don't understand what isn't udnerstood about changing an entity's refrence during runtime , i have one class for example optionSampleIntrDay
which is as i said a table that adds 480rows per option daily and i keep data for 50 options a day i wanted to keep data per month or per option in seperate tables but the tables are the same (same columns) so i wanted to load a diffrent table to the entity according to the desired data .
but if i understood partition right it doesn't matter and partition will do the same for me minus the bother of changin the refrence .
# 5
> those are basicly the classes , how can i not set
> fetchtpe to eager when i try to do that without it i
> get an exception
A LazyInitializationException? Thought so, you haven't understood what's going on here. This means that the entity is now detached from the session - you need to leave it attached to the session so that IF the lazy assocation is required THEN it can be loaded. The only reason to use eager fetching is if you're never going to be attached to the session (usually a bad idea), or if you are going to require ALL of the members of the association ALL of the time.
Although the latter case is possible, it's unusual. In the cases where it's necessary, you need to be super careful that you're not bringing in unwanted parent entities.
My guess is you don't need all of these entities most of the time, but you're loading them anyway, hence you have a performance problem.
> i realy don't understand what isn't udnerstood about
> changing an entity's refrence during runtime , i have
> one class for example optionSampleIntrDay
Wht does it have a lower case letter at the start of the name if it's a class? Are you sure it's not a property? I'll assume it's a class.
> which is as i said a table that adds 480rows per
> option daily
I assume you mean that there the class is mapped to the table and that something (what?) adds 480 rows per option to this table. Right? Because that's not quite what you said.
> and i keep data for 50 options a day i
> wanted to keep data per month or per option in
> seperate tables
Probably a bad idea. But not impossible.
> but the tables are the same (same
> columns) so i wanted to load a diffrent table to the
> entity according to the desired data .
Ok. But that's a mapping concern. You can do this in theory, but it's hairy and a bad idea for all sorts of reasons.
> but if i understood partition right it doesn't matter
> and partition will do the same for me minus the
> bother of changin the refrence .
I'm not positive you and I mean the same thing by "partition" because we don't seem to mean the same thing by "reference."
A reference is a Java data type. You don't seem to be talking about references at all.
Please don't use terminology loosely. Explain your terms in plain English, or use technical terms accurately - but if you try to mix the two you're just being confusing.
