February 2, 2009 at 9:26 am
Hi all,
I've got a business need here that needs to be fulfilled with a dynamic key.
To be clearer, a product table has 3 "possible keys" :
UPC, VndCatalog, and VndPrdCode.
Create table GICSPFProduct
(ProductID int identity(1,1)
MfrVendorID int not null
UPC nvarchar(14)
VndCatalog nvarchar(35)
VndPrdCode nvarchar(30)
...
...
...)
Create table GICSPFVendorToMfr
(MfrVendorID int identity(1,1)
VendorID int
MfrID int
....
....
)
Create table GICSPFVendor
(VendorID int identity(1,1)
VendorName nvarchar(50)
VendorKEYColumn nvarchar(15)
...
...)
So, depending on the "VendorKeyColumn" field that is in the VendorTable, the product will have a different key. So I need a field "UpdateKEY" in the product table, that will have the corresponding column in the product table.
Here's an example:
For a product that is linked to a vendor with "UPC" in the VendorKeyColumn:
ProductID UPC VndCatalog VndPrdCode UpdateKey
1 1234567890 ABCDEF NULL 1234567890
For a product with the tag "VndCatalog" in the VendorKeyColumn, here's the wanted Product table.
ProductID UPC VndCatalog VndPrdCode UpdateKey
1 1234567890 ABCDEF NULL ABCDEF
For this situation, we currently have an indexed view, on the product table, which has the calculated column in 1 of it's columns. The other business need we have is to be able to change the column directly in the vendor Screen. This said, when the indexed view was created, it took an awful lot of time to change this key, because the view rearranged itself, which is normal.
This is why we had to drop/recreate the view everytime we wanted to validate the Vendor files that are sent to us, and now, the view creation/deletion is a pain, because it takes soo long to calculate on a few million rows.
I'm looking for a new possibility to add a persisted calculated column to the table, and stop using the indexed view, but I'd like to know if I'm on the right track to achieve this.
What do you guys think?
Thanks in advance for any comments,
Cheers,
J-F
February 2, 2009 at 11:51 am
I don't know about performance, it's something worth testing.
Could you add a column (let's say VendorKeyValue) on GICSPFVendor with a value of 1/2/3 depending on the value of VendorKEYColumn.
Then create a column the same on the VendorTable table with the same name (make it a foreign key). Create a computed column based upon that value (ie when 1 use UPC when 2 use VNDCatalog).
You can even throw an index on it to make it persist.
Doing this the only validation you would need to do is the vendor information against the VendorTable.
That so completely makes sense in my head, I'm not sure if I've put it across clearly.
February 2, 2009 at 12:16 pm
The problem you're running into is because the table violates third normal form. Assuming you can't do much about that, I'd say a calculated column will do what you need to help speed up the view.
I'd be inclined to say that a partitioned table with constraints would be the way to normalize it. That would help with a number of situations you probably have.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2009 at 12:44 pm
i've done some browsing and found a lot of ways to add a computed column on a table, from another field in the table, but can you add a field depending on another table value?
(i.e. VendorKeyColumn field changes from UPC to VndCatalog in the VendorTable, I want my UpdateKey in the Product to change from UPC to VNDCatalog)
I could use a piece of code to do that, on any table, it's fine, I just cannot find any examples for my particular need.
Thanks,
Cheers,
J-F
February 2, 2009 at 1:44 pm
... can you add a field depending on another table
My friend that's in the land of "TRIGGERS" 🙁 !
* Noel
February 2, 2009 at 1:50 pm
You mean I should update the "Key" field depending on the value that has been updated in the Vendor Table? This makes no sense performance-wise... How am I gonna get this in a reasonable elapsed time when updating a few million rows...
Cheers,
J-F
February 2, 2009 at 1:54 pm
Bear in mind you only have to change a row if the value has been updated and you need to use a different code.
Anything not changed will not have to be updated.
February 2, 2009 at 1:58 pm
I don't think that is right.
When you change a row in the vendor, yes, there's only 1 impacted row in THIS table, but if there are 50k corresponding items in the products table, and the vendorKeyColumn changed from UPC to VndCatalog, then the 50k products will have to change it's calculated field (updateKey) from UPC to VndCatalog, then causing 50k updates in the products table.
So, tell me what is the advantage of using a trigger in this case?
Is there any kind of function I could pass a parameter to, and would tell me which field to use from the, let's say, @MfrVendorID?
I do not think the trigger is a good idea in this situation,
Cheers,
J-F
February 2, 2009 at 2:00 pm
Jean-François Bergeron (2/2/2009)
You mean I should update the "Key" field depending on the value that has been updated in the Vendor Table? This makes no sense performance-wise... How am I gonna get this in a reasonable elapsed time when updating a few million rows...
Nicholas is right on the money!
You could, also do the de-normalization if you have control of client side code but if you don't carefully coded triggers may be your only option.
* Noel
February 2, 2009 at 2:01 pm
You could run a query to check which values don't match. Only those need updating. Unless every single vendor makes a change the impact should be pretty small.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply