none
SqlCeTransaction.Commit() Kills Prepare()'d SqlCeCommand Query Plan??

    Question

  • SQL CE 3.5 SP2.

    Repeatedly calling ExecuteNonQuery() on a parameterized SqlCeCommand which is Prepare()'d and retained in the scope of a single SqlCeConnection with only SqlCeParameter values changed between calls.

    If Visual Studio 2013 performance profiling is to be believed, when ExecuteNonQuery() is called with SqlCeCommand.Transaction assigned a SqlCeTransaction, SqlCeCommand.CompileQueryPlan() takes about 14 times more processor utilization than calling SqlCeCommand.CompileQueryPlan() when I call ExecuteNonQuery() with SqlCeCommand.Transaction == null. (Subjective impression of application performance backs up this impression--which is what lead to instrumenting the problem in the first place.)

    This leads me to suppose that when the SqlCeTransaction is Commit()'d or Dispose()'d, the query plan for the compiled SqlCeCommand is discarded and must be regenerated the next time ExecuteNonQuery() is called. Is this a known issue, or is there some way I can prevent the SqlCeCommand's query plan from being discarded when I'm calling ExecuteNonQuery() in the context of SqlCeTransactions?


    • Edited by Tim Trese Wednesday, February 15, 2017 11:44 PM
    Wednesday, February 15, 2017 11:15 PM

Answers

  • No longer pursuing this issue in-house. Since we are using a pessimistic concurrency model, transactions were unnecessary and have been removed. Thank you.

    • Marked as answer by Tim Trese Thursday, June 15, 2017 6:42 PM
    • Edited by Tim Trese Thursday, June 15, 2017 6:43 PM
    Thursday, June 15, 2017 6:42 PM

All replies

  • Hi Tim,

    We are currently looking into this and will give you an update as soon as possible.

    Thank you for your understanding and support.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 17, 2017 2:34 AM
    Moderator
  • Please share a repro project, so we can see your code

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Thursday, June 15, 2017 6:40 AM
    Moderator
  • No longer pursuing this issue in-house. Since we are using a pessimistic concurrency model, transactions were unnecessary and have been removed. Thank you.

    • Marked as answer by Tim Trese Thursday, June 15, 2017 6:42 PM
    • Edited by Tim Trese Thursday, June 15, 2017 6:43 PM
    Thursday, June 15, 2017 6:42 PM