性能:加快应用程序 [英] Performance: Speeding up application

查看：54
本文介绍了性能:加快应用程序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

附带，我已经写了一些代码到我们的SQL DB中，执行一些查询，然后在Windows窗体上应用104个标签的结果.

我已超时这个过程，它需要大约4秒，这是路长，因为我的目标是让此过程中所做瞬间所以每当选择了新员工，他/她的属性是装载尽可能地快.

我的问题:为了实现这个目标，我能做些什么?

 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

解决方案

我至少看到了三个明显的问题，尽管由于我不在您的办公桌旁而无法说出主要的罪魁祸首.

您的SQL很可怕

只需查看此查询中表查询的数量即可！

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

由于问题的严重性，我什至无法开始为您重构它，而且我也不知道您的模式，但是我不得不猜测这是主要的罪魁祸首之一.如果可能的话，请在单个表中缓存其中的某些或全部(如果性能确实是您的主要目标).

不必要的循环

您要返回多少行?如果只需要一行，为什么还要有多行呢?这种循环是完全没有必要的，并且可能可能会损害您的性能:

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

低效率* 52 +重涂

由于上面的代码效率低下，您叫它 52次使情况变得更糟！我很惊讶，这只需要4秒钟.

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

除了效率低下的函数调用外，您还强制窗体重新绘制自身104次(myControl1.Text一次，myControl2.Text一次).某些WinForm控件(面板等)具有属性或方法，可以设置或调用该属性或方法，以使您可以在最后一次重绘时加载控件( SuspendLayout ).如果这对您不起作用，那么您可能会发现这篇文章很有帮助:

如何暂停绘画控制及其子项?

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

解决方案

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?

这篇关于性能:加快应用程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