MS SQL - High performance data inserting with stored procedures

Question

Hi.
Im searching for a very high performant possibility to insert data into a MS SQL database. The data is a (relatively big) construct of objects with relations. For security reasons i want to use stored procedures instead of direct table access.

Lets say i have a structure like this:

• Document
  • MetaData
    • User
    • Device
  • Content
    • ContentItem[0]
      • SubItem[0]
      • SubItem[1]
      • SubItem[2]
    • ContentItem[1]
      • ...
    • ContentItem[2]
      • ...

Right now I think of creating one big query, doing somehting like this (Just pseudo-code):

EXEC @DeviceID = CreateDevice ...;
EXEC @UserID = CreateUser ...;
EXEC @DocID = CreateDocument @DeviceID, @UserID, ...;

EXEC @ItemID = CreateItem @DocID, ...
EXEC CreateSubItem @ItemID, ...
EXEC CreateSubItem @ItemID, ...
EXEC CreateSubItem @ItemID, ...
...

But is this the best solution for performance? If not, what would be better? Split it into more querys? Give all Data to one big stored procedure to reduce size of query? Any other performance clue?

I also thought of giving multiple items to one stored procedure, but i dont think its possible to give a non static amount of items to a stored procedure. Since 'INSERT INTO A VALUES (B,C),(C,D),(E,F) is more performant than 3 single inserts i thought i could get some performance here.

Thanks for any hints, Marks

Answer 1

One stored procedure so far as possible:

INSERT INTO MyTable(field1,field2)
SELECT "firstValue", "secondValue"
UNION ALL
SELECT "anotherFirstValue", "anotherSecondValue"
UNION ALL

If you aren't sure about how many items you're inserting you can construct the SQL query witin the sproc and then execute it. Here's a procedure I wrote to take a CSV list of groups and add their relationship to a user entity:

ALTER PROCEDURE [dbo].[UpdateUserADGroups]
@username varchar(100),
@groups varchar(5000)
AS
BEGIN
DECLARE @pos int,
@previous_pos int,
@value varchar(50),
@sql varchar(8000)

SET @pos = 1
SET @previous_pos = 0
SET @sql = 'INSERT INTO UserADGroups(UserID, RoleName)'

DECLARE @userID int
SET @userID = (SELECT TOP 1 UserID FROM Users WHERE Username = @username) 

WHILE @pos > 0
BEGIN
 SET @pos = CHARINDEX(',',@groups,@previous_pos+1)
 IF @pos > 0
 BEGIN
 SET @value = SUBSTRING(@groups,@previous_pos+1,@pos-@previous_pos-1)
 SET @sql = @sql + 'SELECT ' + cast(@userID as char(5)) + ',''' + @value + ''' UNION ALL '
SET @previous_pos = @pos
END
END

IF @previous_pos < LEN(@groups)
BEGIN
  SET @value = SUBSTRING(@groups,@previous_pos+1,LEN(@groups))
  SET @sql = @sql + 'SELECT ' + cast(@userID as char(5)) + ',''' + @value + ''''
END
print @sql
exec (@sql)

END

This is far faster than individual INSERTS.

Also, make sure you just a single clustered index on the primary key, more indexes will slow the INSERT down as they will need to update.

However, the more complex your dataset is, the less likely it is that you'll be able to do the above so you will simply have to make logical compromises. I actually end up calling the above routine around 8000 times.