Hibernate version: 1.2
MySQL 5
Winform App
Vb.net 2.0

I'm running into some performance issues when executing queries that return a large number of rows (objects). To make things much faster the first thing i did was create a 'lite' version of my class - this worked great. Unfortunately, when loading thousands of these lite objects it can take quite some time. I want the user to be able to cancel this load.

What is the best way to approach this?

I'm thinking of spawning a worker thread and then if user wishes to stop the load abort the thread with thread.abort().

Does this sound like a plausible approach? Any one else have a better idea?




[/b]

Did you try to use eager fetching for this kind of query?

http://www.hibernate.org/hib_docs/refer ... mance.html

=> 19.1.2. Tuning fetch strategies

Yes, it does not have much of an effect.

Just over 8000 records takes around 17 seconds. While i would love for my queries to be faster i can always see the need for cancelling a search or database query

There's a cancelQuery-method on the Session-object, which can be called from a "let me stop it"-thread:

http://www.hibernate.org/hib_docs/v3/ap ... ncelQuery()

We were just aborting the background thread, so I tried changing our code to call ISession.CancelQuery(). I noticed a few issues:

1. With Thread.Abort(), our code running the background thread could catch exceptions and detect ThreadAbortException. With ISession.CancelQuery, we get a database-vendor-specific exception message -- using SQL Server, we get something like "SqlException: operation canceled by user". This is a lot messier to detect. It would be nice if NHibernate wrapped this exception in a specific, database-vendor-independent one of its own to clearly and reliably indicate that the query was canceled.

2. I ran into the case where the query I tried to cancel had actually completed at the database level by the time I attempted to abort, so ISession.CancelQuery() had no effect. NHibernate had started the process of instantiating the tens of thousands of entities returned by the query. This ate up a lot of local processor time, which I couldn't cancel out of.

Can someone explain why it's not a good idea to just abort the worker thread in order to cancel a query? If it's just that the query may still be running on the database server, then we'll try setting a timer after CancelQuery() returns, and if the worker thread isn't done within a few seconds more, then abort the worker thread. This should limit the waste instantiating a ton of entities from an already-completed database query ...

you should not use thread.abort() it's not so good
check out the msdn on threads for best practices
cheers

isn't there a batch attribute that allows you to load a subset of records? say your batch is 1000 records. then 8 queries would be executed at the time they are required. even if you foreach the collection. it won't load the next batch until you reach batchSize + 1.