none
RowCount for SQL Server 2014 Memory Tables

    Question

  • I'm experimenting with the new In-Memory tables. I want to drive them using some OR mapper (I know an OR mapper is not the fastest thing for a memory table, but I experimenting only). 

    However, it seems if i update rows in a mem table using native sp, it does not return the xxx rows affected information message, so, the OR mapper thinks there is a concurrency issue.

    I suppose this is by design in Hekaton to reduce unnecessary communication between the client and the server, but I would like to hear a confirmation for this.

    My 2nd question is, if an OR mapper drives a memory table using ordinary insert/update/delete sql commands, it goes trough the internal interop layer, so, it will be much slower than the native sp approach, right?

    Thanks,

    Zsolt

     

    Tuesday, April 22, 2014 7:38 AM

Answers

  • Hello,

    However, it seems if i update rows in a mem table using native sp, it does not return the xxx rows affected information message, so, the OR mapper thinks there is a concurrency issue.

    You are correct. I'm not entirely sure why this was chosen (probably for performance and interop reasons) but here is the documentation:

    http://msdn.microsoft.com/en-us/library/dn452281(v=sql.120).aspx : The section you're interested in is the SET OPTION area for atomic blocks where it is forcefully set and cannot be changed NOCOUNT ON.

    Even if you attempted to set this, it would not take and it is not possible to set this inside of an atomic block: http://msdn.microsoft.com/en-us/library/dn452285(v=sql.120).aspx

    So, how to get around it? While it isn't the *best* answer (I haven't viewed any other work-arounds) it is possible to use the RETURN value as the number of rows through @@ROWCOUNT. Again, it's ugly, butif that's what the OR needs....

    My 2nd question is, if an OR mapper drives a memory table using ordinary insert/update/delete sql commands, it goes trough the internal interop layer, so, it will be much slower than the native sp approach, right?

    To the first part of the question, yes. If it isn't a natively compiled procedure then it's interop. The second part of that, it depends on your definition of "much slower" :) but yes it *should/will* be slower.


    Sean Gallardy | Blog | Twitter

    • Marked as answer by Zsolt Soczo Monday, April 28, 2014 8:35 AM
    Thursday, April 24, 2014 2:50 AM

All replies

  • Hello,

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
    Thank you for your understanding and support.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here. 


    Fanny Liu
    TechNet Community Support

    Thursday, April 24, 2014 2:29 AM
    Moderator
  • Hello,

    However, it seems if i update rows in a mem table using native sp, it does not return the xxx rows affected information message, so, the OR mapper thinks there is a concurrency issue.

    You are correct. I'm not entirely sure why this was chosen (probably for performance and interop reasons) but here is the documentation:

    http://msdn.microsoft.com/en-us/library/dn452281(v=sql.120).aspx : The section you're interested in is the SET OPTION area for atomic blocks where it is forcefully set and cannot be changed NOCOUNT ON.

    Even if you attempted to set this, it would not take and it is not possible to set this inside of an atomic block: http://msdn.microsoft.com/en-us/library/dn452285(v=sql.120).aspx

    So, how to get around it? While it isn't the *best* answer (I haven't viewed any other work-arounds) it is possible to use the RETURN value as the number of rows through @@ROWCOUNT. Again, it's ugly, butif that's what the OR needs....

    My 2nd question is, if an OR mapper drives a memory table using ordinary insert/update/delete sql commands, it goes trough the internal interop layer, so, it will be much slower than the native sp approach, right?

    To the first part of the question, yes. If it isn't a natively compiled procedure then it's interop. The second part of that, it depends on your definition of "much slower" :) but yes it *should/will* be slower.


    Sean Gallardy | Blog | Twitter

    • Marked as answer by Zsolt Soczo Monday, April 28, 2014 8:35 AM
    Thursday, April 24, 2014 2:50 AM