主题：[求助]关于效率优化的问题~~~~~~~~~~

 在一个窗口table的一个按钮 button里有一段代码

此主题相关图片如下：表格.jpg



Dim dtb As New DataTableBuilder("历史进店记录1")
dtb.Build()
DataTables("历史进店记录1").Fill("Select * From [历史进店记录]","华泰内控系统-ERP",False)
Tables("售后客户档案_售后客户档案Table2").StopRedraw

For Each dr As Row In Tables("售后客户档案_售后客户档案Table2")
    If DataTables("历史进店记录1").Compute("Count(进店时间)","进店时间 >= #"& Functions.Execute("服务器时间").addyears(-1) &"# And 底盘号 = '" & dr("底盘号") & "'") >= 3 Then
        dr("客户等级") = "A"
    ElseIf DataTables("历史进店记录1").Compute("Count(进店时间)","进店时间 >= #"& Functions.Execute("服务器时间").addyears(-1) &"# And 底盘号 = '" & dr("底盘号") & "'") = 2 Then
        dr("客户等级") = "B"
    ElseIf DataTables("历史进店记录1").Compute("Count(进店时间)","进店时间 >= #"& Functions.Execute("服务器时间").addyears(-1) &"# And 底盘号 = '" & dr("底盘号") & "'") = 1 Then
        dr("客户等级") = "C"
    Else
        dr("客户等级") = "D"
    End If
   
    If Functions.Execute("服务器时间").addmonths(-3) < dr("建档日期") Then
        dr("客户等级") = "A"
    End If
   
    If dr.IsNull("编号") = True Then
        dr("客户等级") = "E"
    End If
Next
Tables("售后客户档案_售后客户档案Table2").ResumeRedraw



这段代码就是用来根据今天的时间来调整 整个表里的客户等级    我一执行这段代码就出现卡死，
后来我优化了代码，采用了查询与赋值分开的做法



Dim dtb As New DataTableBuilder("历史进店记录1")
dtb.Build()
DataTables("历史进店记录1").Fill("Select * From [历史进店记录]","华泰内控系统-ERP",False)
Tables("售后客户档案_售后客户档案Table2").StopRedraw

Dim Dic As new Dictionary(of Row, String)
For Each dr As Row In Tables("售后客户档案_售后客户档案Table2")
    If DataTables("历史进店记录1").Compute("Count(进店时间)","进店时间 >= #"& Functions.Execute("服务器时间").addyears(-1) &"# And 底盘号 = '" & dr("底盘号") & "'") >= 3 Then
        dic.Add(dr, "A")
    ElseIf DataTables("历史进店记录1").Compute("Count(进店时间)","进店时间 >= #"& Functions.Execute("服务器时间").addyears(-1) &"# And 底盘号 = '" & dr("底盘号") & "'") = 2 Then
        dic.Add(dr, "B")
    ElseIf DataTables("历史进店记录1").Compute("Count(进店时间)","进店时间 >= #"& Functions.Execute("服务器时间").addyears(-1) &"# And 底盘号 = '" & dr("底盘号") & "'") = 1 Then
        dic.Add(dr, "C")
    Else
        dic.Add(dr, "D")
    End If
Next

For Each dr As Row In dic.Keys
    dr("客户等级") = dic(dr)
Next
Tables("售后客户档案_售后客户档案Table2").ResumeRedraw

但是还出现卡死，求解了~~~~~~~~~~~~~~~~~~

 两个地方存在优化的余地。

 第一个是 获取服务器时间，这个不需要每次重复获取的，在开始的时候获取一次就行了。

 第二个是 你在循环里多次使用到Compute，你可以改用其他的方法统计，参考代码优化一章。当然，最好是上传你的一个下例子啦。

http://www.foxtable.com/help/topics/2219.htm

http://www.foxtable.com/help/topics/1935.htm

 代码基本写出来了，但是有个小问题 。由于代码比较繁琐，所以放项目出来，希望解答下~~~

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:管理项目2.rar

在主表为 售后客户档案里 的情况下 我在命令窗口执行下面的命令会出错


此主题相关图片如下：未命名.jpg



Dim dt As Date = Date.Today
Dim dt1 As Date
Dim sm As Double
Dim v As Double
Dim ro As Row

