Comparing massive amountdata between 2 tables on 2 databases

Thomas

Hi. I have 2 similar tables on two different databases. Lets call them A and B. I want to make a comparison and transfer all rows from A.table to B.table where there is a difference in the "value" column.

I have the following code where a loop though a datareader and for each row call a stored procedure that compare the rows.

My problem is not that the code does not word, because it does. The problem is that it takes about 10 minutes to complete about 300 rows and I have about 16 million rows to go through.

The C# code:

_____________________________________________

OdbcConnection connODBC = new OdbcConnection(strConnStringFrom);

OdbcCommand commandODBC = new OdbcCommand(strQuery, connODBC);

using (connODBC)

{

connODBC.Open();

using (OdbcDataReader reader = commandODBC.ExecuteReader())

{

using (SqlConnection con = new SqlConnection(strConnStringTo))

{

using (SqlCommand cmd = new SqlCommand(strPostSPDBFrom, con))

{

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@project_id", SqlDbType.Int);

cmd.Parameters.Add("@event_id", SqlDbType.Int);

cmd.Parameters.Add("@record", SqlDbType.VarChar);

cmd.Parameters.Add("@field_name", SqlDbType.VarChar);

cmd.Parameters.Add("@value", SqlDbType.VarChar);

cmd.Parameters.Add("@instance", SqlDbType.SmallInt);

con.Open();

while (reader.Read())

{

cmd.Parameters["@project_id"].Value = reader["project_id"];

cmd.Parameters["@event_id"].Value = reader["event_id"];

cmd.Parameters["@record"].Value = reader["record"];

cmd.Parameters["@field_name"].Value = reader["field_name"];

cmd.Parameters["@value"].Value = reader["value"];

cmd.Parameters["@instance"].Value = reader["instance"];

cmd.ExecuteNonQuery();

}

_____________________________________________

The Stored Procedure:

_____________________________________________

ALTER PROCEDURE [dbo].[sp]

@project_id AS INT = NULL,

@event_id AS NVARCHAR(100) = NULL,

@record AS VARCHAR(100) = NULL,

@field_name AS VARCHAR(100) = NULL,

@value AS VARCHAR(MAX) = NULL,

@instance AS SMALLINT = NULL

DECLARE @valueTemp varchar(MAX);

Set @valueTemp = (select value from B.table where project_id = @project_id

and event_id = @event_id

and record = @record

and field_name = @field_name

and value = @value

and instance = @instance)

IF @valueTemp IS NULL

BEGIN

insert into B.table (project_id, event_id, record, field_name, value, instance)

values (@project_id, @event_id, @record, @field_name, @value, @instance)

END

ELSE If @valueTemp <> @value

BEGIN

update B.table SET value = @value

where project_id = @project_id

and event_id = @event_id

and record = @record

and field_name = @field_name

END

_____________________________________________

Can anybody help me?

Regards Thomas

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Accepted answer
I think I can.

Please read the questions carefully and please try to answer in as much depth as you possibly can. If you have further questions, please keep on asking.

Does both table a and table b have indexes on project_id columns? That would be truly helpful.

Did you check the spread of records across project Ids? For example, fire query something like this:

select project_id, count(1) records
from tablea
group by project_id
order by prject_id

See if you can loop through only 1 project_id at a time. Meaning your first datareader should have a fixed project_id only.

Does your value column have to be varchar(max)? What does it contain? Image, xml, what is it?

This is just to start with...will dig deeper later.
+3
Nitin Sontakke9y ago
Fair enough and thank you.

I had few more tricks in my bag.

For record, do not do any DML operation in stored procedure. Just check if records exists or not. Then taking that input in C#, create a text file with the script of inserts and updates. Then execute that script in target database.
0
Thomas9y ago
My deadline is very soon and my boss has told me to go in another direction, so the question is not that relevant anymore.
I want to thank you for your time and suggestions, I am sure they would have worked.

Regards Thomas
0
Nitin Sontakke9y ago
Noted. However, unless you don't have permissions, you can create index yourself. Anyway.

As I mentioned, if you are doing it only for checking if record exists, you can modify it to be as follows and it would be lot quicker.

if exists(select 1 from B.table where project_id = @project_id and event_id = @event_id
and record = @record and field_name = @field_name and value = @value and instance = @instance)
update....
else
insert....

One more optimization you can do. While reading values from data reader read them with proper data type specific methods. As shown below:
cmd.Parameters["@project_id"].Value = reader.GetInt32(0);
cmd.Parameters["@event_id"].Value = reader.GetInt32(1);
cmd.Parameters["@record"].Value = reader.GetString(2);
cmd.Parameters["@field_name"].Value = reader.GetString(3);
cmd.Parameters["@value"].Value = reader.GetString(4);
cmd.Parameters["@instance"].Value = reader.GetInt16(5);

Use column ordinal values as shown above, instead of column names. This is quicker as well.

Put some sort logging around each execution so that you know that the performance is really improving.
+2
Thomas9y ago
I only have "control" og B and there is no index. Our technitian is on holyday and will be back to have in indexed next week.
I have changed the value column to varcher(8000) now

The point of "Set @valueTemp = (select value from B.table where project_id = @project_id" is the following:
If @valuetemp is NULL, there is no existing row and therefor i will INSERT a row.
If @valuetemp is not null I will UPDATE the row with the new value IF the value is different
0
Nitin Sontakke9y ago
What is the purpose of the following query?

Set @valueTemp = (select value from B.table where project_id = @project_id
and event_id = @event_id
and record = @record
and field_name = @field_name
and value = @value
and instance = @instance)

Are you doing this just to see if record exists? If yes, you are better of using if exists instead.
If it has to be as the way it is modify it to as follows:

select @valueTemp = value
from B.table where project_id = @project_id
and event_id = @event_id
and record = @record
and field_name = @field_name
and value = @value
and instance = @instance
+2