The code works fine but the scope of the system makes this part take many many minutes....
Where can I make this faster? Maybe combine the SQL somehow? Not sure and looking for opinions. I really do not want to reduce the scope of the system. It currently has ~4500 schools with players totaling ~170k.
The code goes through and selects every player at a given weight from a single school and then takes the player with the most skill and puts him as the sole starter for that weight at that school.
'Updates the starting player for each weight class based on skill, for each computer controlled school. Public Sub loadComputerStartingLineup() Try Dim aID, bID As Integer Dim aSKILL, bSKILL As Double con.ConnectionString = connection_String cmd.Connection = con con.Open() 'aprox 4500 schools schoolTotal = totalRows("school") 'loop through every school For i As Integer = 0 To schoolTotal Debug.Print(i) 'loop through every weight class - 14 total For j As Integer = 0 To weightClass.GetUpperBound(0) 'check to see if school is mine - do nothing if true Select Case mySchool Case i 'otherwise select id and skill level from players at current school and weight class Case Else cmd.CommandText = "SELECT ID, SKILL FROM PLAYER WHERE PLAYER.SCHOOL = " & i & " AND WEIGHT = " & weightClass(j) myReader = cmd.ExecuteReader aSKILL = 0 'compare skill to find highest skill at current weight class Do While myReader.Read() bID = myReader("ID") bSKILL = myReader("SKILL") Select Case aSKILL Case Is < bSKILL aSKILL = bSKILL aID = bID End Select Loop 'can only have one starter at a given weight for any school; so set every players starter value to false cmd.CommandText = "UPDATE PLAYER SET STARTER = 'FALSE' WHERE SCHOOL=" & i & " AND WEIGHT = " & weightClass(j) ' & " AND ID <> " & aID cmd.ExecuteNonQuery() 're-update to give highest skill a starter value of true cmd.CommandText = "UPDATE PLAYER SET STARTER = 'TRUE' WHERE ID = " & aID cmd.ExecuteNonQuery() End Select 'next weight class, same school Next 'next school Next con.Close() Catch ex As Exception MessageBox.Show(ex.StackTrace) MessageBox.Show("Error while updating record on table..." & ex.Message & vbNewLine & vbNewLine & cmd.CommandText, "Update Records") Finally con.Close() End Try End Sub