Tables("售后客户档案").StopRedraw
Dim Dic As new Dictionary(of Row, String)
Dim drs As List(of DataRow)
For Each dr As Row In Tables("售后客户档案").Rows
    drs = DataTables("历史进店记录").Select("底盘号 = '" & dr("底盘号") & "'","公里数 desc")
    v = drs(0)("公里数")
    drs = DataTables("历史进店记录").Select("底盘号 = '" & dr("底盘号") & "'","进店时间 desc")
    dt1 = drs(0)("进店时间")
    If  v < 7000 And (CDate(drs(0)("进店时间")) - dt1).totaldays < 180 Then   '''最大公里数未超过7000公里，半年之内有来过店里维修的
        dic.Add(dr, "A")
        Continue For
    End If
    If dt.addmonths(-3) < dr("建档日期") Then
        dic.Add(dr, "A")
        Continue For
    End If
    If dr.IsNull("编号") = True Then
        dic.Add(dr, "E")
        Continue For
    End If
   
    sm = 0   '''根据drs的历史进店记录来计算来店的的次数
    For Each dr1 As DataRow In drs
        If dr1("进店时间") >= dt.addyears(-1) Then
            sm = sm + 1
        Else
            Continue For
        End If
    Next
   
    If sm >=3 Then
        dic.Add(dr, "A")
    ElseIf sm = 2 Then
        dic.Add(dr, "B")
    ElseIf sm = 1 Then
        dic.Add(dr, "C")
    Else
        dic.Add(dr, "D")
    End If
Next
For Each dr As Row In dic.Keys
    dr("客户等级") = dic(dr)
Next
Tables("售后客户档案").ResumeRedraw

貌似把红色的部分删除了 就没啥问题 

 究竟是什么问题？？

 优化，例子。

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:管理项目2.table

 代码。
Dim cmd As New SQLCommand
cmd.CommandText = "select a.[_Identify] from {售后客户档案} as a left join {历史进店记录} as b on (a.底盘号 = b.底盘号) where 进店时间 >= #" & Date.Today.AddYears(-1) & "#"
Dim dt As DataTable = cmd.ExecuteReader

DataTables("售后客户档案").StopRedraw
Dim prev As Integer = dt.DataRows(0)("_Identify")
Dim count As Integer = 0
For Each dr As DataRow In dt.DataRows
    If prev <> dr("_Identify") Then
        Dim fdr As DataRow = DataTables("售后客户档案").Find("_Identify = " & prev)
        If count >= 3 Then
            fdr("客户等级") = "A"
        Else If count = 2 Then
            fdr("客户等级") = "B"
        Else If count = 1 Then
            fdr("客户等级") = "C"
        Else
            fdr("客户等级") = "D"          
        End If
        prev = dr("_Identify")
        count = 0
    Else
        count +=1
    End If
Next
DataTables("售后客户档案").ResumeRedraw

 或者，代码者可以这样写。

Dim cmd As New SQLCommand
cmd.CommandText = "select a.[_Identify], b.进店时间 from {售后客户档案} as a left join {历史进店记录} as b on (a.底盘号 = b.底盘号)"
Dim dt As DataTable = cmd.ExecuteReader

DataTables("售后客户档案").StopRedraw
Dim prev As Integer = dt.DataRows(0)("_Identify")
Dim count As Integer = 0
For Each dr As DataRow In dt.DataRows
    If prev <> dr("_Identify") Then
        Dim fdr As DataRow = DataTables("售后客户档案").Find("_Identify = " & prev)
            If count >= 3 Then
                fdr("客户等级") = "A"
            Else If count = 2 Then
                fdr("客户等级") = "B"
            Else If count = 1 Then
                fdr("客户等级") = "C"
            Else
            fdr("客户等级") = "D"           
        End If
        prev = dr("_Identify")
        count = 0
    Else If dr("进店时间") >= Date.Today.AddYears(-1) Then
        count +=1
    End If
Next
DataTables("售后客户档案").ResumeRedraw

 红色部分 要添加的  您有话的代码里貌似没这段~

 看看是不是这样。

Dim cmd As New SQLCommand
cmd.CommandText = "select a.[_Identify], a.编号, a.建档日期, b.公里数, b.进店时间 from {售后客户档案} as a left join {历史进店记录} as b on (a.底盘号 = b.底盘号)"
Dim dt As DataTable = cmd.ExecuteReader

DataTables("售后客户档案").StopRedraw
Dim prev As Integer = dt.DataRows(0)("_Identify")
Dim count As Integer = 0
Dim level As String = ""
For Each dr As DataRow In dt.DataRows
    If prev <> dr("_Identify") Then
        Dim fdr As DataRow = DataTables("售后客户档案").Find("_Identify = " & prev)
        If level > "" Then
            fdr("客户等级") = level
        Else
            If count >= 3 Then
                fdr("客户等级") = "A"
            Else If count = 2 Then
                fdr("客户等级") = "B"
            Else If count = 1 Then
                fdr("客户等级") = "C"
            Else
                fdr("客户等级") = "D"
            End If
        End If
        prev = dr("_Identify")
        count = 0
        level = ""
    Else If dr("进店时间") >= Date.Today.AddYears(-1) Then
        count +=1
    End If
    If dr("公里数") < 7000 AndAlso dr("进店时间") > Date.Today.AddDays(-180) Then
        level = "A"
    Else If dr("建档日期") > Date.Today.AddMonths(-3) Then
        level = "A"
    Else If dr.IsNull("编号") Then
        level = "E"
    End If
Next
DataTables("售后客户档案").ResumeRedraw

 多谢了 我去调试下看看！