|
This can happen for a number of reasons. I have seen this many times. From bad scripts, to some of MS data compare tools, etc.
Hopefully you have the scripts handy, an you can just recreate the missing keys. 
-- rants are the vehicle of the lazy and uninspired - JSOP 2/2018
|
|
|
|
|
Well, I certainly hope you don't have a production application that relies on the existence of them for correct behaviour. 
|
|
|
|
|
Your subject line upset me [until I read the message].
|
|
|
|
|
I had an Enterprise Architect come to me and demand that all the FKs be dropped in the lower environments because it was causing issues. I, politely, suggested that maybe the code was wrong. That did not go over well. So then I told him that I didn't have the authority to do that so he would need to talk to my boss (my boss was a much more devoted FK adherent). They fought, my boss lost. So I dropped them.
Less than a week later the test data was complete trash. At one point Texas was a province in Canada. There were many other examples, but that is the one that continues to stick with me to this day. When I showed them the bad data it was dismissed as just test data and that I shouldn't worry about it. There were other signs that this project was going to be a disaster so I transferred out. About four or five months later they went to production. Within ten days it had to be backed out of production. At this point I had been at this company for seven or eight years. In that entire time I had never heard of project going to production and then being backed out. It was a disaster. The executives had no stomach to spend the money it would take to get it working so they canned the project and let pretty much everyone on the project go. Which is a shame. There were some really good people who tried very hard to make it work, but the bumbling idiot E.A. was just too much to overcome.
|
|
|
|
|
Bloody foreign keys, coming over here, taking indexes away from native keys! Get rid of them all!
veni bibi saltavi
|
|
|
|
|
We won't be able to use them after Brexit anyway.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Bloody foreigners, sending their brightest and best keys over here to...
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
I was brought in to "improve" a system that had horrible performance.
They were not sure why... It was an Industry Product that had been around for decades.
One of their (20+) Goals:
When creating a new XXX, have it take 90 seconds or less to add a new blank row to the grid that the user can then edit. Currently taking 4 minutes on average.
Of course, my first assumption was "What type of grid, and how many rows..."
Then I got access to the DB... They were, in fact, PROUD of not having ANY FK relationships. In fact, they had leaned away from INDEXING as space wasting... Of course, I considered NOT having them as TIME WASTING (per user, for every user)... They had plenty of space to waste...
Upon reviewing the slowest requests, 80% were fixed simply by indexing things properly. They still refused to declare FK relationships (because good data should be avoided at all costs!)...
Oh, and the company had NO IDEA why they were so slow. It literally took someone else (us) to look at their system and ask a few questions... Nobody thought it could be the DB, because it was fast for everyone else... (all much smaller companies).
Ughhh...
|
|
|
|
|
That sounds horribly familiar.
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
[sarcasm on]
Everyone knows foreign keys have been imposed by storage-devices-manufacturers lobbys.
[sarcasm off]
"I'm neither for nor against, on the contrary." John Middle
|
|
|
|
|
Haha.. this is funny - not! I've come across this all the time. The problem "back then" were application designers that had no idea, zilch, nada; about database structures. Yet, here we are in 2018 and I still see the same mistakes. Primary key every table with an auto-fill ID column. Yes, you have a primary key, congratulations! But what's the point if you don't have a reference to the ID from another table? Did someone forget that the ID Primary Key column is a machine necessity, if at all? Like who care my next record starts with ID 2001!? A true Primary Key is one created based on data "snippets" off other columns in the table. Yeah baby... bring back Excel databases!  
Just do what I do when databases have no relations... DROP DATABASE.. oh but don't forget to save your data first. 
|
|
|
|
|
Fandango90 wrote: A true Primary Key is one created based on data "snippets" off other columns in the table.
The "natural vs surrogate primary key" debate is like tabs vs spaces - some people insist that there's only one "correct" way to do, whilst others make a decision on a case-by-case basis. 
SQL Server: Natural Key Verses Surrogate Key — DatabaseJournal.com[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Fandango90 wrote: the same mistakes. Primary key every table with an auto-fill ID column
Sorry, I don't see how this is a mistake.
Fandango90 wrote: Yes, you have a primary key, congratulations! But what's the point if you don't have a reference to the ID from another table?
I think you may have misunderstood the OP's issue. They didn't de-normalize, only removed the FK constraints.
Fandango90 wrote: Did someone forget that the ID Primary Key column is a machine necessity, if at all? Like who care my next record starts with ID 2001!?
I'm not even sure what you mean by 'machine necessity'. Perhaps it's sarcasm and I'm not getting it.
Fandango90 wrote: A true Primary Key is one created based on data "snippets" off other columns in the table
Again, I can't tell if this is sarcasm or a real opinion. So an identity or guid is not a true PK, but a couple of columns where one or more values can change is??? (or, just throw in a timestamp to be sure!) It sure makes future record maintenance a lot easier when I can tag a record with a single condition. Anyway, it would probably make an interesting poll.
"Go forth into the source" - Neal Morse
|
|
|
|
|
A relation may have multiple candidate keys, any one of which may be selected as primary key.
Any attribute that is subject to mutation is not suitable - because they simply cannot be used to identify a row.
The "A true PK is one created on data "snippets" off other columns in the table" is explicitly the wrong way round in terms of normalisation. Every other column should be dependent on the whole of every candidate key, and have no dependencies on anything else. Here, Fandango seems to be proposing exactly the opposite - having the PK dependent on every other column, which is pure insanity, and worthy of an entry in this forum all by itself 
"If you don't fail at least 90 percent of the time, you're not aiming high enough."
Alan Kay.
|
|
|
|
|
Maybe you did it one night after taking some Adderol... 
#SupportHeForShe
Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson
You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun
|
|
|
|
|
was probably having issues removing a row ! HAAAAAA !
|
|
|
|
|
You probably all knew this, but I needed a definitive answer.
I'm using a ConcurrentQueue which is a FIFO collection.
I needed to know that when I went to determine if the queue already had the item, if it would return faster when the item was found at the top (next item out) versus found at the bottom (last out position).
I assumed, but wasn't sure.
This code gives a good definitive answer.
ConcurrentQueue<int> mainList = new ConcurrentQueue<int>();
Console.WriteLine("Adding items...");
for (int x = 1; x < 10000000;x++){
mainList.Enqueue(x);
}
Console.WriteLine("DONE adding items...");
Stopwatch sw = new Stopwatch();
Console.WriteLine("Searching for FIRST item...");
sw.Start();
mainList.FirstOrDefault(item => item == 1);
sw.Stop();
Console.WriteLine ("{0}ms",sw.ElapsedMilliseconds);
sw = new StopWatch();
Console.WriteLine("Searching for LAST item...");
sw.Start();
mainList.FirstOrDefault(item => item == 9999999);
sw.Stop();
Console.WriteLine ("{0}ms",sw.ElapsedMilliseconds);
Output
Adding items...
DONE adding items...
Searching for FIRST item...
0ms
Searching for LAST item...
99ms
The consistent result is around 100ms on my machine.
EDIT
This addition seems to bear expected results too:
for (int z = 9999999; z >= 1;z--){
if (z % 1000000 == 0) {
sw = new Stopwatch();
sw.Start();
mainList.FirstOrDefault(item => item == z);
sw.Stop();
Console.WriteLine ("{0} : {1}ms",z, sw.ElapsedMilliseconds);
}
}
Output for this section is:
9000000 : 99ms
8000000 : 89ms
7000000 : 78ms
6000000 : 71ms
5000000 : 55ms
4000000 : 44ms
3000000 : 33ms
2000000 : 21ms
1000000 : 10ms
Interesting that on my machine it is about 10ms per 1,000,000 items.
EDIT 2
But, wait...there's more!
What about for objects?
I'm creating a queue of anonymous objects via dynamic -- basically cuz I'm lazy.
Then I test to see how long it takes to find the matching value. Answer: Longer
Right, we all assumed longer. The amount of time could just be unboxing of object to get value or whatever.
This answers the question for me though, because I'm going to be putting a bunch of items in a ConcurrentQueue and I want to add more items but don't want to add an item if the collection already contains the item.
void Main()
{
ConcurrentQueue<dynamic> mainList = new ConcurrentQueue<dynamic>();
Console.WriteLine("Adding items...");
for (int x = 1; x < 10000000;x++){
mainList.Enqueue(new {x=x,name="x_" + x});
}
Console.WriteLine("DONE adding items...");
Stopwatch sw = new Stopwatch();
Console.WriteLine("Searching for FIRST item...");
sw.Start();
mainList.FirstOrDefault(item => item.x == 1);
sw.Stop();
Console.WriteLine ("{0}ms",sw.ElapsedMilliseconds);
sw = new Stopwatch();
Console.WriteLine("Searching for LAST item...");
sw.Start();
mainList.FirstOrDefault(item => item.x == 9999999);
sw.Stop();
Console.WriteLine ("{0}ms",sw.ElapsedMilliseconds);
for (int z = 9999999; z >= 1;z--){
if (z % 1000000 == 0) {
sw = new Stopwatch();
sw.Start();
mainList.FirstOrDefault(item => item.x == z);
sw.Stop();
Console.WriteLine ("{0} : {1}ms",z, sw.ElapsedMilliseconds);
}
}
}
Output Looks Like
Adding items...
DONE adding items...
Searching for FIRST item...
4ms
Searching for LAST item...
753ms
9000000 : 643ms
8000000 : 575ms
7000000 : 496ms
6000000 : 430ms
5000000 : 362ms
4000000 : 285ms
3000000 : 217ms
2000000 : 141ms
1000000 : 72ms
modified 5-Feb-18 17:25pm.
|
|
|
|
|
raddevus wrote: I assumed, but wasn't sure.
This code gives a good definitive answer.
Hence, the upvote.
Proof is always better than an assumption
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Not particularly surprising, given the amount of work it's doing.
Reference Source: ConcurrentQueue.cs[^]
It looks like it's implemented as a singly linked list of small arrays (32 items each). Searching a linked list for the last item, or for an item that doesn't exist, is always going to take longer than finding an item close to the start.
It also seems that the order of searches has some effect. Try searching for 1st followed by 31st; then try 31st followed by 1st.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Very good confirming information on that. Thanks a lot.
Richard Deeming wrote: It also seems that the order of searches has some effect. Try searching for 1st followed by 31st; then try 31st followed by 1s
I actually noticed that too as I tested, but my original post became so long I decided not to mention more.
|
|
|
|
|
It is seems to me a very bad practice to use a FIFO/LIFO oriented collection to anything else than push/pop...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Kornfeld Eliyahu Peter wrote: seems to me a very bad practice to use a FIFO/LIFO oriented collection to anything else than push/pop
Yes, a good observation.
I had initially solved this with a ConcurrentDictionary.
Someone else suggested the Queue to me.
1. I have a memory collection which continues to get re-loaded from the database on an interval.
2. I only want to load new items into the collection
3. However, when I get an item, I just want to pop off the oldest item from the collection.
This lead me to two different ways to do this.
1. Queue - load up the queue on interval - insure an item is never added more than once. -- later, simply pop off the oldest item (First-In). The getter code is a bit more simple (obvious / self-documenting) with the Queue.
2. Dictionary - load up Dictionary on interval - TryAdd() handles this for me. As long as I load the dictionary from the database in reverse order (Stored Proc returns oldest first) all I have to do is call .First() on Dictionary when I want the oldest item. The Queue obviously gives you the First added item automatically.
I've now done detailed testing and using the ConcurrentDictionary is much less CPU intensive, but might be slightly more RAM hungry.
|
|
|
|
|
raddevus wrote: all I have to do is call .First() on Dictionary when I want the oldest item.
That's an assumption that's going to bite you in the future. The dictionary classes don't guarantee that the enumerator will return items in any specific order. The order could change with a framework update; or it could change when your dictionary exceeds a certain size.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Ah, interesting. Very good info. So back to the Queue, it is.
There are always trade-offs.
I was sorting the Dictionary first via
waitingFiles.OrderByDescending(of => of.Value.FileCreated).First();
But that is really intensive because from what I can tell those LINQ objects are instantiated to search for the one that matches.
So, I was hoping (and it is really fast and nice) that since I load the data into the Dictionary in ascending order (oldest files first) that I could count on just calling First().
I mean it does work now. I was actually testing the order that the files are then processed.
However, I understand that it is not guaranteed.
Interesting and why I'm trying not to make assumptions about which is better ( Dictionary or Queue ) in this instance.
|
|
|
|
|
Question, do you need to check all or any of the elements in the list, or is it enough to check the last one?
If so you would do better off with a linked list.
There are no concurrent linked lists though. Apparently it was around in the beta versions of dotNet 4.0 but it was left out from the release version as there were no advantages over using a normal linked list with locking.
But a simple lock would still be faster since you don't need to enumerate the collection.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
