Performant way to clone rather large subtree in database?

Currently I'm trying to performance-optimize a co开发者_运维知识库de in a Windows Forms .NET 2.0 application that does copy operations on hierarchical database objects.

Here is an example structure:

Each object in a tree is represented by a database table row. In addition, for each object there are also several "side-objects" associated. E.g. a test case object also has

• 1..n permissions
• 1..n attributes
• 1..n attachments
• ...

These "side-objects" are stored in separate database tables.

Copying trees

The user of the applications can select a tree element, right click and select "copy" then paste it later at another position in the tree.

This operation copies all child objects and all "side-objects" to the new location.

From a database perspective, this can be several hundred or even thousand SELECT and INSERT statements, depending on the size of the child tree to copy.

From a user perspective, a progress dialog is being shown to keep the UI responsive. Beside that, most users complain that it takes way too long to do "...a simple copy and paste..." operation.

Optimizing performance

So my goal is to speed up things.

The current algorithm goes something like this:

1. Read an object from DB.
2. Store this object as a new entry to DB.
3. Do the same for all "side-objects" for the object.
4. Recursively do the same for all child objects of the object.

As you can imagine, for a large number of objects this quickly sums up to a large number of database operations.

Since I have had no clue so far on how to optimize (batch operations? but then how and for which object?), my question is as following.

My question

Can you give me any hints/pattern/best practices on how to clone a large number of hierarchically related objects as described above?

(ideally in a database-agnostic way, although the backend is a Microsoft SQL Server in most cases)

I'm assuming you're doing all the cloning in your .NET app which is causing a lot of roundtrips to the database server?

First make sure you're doing all your cloning on the database and avoiding these roundtrips. It should be possible to do exactly what you are doing, but using a recursive stored procedure, just by rewriting your current C# algorithm in SQL. You should see a big performance boost.

Someone cleverer than me might say it can be done using a single CTE query but this post seems to suggest that's not possible. Certainly, I can't see how you'd do this and preserve the relationships between the new ids