asked on insert multiple records to SQL table - using SP and c#
Hi
I have the following code
Its not currently working... i'm wondering how i pass in multiple rows to be inserted?
currently the logic works one call and one row insert
but now i need to insert upto 10 rows with one call and efficiently as possible as it will be executed 100 times a second
can someone help me in the correct direction
I have the following code
string result = "";
SqlParameter[] param = new SqlParameter[2];
try
{
param[0] = new SqlParameter("@vSequenceID", SqlDbType.BigInt);
param[0].Value = this.vSequenceID;
param[1] = new SqlParameter("@vpkDeviceID", SqlDbType.VarChar, 50);
param[1].Value = this.Deviceid;
ATPLDAL.SqlHelper.ExecuteNonQuery(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"], CommandType.StoredProcedure, "sp_SaveAlertsCaptured", param);
}
Its not currently working... i'm wondering how i pass in multiple rows to be inserted?
currently the logic works one call and one row insert
but now i need to insert upto 10 rows with one call and efficiently as possible as it will be executed 100 times a second
can someone help me in the correct direction
C#Microsoft SQL ServerASP.NET
Last Comment
Guy Hengel [angelIII / a3]
ASKER
Thanks Guy
Each insert will have these 3 items:
- ifkAlertId
- ifkCommonId
- iSoftwarePriority
The first 2 are refering to ID's in other tables
the 3rd one will either be a "1" "2" or "3" value
actually it will mostly be between 1 to 5 rows being inserted
but this SP will eventually be called very often (could be 100 of times a sec) - it will need to scale to this frequency at least!
I figured it would make more sense to insert all records in one go (i.e. 100 times a second instead of 500 times a second if there are 5 rows needing to be inserted on each of the 100)
what do you suggest given the above info?
Each insert will have these 3 items:
- ifkAlertId
- ifkCommonId
- iSoftwarePriority
The first 2 are refering to ID's in other tables
the 3rd one will either be a "1" "2" or "3" value
actually it will mostly be between 1 to 5 rows being inserted
but this SP will eventually be called very often (could be 100 of times a sec) - it will need to scale to this frequency at least!
I figured it would make more sense to insert all records in one go (i.e. 100 times a second instead of 500 times a second if there are 5 rows needing to be inserted on each of the 100)
what do you suggest given the above info?
ASKER
how about this method: table valued parameter?
http://stackoverflow.com/questions/25869110/stored-procedure-insert-multiple-rows
http://stackoverflow.com/questions/25869110/stored-procedure-insert-multiple-rows
quite some preparation work to be done, but in your case this can and should be done to win as much time as possible.
ASKER
Thanks Guy
Based on this article
http://www.dbdelta.com/maximizing-performance-with-table-valued-parameters/
if you scroll down to graph, it says you need at least 4 inserts to get the performance increase
I will know how many i need to insert before hand, so if its 4 or above, I could use TVP's and if its below 4 I could use separate inserts
Do you think this is a good approach?
Based on this article
http://www.dbdelta.com/maximizing-performance-with-table-valued-parameters/
if you scroll down to graph, it says you need at least 4 inserts to get the performance increase
I will know how many i need to insert before hand, so if its 4 or above, I could use TVP's and if its below 4 I could use separate inserts
Do you think this is a good approach?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
for a stored procedure, you only have 2 options:
* call the stored procedure 10 times indeed with the different values
* recode the stored procedure to split the parameter values into the x rows
the 2nd option is not recommended for several reasons, but may be an option still for several scenarios, and you might get inspired by this article: https://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html