Best Approach to Save Data into SQL Server?

Ashish Vishwakarma

I have few records to save into database, approx 12 KB each. They are coming as bunch of 10 to 15 Records. I have option of 2 approaches to follow.

1. Iterating Records in C# and sending them one by one to Database to save. (10 to 15 requests to DB 12 KB each).

2. Pushing all records at once in DB, and let the stored procedure do the Jobs of Iterating and saving each record. (Only one request to DB 120KB to 180KB).

This is gonna be used in a job which is configured to run everyday midnight. Please suggest which is approach is better and why?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Afzaal Ahmad Zeeshan10y ago
Accepted answer
I would consider using the SQL's stored procedure method, because that has the following benefits:
It would be same as the previous one.
The major benefit of this is that you won't need to send multiple requests.
Stored procedures will not require the time required to interpret the commands; you can use the power of caching too.
This is why, I would prefer using the cases where I can make use of space to get more time and better performance.
Afzaal Ahmad Zeeshan10y ago
That makes 4 benefits of using stored procedures and to send the data just once. :-)
Joginder Banger10y ago
hi Afzaal Ahmad Zeeshan
I added one more advantage if you store procedure using this stop sql injection.

for more information check this link
http://www.codeproject.com/Articles/9378/SQL-Injection-Attacks-and-Some-Tips-on-How-to-Prev

JSB