how to deal with large data from sql

Question

0.00/5 (No votes)

See more:

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 :

VB

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.

Posted 9-Sep-14 20:42pm

Hidhoud1991

Add a Solution

Comments

Hidhoud1991 10-Sep-14 2:43am

The code may have some errors because i didnt copy the whole code

_Asif_ 10-Sep-14 2:51am

Are both DBs hosted on same instance?

Hidhoud1991 10-Sep-14 3:00am

yes

RAHUL(10217975) 10-Sep-14 3:09am

I have 1 option i.e. Update it in chunk size means 1000 records then next 1000 records this may ease your query

Hidhoud1991 10-Sep-14 3:13am

could u suggest me a code for doing such update i tried to do that but it only effected the first 1000 rows not the rest

RAHUL(10217975) 10-Sep-14 3:21am

First get the count of rows and Divide by 1000 or by Maths Mod if you got more than 1 then you update it ,you can do it try it out you will get it

Hidhoud1991 10-Sep-14 3:23am

what should we write in the query to do that update??

Hidhoud1991 10-Sep-14 3:18am

im thinking of doing the temp table to get the emails that i want to update and then join them with the main table to delete but before doing so i have to copy that temp table to the other database to join it with the other table that i want to update the status in then delete those temp tables ... this my consume so much time :(

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2014-09-09T21:03:00

Solution 1

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.

SQL

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.

Posted 9-Sep-14 21:03pm

_Asif_

Comments

Hidhoud1991 10-Sep-14 3:09am

i think they are on the same instance but how can we confirm that and if they are on the same instance what else could we do

Hidhoud1991 10-Sep-14 3:11am

Does the store procedure take a long string .. in my case (strEmails)

_Asif_ 10-Sep-14 3:45am

Yes they can. Like this

CREATE PROCEDURE myProc
@IDs varchar(max)
AS

Hidhoud1991 10-Sep-14 3:50am

but it will take the same time to load and i have 2 databases i have to update the status on BD2 to active according to the selected emails from DB1 so that procedure will not do the work since it belong to one of the databases right

_Asif_ 10-Sep-14 4:06am

You can have SP like this which will do all the stuff

CREATE PROCEDURE UPdateEmailStatus
@SomeValue VARCHAR(MAX)
AS
BEGIN

UPDATE T
SET Status='Active'
FROM TABLEDB1 T
INNER JOIN DB2.dbo.TABLEDB2 TA on T.EMAIL = TA.EMAIL
WHERE TA.Value = @SomeValue;

DELETE T
FROM TABLEDB1 T
INNER JOIN DB2.dbo.TABLEDB2 TA on T.EMAIL = TA.EMAIL
WHERE TA.Value = @SomeValue;

Delete B
FROM BounceMail B
INNER JOIN DB2.dbo.TABLEDB2 TA on B.EMAIL = TA.EMAIL
WHERE TA.Value = @SomeValue;

END

Hidhoud1991 10-Sep-14 3:20am

if they are on the same instance can we create a join between 2 tables in different databases?? if so which connection sting should be use and how could we join them

_Asif_ 10-Sep-14 3:57am

You just need to give the access rights to the user you are connecting with on TableDB2.

You can use below sql for queries another db table

select * from DB1.dbo.TableDB1

Below is the access format

select * from [server].[database].[schema].[table]

Hidhoud1991 10-Sep-14 4:11am

ill try it out thanks a lot man.. i appreciate it

Senthil Sambandam · Answer 2 · 2014-11-15T04:41:00

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.

VB

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