循环运行速度非常慢 [英] Loop Running VERY Slow

问题描述

这里是什么我donig：$ b $请帮助我使我的应用程序更快一点，它是永远循环通过，并给我现在的结果。 b 1.从上传的excel文件加载gridview（这可能大约有300条左右的记录）
2.比较制造商，型号和序列号到我的MS SQL数据库（大约20K条记录），看看是否有匹配。

 '根据make / model / serial查找源代码ID无组合。 
 Dim cSource As New clsSource（）
 Dim ds As DataSet = cSource.GetSources（）
 Dim found As Boolean = False 
 
'populate db datatables 
 Dim dt As DataTable = ds.Tables（0）
 Dim rows As Integer = gwResults.Rows.Count（）
 For Each行As GridViewRow In gwResults.Rows 
'move through rows and检查数据集中每行的数据
'1  -  make 
对于每个dataRow作为DataRow在dt.Rows 
 found = False 
如果dataRow（manufacturerName）= row。单元格（1）.Text然后
如果dataRow（modelName）= row.Cells（2）.Text然后
如果dataRow（serialNo）= row.Cells（3）.Text Then 
 found = True 
 End If 
 End If 
 End If 
 
'显示结果
如果找到然后
 lblResults.Text + = row.Cells（1）.Text& /& row.Cells（2）.Text& /& row.Cells（3）.Text& found
 else 
 lblResults.Text + = row.Cells（1）.Text& /& row.Cells（2）.Text& /& row.Cells（3）.Text& 找不到
结束如果
 
下一个
下一个
  

有没有更好的方法来找到两者之间的匹配？我在这里死了。

解决方案

对于您的300个gridview行中的每一行，您都循环遍历所有20k数据行。这使得300 * 20k = 600万循环迭代。难怪你的循环很慢。 ： - ）

让我建议使用以下算法（伪代码）：

  For Each gridviewrow 
用一个WHERE子句在你的数据库上执行一个SELECT语句来比较所有三个组件
如果SELECT语句返回一行
  - >找到
其他
  - >找不到
结束如果
下一个
  

有了这个解决方案，你只有300循环迭代。在每次循环迭代中，您都会在数据库上进行SELECT操作。如果您已正确编制数据库索引（例如，如果您在制造商名称， modelName 和 serialNo ），那么这个SELECT应该是非常快的 - 比在所有20k数据行循环中快得多。

数学观点来看，这会将算法的时间复杂度从 O（n * m）降低到 O（n * log m） code>，其中 n 表示gridview中的行数， m 您的记录数数据库。

please help me make my app a little faster, it's taking forever to loop through and give me results right now.

here is what im donig: 1. load gridview from an uploaded excel file (this would probably be about 300 records or so) 2. compare manufacturer, model and serial No to my MS SQL database (about 20K records) to see if there is a match.

'find source ID based on make/model/serial No combination.
        Dim cSource As New clsSource()
        Dim ds As DataSet = cSource.GetSources()
        Dim found As Boolean = False

        'populate db datatables
        Dim dt As DataTable = ds.Tables(0)
        Dim rows As Integer = gwResults.Rows.Count()
        For Each row As GridViewRow In gwResults.Rows
            'move through rows and check data in each row against the dataset
            '1 - make
            For Each dataRow As DataRow In dt.Rows
                found = False
                If dataRow("manufacturerName") = row.Cells(1).Text Then
                    If dataRow("modelName") = row.Cells(2).Text Then
                        If dataRow("serialNo") = row.Cells(3).Text Then
                            found = True
                        End If
                    End If
                End If

                'display results
                If found Then
                    lblResults.Text += row.Cells(1).Text & "/" & row.Cells(2).Text & "/" & row.Cells(3).Text & " found"
                Else
                    lblResults.Text += row.Cells(1).Text & "/" & row.Cells(2).Text & "/" & row.Cells(3).Text & " not found "
                End If

            Next
        Next

is there a better way to find a match between the two? i'm dying here.

解决方案

For each of your 300 gridview rows, you are looping through all 20k datarows. That makes 300 * 20k = 6 million loop iterations. No wonder your loop is slow. :-)

Let me suggest the following algorithm instead (pseudo-code):

For Each gridviewrow
    Execute a SELECT statement on your DB with a WHERE clause that compares all three components
    If the SELECT statement returns a row
        --> found
    Else
        --> not found
    End If
Next

With this solution, you only have 300 loop iterations. Within each loop iteration, you make a SELECT on the database. If you have indexed your database correctly (i.e., if you have a composite index on the fields manufacturerName, modelName and serialNo), then this SELECT should be very fast -- much faster than looping through all 20k datarows.

From a mathematical point of view, this would reduce the time complexity of your algorithm from O(n * m) to O(n * log m), with n denoting the number of rows in your gridview and m the number of records in your database.

这篇关于循环运行速度非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！