如何处理来自sql的大数据 [英] how to deal with large data from sql

问题描述

我有2个数据库DB1和DB2



DB1有一个包含ID，电子邮件和状态的TABLEDB1



DB2包含TABLEDB2，其中包含ID，电子邮件，值，....



我想更改TABLEDB1中的电子邮件状态在DB2上



i使用此代码：

I have 2 databases DB1 and DB2

DB1 have a TABLEDB1 that contain an ID, Email , and Status

DB2 contain TABLEDB2 that contain ID, email, value ,....

I want to change the status of email in TABLEDB1 accourding to the value on DB2

i used this code :

Dim dttable1 as datatable = execouteselect("Select distinct Email from TABLEDB2 where value = 'somevalue'")

'the number of records return can reach up to 50,000 record 

If dttable1 .Rows.Count > 0 Then
                For Each dr As DataRow In dttable1 .Rows
                    strEmails = (strEmails & Convert.ToString(",")) + "'" + Convert.ToString(dr("Email") + "'")
                Next
                strEmails = strEmails.Remove(0, 1)
 End If

If strEmails <> "" Then
                select_mail_id = EmailCodes.ExecuteSelect("Select Email,ID From TABLEDB1  where Email in (" & strEmails & ")")
                If select_mail_id.Rows.Count > 0 Then
                    For Each dr As DataRow In select_mail_id.Rows
                        strID = (strID & Convert.ToString(",")) + Convert.ToString(dr("ID"))
                    Next
                    strID = strID.Remove(0, 1)
                    Dim Change_status As String = "UPDATE TABLEDB1 set status = 'Active' Where ID_Email in (" & strID & ")"
                   EmailCodes.Execute(Change_status)
            End If
                Dim Email_soft As String = "Delete top (1000) from TABLEDB2 Where Email in (" & strEmails & ")"

                Dim rowupdt3 As Integer = 1
                While rowupdt3 > 0
                    rowupdt3 = BounceCodes.Execute(Email_soft)
                End While

                Dim Email_hard As String = "Delete top (1000) from BounceMail Where Email in (" & strEmails & ")"

                Dim rowupdt4 As Integer = 1
                While rowupdt4 > 0
                    rowupdt4 = BounceCodes.Execute(Email_hard)
                End While
            End If

此代码可以很好地处理小的返回记录，但它会丢失大量数据的资源异常



做这个程序的任何建议

谢谢。

this code work fine with small returned records but it throw and out of resource exception on large data

any suggestions to do that procedure
Thanks.

推荐答案

如果我是你，我可能会把这段代码移到存储过程中。无论如何，不​​需要第二个sql来获取ID，这个ID进一步用于构建逗号分隔的ID字符串，然后由更新消耗。你可以完全忽略它。您可以创建类似于下面的SQL Batch，它可以一次完成您的所有工作。

If i were you i probably moved this code into Stored Procedure. Anyway there is no need to have second sql that fetches ID which further be used to build comma separated ID string and later consumed by an update. You can totally ignore it. You can create SQL Batch similar to below which can do all your work in one go.

Begin

  UPDATE TABLEDB1 set status = 'Active' 
  Where Email in (strEmails);

  Delete from TABLEDB2 Where Email in (strEmails);

  Delete from BounceMail Where Email in (strEmails)
  
End;

如果两个数据库都在同一个实例上，那么你甚至不需要先选择。把它作为提示再试一次。

If both DB are on same instance then you don't even required first select. Take that as a hint and try again.

错误可能是因为多循环，很多变量和经常调用数据库连接。循环技术总是存在大数据的问题。所以在下面的代码中我使用较少的循环，很少的变量和最小的数据库连接。

The error may be because of mulitple loop, lot of variables and calling database connection frequently. The loop technique always problem for huge data. So In below code I using less loop, few variables and min database connections.

Dim dttable1 as datatable = execouteselect("Select distinct Email from TABLEDB2 where value = 'somevalue'")

Dim dRow as DataRow 
Dim rCnt as Integer =0
For Each row in dttable1.rows()
    rCnt=rCnt+1
    dRow=Nothing
    dRow= dttable2.select("email")
    If dRow IsNot Nothing then
       'Execute your update/delete query here
    End If
Next

这篇关于如何处理来自sql的大数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！