Continue to Site

Welcome to EDAboard.com

Welcome to our site! EDAboard.com is an international Electronics Discussion Forum focused on EDA software, circuits, schematics, books, theory, papers, asic, pld, 8051, DSP, Network, RF, Analog Design, PCB, Service Manuals... and a whole lot more! To participate you need to register. Registration is free. Click here to register now.

Register Log in

VS 2012 with SQL Server 2008 a code fragment takes too long

Status
Not open for further replies.
F

fouwad

Full Member level 4
Joined
Nov 29, 2009
Messages
199
Helped
19
Reputation
38
Reaction score
17
Trophy points
1,298
Location
Pakistan
Activity points
2,466
Hi all
i am using VS 2012 and SQL Server 2008
a chunk of code is to be called repeatedly
i have placed it in a timer tick event

it is taking longer than anticipated and as the time passess the execution time increases
i am posting the potential area of problem
thanks


Code: 
if (ds_alarms.Tables[0].Rows.Count != 0) //resolving four different types of alarms for all signals in ds_alarms
                {
                    DateTime time = DateTime.Now;              // Use current time
                    SqlCommand sql_Al=new SqlCommand();
                    sql_Al.Connection = connection1;
                    var alarmSet = ds_alarms.Tables[0].Rows;
                    for (int i = 0; i < alarmSet.Count; i++)
                    {
                        int index = listOfTags.FindIndex(x => x.Equals(ds_alarms.Tables[0].Rows[i].ItemArray[0].ToString().Trim()));
                        bool isAlarm = bool.Parse(ds_alarms.Tables[0].Rows[i].ItemArray[5].ToString()) == true;
                        bool isAlarmValue=engValues[index] > float.Parse(ds_alarms.Tables[0].Rows[i].ItemArray[1].ToString());
                            
                        if (isAlarm && isAlarmValue)
                            {
                                sql_Al.CommandText = "INSERT INTO [MCRCDA].[dbo].[Alarms]([DateTime],[TagName],[AlarmType],[Value],[Units],[Address])    VALUES(" + time.ToString(format) + ", '" + ds_alarms.Tables[0].Rows[i].ItemArray[0].ToString().Trim() + "','HiHi'," + engValues[index] + ",'" + ds_alarms.Tables[0].Rows[i].ItemArray[9].ToString().Trim() + "','" + ds_alarms.Tables[0].Rows[i].ItemArray[10].ToString().Trim() + "')";
                                try
                                {
                                    if (connection1.State != ConnectionState.Open)
                                        connection1.Open();
                                    sql_Al.ExecuteNonQuery();
                                    connection1.Close();
                                }
                                catch (Exception exp)
                                {
                                    MessageBox.Show(exp.Message);
                                }
                            }

                            isAlarm = bool.Parse(ds_alarms.Tables[0].Rows[i].ItemArray[6].ToString()) == true;
                            isAlarmValue = engValues[index] > float.Parse(ds_alarms.Tables[0].Rows[i].ItemArray[2].ToString());
                            if (isAlarm && isAlarmValue)
                            {
                                sql_Al.CommandText = "INSERT INTO [MCRCDA].[dbo].[Alarms]([DateTime],[TagName],[AlarmType],[Value],[Units],[Address])    VALUES(" + time.ToString(format) + ", '" + ds_alarms.Tables[0].Rows[i].ItemArray[0].ToString().Trim() + "','HiHi'," + engValues[index] + ",'" + ds_alarms.Tables[0].Rows[i].ItemArray[9].ToString().Trim() + "','" + ds_alarms.Tables[0].Rows[i].ItemArray[10].ToString().Trim() + "')";
                                try
                                {
                                    if (connection1.State != ConnectionState.Open)
                                        connection1.Open();
                                    sql_Al.ExecuteNonQuery();
                                    connection1.Close();
                                }
                                catch (Exception exp)
                                {
                                    MessageBox.Show(exp.Message);
                                }
                            }

                            isAlarm = bool.Parse(ds_alarms.Tables[0].Rows[i].ItemArray[7].ToString()) == true;
                            isAlarmValue = engValues[index] < float.Parse(ds_alarms.Tables[0].Rows[i].ItemArray[3].ToString());
                            if (isAlarm && isAlarmValue)
                            {
                                sql_Al.CommandText = "INSERT INTO [MCRCDA].[dbo].[Alarms]([DateTime],[TagName],[AlarmType],[Value],[Units],[Address])    VALUES(" + time.ToString(format) + ", '" + ds_alarms.Tables[0].Rows[i].ItemArray[0].ToString().Trim() + "','HiHi'," + engValues[index] + ",'" + ds_alarms.Tables[0].Rows[i].ItemArray[9].ToString().Trim() + "','" + ds_alarms.Tables[0].Rows[i].ItemArray[10].ToString().Trim() + "')";
                                try
                                {
                                    if (connection1.State != ConnectionState.Open)
                                        connection1.Open();
                                    sql_Al.ExecuteNonQuery();
                                    connection1.Close();
                                }
                                catch (Exception exp)
                                {
                                    MessageBox.Show(exp.Message);
                                }
                            }

                            isAlarm = bool.Parse(ds_alarms.Tables[0].Rows[i].ItemArray[8].ToString()) == true;
                            isAlarmValue = engValues[index] < float.Parse(ds_alarms.Tables[0].Rows[i].ItemArray[4].ToString());
                            if (isAlarm && isAlarmValue)
                            {
                                sql_Al.CommandText = "INSERT INTO [MCRCDA].[dbo].[Alarms]([DateTime],[TagName],[AlarmType],[Value],[Units],[Address])    VALUES(" + time.ToString(format) + ", '" + ds_alarms.Tables[0].Rows[i].ItemArray[0].ToString().Trim() + "','LoLo'," + engValues[index] + ",'" +  ds_alarms.Tables[0].Rows[i].ItemArray[9].ToString().Trim() + "','" + ds_alarms.Tables[0].Rows[i].ItemArray[10].ToString().Trim() + "')";
                                try
                                {
                                    if (connection1.State != ConnectionState.Open)
                                        connection1.Open();
                                    sql_Al.ExecuteNonQuery();
                                    connection1.Close();
                                }
                                catch (Exception exp)
                                {
                                    MessageBox.Show(exp.Message);
                                }
                            }
   
                    }
                }
 
