Sleek Update solution pls. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sleek Update solution pls.

Author

Topic

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-06-16 : 14:38:21

Hi,
I have a table T1(id,col1,col2,.........col15)
table T2(id,col1,col2,,,,,,,,,,col15)

Both are identical in structure, in different dbs and on same server. If any value changes only then update the respective value.

eg.
If col3 changed in T1, update T2's col3 to the value from T1 and so on.

right now it is

update t2
set col1=t1.col1
,col2=t1.col2
.
.
..
from t1 join t2 on t1.id=t2.id
where t1.col1<>t2.col1
or t1.col2<> t2.col2
.....

So, it is updating all columns, even if it is not that column that changed.
Is there a better way to do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-16 : 14:53:36

You would do this in a trigger and test for which columns have changed. I believe COLUMNS_UPDATED contains this information.

Tara

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 14:55:01

Update t2 Set col1 = t1.col1 From t2 Inner Join t1On t2.id = t1.id Where t2.col1 <> t1.col1
Update t2 Set col2 = t1.col2 From t2 Inner Join t1On t2.id = t1.id Where t2.col2 <> t1.col2
Update t2 Set col3 = t1.col3 From t2 Inner Join t1On t2.id = t1.id Where t2.col3 <> t1.col3
Update t2 Set col4 = t1.col4 From t2 Inner Join t1On t2.id = t1.id Where t2.col4 <> t1.col4
...
Update t2 Set col15 = t1.col15 From t2 Inner Join t1On t2.id = t1.id Where t2.col15 <> t1.col15

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 14:55:32

Doh... I always forget triggers...

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-06-16 : 14:58:49

Thanks Tara.
It is actually part of the build process of warehouse and obviously has huge amount of data. I do not want to use triggers and cursors.

Is it still possible to do it?

TIA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-16 : 15:04:05

No mention of cursors by me. COLUMNS_UPDATED does not use cursors. If you don't want to use triggers, then I would suggest replication.

But why not triggers? Have you tested it for performance?

Tara

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-06-16 : 15:10:39

Tara, I know you did not mention cursor but I just mentioned for anyone else reading. Like I forgot to mention that I am looking for something where I'd like to stay away from n number of update stmts for all the columns.

It is a dimension table and has > 10 millions rows. The warehouse is built/updated once a week. And they are 2 different table in two different databases and almost identical structure.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 15:20:58

are the columns name like col1, col2, col3??

or is it more like bread, milk, pasta, chocolate??

Corey

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-06-16 : 15:28:25

Columns are like FirstName,........blah,blah. Since it is a warehouse, it not in the normal form and has many columns.

So, if no other solution surfaces, which will be more optimal?
1. having upd stmt for each column
2. update all columns even if one of them changed.

Any advice is greatly appreciated.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 15:37:49

I have little experience in triggers so Tara can address that.

But of your two options, I believe option #1 would be better. It sucks to code each select, but on the other hand you only have to do it once.

Corey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-16 : 16:21:43

So why not replication?

Tara

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-16 : 18:55:31

You might try this.
I'm not sure how much difference it will make but it could be considerable if there are few rows being updated.
Let us know if you try it out.


update t2
set col1=t1.col1
,col2=t1.col2
.
.
..
from t1 where exists 
(
SELECT 1 FROM 
	( SELECT id, BINARY_CHECKSUM(*) c1chk FROM t1 at1 ) c1,
	( SELECT id, BINARY_CHECKSUM(*) c2chk FROM t2 at2 ) c2
	WHERE c1.c1chk <> c2.c2chk
	and at1.id = at2.id
	AND t1.id = c1.id
) 
and t1.id = t2.id and 
(
   t1.col1<>t2.col1
or t1.col2<> t2.col2
...
)

Not sure if you can have a correlated subquery in an update. If not, maybe this would work:


update t2
set col1=t1.col1
,col2=t1.col2
.
.
..
from t1 , t2 
where t1.id in
(
	SELECT c1.id FROM 
	( SELECT id, BINARY_CHECKSUM(*) c1chk FROM t1 ) c1,
	( SELECT id, BINARY_CHECKSUM(*) c2chk FROM t2 ) c2
	WHERE c1.c1chk <> c2.c2chk
	and c1.id = c2.id
)
and t1.id = t2.id and 
(
   t1.col1<>t2.col1
or t1.col2<> t2.col2
...
)

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-16 : 19:04:53

