Nullable foreign key performance

5 posts, 0 answers
  1. Aron
    Aron avatar
    80 posts
    Member since:
    Dec 2005

    Posted 07 Nov Link to this post

    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

     

  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 10 Nov Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  3. DevCraft banner
  4. Aron
    Aron avatar
    80 posts
    Member since:
    Dec 2005

    Posted 10 Nov Link to this post

    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?

  5. Aron
    Aron avatar
    80 posts
    Member since:
    Dec 2005

    Posted 10 Nov in reply to Aron Link to this post

    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.
  6. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 14 Nov Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Back to Top
DevCraft banner