Performance: Speeding up application

Question

Attached, I have written some code that goes into our SQL DB, performs some queires, and then applies the results for 104 labels, on the windows form.

I have timed this procedure and it takes about 4 seconds which is way to long because my goal is to have this process done instantly so whenever a new employee is selected, his/her stats are loading as fast as possible.

My question: What can I do differenlty to make this goal possible?

Dim RESULT1 As Decimal 'declare this as global  
Dim RESULT2 As Decimal 'declare this as global

Private Sub Week(ByVal week As Integer)
    Dim queryString As String = "SELECT " & _  
        "(SELECT CAST(SUM(TARGET_SECONDS) AS DECIMAL)/ CAST(SUM(ROUTE_SECONDS) AS DECIMAL) FROM dbo.APE_BUSDRIVER_MAIN WITH(NOLOCK) WHERE WEEK_TIME = " & week & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND EMPLOYEE_NAME = '" & cbEmployeeName.Text & "' AND YEAR_TIME = '" & cbYear.Text & "' AND ACTIVE = 1) AS RESULT1," & _
        " (SELECT (SELECT CAST(COUNT(APE_BUSDRIVER_STATUS_OBJID) AS DECIMAL) FROM dbo.APE_BUSDRIVER_MAIN AS RESULT2 WHERE WEEK_TIME = " & week & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND EMPLOYEE_NAME = '" & cbEmployeeName.Text & "' AND YEAR_TIME = '" & cbYear.Text & "' AND ACTIVE = 1 AND APE_BUSDRIVER_STATUS_OBJID = 1)/(SELECT CAST(COUNT(APE_BUSDRIVER_STATUS_OBJID) AS DECIMAL) FROM dbo.APE_BUSDRIVER_MAIN AS RESULT2 WHERE WEEK_TIME = " & week & " AND APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND EMPLOYEE_NAME = '" & cbEmployeeName.Text & "' AND YEAR_TIME = '" & cbYear.Text & "' AND ACTIVE = 1)) AS RESULT2" & _
        " FROM dbo.APE_BUSDRIVER_MAIN "

    Using connection As New SqlConnection(SQLConnectionStr)
        Dim command As New SqlCommand(queryString, connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        ' Call Read before accessing data. 
        If reader.HasRows Then
            While reader.Read()
                RESULT1 = reader("RESULT1")
                RESULT2 = reader("RESULT2")
            End While
        Else
            RESULT1 = 0
            RESULT2 = 0
        End If
        ' Call Close when done reading.
        reader.Close()
    End Using
End Sub


Private Sub LoadWeeklyStats()

    For i As Integer = 0 To 51
        Week(i + 1)
        Dim LabelWkEff As String = "LblWkEff" + (i + 1).ToString
        Dim myArray1 As Array = Controls.Find(LabelWkEff, False)
        Dim myControl1 As Label = myArray1(0)
        myControl1.Text = RESULT1    


        Dim LabelDeliveryStat As String = "lblDeliveryStat" + (i + 1).ToString
        Dim myArray2 As Array = Controls.Find(LabelDeliveryStat, False)
        Dim myControl2 As Label = myArray2(0)
        myControl2.Text = RESULT2
    Next

End Sub

Answer 1

I see at least three big glaring problems, though it's impossible to say which is the main culprit because I'm not sitting at your desk.

Your SQL is Horrendous

Just look at the number of table queries in this query!

SELECT
        (
            SELECT
                    CAST(SUM(TARGET_SECONDS) AS DECIMAL) / CAST(SUM(ROUTE_SECONDS) AS DECIMAL)
            FROM
                    dbo.APE_BUSDRIVER_MAIN WITH(NOLOCK)
            WHERE
                    WEEK_TIME = @week
                AND APE_AREA_OBJID = @areaOBJID
                AND EMPLOYEE_NAME = @EmployeeName
                AND YEAR_TIME = @Year
                AND ACTIVE = 1
        ) AS RESULT1
    ,   (
            SELECT
                    (
                        SELECT
                                CAST(COUNT(APE_BUSDRIVER_STATUS_OBJID) AS DECIMAL)
                        FROM
                                dbo.APE_BUSDRIVER_MAIN AS RESULT2
                        WHERE
                                WEEK_TIME = @week
                            AND APE_AREA_OBJID = @AreaOBJID
                            AND EMPLOYEE_NAME = @EmployeeName
                            AND YEAR_TIME = @Year
                            AND ACTIVE = 1
                            AND APE_BUSDRIVER_STATUS_OBJID = 1
                    ) / (
                        SELECT
                                CAST(COUNT(APE_BUSDRIVER_STATUS_OBJID) AS DECIMAL)
                        FROM
                                dbo.APE_BUSDRIVER_MAIN AS RESULT2
                        WHERE
                                WEEK_TIME = @week
                            AND APE_AREA_OBJID = @AreaOBJID
                            AND EMPLOYEE_NAME = @EmployeeName
                            AND YEAR_TIME = @Year
                            AND ACTIVE = 1
                    )
        ) AS RESULT2
FROM dbo.APE_BUSDRIVER_MAIN

I can't even begin to refactor this for you because of the enormity of the problem and I don't know your schema, but I'd have to guess that this is one of the primary culprits. If at all possible, cache some or all of this in a single table (if performance really is your primary goal).

Unnecessary looping

How many rows are you returning? And why are there multiple rows if you only need one? This looping is completely unnecessary and might be killing some performance for you:

    If reader.HasRows Then
        While reader.Read()
            RESULT1 = reader("RESULT1")
            RESULT2 = reader("RESULT2")
        End While
    Else
        RESULT1 = 0
        RESULT2 = 0
    End If

Inefficiency * 52 + Repaint

As ineffecient as the code above is, you've made it worse by calling it 52 times! I'm amazed this is only taking 4 seconds.

For i As Integer = 0 To 51
    Week(i + 1)
    Dim LabelWkEff As String = "LblWkEff" + (i + 1).ToString
    Dim myArray1 As Array = Controls.Find(LabelWkEff, False)
    Dim myControl1 As Label = myArray1(0)
    myControl1.Text = RESULT1    


    Dim LabelDeliveryStat As String = "lblDeliveryStat" + (i + 1).ToString
    Dim myArray2 As Array = Controls.Find(LabelDeliveryStat, False)
    Dim myControl2 As Label = myArray2(0)
    myControl2.Text = RESULT2
Next

In addition to the ineffecient function call, you are forcing your form to repaint itself 104 times (once for myControl1.Text and again for myControl2.Text). Some WinForm controls (panels, etc) have a property or method you can set or call to allow you to load controls with a single repaint at the end (SuspendLayout for example). If that doesn't work for you, you may find this post helpful:

How do I suspend painting for a control and its children?