The Weird and The Wonderful

That someone could have been fan of No SQL world and sees relationship as spaghetti code

// ♫ 99 little bugs in the code,
// 99 bugs in the code
// We fix a bug, compile it again
// 101 little bugs in the code ♫

Tell your manager, while you code: "good, cheap or fast: pick two. "

Or as a tight-coupling to a specific storage paradigm.

"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli

On my own projects I always script my datatabase into an SQL file and save it into subversion along with my source code. Any changes can be found in the svn log afterwards, works great.

Wout

Got a drawer full of them.

Peter Wasser

"The whole problem with the world is that fools and fanatics are always so certain of themselves, and wiser people so full of doubts." - Bertrand Russell

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. Thumbs Up | :thumbsup:

-- 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. OMG | :OMG:

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! Confused | :confused:

Just do what I do when databases have no relations... DROP DATABASE.. oh but don't forget to save your data first. Blush | :O

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. Smile | :)

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. Confused | :confused:

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. Confused | :confused:

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. Confused | :confused:

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. Smile | :)

"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 Wink | ;-)

"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... Poke tongue | ;-P

#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.

C#

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:

C#

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.

C#

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 Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Not particularly surprising, given the amount of work it's doing. Smile | :)

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. Smile | :)

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.