I'm new here, so hello all.

I'm in the process of tuning the performance of our database (Oracle 10g) interaction via NH, which has mainly involved replacing a lot of fetch operations with HQL. There are, however, a few cases where performance remains unacceptably slow - while there are some inherent problems with the legacy database design, I can't see any obvious reason for the sluggish behaviour.

The software is essentially a web service for our utilities industry pacakge and two of the performance issues are in an aspect of the system which is heavily used and ought to be blazing fast.

1. Query a metering subsystem which has a 3 column composite-id (did I mention it's a legacy DB :P) 4 simple properties, 2 eager many-to-one associations(fetch=join) and a lazy many-to-one association. I do a simple FROM query with an INNER JOIN onto one of the associated objects which is used in the WHERE clause (it is checking to see if a given meter is already installed anywhere, in theory we don't actually need to fetch the data). NHibernate Profiler shows 1 statement being issued, fetching exactly the columns I would expect (i.e only the basic properties of the subsystem) - duration is a mere 1ms for the query itself but a total time of 4752ms elapses.

2. Querying a single meter which has an Int64 id, 20 simple properties (4 inside a component class) and 3 eager many-to-one associations to simple "lookup" items. In the HQL I am searching on an indexed field and again the query itself takes a single statement and 1ms to execute....yet NH takes 8738ms to return the object.

Since both queries are keyed on the same string (meter serial number), I wonder if that has something to do with it. On the database it is a varchar2[14], while in the hibernate mapping and class it is a String (changing it to AnsiString made no difference). They really stick out like a sore thumb in the profiler output, most operations take 1-3 ms.
I'm really struggling to see how these operations can take so long to complete. 4 seconds is a ridiculously long time to initialise a simple object with 5 values. For reference, fetching an equipment template by id is 12ms total and it is of comparable complexity to the meter object in scenario 2.

Can anyone proffer any advice? Exact HQL and mappings can be provided.