CODEWITHSUNDEEP
c#sqlasp.netsql-serverperformance
Anyname Donotcare
Anyname Donotcare

Reputation: 11403

Migrate data from informix db to sqlserver db

I try to Migrate the data for list of employees from informix db to sqlserver2012 db ,so firstly i select

-The data from the tables in informix like this :

   string cmdText = "select * from permission where emp_num in( " + emplyeeRange + " ) and perm_date>=? and perm_date <=?"; 
   DataTable permissionDT = ifx_conn.Return_DataTable(cmdText, CommandType.Text, paramList1);

   cmdText = "select * from holid where emp_num in( " + emplyeeRange + " ) and end_date>=? and start_date<=?  ";
   DataTable vacationDT = ifx_conn.Return_DataTable(cmdText, CommandType.Text, paramList1);

   cmdText = "select * from empmission where emp_num in( " + emplyeeRange + " ) and date(to_date)>=? and date(from_date)<=? ";
   DataTable missionDT = ifx_conn.Return_DataTable(cmdText, CommandType.Text, paramList1);

-Then i delete the data from sqlserver in the same range date like this : 

            cmdText = "delete from permission where emp_num in( " + emplyeeRange + " ) and perm_date>=@from_date and perm_date <=@to_date";
            sql_command.CommandType = CommandType.Text;
            sql_command.Parameters.AddWithValue("@from_date", from_date.ToShortDateString());
            sql_command.Parameters.AddWithValue("@to_date", to_date.ToShortDateString());
            sql_command.CommandText = cmdText;


            result = sql_command.ExecuteNonQuery();

            if (result >= 0)
            {
                cmdText = "delete from holid where emp_num in( " + emplyeeRange + " ) and end_date>=@from_date and start_date<=@to_date ";

                sql_command.CommandText = cmdText;
                result = sql_command.ExecuteNonQuery();

                if (result >= 0)
                {

                    cmdText = "delete from empmission where emp_num in( " + emplyeeRange + " ) and to_date>=@from_date and from_date<=@to_date";
                    sql_command.CommandText = cmdText;
                    result = sql_command.ExecuteNonQuery();
               }
            }

-Then Insert the updated data in sqlserver like this :

 cmdText = "insert into permission select * from @permissionDT ";
    sql_command.CommandText = cmdText;
    sql_command.Parameters.Clear();
    sql_param = sql_command.Parameters.AddWithValue("@permissionDT", permissionDT);
    sql_param.SqlDbType = SqlDbType.Structured;
    sql_param.TypeName = "dbo.permissionType";

    result = sql_command.ExecuteNonQuery();

       if (result >= 0)
           {
               cmdText = "insert into holid select * from @vacationDT";
               sql_command.CommandText = cmdText;
               sql_command.Parameters.Clear();
               sql_param = sql_command.Parameters.AddWithValue("@vacationDT", vacationDT);
               sql_param.SqlDbType = SqlDbType.Structured;
               sql_param.TypeName = "dbo.holidType";

               result = sql_command.ExecuteNonQuery();

                      if (result >= 0)
                         {
                            cmdText = "insert into empmission select * from @missionDT";
                            sql_command.CommandText = cmdText;
                            sql_command.Parameters.Clear();
                            sql_param = sql_command.Parameters.AddWithValue("@missionDT", missionDT);
                            sql_param.SqlDbType = SqlDbType.Structured;
                            sql_param.TypeName = "dbo.empmissionType";

                            result = sql_command.ExecuteNonQuery();
                         }
          }

My Main problem is :

This process takes so long time and decrease the performance of sqlserver ,How to optimize this code and the queries concerning the db operations ?

Upvotes: 0

Views: 98

Answers (1)

agfc
agfc

Reputation: 892

Replace your SQLCommand ("insert into holid select * from @vacationDT") by SQLBulkCopy it will write you your server a lot faster. e.g.:

using (var sbc = new SqlBulkCopy(myCOnnection)
{

  sbc.DestinationTableName = "holid";
  sbc.WriteToServer(vacationDT);

}

You may have to set ColumnMappings and/or set identity insert/on-off before and after this. More on SQLBulkCopy: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx

Also, check if your DB has all the right indexes especially on start_date and end_date

Upvotes: 1

Related Questions