none
Creating EF object results in long-running query RRS feed

  • Question

  • Hello:

    I have a database schema that includes tables Orders, Options [Id, OptionGroupId, ...], OrderOptions [Id, OptionId] and an associative table OrderSheetOptions [OrderSheetId, OrderOptionId].

    My C# code is creating an OrderOption EF object (an object that maps to the database table):

                // Select all default options
                var options = Sheet.OptionGroups.Select(og => og.Option);
    
                foreach (var option in options)
                {
                    // Add all default options
                    OrderOptions
                        .Add(new OrderOption
                            {
                                Option = option
                            });
                }

    The problem seems to arise because I am setting the Option property of OrderOption when it gets created.  This results in EF running this query:

    exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[OptionId] AS [OptionId]
    FROM [dbo].[OrderOptions] AS [Extent1]
    WHERE [Extent1].[OptionId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

    The problem is that the OrderOptions table is getting large, every entry in this table has an OptionId = 1, so the entire table is returned with this query.  It is starting to take a long time and is severely slowing the web site down.

    I am typically only adding 3 records, as those are the default options.  I have added an index to OrderOptions, but it doesn't help because every record in that table has an OptionId = 1, so the automatic EF query returns all records.

    It looks like EF is querying the entire OrderOptions table to ensure that a duplicate object is not being created, but the OrderOption is Order-specific, so it only has to worry about the current order.   However, Options is not directly tied to Orders; only indirectly via OrderSheetOptions. So, the EF query can't restrict itself to the current order. Can anyone tell me how I could optimize this so that EF doesn't have to query the entire OrderOptions table just to add a new item?

    Thanks!


    m j klassen







    
    
    
    
    
    
    
    • Moved by Caillen Wednesday, April 23, 2014 8:58 AM
    • Edited by MJKlassen Wednesday, April 23, 2014 5:21 PM
    Tuesday, April 22, 2014 5:38 PM

Answers

  • Thanks for your help everyone.  I have solved this problem by creating a new Option when I add a new OrderOption:

        Option o = new Option { Id = option.Id, OptionGroupId = option.OptionGroupId... };

        OrderOptions.Add(new OrderOption { Option = o});

    The Options created are superfluous and make it a bit messy, but EF no longer has to query 40K (at last count) records all with OptionId = 1.

    
    

    m j klassen

    • Marked as answer by MJKlassen Thursday, April 24, 2014 11:23 AM
    Thursday, April 24, 2014 11:23 AM

All replies

  • I think that it will work faster if the column ‘OptionId’ has a proper database index. If the collection is to be displayed on Web page, then consider paginations, using queries like 'Sheet.OptionGroups.Select(og => og.Option).Skip(30).Take(10)'.


    • Edited by Viorel_MVP Tuesday, April 22, 2014 7:04 PM
    Tuesday, April 22, 2014 7:03 PM
  • Most likely, for each iteration in the loop, it is going back again to the database to read each record.  So, you are doing a double pull of the same information.

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

    Tuesday, April 22, 2014 9:16 PM
  • Can you show some more of your schema?  Is there an Order table as well as the OrderOptions table?  Normally you would be adding OrderOptions to a particular Order rather than just adding them into the table.  It is hard to know without some more context on what you are doing.

    Paul Linton

    Tuesday, April 22, 2014 11:36 PM
  • I already tried adding an index, didn't help.  Makes sense, because there's nothing to index - every record in the table has an OptionId = 1.

    m j klassen

    Wednesday, April 23, 2014 3:46 AM
  • Try to find a filter that you can add to the query. It will result less results.

     

    Noam B.


    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...

    Wednesday, April 23, 2014 10:17 AM
  • Hi Paul:

    I modified my original post to clarify; not sure if it's any clearer.  The crux of the matter is that I am in fact adding an OrderOptions to a specific Order - I am typically adding only 3 records as these are the defaults.  But, for some reason, EF is querying the entire OrderOptions table in the process of adding them.  If I add a new OrderOptions without setting the Option property, it is fine - it happens fast without any querying.  It is because I am setting the Option property that it queries the OrderOptions table.


    m j klassen

    Wednesday, April 23, 2014 4:01 PM
  • While you say OrderOptions is an associative table, it looks like there is more to it.  Usually EF doesn't generate explicit objects for simple associative tables, but rather just generates the related collections on each associated object.  So, usually you will see code more like:

    var myNewOrder = new Order();
    
    // Select all default options
    var options = Sheet.OptionGroups.Select(og => og.Option);
    
    foreach (var option in options)
    {
        // Add all default options
        myNewOrder.Options.Add(option);
    }

    Also, I see when you create the OrderOption instance, you set Option but not Order.  Why is that?

    Wednesday, April 23, 2014 4:17 PM
  • 

    Hi Mike:

    You are absolutely right!  I was trying to be brief, but it looks like more detail is required.  Edited; thanks.

    Re: Option vs. Order, I think the edits probably have answered that.  OrderOptions is not directly related to Orders; only indirectly via OrderSheetOptions.  Therein lies my problem I believe...


    m j klassen

    Wednesday, April 23, 2014 5:23 PM
  • Hmm... could you perhaps try adding the new OrderOption directly to the instance of OrderSheet, so that it has that particular Order in scope?
    Wednesday, April 23, 2014 6:40 PM
  • Thanks for your help everyone.  I have solved this problem by creating a new Option when I add a new OrderOption:

        Option o = new Option { Id = option.Id, OptionGroupId = option.OptionGroupId... };

        OrderOptions.Add(new OrderOption { Option = o});

    The Options created are superfluous and make it a bit messy, but EF no longer has to query 40K (at last count) records all with OptionId = 1.

    
    

    m j klassen

    • Marked as answer by MJKlassen Thursday, April 24, 2014 11:23 AM
    Thursday, April 24, 2014 11:23 AM
  • Mike - thank for the suggestions.  This web site is in production, and this slowness problem surfaced just recently as the database has grown, so I don't want to make a change like this at this point.  I've solved the problem (see above) by creating new Options each time.  Not ideal, but it works.  Thanks again!

    m j klassen

    Thursday, April 24, 2014 11:25 AM