March 29, 2016 at 8:40 am
It looks to me like two different people wrote some overlapping business logic not knowing what the other person was doing. I've never seen or heard anything like this to have two relationships with different fields against the same child table? Strange. Is there any chance of identifying who wrote this/these, and if they're still at the company, get an explanation? Not knowing the PKs of the t_order_detail_comment or how the data is populated, could the first FK be removed? To me it looks like the second FK better ties the child to the parent, but I don't know the system. If you remove the first, I'd rename the second constraint to remove the _2 otherwise someone in the future may wonder what happened to the predecessor.
My $0.002 worth. Wow.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 29, 2016 at 9:10 am
Oh my gosh, I was hoping for exactly not that response. But am leery of what was done before I got here.
I cannot see the dates of the created constraints in SSMS. Is there a query I can write to pull back the created/modified dates of the constraints? That would give a start on when then changes happened.
thank you so much,
March 29, 2016 at 9:56 am
you can use the following view to see all the foreign keys in a database and the created/modified dates.
select * from sys.foreign_keys
These other views are useful for looking at other constraints
SELECT * FROM sys.default_constraints
SELECT * FROM sys.check_constraints
SELECT * FROM sys.key_constraints
March 29, 2016 at 10:02 am
I'm hoping someone else can pop in with another opinion. Like I said, I've never seen anything like it. It just doesn't make business sense to me to have two different FK constraints for what seems like a very simple, straight-forward, purpose.
You can use something like this
SELECT st.name AS ParentTable, so.*
FROM sys.objects so
JOIN sys.tables st
ON so.parent_object_id = st.object_id
WHERE so.TYPE = 'F'
ORDER BY st.name, so.name;
to view the create date, you can refine the WHERE for just the specific constraints involved (I like messing with system meta data).
I hope it helps!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 29, 2016 at 10:41 am
Thanks for the help! The news is this is the way the DB was installed from the vendor. All of the constraint dates come back with 2013-12-26 "same relative create time".
So next I will ask the vendor why this is in place this way.
It would appear they want to have the:
- identity column constraint for uniqueness between parent child relationships - ok I get that.
- business columns constraint for holding parents and children together and for running automated purges of "old" rows. Maybe that's the biggest reason why.
I am having issues with lock contention and performance. I am wondering the effect of less than perfect coding for "units of work" and cascade deletes. But I see no cascade updates. hmm... this one is making me think.
March 29, 2016 at 11:43 am
There are so many things that can affect performance that it's hard to know where to begin, and since you're dealing with a canned vendor package there are very definite limitations as to what you can do without jeopardizing your support from them.
The obvious things to check are to make sure statistics are current, indexes and tables aren't badly fragmented, and that there's no corruption. I run DBCCs every night, the size of the database(s) and maintenance window dictates whether that's possible. Since there is already an FK relationship, you know the data types match, so you shouldn't be getting any flack from the query optimizer for problems involving joins with those tables.
Have you restored a backup to another database to make sure they are restorable? That's ALWAYS a good thing to confirm if there's any risk of corruption. A DBCC problem can be indicative of a disk problem, which could cause performance issues. So there's lots of things that you can dig in to.
If you can identify a specific process/query that's performing poorly, if the database is small enough that you can restore it elsewhere, running said process/query against the new copy and seeing if the performance is similar could be informative.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 29, 2016 at 4:43 pm
I just read an article[/url] by Robert Sheldon on Simple Talk discussing metadata and some specific functions. Some things in it that you might find useful, including one to show you how current your statistics are. You might want to check it out.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply