Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stuck with this update statement


Stuck with this update statement

Author
Message
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4623 Visits: 9571
WHERE A.Pid = 9
AND ( Isnull(A.ID, 0) <> Isnull(V.ID, 0)
OR Isnull(A.FacilityPatientID, '') <> Isnull(V.FacilityPatientID, '')
OR Isnull(A.FirstName, '') <> Isnull(V.FirstName, '')
OR Isnull(A.MiddleInitial, '') <> Isnull(V.MiddleInitial, '')
OR Isnull(A.LastName, '') <> Isnull(V.LastName, '')
OR Isnull(A.SSN, '') <> Isnull(V.SSN, '') . . .
. . .
. . .
. . .


If you are comparing two tables with similar columns, something like a transactional table versus a staging table, then perhaps you can add to both tables a persisted computed column derived from a CHECKSUM of the column list. Once done, add index on PID + the checksum column. If my assumption is correct, the join operation could potentially be covered by this 2 column index, which would be very efficient.

create table MYTable
(
pid int not null,
a int null, b int null, c int null,
xupdate as checksum( a, b, c) persisted
);

create index ix_pid_xupdate on MYTable ( pid, xupdate );




"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
Eric M Russell (5/15/2013)
WHERE A.Pid = 9
AND ( Isnull(A.ID, 0) <> Isnull(V.ID, 0)
OR Isnull(A.FacilityPatientID, '') <> Isnull(V.FacilityPatientID, '')
OR Isnull(A.FirstName, '') <> Isnull(V.FirstName, '')
OR Isnull(A.MiddleInitial, '') <> Isnull(V.MiddleInitial, '')
OR Isnull(A.LastName, '') <> Isnull(V.LastName, '')
OR Isnull(A.SSN, '') <> Isnull(V.SSN, '') . . .
. . .
. . .
. . .


If you are comparing two tables with similar columns, something like a transactional table versus a staging table, then perhaps you can add to both tables a persisted computed column derived from a CHECKSUM of the column list. Once done, add index on PID + the checksum column. If my assumption is correct, the join operation could potentially be covered by this 2 column index, which would be very efficient.

create table MYTable
(
pid int not null,
a int null, b int null, c int null,
xupdate as checksum( a, b, c) persisted
);

create index ix_pid_xupdate on MYTable ( pid, xupdate );




Thanks. Few questions before i try:

i) in reality i have about 20 columns in my table, so for this new computer column would i do like checksum( col1,col2...col 20)?

ii) What changes do i need to make in my update statement?

Thanks
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4623 Visits: 9571
curious_sqldba (5/15/2013)

Thanks. Few questions before i try:

i) in reality i have about 20 columns in my table, so for this new computer column would i do like checksum( col1,col2...col 20)?

ii) What changes do i need to make in my update statement?

Thanks

The CHECKSUM function returns a hashed integer value computed over a list of columns or expressions. I'm thinking you would include the full list of non-key columns for which you are comparing.

select 
checksum( 200, 'abc', '2013/05/10' ) checksum_a,
checksum( 200, 'xyz', '2013/05/10' ) checksum_b;

checksum_a checksum_b
----------- -----------
-713813704 -713651384



If you are comparing two different tables, then when adding the checksum column, the column list and datatypes should be specified exactly the same to insure successful matching. I don't know what maximum number of columns are supported in the list, but it's far more than 20 at least.

In your JOIN or WHERE clause, you would still be doing a seperate exact match on the key column(s) like PID or ID. So if you want to write a query that compares tables A and B, both keyed on PID, and return those rows for which non-key columns are different, it would be something like this:

select *
from A
join B on B.PID = A.PID and B.xchecksum != A.xchecksum;



