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


  • 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

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.


    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

    Friday, February 17, 2017 2:34 AM