Nhibernate Group By into Sum > Slow materialization

I’m having an issue with querying the database for some data with NHibernate, grouping it and summing up various columns. The summing and grouping is done in the query and takes a minimal amount of time, yet NHibernate takes a very long time to create objects from it, 3 minutes with a small amount of data which is instantaneous for SQL to handle.

This is the C# code which goes through NHibernate:

return from actual in session.Query<Actual>()
        from contract in session.Query<Contract>()
        where contract.Customers.Contains(Actual.Customer)
        where _dateRange.End > contract.StartDate && _dateRange.Start < contract.EndDate
        where contract.Market == null || _employee.Markets.Contains(contract.Market)
        where actual.Date >= contract.StartDate && actual.Date <= contract.ContractClosedDate
        where actual.Date >= _dateRange.Start && actual.Date <= _dateRange.End
        group actual by actual.Customer into grouping
        select new CustomerModelForDocumentGeneration(
                                                      grouping.Sum(ca => ca.Sales),
                                                      grouping.Sum(ca => ca.Profit),
                                                      grouping.Sum(ca => ca.Contribution));

This generates an SQL query like this:

select actual.CustomerId                                             as col_0_0_,
       cast(sum(actu0_.Sales) as DECIMAL(19, 5))                     as col_1_0_,
       cast(sum(actu0_.Profit) as DECIMAL(19, 5))                    as col_2_0_,
       cast(sum(actu0_.Contribution) as DECIMAL(19, 5))              as col_3_0_
from   Actual actu0_,
       Contract contract1_
where  contract1_.SystemType in ('1', '5', '3')
       and (actu0_.CustomerId in (select cust3_.Id
                                        from   ContractCustomers custs2_,
                                               Customer cust3_
                                        where  contract1_.Id = custs2_.ContractId
                                               and custs2_.CustomerId = cust3_.Id))
       and '2015-12-31T23:59:59.00' /* @p0 */ > contract1_.StartDate
       and '2014-01-01T00:00:00.00' /* @p1 */ < contract1_.EndDate
       and (contract1_.MarketId is null
             or contract1_.MarketId in ('716c319b-cb75-4460-91d2-eb5798d453ed' /* @p2 */, 'abf872da-85c4-4f30-883f-eede4bbaf082' /* @p3 */, '10a4dc59-5b67-4016-ab03-038ae0b37cf8' /* @p4 */, 'ba0c422e-ac40-4b30-b366-7d7e7a6c0f0b' /* @p5 */,
                                              '23a3d80a-349c-463a-a566-993bdc556ad5' /* @p6 */, '990d887a-01ee-4039-a5ae-bcb0a24a552e' /* @p7 */, '72d3136c-86c6-4a5f-a872-94748353b60b' /* @p8 */, 'c6ae8c44-ced7-4055-9f6b-ded7421adc8d' /* @p9 */,
                                              'b7d4d42c-a558-432a-aec4-e30f543985b6' /* @p10 */, 'b69d7a9a-b796-4d5f-b908-ed57c54f3d2a' /* @p11 */, '9dfa6b90-2167-495f-b698-53681248dde1' /* @p12 */, '8260263a-a83d-442e-a9a7-0864676bd60f' /* @p13 */,
                                              '53a065b6-ca27-4890-825d-32120da41996' /* @p14 */, 'a3fe8098-312e-402d-ae20-32aa806e9949' /* @p15 */, '4b69dc6f-03c5-4678-87d6-708aa5795718' /* @p16 */, '16b7e39e-0136-4118-87dc-804503a356dc' /* @p17 */,
                                              'c037b574-492a-49c8-b3be-86d57b637d5b' /* @p18 */, '721c8381-4e83-491a-a1be-89db90142b6b' /* @p19 */, '6c8a105c-cb8a-47f6-a6c6-de3c65537869' /* @p20 */))
       and actu0_.Date >= contract1_.StartDate
       and actu0_.Date <= contract1_.ContractClosedDate
       and actu0_.Date >= '2014-01-01T00:00:00.00' /* @p21 */
       and actu0_.Date <= '2015-12-31T23:59:59.00' /* @p22 */
group  by actu0_.CustomerId

After the query is run, NHibernate hibernates deep inside its code for a long amount of time, even though all the actual work should have been done server side already. If you remove the group summing from the query, the models are generated immediately instead.

Source: mysql

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.