Issue during update
-
Hello,
I have created a job that fill and update a database from a source db with same structure.
INSERT code is made up comparing the pk column in the source and dest db, the missed ones are filled in the destination.
UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB
UPDATE tableADest
SET col1=source.Col1, col2=source.col2, ... coln=source.coln
FROM tableASource source
INNER JOIN tableAdest dest
ON dest.colpk = source.colpk
The main problem is that I cannot identify the row update in the souce db, everytime I have to compare the whole equivalent tables (source and dest db), because there are not timestamp, updated cols or any cols usefuls, to have a subset of data and find any new or upadeted rows.
I thing to use a trigger to catch the pk (new and updated) for each big table may useful.
I tried replication, but it does not work on that db.
What do you think?
Thank
-
One small thing may help here...
INNER JOIN ON ... WHERE Source.Col1 <> Dest.Col1 --watch out for nulls here
That way you have less writes on the disk, but it's harder on the cpu, but then again, it's faster to perform.
Another thing you may be able to do is have audit triggers in place that flag the rows to update (just by inserting the id col in another table). Other than that, there's always a full backup / restore that will most likely outperform the transfer method.
-
Hello,
thank for your faster reply.
I thought the same with trigger.
Interesting the solution on Where condition.
Back/restore is not useful because the destination db has more tables than the source.
Thank
-
Anyway you can setup replication?
I'm no expert at this but it may very well solve the problem.
-
One last trick...
Have you tried TRUNCATE / Full insert?
Truncate is almost not logged and the insert would have a lot of writes, but 0 work on the cpu.
-
Hello,
already done for replication, but some tables have problem, so we decided another way.
I will try your suggestions
Thank
-
Hello,
I have tried to add <> for columns in the UPDATE statement, it looks like faster, but you say that can affect CPU.
Affecting CPU can affect other operation on server ?
Any suggestion will be very appreciated.
Thank
-
Test it both ways. The advantage of the <> method is that the server will do less writes on the HD (which is usually the slowest operation to do on the server). However the cost of saving those writes is to do some more checks to see if the write is required. This takes more cpu power, but less HD power. Depending on where your server is shorter on ressources, it might be better to do more writes and less cpu cycles. Both you have to test that in your environement to be sure. In my experience, it's usually better to make the cpu work... but then again, you always have to test on the pc in question.
-
Set up a trigger on source table to record (in separate table) PK values affected by INSERT/UPDATE/DELETE statements performed on that table.
Then you need just join to this trace table in order to refresh only changed rows.
Don't forget to clean trace table after synchronization is completed.
_____________
Code for TallyGenerator -
Hello,
days ago I applied both solution together: "<>" and Triggers.
Appliyng "<>" is good, performance increase a lot.
Triggers as well.
But when I tried them in test machine, the application that make continuosly update and insert on source table has gone time out everytime, because of triggers. When I disabled triggers non errors occur anymore.
How can I use triggers avoiding that they affect any application running against source database?
Thank
-
> How can I use triggers avoiding that ...
Don't use cursors in triggers.
_____________
Code for TallyGenerator -
Trigger should contain single statement:
INSERT INTO dbo.IDsToUpdate (ID)
SELECT i.PK_ID
FROM inserted i
WHERE NOT EXISTS (select 1 from dbo.IDsToUpdate U
where U.ID = i.PK_ID)
Then use join to this table in you UPDATE statement.
_____________
Code for TallyGenerator -
UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB
...that might be part of the problem... takes a bit to do that...
Why not just compare the Binary_CheckSum of the two rows?
And, as Serqiy said... don't use a cursor in a trigger. I'll also throw in, don't use ANY kind of a loop in a trigger.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hello,
my triggers is little bit different, but produce the same result.
I' ll try to modify them following your example just to see any increasing in performance.
About cursor or triggers they are not present. The application goes time out only when triggers are enabled.
Thank
-
The,I will test it and I inform you about that
Thank
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply