I have a Criteria that I am using to do an export of data. I can run the generated sql in query analyzer (I have a SQL Server 2005 db) and it returns in less than 20 seconds which is acceptable for my export data and expected because the query involves several complicated database views.

However, when the Criteria is ran on my app server, it takes several minutes for that one query to execute. I can see in the debugger that it takes this long from the time I call criteria.list until it returns.

I have no idea what could be causing this big of a performance difference and would appreciate any suggestions that I could try to improve it. My only guess would involve object creation but my query only returns <200 rows and there aren't that many joined objects that would be created.

Here is the sql statement and log statements that follow it. Note that I changed the select clause to "select *" rather than a select list to conserve space.

UPDATE: I tried changing the most expensive view that was joined into this query to lazy load rather than eager load and on a small result set saw huge gains (even though it caused more queries -- I do have batch fetching turned on so it wasn't a lot more queries). There don't seem to be gains as much on a large result set. I still don't get why I can execute the query with the joins on the database so much quicker than in the app server.

select *
from
Position this_
left outer join
CachedCompensation cachedcomp5_
on this_.positionId=cachedcomp5_.cachedCompensationId
left outer join
CompExportView compexport6_
on this_.positionId=compexport6_.compExportViewId
left outer join
EmployeeQuartile employeequ7_
on compexport6_.quartile=employeequ7_.employeeQuartileId
left outer join
Incentive incentive8_
on compexport6_.incentiveId=incentive8_.incentiveId
left outer join
Salary salary9_
on compexport6_.salaryId=salary9_.salaryId
left outer join
Employee currentemp1_
on this_.employeeId=currentemp1_.employeeId
left outer join
Person person11_
on currentemp1_.personId=person11_.personId
left outer join
Location location12_
on this_.locationId=location12_.locationId
inner join
Organization orgalias3_
on this_.organizationId=orgalias3_.organizationId
left outer join
SystemLookup budgetstat2_
on this_.budgetStatusId=budgetstat2_.systemLookupId
where
(
(
(
currentemp1_.corporateId=?
and this_.startDate>=?
)
and this_.startDate<=?
)
and budgetstat2_.lookupShort in (
?, ?, ?
)
)
and (
orgalias3_.leftExtent between ? and ?
and not this_.positionId in (
?, ?
)
)
2007-09-18 15:09:53,778 DEBUG [org.hibernate.jdbc.AbstractBatcher] preparing statement
2007-09-18 15:09:53,778 DEBUG [org.hibernate.type.StringType] binding 'P999999' to parameter: 1
2007-09-18 15:09:53,778 DEBUG [org.hibernate.type.CalendarType] binding '2007-01-01 00:00:44' to parameter: 2
2007-09-18 15:09:53,778 DEBUG [org.hibernate.type.CalendarType] binding '2007-12-31 23:59:44' to parameter: 3
2007-09-18 15:09:53,778 DEBUG [org.hibernate.type.StringType] binding 'Budgeted' to parameter: 4
2007-09-18 15:09:53,778 DEBUG [org.hibernate.type.StringType] binding 'Budget Exception' to parameter: 5
2007-09-18 15:09:53,778 DEBUG [org.hibernate.type.StringType] binding 'Budget Replacement' to parameter: 6
2007-09-18 15:09:53,778 DEBUG [org.hibernate.type.IntegerType] binding '1581' to parameter: 7
2007-09-18 15:09:53,778 DEBUG [org.hibernate.type.IntegerType] binding '4136' to parameter: 8
2007-09-18 15:09:53,778 DEBUG [org.hibernate.type.LongType] binding '19631' to parameter: 9
2007-09-18 15:09:53,778 DEBUG [org.hibernate.type.LongType] binding '6106' to parameter: 10
2007-09-18 15:10:55,630 INFO [STDOUT] Cleaning thread removed 1 expired object from ObjectPool
2007-09-18 15:12:04,023 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open ResultSet (open ResultSets: 0, globally: 0)
2007