C# Entity Framework 4 Navigation properties causing slow performance on commit

Question

I apologize for the lack of detail in this question - the first thing I need help on is knowing where to look to find more detail.

I have a problem with enity framework 4 navigation properties apparently causing poor performance when committing changes:

this.ObjectContext.SaveChanges();

Is taking 30+ seconds when one of the navigation properties (Receipts table) contains around 8000 rows (which is not many, so should be fine).

I have used the SQL profiler and can see that EF issues a select * from Receipts and that it is very slow:

exec sp_executesql N'SELECT 
[Extent1].[Id] AS [Id], 
// full field list cut for brevity 
FROM [dbo].[Receipts] AS [Extent1]
WHERE [Extent1].[WarehouseId] = @EntityKeyValue1',
N'@EntityKeyValue1 int',@EntityKeyValue1=1

At the moment I can't even see why it needs to select all rows from this table when ObjectContext.SaveChanges() is called.

It does need to insert 1 row into this table, but that doesn't explain why it does a select all first - and doesn't explain why that select takes so long (the same query takes < 1 second in query manager)

So my question right now - I don't exactly know what the problem is yet - is:

• Where/how can I look for more details about the problem? I can't debug into ObjectContext.SaveChanges(), so I don't know whats happening inside it.
• Why would EF try to select * from Receipts?
• Why is it so slow? The exact same query copied + pasted into query manager is nearly instant

EDIT:

I have confirmed that it is the receipt code that is slow by commenting out the call to this method:

    private void AddReceipt(PurchaseInvoice invoice, 
                               PurchaseInvoiceLine invoiceLine)
    {
        if (invoice != null && invoiceLine != null)
        {
            Product product = invoiceLine.Product;
            if (product != null)
            {
                Receipt receipt = new Receipt{ foo = bar };
                WarehouseDetail detail = new WarehouseDetail{ foo = bar };
                receipt.WarehouseDetails.Add(detail);
                invoice.Receipts.Add(receipt);
            }
        }
    }

But I still cannot see why this causes EF to issue that select * query.

I believe that it might be a lazy loading issue caused by invoice.Receipts.Add(receipt). Because before that line invoice.Receipts is empty, and in order to .Add to the Receipts, it must first load the collection. BUT that does not explain why it is selecting by warehouseId=1, when it should be selecting by the invoiceId.

EDIT 2:

I have "fixed" the problem by replacing the EF code in this method with direct SQL commands. This is not a great idea - I should not be throwing SQL around when I've got an otherwise perfectly good ORM. But right now I still do not understand why EF was running the select * query

    private void AddReceipt(PurchaseInvoice invoice, 
                               PurchaseInvoiceLine invoiceLine)
    {
        if (invoice != null && invoiceLine != null)
        {
            Product product = invoiceLine.Product;
            if (product != null)
            {
                Receipt receipt = new Receipt{ foo = bar };
                WarehouseDetail detail = new WarehouseDetail{ foo = bar };
                int id = SqlHelper.AddWarehouseDetail(detail);
                receipt.WarehouseDetailId = id;
                SqlHelper.AddReceipt(receipt);
            }
        }
    }

Ticker

Answer 1

You issue is with the "Navigation Property" on your "Warehouse" entity. Remove this navigation property. The relationship will still be there, but it will not query all receipts with that warehouse anymore when you create a receipt entity. I had the same issue and this solved my issue.

Phyllida

Can you please elaborate? This issue is still unsolved (it has a "fix" applied by using direct sql insert statements). All of my POCO classes are autogenerated, so they contain a navigation property for each FK relationship. Do you have a URL to a page that describes removing the navigation properties? (eg how do I then find out what receipt.Warehouse is when I don't have the property anymore). Thanks – Ticker

Answer 2

Since this is an insert, it refreshes your object, by selecting the value back and repopulating the object. Now let me answer your questions that you laid out:

1. You shouldn't need to debug instead of SaveChanges(), what you see probably wouldn't make much sense anyways.

2. It is not actually doing a select * from Receipts. It is doing a select * from Receipts where WarehouseId = 1. So for some reasons you object is pulling all the Receipts for the Warehouse with the Id of 1.

3. This could depend on so many things, that you really can't get into it now. But one place to start is to check the ping rate between your app box and your db box. Also check that the RAM isn't full on the db box. That is where I would start, and that is the usual problem for what you are describing.

A good tool to debug EF is the EF Profiler. http://efprof.com This will help you alot more than SQL profiler will.

Equidistant

+1 for suggesting efprof.com - I wasnt aware of this. -1 for the cost, $305?? Wow – Ticker

Re your point #2. I'm trying to find the reason - this is probably where the problem lies. I did find that the navigation property was not being used correctly, but fixing this has not removed the select * query – Ticker

I've found where it happens by commenting stuff out until it worked. But I cant see from the code why EF issues the select query. – Ticker

Why are you adding the WarehouseDetail? I don't really know what your product does, but shouldn't you lookup the warehouse for the invoice not create a new warehouse? – Equidistant

WarehouseDetail is not Warehouse, it is the record of what stock has been placed in the warehouse: it has columns Id, WarehouseId, ProductId, Qty, etc. – Ticker

@Ticker Another sql profiler, but this one's free: anjlab.com/en/projects/opensource/sqlprofiler. – Ageratum

Answer 3

Do you have lazy loading turned on? If so it will fire queries for the WarehouseDetails and Receipts tables when you access the related navigation properties. I always ensure that lazy loading is turned off so that I don't unintentionally fire queries.

Mckoy