Performance: Speeding Up Application

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

Solution 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
        ) ASRESULT1
    ,   (
            SELECT
                    (
                        SELECT
                                CAST(COUNT(APE_BUSDRIVER_STATUS_OBJID) AS DECIMAL)
                        FROM
                                dbo.APE_BUSDRIVER_MAIN AS RESULT2
                        WHERE
                                WEEK_TIME = @weekAND APE_AREA_OBJID = @AreaOBJIDAND EMPLOYEE_NAME = @EmployeeNameAND YEAR_TIME = @YearAND ACTIVE = 1AND APE_BUSDRIVER_STATUS_OBJID = 1
                    ) / (
                        SELECT
                                CAST(COUNT(APE_BUSDRIVER_STATUS_OBJID) AS DECIMAL)
                        FROM
                                dbo.APE_BUSDRIVER_MAIN AS RESULT2
                        WHERE
                                WEEK_TIME = @weekAND APE_AREA_OBJID = @AreaOBJIDAND EMPLOYEE_NAME = @EmployeeNameAND YEAR_TIME = @YearAND ACTIVE = 1
                    )
        ) ASRESULT2FROMdbo.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 ThenWhile reader.Read()
            RESULT1 = reader("RESULT1")
            RESULT2 = reader("RESULT2")
        EndWhileElse
        RESULT1 = 0
        RESULT2 = 0EndIf

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 AsInteger = 0To51
    Week(i + 1)
    Dim LabelWkEff AsString = "LblWkEff" + (i + 1).ToString
    Dim myArray1 As Array = Controls.Find(LabelWkEff, False)
    Dim myControl1 As Label = myArray1(0)
    myControl1.Text = RESULT1    


    Dim LabelDeliveryStat AsString = "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?