Problem with big table (Oracle DB)
Hi,
We are designing a history reporting system and since the customer wished for a generic DB solution we needed to create one very big statistical table to hold statistical value information. The problem is that this table is being updated each 10 minutes with statistical information and for 3 years of statistical data we have more than 500000 records.
We have tried to use Hibernate as a persistence layer and the results are very bad since it took about 90sec just for 6 months (and the projected time is 5 seconds!). Using standard JDBC for the same period of time takes about 500sec!
Has anybody dealt with such a case before and if so can you point to some method of boosting up the process?
Thanks
# 1
Indexes, lazy loading, query optimisation, underlying hardware performance, expected performance.All topics you've completely failed to touch upon. I assume you have no idea what you're doing.
# 2
The table has two indexes: one per Date of Insertion of record and one per Statistical Entity.
And yes we use lazy initializing, but still the performance is far from expected. The queries are optimized since raw query execution takes only ~1.5sec.
The hardware underlying the system is 2 3GHz processors with 2GB RAM and half TB of disk space.
# 3
Better.
How many columns in your "very big" statistical table, how many are involved in the query?
Where does the application reside, where does the database reside, (if applicable) what connectivity do you have between the two, what database are you using, what driver are you using?
And so on.
# 4
The table has total of 15 columns, 13 are involved in the query. The server application and the DB are local to each other (based on the same machine), the DB is Oracle 10g release 1, and we are using the official Oracle driver provided for the that version/release, type 4 driver.
The problem is I think in the hibernate initialization since it has to pass trough all the rows (i.e. for two different statistical information 5920 rows) and instantiate an object for each of them.
And this is by using maximum fetch depth of 0 and maximum fetch size of row count, so 5920.
# 5
Have you enabled show_sql to see what queries Hibernate is issuing? How long do these take to run via sqlplus?
# 6
Also what do you mean by "initialization" and "statistical information"? Are you talking about a hibernate query, criteria, or some operation you're performing directly on the persistent entities?The latter would almost certainly be a terrible idea.D.
# 7
By initialization i mean that we are using hibernate queries to initialize the objects required, and as for "statistical information" that is one statistical type for which we hold data on a certain time period.
The queries take no significant execution time (~2 sec). So I can only relate the problem somewhere in the way that Hibernate initializes the persistent objects.
# 8
"The queries take no significant execution time (~2 sec)."
What queries are you talking about here? The ones that you see Hibernate executing when you have show_sql switched on, or some other set of queries that you've run against the database?
Either you've got slow queries: find out why, optimise.
Or you've got slow Java logic: optimise in the normal way.
I'm not getting the warm fuzzies about your analysis of the problem.
