Copy Data from Informix to SQL Server

Question

The following method copies data from Informix db to SQL Server db and it's so slow, I want to refactor it so it could be more readable and faster:

public static int copyEmpData(DataTable employeeDT, DateTime from_date, DateTime to_date, string con)
{

    string db_con = con;
    if (con != "1")
        con = "1";
    if (string.IsNullOrEmpty(con))
    {
        con = "1";
    }

    int month_from = from_date.Month;
    int month_to = to_date.Month;
    int yr_from = from_date.Year;
    int yr_to = to_date.Year;
    int result;
    //int Ldayfrom=  DateTime.DaysInMonth(yr_from,month_from);
    int Ldayto = DateTime.DaysInMonth(yr_to, month_to);
    string fromdate = from_date.Month + "-01-" + from_date.Year;
    string todate = to_date.Month + "-" + Ldayto + "-" + to_date.Year;
    string separator;
    string emplyeeRange = "";
    foreach (DataRow row in employeeDT.Rows)
    {
        if (row[0].ToString().Trim() == employeeDT.Rows[employeeDT.Rows.Count - 1][0].ToString().Trim())//point to last index            
            separator = "";
        else
            separator = ", ";
        emplyeeRange += row[0] + separator;
    }

    //1- select data from informix
    //---------------------------------------------------------------------------
    DBConnection ifx_conn = new DBConnection(con, false);
    //    DBConnection ifdoc_conn = new DBConnection("5", false);
    Dictionary<string, string> paramList1 = new Dictionary<string, string>();
    paramList1.Add("from_date", from_date.ToShortDateString());
    paramList1.Add("to_date", to_date.ToShortDateString());


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

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

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

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

    cmdText = "select * from KLvacationadds where emp_num in(" + emplyeeRange + " ) and vacation_date>=? and vacation_date <=?"; //"select * from KLpermission where emp_num = ? and month(perm_daet)=?"; 
    DataTable vacationadds = ifx_conn.Return_DataTable(cmdText, CommandType.Text, paramList1);


    cmdText = "select distinct * from KLreqovertime where month(req_date)= " + from_date.Month + " and year(req_date)=" + from_date.Year +
              " and emp_num in (" + emplyeeRange + ")";
    DataTable KLreqovertime = ifx_conn.Return_DataTable(cmdText);



    cmdText = "select * from KLjobinfo where emp_num in ( " + emplyeeRange + ") and month(job_date)=" + from_date.Month + " and year(job_date)=" + from_date.Year;
    DataTable KLjobinfo = ifx_conn.Return_DataTable(cmdText);


    cmdText = "select * from KLempjobtitle where emp_num in ( " + emplyeeRange + ")";
    DataTable KLempjobtitle = ifx_conn.Return_DataTable(cmdText);


    //Get Employees' Data
    //=============================
    DataTable empData = new DataTable();
    empData.Columns.Add("BADGENUMBER", typeof(string));
    empData.Columns.Add("NAME", typeof(string));
    empData.Columns.Add("dep_code", typeof(int));
    empData.Columns.Add("dep_year", typeof(int));
    empData.Columns.Add("camp_code", typeof(int));

    for (int i = 0; i < employeeDT.Rows.Count; i++)
    {
        empData.Merge(GetEmployeeData(Convert.ToInt32(employeeDT.Rows[i][0]), db_con));
    }

    //2- copy data to sql
    //---------------------------------------------------------------------------
    if (con != "1")
    {
        DBConnection ifx_conn2 = new DBConnection("1", false);

        string map_num = ifx_conn2.Return_Scalar("select map_value from hp0empnum_map where db_code=" + con);
        if (!string.IsNullOrEmpty(map_num))
        {
            emplyeeRange = "";
            foreach (DataRow row in employeeDT.Rows)
            {
                if (row[0].ToString().Trim() == employeeDT.Rows[employeeDT.Rows.Count - 1][0].ToString().Trim())//point to last index            
                    separator = "";
                else
                    separator = ", ";
                emplyeeRange += (int.Parse(map_num) + int.Parse(row[0].ToString())) + separator;
            }

        }

        for (int z = 0; z < empData.Rows.Count; z++)
        {
            empData.Rows[z]["BADGENUMBER"] = (int.Parse(map_num) + int.Parse(empData.Rows[z]["BADGENUMBER"].ToString()));
        }

        for (int z = 0; z < permissionDT.Rows.Count; z++)
        {
            permissionDT.Rows[z]["emp_num"] = (int.Parse(map_num) + int.Parse(permissionDT.Rows[z]["emp_num"].ToString()));
        }
        for (int z = 0; z < vacationDT.Rows.Count; z++)
        {
            vacationDT.Rows[z]["emp_num"] = (int.Parse(map_num) + int.Parse(vacationDT.Rows[z]["emp_num"].ToString()));
        }
        for (int z = 0; z < missionDT.Rows.Count; z++)
        {
            missionDT.Rows[z]["emp_num"] = (int.Parse(map_num) + int.Parse(missionDT.Rows[z]["emp_num"].ToString()));
        }
        for (int z = 0; z < holspDT.Rows.Count; z++)
        {
            holspDT.Rows[z]["emp_num"] = (int.Parse(map_num) + int.Parse(holspDT.Rows[z]["emp_num"].ToString()));
        }
        for (int z = 0; z < vacationadds.Rows.Count; z++)
        {
            vacationadds.Rows[z]["emp_num"] = (int.Parse(map_num) + int.Parse(vacationadds.Rows[z]["emp_num"].ToString()));
        }
        for (int z = 0; z < KLreqovertime.Rows.Count; z++)
        {
            KLreqovertime.Rows[z]["emp_num"] = (int.Parse(map_num) + int.Parse(KLreqovertime.Rows[z]["emp_num"].ToString()));
        }
        for (int z = 0; z < KLjobinfo.Rows.Count; z++)
        {
            KLjobinfo.Rows[z]["emp_num"] = (int.Parse(map_num) + int.Parse(KLjobinfo.Rows[z]["emp_num"].ToString()));
        }
        for (int z = 0; z > KLempjobtitle.Rows.Count; z++)
        {
            KLempjobtitle.Rows[z]["emp_num"] = (int.Parse(map_num) + int.Parse(KLempjobtitle.Rows[z]["emp_num"].ToString()));
        }
    }


    SqlConnection sql_connection = new SqlConnection(ConfigurationManager.ConnectionStrings["sql2"].ToString());
    sql_connection.Open();
    SqlTransaction sql_current_trans = sql_connection.BeginTransaction();
    SqlCommand sql_command = sql_connection.CreateCommand();
    sql_command.Transaction = sql_current_trans;

    SqlParameter sql_param;


    //2-1- delete old data
    //--------------------

    cmdText = "delete from KLpermission 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();//sql_conn.Execute_NonQueryWithTransaction(cmdText, CommandType.Text, paramList1);

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

        sql_command.CommandText = cmdText;
        result = sql_command.ExecuteNonQuery();//result = sql_conn.Execute_NonQueryWithTransaction(cmdText, CommandType.Text, paramList1);

        if (result >= 0)
        {

            cmdText = "delete from KLempmission where emp_num in( " + emplyeeRange + " ) and to_date>=@from_date and from_date<=@to_date";
            sql_command.CommandText = cmdText;
            result = sql_command.ExecuteNonQuery();//sql_conn.Execute_NonQueryWithTransaction(cmdText, CommandType.Text, paramList1);

            if (result >= 0)
            {

                cmdText = "delete from KLholsp 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 KLvacationadds where emp_num in( " + emplyeeRange + " ) and vacation_date>=@from_date and vacation_date<=@to_date";
                    sql_command.CommandText = cmdText;
                    result = sql_command.ExecuteNonQuery();

                    if (result >= 0)
                    {

                        cmdText = "delete from KLreqovertime where emp_num in( " + emplyeeRange + " )and month(req_date)= " + from_date.Month + " and year(req_date)=" + from_date.Year;
                        sql_command.CommandText = cmdText;
                        result = sql_command.ExecuteNonQuery();

                        if (result >= 0)
                        {

                            cmdText = "Delete from KLjobinfo where emp_num in ( " + emplyeeRange + ") and month(job_date)=" + from_date.Month + " and year(job_date)=" + from_date.Year;
                            sql_command.CommandText = cmdText;
                            result = sql_command.ExecuteNonQuery();


                            if (result >= 0)
                            {

                                cmdText = "Delete from KLempjobtitle where emp_num in ( " + emplyeeRange + ")";

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


                                if (result >= 0)
                                {

                                    //2-2- insert new data 
                                    //--------------------
                                    cmdText = "insert into KLpermission 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.hp3permissionType";

                                    result = sql_command.ExecuteNonQuery();

                                    if (result >= 0)
                                    {
                                        cmdText = "insert into KLholid 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.hp3holidType";

                                        result = sql_command.ExecuteNonQuery();

                                        if (result >= 0)
                                        {
                                            cmdText = "insert into KLempmission 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.hp3empmissionType";

                                            result = sql_command.ExecuteNonQuery();

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

                                                result = sql_command.ExecuteNonQuery();//sql_conn.Execute_NonQueryWithTransaction(cmdText, CommandType.Text, paramList1);
                                                if (vacationadds != null && vacationadds.Rows.Count > 0)
                                                {
                                                    vacationadds.Columns[1].DataType = typeof(DateTime);
                                                    for (int y = 0; y < vacationadds.Rows.Count; y++)
                                                    {
                                                        vacationadds.Rows[y][4] = DateTime.Parse(vacationadds.Rows[y][4].ToString()).ToShortTimeString();
                                                    }
                                                }
                                                vacationadds.AcceptChanges();

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

                                                    result = sql_command.ExecuteNonQuery();//sql_conn.Execute_NonQueryWithTransaction(cmdText, CommandType.Text, paramList1);

                                                    if (result >= 0)
                                                    {

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

                                                        result = sql_command.ExecuteNonQuery();
                                                        if (result >= 0)
                                                        {

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

                                                                cmdText = "insert into KLempjobtitle select * from @KLempjobtitle";
                                                                sql_command.CommandText = cmdText;
                                                                sql_command.Parameters.Clear();
                                                                sql_param = sql_command.Parameters.AddWithValue("@KLempjobtitle", KLempjobtitle);
                                                                sql_param.SqlDbType = SqlDbType.Structured;
                                                                sql_param.TypeName = "dbo.hp2empjobtitle";
                                                                result = sql_command.ExecuteNonQuery();



                                                                if (result >= 0)
                                                                {

                                                                    //2-3- Update Employees Data
                                                                    //---------------------------


                                                                    cmdText = "UPDATE USRRD " +
                                                                                " SET " +
                                                                                " USRRD.NAME = b.NAME, " +
                                                                                " USRRD.dep_year = b.dep_year , " +
                                                                                " USRRD.camp_code = b.camp_code, " +
                                                                                " USRRD.dep_code = b.dep_code , " +
                                                                                " USRRD.DEFAULTDEPTID = b.dep_code + 1 " +
                                                                                " FROM " +
                                                                                " USRRD" +
                                                                                " INNER JOIN    " +
                                                                                " @USRRD b " +
                                                                                " ON " +
                                                                                " USRRD.BADGENUMBER = b.BADGENUMBER  " +
                                                                                " WHERE " +
                                                                                " USRRD.BADGENUMBER in (" + emplyeeRange + ")";

                                                                    sql_command.CommandText = cmdText;
                                                                    sql_command.Parameters.Clear();
                                                                    sql_param = sql_command.Parameters.AddWithValue("@USRRD", empData);
                                                                    sql_param.SqlDbType = SqlDbType.Structured;
                                                                    sql_param.TypeName = "dbo.UserInfoType2";
                                                                    result = sql_command.ExecuteNonQuery();

                                                                    if (result >= 0)
                                                                    {


                                                                        int M_fromDate = from_date.Month;
                                                                        int M_todate = to_date.Month;
                                                                        SQLDBConnection db_connection = new SQLDBConnection("sql2", false);
                                                                        cmdText = "select count(*) from KLvacationadds where emp_num in( " + emplyeeRange + " ) and month(vacation_date)=" + M_fromDate + " and month(vacation_date)=" + M_todate + " and DB_flag='SQL'";
                                                                        DataTable KLvacationadds = db_connection.Return_DataTable(cmdText);


                                                                        if (hp3vacationadds.Rows.Count > 0)
                                                                        {
                                                                            cmdText = "delete KLvacationadds where emp_num in(" + emplyeeRange + " ) and month(vacation_date)=" + M_fromDate + " and month(vacation_date)=" + M_todate + " and DB_flag='SQL'";
                                                                            int k = ifx_conn.Execute_NonQuery(cmdText);

                                                                            cmdText = "insert into KLvacationadds select * from @KLvacationadds";
                                                                            int z = ifx_conn.Execute_NonQuery(cmdText);


                                                                        }



                                                                        sql_current_trans.Commit();
                                                                        if (sql_connection.State == ConnectionState.Open)
                                                                        {
                                                                            sql_connection.Close();
                                                                        }
                                                                    }
                                                                    else
                                                                    {
                                                                        //roll back
                                                                        sql_current_trans.Rollback();
                                                                        if (sql_connection.State == ConnectionState.Open)
                                                                        {
                                                                            sql_connection.Close();
                                                                        }
                                                                    }
                                                                }

                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            else
                            {
                                //roll back
                                sql_current_trans.Rollback();
                                if (sql_connection.State == ConnectionState.Open)
                                {
                                    sql_connection.Close();
                                }
                            }

                        }
                        else
                        {
                            //roll back
                            sql_current_trans.Rollback();
                            if (sql_connection.State == ConnectionState.Open)
                            {
                                sql_connection.Close();
                            }
                        }
                    }
                    else
                    {
                        //roll back
                        sql_current_trans.Rollback();
                        if (sql_connection.State == ConnectionState.Open)
                        {
                            sql_connection.Close();
                        }
                    }
                }
                else
                {
                    //roll back
                    sql_current_trans.Rollback();
                    if (sql_connection.State == ConnectionState.Open)
                    {
                        sql_connection.Close();
                    }
                }

            }
            else
            {
                //roll back
                sql_current_trans.Rollback();
                if (sql_connection.State == ConnectionState.Open)
                {
                    sql_connection.Close();
                }
            }
        }
        else
        {
            //roll back
            sql_current_trans.Rollback();
            if (sql_connection.State == ConnectionState.Open)
            {
                sql_connection.Close();
            }
        }
    }
    else
    {
        //roll back
        sql_current_trans.Rollback();
        if (sql_connection.State == ConnectionState.Open)
        {
            sql_connection.Close();
        }
    }
    if (result >= 0)
    {
        return 1;
    }
    else
    {
        return -1;
    }
}

Answer 1

@BCdotWEB gave you some constructive criticism about how to restructure this code to help break a giant operation into smaller, more manageable pieces. I strongly suggest you do that regardless of what your optimization research uncovers.

I'm going to try and help with your specific question of how to make this existing method faster as it currently is written. Your answer is to either reduce redundancy or refactor to more efficient mechanisms.

Reduce Redundancy

Take a look at the places where you repeat an operation that you 'know' will give you the same results. For example: You make this call: 'int.Parse(map_num)' inside of a half-dozen different for loops. However, map_num is a string you set at the top of the method. Since it doesn't change, just parse it once and save that integer for future use throughout the method. (ie. var map_num_int = int.Parse(map_num);)

More Efficient Mechanisms

Unfortunately, I can't help you much here. I don't know what these tables are are why so many of them need to be iterated over every row based on the addition of a single employee's data. From a high level, the code doesn't seem to be well thought out. Following @BCdotWEB's suggestions will help here because the more you try to untangle the logic, the more you may find that you're working too hard to copy this data.

I'd hazard a guess that it would be better to deal with a single employee row at a time rather than building up a brand new empData table and then doing a mass-insert. This existing method seems like it could result in enormous memory issues if employeeDT is very large.

HOWEVER, You can't really start optimizing until you isolate precisely which operations are taking the longest. It's possible one database call is slowing down everything and causing the entire function to perform slowly.

I suggest you can start adding simple debugging checks to see where your application is taking the longest time to complete. Visual Studio has profilers that can help with this, but my go-to method for optimization is much simpler to set up. Simply add some DateTime.Now statements before your suspected 'long' operations and then calculate the total elapsed time and print it to your logger (or console) of choice. For example:

    var startTime = DateTime.Now;
    for (var i = 0; i < 5; i++)
    {
        System.Threading.Thread.Sleep(500);
        Console.WriteLine("Elapsed Time: {0}", (DateTime.Now - startTime).TotalMilliseconds);
    }
    Console.ReadLine();

You can be as clever with this as you prefer (for example putting a timestamp right inside each of your nested if statements and then calculating the total elaspsed time for each sub-operation), but it takes just a few seconds to try each area.

Answer 2

A method that's nearly 500 lines? Which does I don't know how many different database actions? Which has at least a dozen nested ifs? And that's just what I gather from a first glance.

You'll definitely need to refactor this into much smaller methods, each of which does one thing. Structure your code.

---

Just looking at those first lines is worrisome.

The method is incorrectly named (method names must be PascalCase), contains an unusual abbreviation (what is "Emp"?), and is not telling me much: copyEmpData. I'm willing to bet this method does a whole lot more than merely copying the data, considering it is close to 500 lines.

Equally bad are the parameter names: employeeDT shouldn't contain an abbreviation of what its type is (a DataTable). And from_date and to_date contain underscores which goes against the naming conventions; parameter names should be camelCase.

You pass a connectionstring as the final parameter -- at least I assume that's what con is. Except next there's this code:

string db_con = con;
if (con != "1")
    con = "1";
if (string.IsNullOrEmpty(con))
{
    con = "1";
}

First of all: how can con be null or empty, considering you assign "1" as its value on the previous lines if the value of con isn't "1"? Secondly: is con some kind of boolean? And also: why are both con and db_con used in the rest of the code? See, that's what bad naming does: I cannot figure out what con and db_con are supposed to be, even within context.

---

Why do you do this:

int month_from = from_date.Month;
int month_to = to_date.Month;
int yr_from = from_date.Year;
int yr_to = to_date.Year;

Why don't you simply use from_date.Month etc. in the rest of your code? In fact, some of these variables aren't used at all!

---

This is unreadable and simply bad:

string emplyeeRange = "";
foreach (DataRow row in employeeDT.Rows)
{
    if (row[0].ToString().Trim() == employeeDT.Rows[employeeDT.Rows.Count - 1][0].ToString().Trim())//point to last index            
        separator = "";
    else
        separator = ", ";
    emplyeeRange += row[0] + separator;
}

It's not even ten lines long and yet trying to figure out what it is supposed to do is way too hard. Plus you're concatenating strings, which is almost always a bad idea if you're not using a StringBuilder. Or, in this case, string.Join().

---

I'm only thirty lines into your code, and already this review is starting to get long.

Next I see another badly named variable (ifx_conn), and another one (paramList1).

I notice strings like "BADGENUMBER" which are repeatedly used, so they should be const.

There's a block of code that starts with if (con != "1"), yet at the top you've already made sure that con can only be "1".

I see the same similar lines repeated over and over again, which suggests they should be written as a single method with a couple of appropriate parameters:

empData.Rows[z]["BADGENUMBER"] = (int.Parse(map_num) + int.Parse(empData.Rows[z]["BADGENUMBER"].ToString()));
// ...
permissionDT.Rows[z]["emp_num"] = (int.Parse(map_num) + int.Parse(permissionDT.Rows[z]["emp_num"].ToString()));
// ...
vacationDT.Rows[z]["emp_num"] = (int.Parse(map_num) + int.Parse(vacationDT.Rows[z]["emp_num"].ToString()));

How many times did you copy-paste this block:

else
{
    //roll back
    sql_current_trans.Rollback();
    if (sql_connection.State == ConnectionState.Open)
    {
        sql_connection.Close();
    }
}

The moment you start copy-pasting code, you need to realize you're doing something wrong, and that such code should be moved to a method of its own.

---

Roughly speaking this code should be split into multiple classes:

• one to retrieve data from Informix,
• one to transform that data into whatever empData is,
• one to update the various DataTables which contain the Informix data,
• one to maintain the SQL Server tables.

Start from there, and create small methods.

Also find out which part is slow. Right now it could be anywhere, and since we don't know how much data this code is dealing with. You might even need to rethink the whole SQL Server part if the slowness is caused by the need to keep everything in one transaction.

But first split up your code into smaller chunks and determine which part is causing the slowness.

---

BTW Don't use AddWithValue().