Last edited by a moderator:

fouwad said:
Hi all
i am using VS 2012 and SQL Server 2008
a chunk of code is to be called repeatedly
i have placed it in a timer tick event

it is taking longer than anticipated and as the time passess the execution time increases
i am posting the potential area of problem
thanks



if (ds_alarms.Tables[0].Rows.Count != 0) //resolving four different types of alarms for all signals in ds_alarms
{
DateTime time = DateTime.Now; // Use current time
SqlCommand sql_Al=new SqlCommand();
sql_Al.Connection = connection1;
var alarmSet = ds_alarms.Tables[0].Rows;
for (int i = 0; i < alarmSet.Count; i++)
{
int index = listOfTags.FindIndex(x => x.Equals(ds_alarms.Tables[0].Rows.ItemArray[0].ToString().Trim()));
bool isAlarm = bool.Parse(ds_alarms.Tables[0].Rows.ItemArray[5].ToString()) == true;
bool isAlarmValue=engValues[index] > float.Parse(ds_alarms.Tables[0].Rows.ItemArray[1].ToString());

if (isAlarm && isAlarmValue)
{
sql_Al.CommandText = "INSERT INTO [MCRCDA].[dbo].[Alarms]([DateTime],[TagName],[AlarmType],[Value],[Units],[Address]) VALUES(" + time.ToString(format) + ", '" + ds_alarms.Tables[0].Rows.ItemArray[0].ToString().Trim() + "','HiHi'," + engValues[index] + ",'" + ds_alarms.Tables[0].Rows.ItemArray[9].ToString().Trim() + "','" + ds_alarms.Tables[0].Rows.ItemArray[10].ToString().Trim() + "')";
try
{
if (connection1.State != ConnectionState.Open)
connection1.Open();
sql_Al.ExecuteNonQuery();
connection1.Close();
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
}

isAlarm = bool.Parse(ds_alarms.Tables[0].Rows.ItemArray[6].ToString()) == true;
isAlarmValue = engValues[index] > float.Parse(ds_alarms.Tables[0].Rows.ItemArray[2].ToString());
if (isAlarm && isAlarmValue)
{
sql_Al.CommandText = "INSERT INTO [MCRCDA].[dbo].[Alarms]([DateTime],[TagName],[AlarmType],[Value],[Units],[Address]) VALUES(" + time.ToString(format) + ", '" + ds_alarms.Tables[0].Rows.ItemArray[0].ToString().Trim() + "','HiHi'," + engValues[index] + ",'" + ds_alarms.Tables[0].Rows.ItemArray[9].ToString().Trim() + "','" + ds_alarms.Tables[0].Rows.ItemArray[10].ToString().Trim() + "')";
try
{
if (connection1.State != ConnectionState.Open)
connection1.Open();
sql_Al.ExecuteNonQuery();
connection1.Close();
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
}

isAlarm = bool.Parse(ds_alarms.Tables[0].Rows.ItemArray[7].ToString()) == true;
isAlarmValue = engValues[index] < float.Parse(ds_alarms.Tables[0].Rows.ItemArray[3].ToString());
if (isAlarm && isAlarmValue)
{
sql_Al.CommandText = "INSERT INTO [MCRCDA].[dbo].[Alarms]([DateTime],[TagName],[AlarmType],[Value],[Units],[Address]) VALUES(" + time.ToString(format) + ", '" + ds_alarms.Tables[0].Rows.ItemArray[0].ToString().Trim() + "','HiHi'," + engValues[index] + ",'" + ds_alarms.Tables[0].Rows.ItemArray[9].ToString().Trim() + "','" + ds_alarms.Tables[0].Rows.ItemArray[10].ToString().Trim() + "')";
try
{
if (connection1.State != ConnectionState.Open)
connection1.Open();
sql_Al.ExecuteNonQuery();
connection1.Close();
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
}

isAlarm = bool.Parse(ds_alarms.Tables[0].Rows.ItemArray[8].ToString()) == true;
isAlarmValue = engValues[index] < float.Parse(ds_alarms.Tables[0].Rows.ItemArray[4].ToString());
if (isAlarm && isAlarmValue)
{
sql_Al.CommandText = "INSERT INTO [MCRCDA].[dbo].[Alarms]([DateTime],[TagName],[AlarmType],[Value],[Units],[Address]) VALUES(" + time.ToString(format) + ", '" + ds_alarms.Tables[0].Rows.ItemArray[0].ToString().Trim() + "','LoLo'," + engValues[index] + ",'" + ds_alarms.Tables[0].Rows.ItemArray[9].ToString().Trim() + "','" + ds_alarms.Tables[0].Rows.ItemArray[10].ToString().Trim() + "')";
try
{
if (connection1.State != ConnectionState.Open)
connection1.Open();
sql_Al.ExecuteNonQuery();
connection1.Close();
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
}

}
}
Click to expand...


its been a while since i worked with sql,
but first thing that come in to my mind is to try to put all
inserts in same transaction.
 

Status
Not open for further replies.

Similar threads

Part and Inventory Search

Welcome to EDABoard.com

Sponsor

Back
Top