For that reason, the index on each table would be on PID and xchecksum.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
curious_sqldba (5/15/2013)
Eric M Russell (5/15/2013)
WHERE A.Pid = 9
AND ( Isnull(A.ID, 0) <> Isnull(V.ID, 0)
OR Isnull(A.FacilityPatientID, '') <> Isnull(V.FacilityPatientID, '')
OR Isnull(A.FirstName, '') <> Isnull(V.FirstName, '')
OR Isnull(A.MiddleInitial, '') <> Isnull(V.MiddleInitial, '')
OR Isnull(A.LastName, '') <> Isnull(V.LastName, '')
OR Isnull(A.SSN, '') <> Isnull(V.SSN, '') . . .
. . .
. . .
. . .


If you are comparing two tables with similar columns, something like a transactional table versus a staging table, then perhaps you can add to both tables a persisted computed column derived from a CHECKSUM of the column list. Once done, add index on PID + the checksum column. If my assumption is correct, the join operation could potentially be covered by this 2 column index, which would be very efficient.

create table MYTable
(
pid int not null,
a int null, b int null, c int null,
xupdate as checksum( a, b, c) persisted
);

create index ix_pid_xupdate on MYTable ( pid, xupdate );




Thanks. Few questions before i try:

i) in reality i have about 20 columns in my table, so for this new computer column would i do like checksum( col1,col2...col 20)?

ii) What changes do i need to make in my update statement?

Thanks


Ok makes sense, i am joining on a table and a view, i might have to add this column on all the underlying tables...
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8979 Visits: 19028
There's a very good chance that the smart folks here could eliminate the view from the query, if they could see the view definition.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4623 Visits: 9571
curious_sqldba (5/16/2013)
[quote]
Ok makes sense, i am joining on a table and a view, i might have to add this column on all the underlying tables...

I don't know what lay beneath your view, but indexed hash columns are an efficient alternative to performing non-equal joins on non-key and non-indexed columns. It's useful in a scenario where you need to know if at least one of X columns differ, but you arn't concerned with which specific column.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
Eric M Russell (5/16/2013)
curious_sqldba (5/16/2013)
[quote]
Ok makes sense, i am joining on a table and a view, i might have to add this column on all the underlying tables...

I don't know what lay beneath your view, but indexed hash columns are an efficient alternative to performing non-equal joins on non-key and non-indexed columns. It's useful in a scenario where you need to know if at least one of X columns differ, but you arn't concerned with which specific column.


If i join a view and a table, do i need to have that computed columns on all the tables used in the view?
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4623 Visits: 9571
curious_sqldba (5/16/2013)
Eric M Russell (5/16/2013)
curious_sqldba (5/16/2013)
[quote]
Ok makes sense, i am joining on a table and a view, i might have to add this column on all the underlying tables...

I don't know what lay beneath your view, but indexed hash columns are an efficient alternative to performing non-equal joins on non-key and non-indexed columns. It's useful in a scenario where you need to know if at least one of X columns differ, but you arn't concerned with which specific column.


If i join a view and a table, do i need to have that computed columns on all the tables used in the view?

Now it sounds as if the table you're updating, what you're calling MyTable in the provided example, is a denormalized and it's columset is derived from multiple other tables joined behind the scenes in a view.

If that's the case, then you'll need to add a column computed on checksum() on MyTable, and then index it on ID and checksum. You'll also need to add the same computed column on the view, but that one can't be persisted or indexable. Still it will be an improvement over what you're doing now comparing 20+ columns against each other.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
I may be wwrong, but since all the columns used to update the table come from the columns defined in the view, I would say the computed column needs to be added to the view.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4623 Visits: 9571
Lynn Pettis (5/16/2013)
I may be wwrong, but since all the columns used to update the table come from the columns defined in the view, I would say the computed column needs to be added to the view.

This is really sounding like a big denormalized table with columns derived from multiple other tables, and now there is a need to keep it in sync with updates.

An alternative to performing perioddic mass updates would be to implment insert / update triggers on the base tables, which would then insert and update columns in the denormalized table incrementally as changes occur.

Or perhaps that big table could be something like an indexed view rather than an actual table.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search