Also just to find out if you even need to check rows you can check the entire table first:



IF ( 
SELECT count(1) FROM 
( SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) c1chk FROM t1 ) c1,
( SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) c2chk FROM t2 ) c2
WHERE c1.c1chk <> c2.c2chk
   ) > 0 UPDATE ...

Or something like that...

Kristen
Test

22859 Posts

Posted - 2004-06-17 : 07:09:12

quote:
Originally posted by SQLCode

So, it is updating all columns, even if it is not that column that changed.
Is there a better way to do this?

Do you mind updating all columns but for only rows with at least one change?

I'm assuming that you are trying to "freshen", rather than "replace", the data in your warehouse ...

... if so then you could do


UPDATE DEST
SET    Col1 = SRC.Col1,
       Col2 = SRC.Col2,
       etc.
FROM   SourceDB.dbo.MyTable SRC
       JOIN DestinationDB.dbo.MyTable DEST
           ON DEST.PKCol1 = SRC.PKCol1
WHERE  (
             SRC.Col1 <> DEST.Col1
          OR (SRC.Col1 IS NULL AND DEST.Col1 IS NOT NULL)
          OR (SRC.Col1 IS NOT NULL AND DEST.Col1 IS NULL)
       ) AND
       (
             SRC.Col2 <> DEST.Col2
          OR (SRC.Col2 IS NULL AND DEST.Col2 IS NOT NULL)
          OR (SRC.Col2 IS NOT NULL AND DEST.Col2 IS NULL)
       )
etc.

followed by an
INSERT destination...
SELECT *
FROM source
WHERE NOT EXISTS (SELECT * FROM destination)
and
DELETE destination...
WHERE NOT EXISTS (SELECT * FROM source)

Kristen

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-17 : 07:38:39

The solution using BINARY_CHECKSUM posted by kselvia seems to be a good answer to your problem. However there is a problem using BINARY_CHECKSUM. This function can tell you if two rows are different, but it cannot tell you if they are the same. More specifically, if the checksum of two rows is different, you can be sure that they are different. But if two rows return the same checksum, there is no guarantee that these two rows are the same. They may be different and yet return the same checksum. Am I clear as mud?

OS

dataphile
Yak Posting Veteran

71 Posts

Posted - 2004-06-17 : 07:52:26

Use a view in the datawarehouse instead of a table.

create view server.db2.dbo.t2 as
select id,col1,col2,,,,,,,,,,col15 from server.db1.dbo.t1

You might even want to look into a materialised view if the structure is the same and you don't use any functions.

many tricks to it so have a look in books online.
The short version :
Create the view "with schemabinding" and create a clustered index on it.

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-06-17 : 09:53:33

Thanks guys, for your time and all the suggestions.
I do not think it falls in indexed view category. Anyways, that is not an option for me.

MohdOwais, in that case Checksum is also not an option.

Tara, I do not know much about replication. My understanding is that replication is only possible if both databases are exactly the same. They are not. Right now, it uses stored procs to build the warehouse via dts packages.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-17 : 12:11:29

The databases do not need to be exactly the same to use replication. You can setup replication to use stored procedures which you can then modify.

Tara

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-17 : 12:17:31

You might look at SQL Compare from Red Gate Software (http://www.red-gate.com/sql/summary.htm) if buying software is an option. They offer an evaluation version.

I have never use replication but it sounds like a good option. If replication is more efficient on databases than tables using stored procedures, what if you moved just this table to it's own DB and created views of it in your main database and used replication on that database? Views can be updated and selects should be transparent to the client.

I think I would put an update trigger on the main table and insert modified id's into another table, then schedule a job to update only those rows on the remote table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-17 : 12:21:06

I love SQL Compare, but it is not a solution for this. SQL Compare is used to compare two schemas. Red Gate also has a SQL Data Compare, but that is not an appropriate solution for this either.

I would go with triggers.

Tara

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-06-17 : 12:27:10

Hmmm... I need to do some research on replication. If you know any good links and pass it, I'd appreciate.

So the procedures can be set up for replication? Well, this is also part of a procedure which needs update stmts.

Uhh! Never mind. I first need to learn about replication.

Thanks.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources