Stored Procedure Vs LINQ - Insert data into SQL Server From C#

Question

I want to insert data into Database from C#. Below are the two ways I am trying.

Using Stored Procedure - In this I have insert statement. Calling this stored procedure from C# by passing parameters.
Using LINQ - In this, I am using Data Context, with that I will get table and calling InsertOnSubmit method by passing required parameter.

I am able to insert in both the ways. Right now we have less data so not able to judge which is better. And this Insertion will happen for millions of data. I will be calling this stored procedure or LINQ InsertOnSubmit for those many times.

Can anyone Suggest which of this method will be faster? Do we have any other way which is better than these also will be fine.

Thanks in Advance.

In 99% LINQ is enough, but always there are cases when SP is faster — Backs, Jan 31 '18 at 07:05
@Backs There are never cases where SP is faster. Look up SQL Server Parameterized Queries. — Aron, Jan 31 '18 at 14:33

Risto M · Accepted Answer · 2018-01-31T07:13:02.730

2

If you are inserting in same time those millions of rows, you should create fever but bigger bunches of data to be inserted with minimun amount of database transactions. Millions of rows with one-by-one insertions is slow in any case.

Bulk operations can be implemented for example following ways:

With SQL Bulk Insert operations provided by .NET framework
By using external library which is specialized on high-speed Bulk operations
By crafting sql stored procedure which takes array of data as parameter. More information about table-valued parameters can be found in https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine

edited Jan 31 '18 at 07:13

answered Jan 31 '18 at 07:05

Risto M

2,919
1
14
27

1

Thanks for great information. This will really help me to improve my way of thinking for this problem. – trinadh Jan 31 '18 at 07:12
Happy to help! It is good to hear that you are thinking performance issues in early phase of your project. Sometimes those questions arise too late. – Risto M Jan 31 '18 at 07:17

Stored Procedure Vs LINQ - Insert data into SQL Server From C#

1 Answers1