MS SQL - High performance data inserting with stored procedures
- by Marks
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
