[meowbits]

I am sure it is not the greatest but I have been more worried about functionality - until now.
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.


As always,
Thanks!

'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

[_HAWK_]

Take out the debug.print part - that makes large loops execute very slow.

You are also connecting to your database alot depending on the Case - is that necessary?

[nK0de]

Using Stored Procedures can boost the performance considerably.
Since you don't have multiple options to chose from, I suggest using If Else statements instead of Select Case blocks.

[AdamSpeight2008]

Also use parametrised queries.

[Ryano121]

Just to add on to what AdamSpeight2008 said about using parametrised queries, here is a tutorial about its importance (its in C#, but you should get the gist)

Parameterizing Your SQL Queries: The RIGHT Way To Query A Database.

[meowbits]

Thanks.

I tried to make a query using parameters but I am a little confused on how to use the reader with it. How do I use the function as a reader to return multiple values?
I have not tried the second or third function yet due to errors on the first, is there a way to combine them or is it best to have them separate as far as performance goes?

Line 23 is where I use the reader, or try too.

    '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 & schoolTotal)

                'loop through every weight class - 14 total
                For j As Integer = 0 To weightClass.GetUpperBound(0)

                    'find players
                    'cmd.CommandText = "SELECT ID, SKILL FROM PLAYER WHERE PLAYER.SCHOOL = " & i & " AND WEIGHT = " & weightClass(j)
                    'myReader = cmd.ExecuteReader
                    myReader = selectPlayerIdSkill(i, weightClass(j))
                    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
                        If bSKILL > aSKILL Then
                            aSKILL = bSKILL
                            aID = bID
                        End If
                    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()
                    updatePlayerStarterFalse(i, weightClass(j))

                    ''re-update to give highest skill a starter value of true
                    'cmd.CommandText = "UPDATE PLAYER SET STARTER = 'TRUE' WHERE ID = " & aID
                    'cmd.ExecuteNonQuery()
                    updatePlayerStarterTrue(i, weightClass(j), aID)

                    '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


    Public Function selectPlayerIdSkill(ByVal schoolId As Integer, ByVal weight As Integer) As System.Data.SqlServerCe.SqlCeDataReader
        Dim sql As String = "SELECT ID, SKILL FROM PLAYER WHERE SCHOOL = @schoolId AND WEIGHT = @weight"
        Using cn As New SqlCeConnection(connection_String), cmd As New SqlCeCommand(sql, cn)
            cmd.Parameters.Add("@schoolId", SqlDbType.Int, 4).Value = schoolId
            cmd.Parameters.Add("@weight", SqlDbType.Int, 4).Value = weight
            Return cmd.ExecuteReader()
        End Using
    End Function

    Public Function updatePlayerStarterFalse(ByVal schoolId As Integer, ByVal weight As Integer)
        Dim sql As String = "UPDATE PLAYER SET STARTER = @FALSE WHERE SCHOOL = @schoolId AND WEIGHT = @weight"
        Using cn As New SqlCeConnection(connection_String), cmd As New SqlCeCommand(sql, cn)
            cmd.Parameters.Add("@schoolId", SqlDbType.Int, 4).Value = schoolId
            cmd.Parameters.Add("@weight", SqlDbType.Int, 4).Value = weight
            Return cmd.ExecuteNonQuery()
        End Using
    End Function

    Public Function updatePlayerStarterTrue(ByVal schoolId As Integer, ByVal weight As Integer, ByVal playerId As Integer)
        Dim sql As String = "UPDATE PLAYER SET STARTER = @TRUE WHERE SCHOOL = @schoolId AND WEIGHT = @weight AND ID = @playerId"
        Using cn As New SqlCeConnection(connection_String), cmd As New SqlCeCommand(sql, cn)
            cmd.Parameters.Add("@schoolId", SqlDbType.Int, 4).Value = schoolId
            cmd.Parameters.Add("@weight", SqlDbType.Int, 4).Value = weight
            cmd.Parameters.Add("@playerId", SqlDbType.Int, 4).Value = playerId
            Return cmd.ExecuteNonQuery()
        End Using
    End Function

This post has been edited by meowbits: 23 March 2012 - 03:02 AM

[raziel_]

parametrised queries are just for your query string for example:

cmd.CommandText ="Select SomeFields From MyTable Where Field1=@f1 AND Field2=@f2
cmd.Parameters.AddWithValue("@f1",myFirstVal)
cmd.Parameters.AddWithValue("@f2",mySecondVal)
myReader = cmd.ExecuteReader

While(myReader.Read)
'DO HERE WHAT YOU WANT WITH YOUR RESULT
End While

The param. queries protects your Database from injections that are inserted in your query to the db. the reader just returns the results from your query and you can select the fields that you want from it.

This post has been edited by raziel_: 23 March 2012 - 06:54 AM

[meowbits]

ty