Databases
StackOverflow2013
Postsdbo
POLoop takes too long to execute (looping through 365 days a year)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLoop takes too long to execute (looping through 365 days a year)
    text
    copied!<p>I have a list of manpower (40 people). I need to loop through a year (365 days) for each person to extract data for each day. But this takes too much time. Is there any suggestion to improve speed or a different method?</p> <pre><code>for (int man=0;man&lt;40;man++) { for (DateTime date = DateTime.Now.Date;date&lt;DateTime.Now.AddYears(1);date=date.AddDays(1)) { //do stuff } } </code></pre> <p>EDIT: Stuff involves extracting data from a database where i get Projects each person is involved in, and start and end dates of the projects per person. (Each person has his own table and there is a master table with a list of all men). I have a datagridview, in which I show 40 men in the vertical axis. 365 days in the horizontal. Depending on the no. of projects, each person is involved in, I need to colourcode the corresponding cell.</p> <p>EDIT 2: //A is a class that connects to a database, forwards a query and returns the result</p> <pre><code> void colourchanger() { for (int m = 0; m &lt; i; m++)//i=40 { int copy = m; List&lt;string&gt;[] list = new List&lt;string&gt;[5]; int number = A.Countproj(abc);//return no. of projects under man list = A.manprojselect(abc);//returns project details thread[copy] = new Thread(() =&gt; threader(copy,2*copy,list,number)); thread[copy].Start(); } } void threader(int p, int x,List&lt;string&gt;[] list,int numberer) { DateTime labeldate; DataGridViewCellStyle AL = new DataGridViewCellStyle(); AL.BackColor = Color.Brown;//AL DataGridViewCellStyle NS = new DataGridViewCellStyle(); NS.BackColor = Color.Aqua;//NS DataGridViewCellStyle training = new DataGridViewCellStyle(); training.BackColor = Color.Maroon;//training DataGridViewCellStyle one = new DataGridViewCellStyle(); one.BackColor = Color.Green;//one project DataGridViewCellStyle overseas = new DataGridViewCellStyle(); overseas.BackColor = Color.Blue;//overseas DataGridViewCellStyle two = new DataGridViewCellStyle(); two.BackColor = Color.Yellow;//2 projects DataGridViewCellStyle three = new DataGridViewCellStyle(); three.BackColor = Color.Red;//3 projects DataGridViewCellStyle unfeasible = new DataGridViewCellStyle(); unfeasible.BackColor = Color.Black;//self explanatory DataGridViewCellStyle none = new DataGridViewCellStyle(); none.BackColor = Color.Gray;//self explanatory string[] projname; string[] country; string[] start; string[] end; string temp; DateTime startdate; DateTime enddate; int[] track = new int[366]; string[] projnames = new string[366]; for (int y = 0; y &lt; 366; y++) { projname = list[0].ToArray(); country = list[2].ToArray(); start = list[3].ToArray(); end = list[4].ToArray(); temp = dataGridView1.Rows[x].Cells[y].ToolTipText; temp = temp[0].ToString() + temp[1].ToString() + temp[2].ToString() + temp[3].ToString() + temp[4].ToString() + temp[5].ToString() + temp[6].ToString() + temp[7].ToString() + temp[8].ToString() + temp[9].ToString(); labeldate = DateTime.ParseExact(temp, "dd-MM-yyyy", CultureInfo.InvariantCulture); for (int l = 0; l &lt; numberer; l++) { startdate = DateTime.ParseExact(start[l], "dd-MM-yyyy HH:mm:ss", CultureInfo.InvariantCulture); enddate = DateTime.ParseExact(end[l], "dd-MM-yyyy HH:mm:ss", CultureInfo.InvariantCulture); int r1 = DateTime.Compare(startdate, labeldate); int r2 = DateTime.Compare(labeldate, enddate); if (r1 &lt;= 0 &amp;&amp; r2 &lt;= 0) { track[y]++; projnames[y] = projnames[y] + ", " + projname[l]; if (String.Compare(country[l], "Singapore ") != 0) { track[y] = 10; projnames[y] = " " + projname[l]; } if (String.Compare(projname[l], "ANNUAL LEAVE") == 0) { track[y] = 20; projnames[y] = " " + projname[l]; } if (String.Compare(projname[l], "NATIONAL SERVICE") == 0) { track[y] = 30; projnames[y] = " " + projname[l]; } if (String.Compare(projname[l], "TRAINING") == 0) { track[y] = 40; projnames[y] = " " + projname[l]; } } } } for (int y = 0; y &lt; 366; y++) { if (track[y] == 0) { dataGridView1.Rows[x].Cells[y].Style = none; dataGridView1.Rows[x].Cells[y].ToolTipText = dataGridView1.Rows[x].Cells[y].ToolTipText + projnames[y]; } if (track[y] == 1) { dataGridView1.Rows[x].Cells[y].Style = one; dataGridView1.Rows[x].Cells[y].ToolTipText = dataGridView1.Rows[x].Cells[y].ToolTipText + projnames[y]; } if (track[y] == 2) { dataGridView1.Rows[x].Cells[y].Style = two; dataGridView1.Rows[x].Cells[y].ToolTipText = dataGridView1.Rows[x].Cells[y].ToolTipText + projnames[y]; } if (track[y] == 3) { dataGridView1.Rows[x].Cells[y].Style = three; dataGridView1.Rows[x].Cells[y].ToolTipText = dataGridView1.Rows[x].Cells[y].ToolTipText + projnames[y]; } if (track[y] == 10) { dataGridView1.Rows[x].Cells[y].Style = overseas; dataGridView1.Rows[x].Cells[y].ToolTipText = dataGridView1.Rows[x].Cells[y].ToolTipText + projnames[y]; } if (track[y] == 20) { dataGridView1.Rows[x].Cells[y].Style = AL; dataGridView1.Rows[x].Cells[y].ToolTipText = dataGridView1.Rows[x].Cells[y].ToolTipText + projnames[y]; } if (track[y] == 30) { dataGridView1.Rows[x].Cells[y].Style = NS; dataGridView1.Rows[x].Cells[y].ToolTipText = dataGridView1.Rows[x].Cells[y].ToolTipText + projnames[y]; } if (track[y] == 40) { dataGridView1.Rows[x].Cells[y].Style = training; dataGridView1.Rows[x].Cells[y].ToolTipText = dataGridView1.Rows[x].Cells[y].ToolTipText + projnames[y]; } if (track[y] &gt; 3 &amp;&amp; (track[y] != 10 &amp;&amp; track[y] != 20 &amp;&amp; track[y] != 30 &amp;&amp; track[y] != 40)) { dataGridView1.Rows[x].Cells[y].Style = unfeasible; dataGridView1.Rows[x].Cells[y].ToolTipText = dataGridView1.Rows[x].Cells[y].ToolTipText + projnames[y]; } } } </code></pre> <p>DB used: SQL CE </p> <p>EDIT 3: Manprojselect() posted</p> <pre><code>public List&lt;string&gt;[] manprojselect(string manpowername) { string query = "SELECT * FROM "+ manpowername; //Create a list to store the result List&lt;string&gt;[] list = new List&lt;string&gt;[5]; list[0] = new List&lt;string&gt;(); list[1] = new List&lt;string&gt;(); list[2] = new List&lt;string&gt;(); list[3] = new List&lt;string&gt;(); list[4] = new List&lt;string&gt;(); //Open connection if (this.OpenConnection() == true) { //Create Command SqlCeCommand cmd = new SqlCeCommand(query, connection); //Create a data reader and Execute the command SqlCeDataReader dataReader = cmd.ExecuteReader(); //Read the data and store them in the list while (dataReader.Read()) { list[0].Add(dataReader["Name"] + ""); list[1].Add((dataReader["Allotment_number"]).ToString() + ""); list[2].Add(dataReader["Country"] + ""); list[3].Add(dataReader["Start_date"] + ""); list[4].Add(dataReader["End_date"] + ""); } //close Data Reader dataReader.Close(); //close Connection this.CloseConnection(); //return list to be displayed return list; } else { return list; } } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload