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.
| Author |
Topic |
|
Heinz23
Yak Posting Veteran
84 Posts |
 Posted - 2011-01-07 : 11:42:07
|
Hi all,I have a user table with 50 columns, like firstname, lastname, loginname, organisation etc. The values are retrieved on demand from another system and need to be stored in the SQL table. What is the best way: - Always send an UPDATE-command even if a row did not change
- Compare all 50 values whether there are any changes and then update the whole row if necessary
- Only update the modified / new fields (might result in 20 or 30 UPDATE commands which might not be the best idea...)
What do you think? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2011-01-07 : 12:30:30
|
| Really? So for each of the 50 columns I should do the following:If NOT (@Firstname = Firstname) UPDATE Table SET FirstName = @Firstname WHERE UserID = @UserIDAs said, this might result in 20 or 30 updates on one single run. Would this be ok? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-07 : 12:47:55
|
We do #2I definitely would not do #1 because of the logging require of records updated, but not materially changed (plus in our case we have Triggers that set UpdateDate, which would then be a bit of a joke!, and record the change in an AuditTable - and that would get a bit bloated!Personally I'm relaxed about #3. A Trigger that uses UPDATE() to determine which columns were included in the UPDATE statement could perhaps be foxed by including all columns in the UPDATE. However, we don't use UPDATE() because it ONLY relies on columns that were present in the UPDATE statement, rather than columns containing data that have ACTUALLY changed ... YMMVWe get data from the remote system into a staging table. This is a complete copy of the rows in the remote system, but ONLY the columns we are actually interested in. There is NO modification of the data at this stage.We get the data and compare against existing rows in the staging table (every column, but only the columns we are interested in) and UPDATE rows where there is a difference. We have a CHANGE DATE column on the row (and a column to indicate if it was an INSERT/UPDATE or a DELETE).We insert any rows that are NEW, and we flag any rows where that row no longer exists in the SOURCE.(Note that it is entirely possible that a row is flagged as Deleted, and then re-Flagged as Updated before it gets used anywhere else in our system - e.g. where the user does a Delete followed by a re-Create on the other system).We then use the CHANGE DATE column to process "all rows since last time" into whatever other system(s) we need(The value for "last time" is stored in the target database, so if that gets restored we will, in effect, reprocess the rows since "last time" and thus not miss any out)Our comparison is something like:-- NOTE: In practice we actually usually use OPENQUERY for this because REMOTE is usually Oracle or somesuch -- and we just want a pass-through queryINSERT INTO #TEMP(Col1, ...)SELECT Col1, ...FROM RemoteServer.RemoteDatabase.dbo.RemoteTableWHERE ChangedDate > @ChangeDateFromLastBatch -- or some other criteraUPDATE DSET Type = 'U', -- Udpated ChangeDate = GetDate(), Col1 = S.Col1, ...FROM StagingDatabase.dbo.StagingTable AS D -- Destination JOIN #TEMP AS S -- Source ON S.ID = D.IDWHERE (S.Col1 != D.Col1 OR (S.Col1 IS NULL AND D.Col1 IS NOT NULL) OR (S.Col1 IS NOT NULL AND D.Col1 IS NULL)) OR (S.Col2 ... For CHAR columns we doS.Col1 != D.Col1 COLLATE Latin1_General_BIN2 so that we catch columns where the only change is Upper/Lower case (assuming the database is case INsensitive)(We generate the WHERE clause mechanically, otherwise it would be a bit of a PITA to do / maintain!) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-07 : 12:59:34
|
quote:
Originally posted by Heinz23
Really? So for each of the 50 columns I should do the following:If NOT (@Firstname = Firstname) UPDATE Table SET FirstName = @Firstname WHERE UserID = @UserIDAs said, this might result in 20 or 30 updates on one single run. Would this be ok?
I think Tara may have been meaning to use dynamic SQL so that the SET statement, within the UPDATE, would only include columns that had changed - not that a given row would be updated multiple times, once for each COLUMN that had changed (if I'm understanding you correctly) |
|
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2011-01-08 : 06:23:43
|
| Hi Kristen,thanks for your detailed answer, it's really helpful. Regarding Dynamic SQL, that might be a good idea.But if you did not encounter any performance problems with the proposed option #2 I think I'll try this one. Many thanks for the code example! Just one further question: Is it possible to create a general comparison function? I could use such a comparison on several tables where data from external source is retrieved. So it would of course be great to have e.g. a Function 'IsModified' where I could supply 2 rows and these are then compared to each other. In first call the rows might be from table 'User' while in second call they might be from table 'Customer' or 'Order' or ... The 2 rows are of course always from the same table in one single call. Was it understandable? Is this possible?Many thanks! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-08 : 20:39:08
|
Problem with a general purpose comparison function would be the data types. Either SQL would do implicit data conversion, or you would have to have a different function for each database AND be sure you used the right one each time. It would be slow too I reckon  So we mechanically generate the comparison statements instead (i.e. we use SQL code to generate the WHERE clauses) |
|
|
|
|
|
|
|
|
Update row everytime?