Hi,

I need to display a list (dataGridView) of accounts. The class Account has only 3 attributes: Id, No, Description. I need to display the No and Description in the dataGridView. Therefore I execute this HQL query: "from Account".

My problem is, the table belonging to the class Account has 25000 rows or more. I have to wait around 2 minutes till all the 25000 objects are created.

Is there a more performant way to do this with hibernate?

I could use a DataSet without using hibernate. That would be more performant, but not object oriented. Is hibernate the right way for such a query?

Thanks a lot.

Steffen

Hi Steffen!

You have to use some sort of paging technique - or better get some criteria from user to limit resultset - it is not a bug - it is just bad design - you have to avoid large resultsets in all means.

Hi,

the query returned a List. I changed it, so the query now returns an object from the type Enumerable. It is now much more performant.

Thanks.

Steffen

I really wouldn't recommend this approach. This is not the intent of enumerable. Instead I would say you should use the SetMaxResults() method to limit the number of search results returned by your query.

Enumerable will return one record at a time.

Hi,

I think jchapman is right: enumerable is worse because it'll emit some SQL for each iteration (this will only be an improvement if you are only needing a very restricted set of rows, but it won't scale well).

I believe you should focus on the real base of the problem here. For a modern database, on a non-overloaded server, 25000 rows is peanuts. For NHibernate and .NET, creating 25000 objects with only 3 attributes should also be very quick.
(of course, 'very quick' is relative, but it definitely shouldn't take 2 minutes)

I'm wondering: are you doing something in the constructor of the Account class or in one of those 3 properties that is causing such extreme delay ?

Hi!

I have a similar problem, I have to both save and return between 50 000 and 100 000 rows of profile data (input data split down to every 15 minitues for 10 years) used in simulations. I need to load everything in one go as the simulation result will be dependent on the whole time series and this is used thoughout the rest of the use case. Lazyloading parts of the time series or paging is no option.

By the way, the requirement is not worse then 30 seconds but aims for 10 seconds. This includes the calculations occurring in the simulation as well.

Each object in the collection has four columns with double values and one column with a datetime value. I guess I have the following options:
- Try to redesign ProfileData so that one object contains more data and thus reduces the number of rows to represent the same logic (It does not feel great from an OO perspective).
- Use pure jdbc and batch commits.
- Saving the collection as a blob instead, serializing the c# object (data becomes tied to c# and updating one row or querying for an interval of rows becomes impossible, gives an overhead when these scenarios occurr).
- Using a generic stored procedure.

If anyone has other suggestions for getting this down to 2 secs, then I would be very grateful.

Thanks,
Steinar.

Hello,

I think you might shave off some time using pure SQL for your SELECT and UPDATE statements. Still: saving 50.000 to 100.000 records in 10 seconds (omitting the calculations), that's a lot !
Admittedly, it isn't very OO style, but I guess you purely need the information to execute calculations and statistics, and so I don't think it's bad programming.

WARNING: PERSONAL OPINION FOLLOWING *****************
After all: good programming is about making the best design choice to solve a problem, not about sticking to 1 paradigm against all odds.
OKAY, LET'S CONTINUE *****************

You might save some time also by:
a) removing as many indexes as possible on your table (I guess you only need an index on the datetime column)
b) perform the update in 1 transaction
But I guess both a) and b) are already okay, so that won't help you a lot.

Of course, I don't know your requirements, but I would't save the whole collection in one BLOB unless you're certain you will always retrieve the collection as a whole and won't need individual records from it.
On the other hand: saving the whole collection as a BLOB and not using objects but simple value types, might just get you there.

Hi!

Just thought I should ping back an update on this issue as I've now gotten it down to 1 sec +- 0.2 secs depending on the case.

And how did you do it ?

I created a new class WeeklyProfileData that transforms the hourly or less profile data into dataseries for one week. That means storing 168 values at min and 168*4 at max in one row. Thus, for the 50 000 rows case the new number of rows are reduced to only 298!

The data is still queryable by weekly resolution, that means that we only have to load one week of time series if we are to change the data for the simulation. A whole simulation usually involves 298 or 496 week records.

This is all I had to do. A compromise between OO design and sql queryability in the DB. At least customers are happy!