如何处理来自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.

推荐答案

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.

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的大数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！