Nullable foreign key performance

[Aron]

Hi. We found a very strange performance issue with Int nullable foreign key. We use Azure SQL database.

The main problem: when we try to assign any value to the nullable foreign key - Telerik DataAccess executes strange SQL queries. In full solution it takes 10-50 seconds, in test console app without any load - it takes ~7 seconds.

1. Why does Telerik execute SQL queries on set operation?

2. Why is it so slow? We tried to assign data using loaded object instead of id - same issue.

 

I am attaching the DotTrace stacktrace.

https://drive.google.com/open?id=0BwihrvDHTemyOTBjdEpZS2tCd3c

 

[Thomas]

I think the issue lies in the way your model is done. It seems, that setting the OrderStatus field causes a managed inverse relationship maintenance, causing a database round-trip. So I believe you have set up a foreign key to depend on the OrderStatus field; can you check that? From my humble understanding of Order/OrderDetails things I would assume that the status should not play a role in any FK relation as a state change would break the references in your model. If you have a dependency on the OrderState value in some part of the code it might be better expressed with an expclicit query.

Regards,
Thomas
Telerik by Progress

 

[Aron]

Hi. Thank you for the response.

1. I removed IsManaged from OrderStatus configuration:

configuration.HasAssociation<Order>(x => x.Orders).WithOpposite(op => op.OrderStatus).WithDataAccessKind(DataAccessKind.ReadWrite).IsManaged().HasConstraint((x, y) =>  x.OrderStatusID == y.OrderStatusId);

 

After this everything is fast.

2. I found why it was so long. When we assign order.OrderStatusId = value with enabled IsManaged configuration - it loads ALL orders, not the single one. I think that it is a bug, I understand that you want to add this current order to the OrderStatus.Orders collection, but it is not loaded, why should it do this?

[Aron]

To be more correct, it load not all orders, but only orders with assigned OrderStatusId. In production environment this number is very big, that's why we have not seen this issue during testing.

[Thomas]

Hi Aron,

you are right, it's probably something that we could optimize further. On the other hand, we have in our API callback functionality that depend on notifications of such events, and we need to execute the fetches for those instance-to-be-notified. It's non-trivial code and interactions and other customers code expecting this behavior.

Regards,
Thomas
Telerik by Progress