none
SF 2.1 new bulk insert feature ... optional? RRS feed

  • Question

  • SF 2.1 boasts an enhancement that allows server-side inserts to be done in bulk instead of the 2.0 way of inserting each record one at a time (by running a stored procedure for each one).

    A foundational component of my application is built on the 2.0 record-by-record insert methodology in that I accomplish server-side generation of key field numbers by allowing the server to autonumber inserted records and echoing those new records back to the client as replacements for the original (temporary) records on the client.  The key to making this work is that as each record is inserted I can capture the temporary key field value and the new autonumber value to a mapping table.  (FYI: I maintain a mapping table of the temporary client-side ID number (client tables are all: seed -1 / step -1) and what they became on the server then use this table to roll the changes to any FK fields and to delete the temporary records from the client)

    Questions:

    #1) Is there a way to implement server-side generation of key field autonumbers with SF 2.1 bulk inserts?  (either via new 2.1 functionality ... or the ability to build my maping table after the bulk inserts)

    #2) SF 2.1 release documentation says this feature is the "default" ... does this mean it replaces the 2.0 way or does this mean that you can choose to configure this feature in SF 2.1 to continue to work in the 2.0 way?

    Thursday, August 26, 2010 7:07 AM

Answers

  • what i meant is that during provisioning, it creates one bulk stored proc for inserts, one for update and one for delete. Inside these stored procs, there's a temporary table named @changed that temporarily stores what was successfully applied.

    Anyway, you mentioned "Designer" (Local Database Cache project item?), so the bulk stored procedures is not be applicable to you as you are using the offline providers (SqlCeClientSyncProvider/DbServerSyncProvider) as against the SqlSyncProvider. It's the SqlSyncProvider/SqlCeSyncProvider that's using these new bulk stored procedures.

    • Edited by JuneTModerator Thursday, August 26, 2010 2:12 PM
    • Marked as answer by P H T Thursday, August 26, 2010 2:15 PM
    Thursday, August 26, 2010 9:01 AM
    Moderator
  • i think you're project should still work as it is.

    The filtering enhancements are on the SqlSyncProvider/SqlCeSyncProvider as well, not the offline providers

    • Marked as answer by P H T Thursday, August 26, 2010 2:22 PM
    Thursday, August 26, 2010 2:01 PM
    Moderator

All replies

  • Use of Bulk Procedures is the default if you're using SQL 2008 or SQL Azure. you can override it during provisioning by setting SetUseBulkProceduresDefault to false.

    are you doing your server-side mapping using stored procedures? i think you can still do the same as the Bulk approach still creates one stored procedure each for Insert/Update/Delete. you can query the @changed temporary table inside the stored proc to retrieve the rows successfully inserted.

    Thursday, August 26, 2010 7:44 AM
    Moderator
  • Thanks JuneT!

    I am editing the SP that the designer creates so that it creates the mapping.  Because 2.0 calls the Inserts SP for each record that is being inserted - so I can capture the new ID with Scope_Identity() after each insert.

    But I am confused now ... you said: "the Bulk approach still creates one stored procedure each for Insert/Update/Delete"

    How can 2.1 do bulk inserts if the SF is calling a SP for each record in the I/U/D dataset? ... or do you mean one SP for all Inserts / one for all Upd / one for all Del?

    I thought I understood from the 2.1 docs that all record inserts were done with a single call to a SP ... am I wrong or did I misread your answer?

    I'm not familiar with the @changed temp table ... quick Google did not find anything ... is this created by SF - any documentation on it? - if it is what it sounds like it is (a table of the changes just made) that would be awesome because it would allow me to Insert in bulk AND get the IDs just created ... !

    Thursday, August 26, 2010 8:35 AM
  • what i meant is that during provisioning, it creates one bulk stored proc for inserts, one for update and one for delete. Inside these stored procs, there's a temporary table named @changed that temporarily stores what was successfully applied.

    Anyway, you mentioned "Designer" (Local Database Cache project item?), so the bulk stored procedures is not be applicable to you as you are using the offline providers (SqlCeClientSyncProvider/DbServerSyncProvider) as against the SqlSyncProvider. It's the SqlSyncProvider/SqlCeSyncProvider that's using these new bulk stored procedures.

    • Edited by JuneTModerator Thursday, August 26, 2010 2:12 PM
    • Marked as answer by P H T Thursday, August 26, 2010 2:15 PM
    Thursday, August 26, 2010 9:01 AM
    Moderator
  • Thanks for picking up on the fact that I have a local data cache project (via WCF) ... so if I upgrade to 2.1 the Inserts "should" continue to work as they do in 2.0 ... ?

    Will the filtering enhancements in 2.1 be available to my offline project?

    Thursday, August 26, 2010 9:14 AM
  • i think you're project should still work as it is.

    The filtering enhancements are on the SqlSyncProvider/SqlCeSyncProvider as well, not the offline providers

    • Marked as answer by P H T Thursday, August 26, 2010 2:22 PM
    Thursday, August 26, 2010 2:01 PM
    Moderator