CODEWITHSUNDEEP
sql-serverc#-4.0foreachprogress-bar
lokendra jayaswal
lokendra jayaswal

Reputation: 308

Data insertion in Sql Table is taking too much time

I am inserting data in the sql server.Here is the code:

SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter();
            param[0].ParameterName = "@Exch";
            param[0].SqlDbType = SqlDbType.Int;
            param[0].Value = "BSE";
            formObjSqlConnection.SQLConnection.Executes("LPExch..DeleteVarMargin", param);
            foreach (BseVar objBseVar in objListNseData)
            {

                currentIndex++;
                param = new SqlParameter[10];
                param[0] = new SqlParameter();
                param[0].ParameterName = "@Exch";
                param[0].SqlDbType = SqlDbType.Char;
                param[0].Value = "BSE";

                param[1] = new SqlParameter();
                param[1].ParameterName = "@Symbol";
                param[1].SqlDbType = SqlDbType.Char;
                param[1].Size = 10;
                param[1].Value = objBseVar.Symbol;



                param[2] = new SqlParameter();
                param[2].ParameterName = "@Series";
                param[2].SqlDbType = SqlDbType.Char;
                param[2].Value = "EQ";

                param[3] = new SqlParameter();
                param[3].ParameterName = "@SecurityVar";
                param[3].SqlDbType = SqlDbType.SmallMoney;

                param[3].Value = objBseVar.SecurityVar;

                param[4] = new SqlParameter();
                param[4].ParameterName = "@IndexVar";
                param[4].SqlDbType = SqlDbType.SmallMoney;

                param[4].Value = 0;


                param[5] = new SqlParameter();
                param[5].ParameterName = "@VarMargin";
                param[5].SqlDbType = SqlDbType.SmallMoney;

                param[5].Value = objBseVar.IndexVar;

                param[6] = new SqlParameter();
                param[6].ParameterName = "@AdhocMargin";
                param[6].SqlDbType = SqlDbType.SmallMoney;

                param[6].Value = objBseVar.SecurityVar - objBseVar.IndexVar;


                param[7] = new SqlParameter();
                param[7].ParameterName = "@VarMarginRate";
                param[7].SqlDbType = SqlDbType.SmallMoney;

                param[7].Value = objBseVar.IndexVar;

                formObjSqlConnection.SQLConnection.Executes("LPExch..UpdateOrAddCashVarMarginBSE", param);
                if (Convert.ToInt32(1 / progressChange * currentIndex) <= 100)
                    objImportMaster.UpdateProgressBar(Convert.ToInt32(1 / progressChange * currentIndex));
            }
            formObjSqlConnection.SQLConnection.Executes("LPExch..UpdateCashVarMarginBSEScripNo");

Is there any other way i can insert the data in the database.The problem is that database is taking too much time to insert the data.Also i am updating the progressbar in other UI. Is that the reason for slow insertion? Can we use any other way to insert data.?

Upvotes: 0

Views: 616

Answers (2)

SamTech
SamTech

Reputation: 1313

If you are trying to insert many records then consider using bulk copy, it is quite fast. An example is available here.

Upvotes: 0

nvoigt
nvoigt

Reputation: 77304

Right now you are sending every insert one at a time. That's slow. If you can change the database, look up table valued parameters and pass a table to your function so you can send all the records just once.

If you have direct access to a table, you can also use bulk copy.

Upvotes: 1

Related Questions