none
How do I speed up or remove loading from linqtosql RRS feed

  • Question

  • I have two tables, Transaction and TransactionLineItem that are related through a 1:n relationship (TransactionLineItem has a TransactionKey field that points to the Transaction). I've created the Transaction and exactly 1 TransactionLineItem and posted them to the database.

    Transaction tran = new Transaction();
    context
    .Transactions.InsertOnSubmit(tran);
    context
    .SubmitChanges();

    TransactionLineItem tli = new TransactionLineItem();
    tran
    .TransactionLineItems.Add(tli);
    context
    .SubmitChanges();

    Later when I access the TransactionLineItem from the same Transaction object it takes an unreasonable amount of time. On my development system it takes about 1.6 seconds but on my production system (a single board computer with a 1gHz processor and a gig of RAM) it can take as long as 5 seconds.

    List<TransactionLineItem> lineItems = tran.TransactioinLineItems.ToList();

    I've tried several different ways of accessing the item. I've used the LoadWith function on the data context and I've called TransactionLineItems.Load() but all that seems to do is move the lag time around. I've even called the Load() function immediately after the first SubmitChanges() but even with the collection guaranteed to be empty the lag time is still present.

    I've used the SQL server profiler to pull out the query and try running it directly in the Management Studio but the query runs there in under a second. Also at the moment I'm running this against a mostly empty database.

    Either I need to find a way to make the Load() call run faster, or I need a way to bypass the load completely but only in specific cases.

    Thanks.

    Wednesday, April 1, 2009 2:11 PM

All replies

  • Do you have a constructor on your TransactionLineItem?

    [)amien
    Thursday, April 2, 2009 1:21 AM
    Moderator
  • No I'm currently using the default constructor generated from the DBML.
    Thursday, April 2, 2009 1:09 PM
  • Are you classes entirely generated or have you extended them with partials?

    Might be worth trying to run a profiler over it to find out where the actual bottleneck is occurring.

    [)amien
    Tuesday, April 14, 2009 9:22 PM
    Moderator
  • My classes have been extended. I ran a profiler over the code and found that the bottleneck is in trying to access the tran.TransactionLineItem property after the submitChanges() call. It goes back out to the database even though there are no new data to be loaded. That trip to the database can take a signifigant amount of time.
    Wednesday, April 15, 2009 3:56 PM
  • Have you tried disabling deferred loading? ("DataContext.DeferredLoadingEnabled = false;" before making any queries.)
    Thursday, April 16, 2009 1:42 AM
    Answerer
  • Unfortunately deferred loading is an all or nothing proposition. Currently the piece of code I'm working on needs it to be on for other reasons. This would probably fix this specific problem but would cause a great many more that would be difficult to fix. For the moment I'm just using a second List<> to bypass this issue.
    Thursday, April 16, 2009 1:35 PM