May 16, 2013 at 10:03 am
curious_sqldba (5/16/2013)
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 16, 2013 at 10:17 am
Eric M Russell (5/16/2013)
curious_sqldba (5/16/2013)
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?
May 16, 2013 at 10:37 am
curious_sqldba (5/16/2013)
Eric M Russell (5/16/2013)
curious_sqldba (5/16/2013)
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 16, 2013 at 10:37 am
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.
May 16, 2013 at 10:56 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 16, 2013 at 11:03 am
Eric M Russell (5/16/2013)
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.
Without seeing the DDL for the view it is hard to say if an indexed view would be the answer. The use of outer joins, non-deterministic functions, etc. would make that idea moot.
May 16, 2013 at 11:31 am
is Id unique in both tables? If it isn't, maybe you have a cross join going on because you're joining on it. Now if you have a case sensitive collation, then Id might be different from ID, but I noticed that you're also updating a column called "ID" so if you have a case sensitive collation, then maybe you are thinking Id isn't what you think it is. Just a wild guess based on your query!
Why aren't you joining on facilitypatientid instead?!?!
May 17, 2013 at 1:05 am
patrickmcginnis59 10839 (5/16/2013)
is Id unique in both tables? If it isn't, maybe you have a cross join going on because you're joining on it. Now if you have a case sensitive collation, then Id might be different from ID, but I noticed that you're also updating a column called "ID" so if you have a case sensitive collation, then maybe you are thinking Id isn't what you think it is. Just a wild guess based on your query!Why aren't you joining on facilitypatientid instead?!?!
Heh - wild guesses are all we can provide until, like Lynn states, we have sight of the view definition 😛
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
May 17, 2013 at 3:07 am
ChrisM@Work (5/17/2013)
patrickmcginnis59 10839 (5/16/2013)
is Id unique in both tables? If it isn't, maybe you have a cross join going on because you're joining on it. Now if you have a case sensitive collation, then Id might be different from ID, but I noticed that you're also updating a column called "ID" so if you have a case sensitive collation, then maybe you are thinking Id isn't what you think it is. Just a wild guess based on your query!Why aren't you joining on facilitypatientid instead?!?!
Heh - wild guesses are all we can provide until, like Lynn states, we have sight of the view definition 😛
Cartesian products can blow up memory (I know I'm stating the obvious here), and if its anything like where I work, theres a chance we're not going to weed through the amount of code he'd post. Checking to see whether this Id is what he actually should be joining on would be my first step before even going through the views based on the ridiculous amount of memory he said its using, and before asking for view definitions. I would count the number of rows per Id in both the tables and views.
For example, what happens if Id is instead a facility Id and there are only a few distinct values over many rows of facilitypatientid?
If he already has a facilitypatientid then chances are Id is something completely different, and I were a betting man, I'd wager a bit that this is exactly whats happening and doesn't involve the view.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply