0

Given business objects similar to:

public class MyBase
{
    public int Id { get; set; }
}

public class Parent : MyBase
{
    public List<Child> Children { get; set; }
    // Additional objects with 1:1 relationship
}

public class Child : MyBase
{
    public Parent Parent { get; set; }
    public List<Grandchild> Grandchildren { get; set; }
    // Some other properties
}   

public class GrandChild : MyBase
{
    public Child { get; set; }
    // Some other properties including a timestamp
}

I'm trying to replace bulk insert functionality originally written with Entity Framework with ADO.Net and stored procedures due to the poor performance of EF in this scenario.

Not being a DB expert, I first oriented myself with

http://www.sommarskog.se/arrays-in-sql-2008.html

Using that guidance I was able to create a well-performing (relative to EF) stored procedure that inserts a Child along with its List<GrandChild>.

CREATE TYPE tvpInt32List AS TABLE (n int NOT NULL)

CREATE PROCEDURE uspInsertChild @parentId INT, @fk1Id INT, @fk2Id INT, 
@listValues tvpInt32List READONLY 
AS
DECLARE @id INT

BEGIN TRANSACTION   

BEGIN TRY   
    INSERT INTO dbo.Children VALUES(@fk2Id, @fk1Id, NULL, 0, 1, NULL, NULL, NULL, NULL, NULL, @fk1Id, @parentId)

    SET @Id = SCOPE_IDENTITY() 

    INSERT INTO dbo.Grandchildren SELECT n, '', GETDATE(), @id FROM @listValues

    COMMIT TRANSACTION
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

However, I would like to take this a step further and create a stored procedure that accepts data for a Parent object and then creates the associated List<Child>, and the List<GrandChild> for each Child.

I'm conceptually at a loss as to how to approach that in a stored procedure.

What strategy can I use to pass in that type of data structure and process it in the stored procedure?

Community
  • 1
  • 1
Eric J.
  • 147,927
  • 63
  • 340
  • 553
  • 1
    [Erland Sommarskog's](http://www.sommarskog.se/arrays-in-sql.html) articles are still very good. We've had the best performance by passing the object graph as XML. Table valued parameters are cleaner but slower. – Andomar Jan 08 '13 at 19:24
  • I like Andromar's suggestion, you could pretty readily grab the Children and Grandchildren as XML using `SELECT` to traverse the hierarchical XML (good article [here](http://blog.bodurov.com/How-to-Traverse-Hierarchical-XML/)) then send the whole mess to a waiting sproc that has an `xml` type input parameter. – Darth Continent Jan 08 '13 at 19:26
  • @Andomar: Erland states that XML can be a good deal slower than TVPs. The import runs for 3 hours now (8 hours with EF), so I would like every performance edge possible (the data will grow significantly over the next 2-3 years, so runtimes will increase). http://www.sommarskog.se/arrays-in-sql-2005.html#XML – Eric J. Jan 08 '13 at 21:02
  • Oh, and I thought you were looking to optimize web page loading :) For large amounts of data have a look at [SqlBulkCopy](http://www.4guysfromrolla.com/articles/102109-1.aspx) That works without a stored procedure and requires admin rights on the target server, but it's blazingly fast – Andomar Jan 08 '13 at 21:17
  • It seems SqlBulkCopy only works on one table at a time. http://stackoverflow.com/q/6442035/141172 Though, the accepted answer to that question does point to a possible solution. – Eric J. Jan 08 '13 at 22:10

1 Answers1

0

Guess you write the stored procedure, in order to input parent and childs, one of the option would be to send your data in xml, which again could be extracted easily from your parent object and the utilize the power of Store procedure to do your transaction.

Sandip Kumar Nandi
  • 1
  • 1