Performance comparision of SQL queries versus LINQ in Visual Studio 2012

Question

I am new to VS 2012 and need to use it with SQL Server. I need to get data from a table in SQL Server 2012 with 500 rows and 15 columns and process the data in it and update another table. All this has to be done in 100 ms.

I tried traditional method with SQL query filling a dataset and then access the table, rows and item array in each row. It is taking seconds to execute and the time worsening.

Should I use LINQ or another solution?

try
{
    connection1.Open();
    adapter_alarms = new SqlDataAdapter(sql_alarms, connection1);
    adapter_alarms.Fill(ds_alarms);
    connection1.Close();
}
catch (Exception exp)
{
    MessageBox.Show(exp.Message);
}
if (ds_alarms.Tables[0].Rows.Count != 0)
{
    for (int i = 0; i < ds_alarms.Tables[0].Rows.Count; i++)
    {
            if (bool.Parse(ds_alarms.Tables[0].Rows[i].ItemArray[7].ToString()) == true)
                        if (engValues[index] < float.Parse(ds_alarms.Tables[0].Rows[i].ItemArray[3].ToString()))
                        {
                            string sql_Al = "INSERT INTO [MCRCDA].[dbo].[Alarms]([DateTime],[TagName],[AlarmType],[Value],[Units],[Address])    VALUES("+"'10/08/2015'" +", '"+ds_alarms.Tables[0].Rows[i].ItemArray[0].ToString().Trim()+"','Lo',10.4,'C','1.1.1')";
                            try
                            {
                                connection1.Open();
                                adapter_alarms = new SqlDataAdapter(sql_Al, connection1);
                                adapter_alarms.Fill(ds_alarms);
                                connection1.Close();
                            }
                            catch (Exception exp)
                            {
                                MessageBox.Show(exp.Message);
                            }//MessageBox.Show("Lo");
                        }
    }
}

Answer 1

You should factor out some of the dataset selection into a variable, so that you don't have to type that stuff over and over again. This also makes it easier to read.

if (ds_alarms.Tables[0].Rows.Count != 0)
{
    for (int i = 0; i < ds_alarms.Tables[0].Rows.Count; i++)
    {
        if (bool.Parse(ds_alarms.Tables[0].Rows[i].ItemArray[7].ToString()) == true)
            if (engValues[index] < float.Parse(ds_alarms.Tables[0].Rows[i].ItemArray[3].ToString()))
            {
                string sql_Al = "INSERT INTO [MCRCDA].[dbo].[Alarms]([DateTime],[TagName],[AlarmType],[Value],[Units],[Address])    VALUES("+"'10/08/2015'" +", '"+ds_alarms.Tables[0].Rows[i].ItemArray[0].ToString().Trim()+"','Lo',10.4,'C','1.1.1')";
                try
                {
                    connection1.Open();
                    adapter_alarms = new SqlDataAdapter(sql_Al, connection1);
                    adapter_alarms.Fill(ds_alarms);
                    connection1.Close();
                }
                catch (Exception exp)
                {
                    MessageBox.Show(exp.Message);
                }//MessageBox.Show("Lo");
            }
    }
}

Factor out ds_alarms.Tables[0].Rows like this:

var alarms = ds_alarms.Tables[0].Rows;
if (alarms.Count != 0)
{
    for (int i = 0; i < alarms.Count; i++)
    {
        if (bool.Parse(alarms[i].ItemArray[7].ToString()) == true)
            if (engValues[index] < float.Parse(alarms[i].ItemArray[3].ToString()))
            {
                string sql_Al = "INSERT INTO [MCRCDA].[dbo].[Alarms]([DateTime],[TagName],[AlarmType],[Value],[Units],[Address])    VALUES("+"'10/08/2015'" +", '"+alarms[i].ItemArray[0].ToString().Trim()+"','Lo',10.4,'C','1.1.1')";
                try
                {
                    connection1.Open();
                    adapter_alarms = new SqlDataAdapter(sql_Al, connection1);
                    adapter_alarms.Fill(ds_alarms);
                    connection1.Close();
                }
                catch (Exception exp)
                {
                    MessageBox.Show(exp.Message);
                }
            }
    }
}

Never put an if statement inside an if statement without curly braces. You should extract the two conditionals to variables and then put them in the same if statement like this.

var alarms = ds_alarms.Tables[0].Rows;
if (alarms.Count != 0)
{
    for (int i = 0; i < alarms.Count; i++)
    {
        bool alarm = bool.Parse(alarms[i].ItemArray[7].ToString());
        bool isValue = engValues[index] < float.Parse(alarms[i].ItemArray[3].ToString());

        if (alarm && isValue)
        {
            string sql_Al = "INSERT INTO [MCRCDA].[dbo].[Alarms]([DateTime],[TagName],[AlarmType],[Value],[Units],[Address])    VALUES("+"'10/08/2015'" +", '"+alarms[i].ItemArray[0].ToString().Trim()+"','Lo',10.4,'C','1.1.1')";
            try
            {
                connection1.Open();
                adapter_alarms = new SqlDataAdapter(sql_Al, connection1);
                adapter_alarms.Fill(ds_alarms);
                connection1.Close();
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }
    }
}

Doesn't that look cleaner? I am sure you can come up with some better variable names than I have for your booleans.

---

You shouldn't hardcode a date into the query string either this will make it very hard to maintain.

In all reality you should create a stored procedure to do that with, and then call that stored procedure with parameters, it's safer and easier to maintain.

---

Now that I look at the code again, it looks very odd. You are pulling data from a dataset in the same loop where you are trying to fill that dataset?