0

I am currently having a performance problem with the following query written in NHibernate. I am trying to transform the data I queried into DTO's. With this complex structure I cannot use QueryOver to transform the entities. On the other side Linq provider is so useful but it takes ~10 seconds to load and transform ~6000 entities with each 30 child items. It creates an SQL query with left outer join. Are there any other ways to write this query with a better approach? 

var Entities = session.Query<crmEntity>()
         .Where(x => x.EntityType.ID == EntityType)
         .Select(entity => new EntityDTO()
         {
             ID = entity.ID,
             EntityType = entity.EntityType.ID,
             InstanceID = entity.Instance.ID,
             Values = entity.Values.Select(
               value => new CustomFieldValueDTO()
             {
                 ID = value.ID,
                 FieldID = value.Field.ID,
                 Value = value.Value
             }).ToList<CustomFieldValueDTO>()
         }).ToList();
Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
iboware
  • 977
  • 1
  • 12
  • 25
  • Are you sure you're eager fetching `Values`? It doesn't look like so in posted code. – Ortiga Sep 11 '14 at 21:11
  • Yes it is working eagerly. HasMany(x => x.Values) .KeyColumn("Entity") .Cascade.AllDeleteOrphan() .Fetch.Select() .BatchSize(1000); – iboware Sep 11 '14 at 21:29
  • Also when I add Fetch(x=>x.Values) before ".Select(..."It takes ~20 seconds. – iboware Sep 11 '14 at 21:31
  • One suggestion would be to change your `Values` type from `List` into `IEnumerable` and remove the `ToList()` call. This will speed up your conversion a bit. – Alex Barac Sep 11 '14 at 21:32
  • Thanks I'll try it. Is it possible to write the same query in QueryOver or Criteria API's of NH? – iboware Sep 11 '14 at 21:34
  • I am affraid, that you are using NHibernate/orm a bit non standard way. Let's say, if that all would tak *100 ms* would it be ok? In your case, that would mean to return 1/100 of your 6000 set. I.e. 50 records, which could be called "standard page size". Other words. If you want to export lot of stuff, change the approach. Do the most simple SELECT statements (no join) ... create objects ex post in memory your self. That would be my suggestion... – Radim Köhler Sep 12 '14 at 05:35
  • Yes, I know it is a non-standard way, but that scenario is also an exceptional one and requires it. What I want to do is to improve the query and reflection performance in the given conditions. Otherwise paging seems to be the easiest and the most consistent solution. Thanks! – iboware Sep 12 '14 at 11:02
  • A few more questions: How long does it take to run the query directly in DB? Have you tried using a stateless session? Have you tried eager fetching [without join, using future](http://stackoverflow.com/questions/5814390/eagerly-fetch-multiple-collection-properties-using-queryover-linq)? (this last technique may reduce returned data, but I believe it does not work with stateless session) – Ortiga Sep 12 '14 at 15:25
  • Thanks! I tried future + AsEnumerable also fetching, stateless session etc... Unfortunately none of them solved my performance issue. I decided to write it in HQL and then write a custom result transformer. Now the performance is boosted. It takes less then 1 seconds on the server! :) – iboware Sep 14 '14 at 00:09

1 Answers1

0

Here is my solution. if there is any other better way, I am completely open to it:

  session.CreateQuery(@"select vals.ID, 
                               vals.Field.ID,
                               vals.Value,
                               ent.ID 
               from crmEntity ent inner join ent.Values vals
               with vals.Value IS NOT NULL
               where ent.EntityType.ID=:eID and ent.Instance.ID=:instanceID order by ent.ID")
  .SetGuid("instanceID", InstanceID)
  .SetGuid("eID", EntityType)
  .SetResultTransformer(new EntityListTransformer()).Future<ReadOnlyEntityDTO>();

And this is my custom result transformer to get the same hierarchy like my linq query

public class EntityListTransformer : IResultTransformer
    {
        private List<ReadOnlyEntityDTO> list;
        private ReadOnlyEntityDTO lastEntity;
        private Guid instanceID;

        public EntityListTransformer()
        {
            list = new List<ReadOnlyEntityDTO>();
            lastEntity = new ReadOnlyEntityDTO();
        }

        public System.Collections.IList TransformList(System.Collections.IList collection)
        {
            return list;
        }

        public object TransformTuple(object[] tuple, string[] aliases)
        {
            string ValueID = tuple[0].ToString();
            string FieldID = tuple[1].ToString();
            string Value = (string)tuple[2];
            string EntityID = tuple[3].ToString();


            if (lastEntity.ID != EntityID)
            {
                if (lastEntity.ID != null)
                {
                    list.Add(lastEntity);
                }


                lastEntity = new ReadOnlyEntityDTO()
                {
                    ID = EntityID
                };
            }

            lastEntity.Values.Add(new ReadOnlyCustomFieldValueDTO()
            {
                FieldID = FieldID,
                ID = ValueID,
                Value = Value
            });


            return tuple;
        }
    }
iboware
  • 977
  • 1
  • 12
  • 25