none
Bad performance when using POCO RRS feed

  • Question

  • Hello,

    I'm using EF in combination with POCO. I have an entity which has a relation to a transaction entity. Every time when my entity changes a relating transaction is created. For some instances we have about 30.000 transaction items. When I execute the following statement:

    Ors ors = ctx.Ors.Include(

    "OrsTransaction").Where(o => o.GUID == orsGuid).FirstOrDefault();

    This statement takes over 30 seconds to execute when I use POCO. When I use the standard EF configuration it's ready within 5 seconds.

    It's not the database, the database doesn't have a problem to return 30000 records. It looks like the time is taken by updating the relations of the entity and transaction entity for POCO. It doesn't matter when I turn off proxy generation:

    ctx.ContextOptions.ProxyCreationEnabled =

    false;

    Can anybody give an explenation and help me to solve it.

    Thanks!


    rt
    Wednesday, June 22, 2011 3:01 PM

All replies

  • Hi Rene,

    Welcome!

    According to your description, I think you can try to use Query() method. The Query method provides access to the underlying query that the Entity Framework will use when loading related entities. (http://blogs.msdn.com/b/adonet/archive/2011/01/31/using-dbcontext-in-ef-feature-ctp5-part-6-loading-related-entities.aspx)

    the Poco class will do some more works than edmx. There is a power tool here can improve the performance too: http://blogs.msdn.com/b/adonet/archive/2011/05/18/ef-power-tools-ctp1-released.aspx

    BTW, AsNoTricking can also improve the performance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, June 23, 2011 7:13 AM
    Moderator
  • Hello Alan,

    thanks for your answer. The links were very helpfull but I can't see how they solve my issue. If I would do the following with POCO

    var princess1 = context.Princesses
                            .Where(p => p.Name == "Cinderella")
                            .Include(p => p.Unicorns)
                            .FirstOrDefault();

    and when Cinderalla would have 25.000 Unicorns it will take about half a minute for the statetement to execute. I think it is because the navigation properties have to be updated. The query it self runs only a few seconds. When I do the same with default EF it only takes 3 seconds before the statement is ready.

    The following would also take only a few seconds within POCO (note: the second statement uses a different context):

    var princess1 = context.Princesses
                            .Where(p => p.Name == "Cinderella")
                            .Include(p => p.Unicorns)
                            .FirstOrDefault();

    var unicorns = context2.Unicorns
                            .Where(u => u.Princess.Name == "Cinderella")
                            .FirstOrDefault();

    When you use two different contexts everything is fine because the navigation properties won't have to be updated.

    Your suggestion to use Query() doesn't solve my problem since I wont to load the princess and the unicorns.

    Thanks,

    René

    

     

     


    rt
    Thursday, June 23, 2011 1:36 PM
  • Hello,

    AFAIK, in some cases, POCO snapshot tracking is more costly than instant notification tracking. Please check here. http://msdn.microsoft.com/en-us/library/gg696163(v=VS.103).aspx   

    From you mentioned, my option is you can disjoint sets of tables and create some different ones instead of a single one and try it again.

    Hope this helps.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, June 24, 2011 9:11 AM
  • Larcolais thanks,

    please explain what you mean with: my option is you can disjoint sets of tables and create some different ones instead of a single one and try it again.

    Do you mean to use two different contexts? is it possible to load entities in the same context and tell the EF to not relate the entities (so the navigation properties will stay null)?

    Thanks,

    René


    rt
    Friday, June 24, 2011 11:18 AM
  • Hello again,

    There’re some possibilities which can cause POCO performance in my previous provided link.

    ·         When loading metadata, the cost of discovering POCO or proxy types is greater than the cost of discovering attributed EntityObject derived types.

    ·         There is some overhead involved in generating dynamic POCO proxy types.

    ·         Initializing POCO types involves the least overhead compared to proxy and EntityObject derived types.

    ·         In some cases, POCO snapshot tracking is more costly than instant notification tracking. For example, when working with large object graphs.

    So, we want to know if your concern was happened at first time or every time when it was loaded. It looks that your question is related with a large model when I looked your thread first time. Honesty speaking, I didn’t know if your object graphs (table relationships) was large or not, but I suggest you can try to use lazy loading or eagerly loading instead of Include method in your project and try it again.

    Hope this helps. If you have any finding, welcome to post back.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, June 27, 2011 8:10 AM
  • Larcolais thanks again,

    lazy loading or eaferly loading doesn't solve the problem.

    When I load the unicorns for a princess it always takes over a minute. It doesn't matter if I do an include, lazy loading or eagerly loading. It looks like the time is taken to fill the navigation properties. Note: this only happens when there a huge amount of unicorns, for example over 30.000.

    When I do the same with the default EF, the query exexutes within 5 seconds.

    Do you have an example somewhere (a download somewhere which is an example of how you should work with POCO)? I can see if I can reproduce the problem within this example, if so you could have a look at it.

    Thanks,

    René  


    rt
    Tuesday, June 28, 2011 7:17 AM
  • Hi René,

    One test you may try is to create a loop that would add 30.000 new unicorns to a collection, without going to the database - it will give an idea of how long it takes.

    You may also use AdventureWorks to create, for say, a special sales order with 30.000 rows, and then build a model with just SalesOrderHeader and SalesOrderDetails.

    A simple T-SQL script to add a new SalesOrderHeader with 30.000 SalesOrderDetails could be:

     

    INSERT INTO [AdventureWorks].[Sales].[SalesOrderHeader]
    (
    [RevisionNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
      ,[Status]
      ,[OnlineOrderFlag]
      ,[PurchaseOrderNumber]
      ,[AccountNumber]
      ,[CustomerID]
      ,[ContactID]
      ,[SalesPersonID]
      ,[TerritoryID]
      ,[BillToAddressID]
      ,[ShipToAddressID]
      ,[ShipMethodID]
      ,[CreditCardID]
      ,[CreditCardApprovalCode]
      ,[CurrencyRateID]
      ,[SubTotal]
      ,[TaxAmt]
      ,[Freight]
      ,[Comment]
      ,[rowguid]
      ,[ModifiedDate])
    SELECT [RevisionNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
      ,[Status]
      ,[OnlineOrderFlag]
      ,[PurchaseOrderNumber]
      ,[AccountNumber]
      ,[CustomerID]
      ,[ContactID]
      ,[SalesPersonID]
      ,[TerritoryID]
      ,[BillToAddressID]
      ,[ShipToAddressID]
      ,[ShipMethodID]
      ,[CreditCardID]
      ,[CreditCardApprovalCode]
      ,[CurrencyRateID]
      ,[SubTotal]
      ,[TaxAmt]
      ,[Freight]
      ,[Comment]
      ,NEWID()
      ,[ModifiedDate]
     FROM [AdventureWorks].[Sales].[SalesOrderHeader]
     WHERE [SalesOrderID] = 51721;
    

    and then

     

     

    INSERT INTO [AdventureWorks].[Sales].[SalesOrderDetail]
    (
    	[SalesOrderID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[rowguid]
      ,[ModifiedDate]
    )
    SELECT TOP 30000 75124 AS [SalesOrderID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,NEWID()
      ,GETDATE()
     FROM [AdventureWorks].[Sales].[SalesOrderDetail]
    

    Just replace 75124 in the above script with the SalesOrderID of the order header created before, which you can see with a :

     

    SELECT TOP(1) * FROM [Sales].[SalesOrderHeader]
    ORDER BY [SalesOrderID] DESC
    

    It may not totally answer your question, but anyone can use it to have a fair idea of what it takes to fill up a collection with 30.000 POCO objects.

    Not a best practice in my opinion, especially if you just need to query it to retrieve one or a few instances.


    Tuesday, June 28, 2011 9:08 AM
  • Ok, I'm a step further now.

    First I totally agree that you shouldn't load all childs for a parent when you don't need them.

    But that wasn't really the issue. This is my code:

     static void Main(string[] args)
    
      {
    
       using (AdventureWorksEntities ctx = new AdventureWorksEntities())
    
       {
    
        SalesOrderHeader header = ctx.SalesOrderHeaders.Where(h => h.SalesOrderID == 75124).FirstOrDefault();
    
        SalesOrderDetail detail = new SalesOrderDetail();
    
        detail.SalesOrderHeader = header;
    
    
    
        ctx.SalesOrderDetails.Add(detail);
    
    
    
        ctx.SaveChanges();
    
        
    
        Console.ReadKey();
    
       }
    
      }
    
    

    So I get one header, then I create an order detail, I add this to context and then I do a save of the context.

    This works fine for standard EF.

    When I use the ADO.NET POCO Entity Generator the line where I add the detail to the context takes over 30 seconds. With sql profiling I see that at this point all details are retrieved from the database (this only takes a few seconds). I think that for some reason all navigation properties are filled next which takes over 30 seconds.

    When I use the ADO.Net DbContext Generator which also generates POCO then it performs well again.

    So it looks like a bug in ADO.NET POCO Entity Generator.

    So I'm a bit confused. In MSDN I see that EF supports POCO but it only describes how to implement POCO yourself. Next you have generators which are not part of VS like ADO.NET POCO Entity Generator and ADO.Net DbContext Generator which are seperate downloads. You can hoewever download them from Microsoft sites:

    http://visualstudiogallery.msdn.microsoft.com/23df0450-5677-4926-96cc-173d02752313 (ADO.NET POCO Entity Generator)

    http://blogs.msdn.com/b/adonet/archive/2011/05/18/ef-power-tools-ctp1-released.aspx (ADO.Net DbContext Generator)

    Which one should I use? What are the differences? Which one is supported my MS?

    Thanks,

    René


     


    rt
    Wednesday, June 29, 2011 11:11 AM
  • Based on my experience, bad performance was a relative conception for different scenarios. Even you used technical EF, you also meet some performance considerations like: http://msdn.microsoft.com/en-us/library/cc853327.aspx

    Here's a objective testing for author environment, but I also suggest you can check it. http://www.codeproject.com/KB/database/EFPerformance.aspx

    Thanks,

    Esters,


    Just a newbie for everything.
    Friday, July 1, 2011 5:23 AM
  • Thanks Esters,

     

    I still wander when I should use ADO.NET POCO Entity Generator and when ADO.Net DbContext Generator. I also would like to know if MS is fully supporting these generators.

     

    Regards,

    René 


    rt
    Friday, July 1, 2011 11:04 AM
  • This was not the correct answer. For the correct answer see the post from Ladislav below.

    ADO.Net DbContext Generator is meant to be used to generate code when working with DbContext (CodeFirst approach, Entity Framework 4.1) while ADO.Net POCO Entity Generator is meant for ModelFirst or Database approach where you are using ObjectContext API. Although the entity classes look mostly the same the contexts generated from these templates are different and derive from DbContext and ObjectContext respectively.

    Pawel


    Tuesday, July 5, 2011 10:28 PM
  • @Pawel:

    ADO.NET DbContext Generator is not for code-first approach. Code-first is when you write a mapping in your code first without using EDMX. ADO.NET DbContext Generator is for Model and Database first. The only difference with ADO.NET POCO Generator is that it uses DbContext API instead of ObjectContext API.

    Best regards,
    Ladislav


    Wednesday, July 6, 2011 7:26 AM
  • Thanks for correcting me Ladislav. I edited my post above to avoid confusion.

    Pawel

    Wednesday, July 6, 2011 4:49 PM
  • Hi,

     

    the following question still hasn't be answered:

     

    I still wander when I should use ADO.NET POCO Entity Generator and when ADO.Net DbContext Generator. I also would like to know if MS is fully supporting these generators.

    Thanks,

    René


    rt
    Thursday, July 7, 2011 8:14 AM
  • I think If you want to use the new features of 4.1 code first, you can use DbContext Generator, otherwise you used the EF4.0 ObjectContent.
    I am fish.
    Monday, July 11, 2011 12:43 PM
  • Hi,

    I found something on MSDN http://msdn.microsoft.com/en-us/data/hh949853.aspx

    "[

    When a POCO entity does not have a change tracking proxy, changes are found by comparing the contents of your entities against a copy of a previous saved state. This deep comparison will become a lengthy process when you have many entities in your context, or when your entities have a very large amount of properties, even if none of them changed since the last comparison took place.

    In summary: you’ll pay a performance hit when creating the change tracking proxy, but change tracking will help you speed up the change detection process when your entities have many properties or when you have many entities in your model. For entities with a small number of properties where the amount of entities doesn’t grow too much, having change tracking proxies may not be of much benefit.

    ]"

    Maybe this can explain the bad performance you had using POCO and EF

    Wednesday, March 27, 2013 4:38 PM