Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Stuck with this update statement Expand / Collapse
Author
Message
Posted Wednesday, May 15, 2013 3:41 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 23, 2014 2:48 PM
Points: 1,754, Visits: 4,966
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 );

Post #1453284
Posted Wednesday, May 15, 2013 8:40 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:49 AM
Points: 1,295, Visits: 2,994
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
Post #1453306
Posted Thursday, May 16, 2013 7:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 23, 2014 2:48 PM
Points: 1,754, Visits: 4,966
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.
Post #1453521
Posted Thursday, May 16, 2013 9:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:49 AM
Points: 1,295, Visits: 2,994
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...
Post #1453623
Posted Thursday, May 16, 2013 9:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Post #1453626
Posted Thursday, May 16, 2013 10:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 23, 2014 2:48 PM
Points: 1,754, Visits: 4,966
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.
Post #1453648
Posted Thursday, May 16, 2013 10:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:49 AM
Points: 1,295, Visits: 2,994
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?
Post #1453657
Posted Thursday, May 16, 2013 10:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 23, 2014 2:48 PM
Points: 1,754, Visits: 4,966
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.
Post #1453666
Posted Thursday, May 16, 2013 10:37 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
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.



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)
Post #1453667
Posted Thursday, May 16, 2013 10:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 23, 2014 2:48 PM
Points: 1,754, Visits: 4,966
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.
Post #1453681
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse