Should you make multiple insert calls or pass XML?

Question

I have an account creation process and basically when the user signs up, I have to make entries in mutliple tables namely User, Profile, Addresses. There will be 1 entry in User table, 1 entry in Profile and 2-3 entries in Address table. So, at most there will be 5 entries. My question is should I pass a XML of this to my stored procedure and parse it in there or should I create a transaction object in my C# code, keep the connection open and insert addresses one by one in loop?

How do you approach this scenario? Can making multiple calls degrade the performance even though the connection is open?

Answer 1

No offence, but you're over thinking this.

Gather your information, when you have it all together, create a transaction and insert the new rows one at a time. There's no performance hit here, as the transaction will be short lived.

A problem would be if you create the transaction on the connection, insert the user row, then wait for the user to enter more profile information, insert that, then wait for them to add address information, then insert that, DO NOT DO THIS, this is a needlessly long running transaction, and will create problems.

However, your scenario (where you have all the data) is a correct use of a transaction, it ensures your data integrity and will not put any strain on your database, and will not - on it's own - create deadlocks.

Hope this helps.

P.S. The drawbacks with the Xml approach is the added complexity, your code needs to know the schema of the xml, your stored procedure needs to know the Xml schema too. The stored procedure has the added complexity of parsing the xml, then inserting the rows. I really don't see the advantage of the extra complexity for what is a simple short running transaction.

Answer 2

If you want to insert records in multiple table then using XML parameter is a complex method. Creating Xml in .net and extracting records from xml for three diffrent tables is complex in sql server.

Executing queries within a transaction is easy approach but some performance will degrade there to switch between .net code and sql server.

Best approach is to use table parameter in storedprocedure. Create three data table in .net code and pass them in stored procedure.

--Create Type TargetUDT1,TargetUDT2 and TargetUDT3 for each type of table with all fields which needs to insert

CREATE TYPE [TargetUDT1] AS TABLE
             (
             [FirstName] [varchar](100)NOT NULL,
             [LastName] [varchar](100)NOT NULL,
             [Email] [varchar](200) NOT NULL
             )

--Now write down the sp in following manner.

 CREATE PROCEDURE AddToTarget(
     @TargetUDT1 TargetUDT1 READONLY,
     @TargetUDT2 TargetUDT2 READONLY,
     @TargetUDT3 TargetUDT3 READONLY)
     AS
 BEGIN
       INSERT INTO [Target1]
       SELECT * FROM @TargetUDT1

       INSERT INTO [Target2]
       SELECT * FROM @TargetUDT2

       INSERT INTO [Target3]
       SELECT * FROM @TargetUDT3
 END

In .Net, Create three data table and fill the value, and call the sp normally.