|
Ok...well I can't post the screenshot...or tell why, but here it is...
While learning an inherited database schema for a suite of apps that we are taking over, I found a database diagram called Unrelated Tables containing sure enough, 22 unrelated tables.
I had to stop there. 
|
|
|
|
|
while not the best practice I have several sets of unreleated tables, simply because there is no real way to relate them to anything, and they have over 400 million rows of data between them, and some other groups that are transactional and only related to each other for application performance reasons. I can search the 400 million+ rows in less than a second(on a 10 character compare no less, no or very few int compares), the old db server could do it in under a second and it was 4 or 5 years old.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
I should have been more specific...in all the tables, there are a few records to a few thousand. They do relate to each other, but have no relationships setup.
|
|
|
|
|
thats better than a product we found...dozens if not hundreds of FK's many circuliar, and several per table(read 2 or 3 FK's to table b, which has several back to table a, along with various others..some are circular and involve 3 to 4 tables in a big loop), all on character keys. doing anything in that DB takes forever. importing 20k rows took 24 or so hours(run time), because of all the extra data that had to be created and integrity checks the server had to make.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
These tables might have their merit.
For example for storing data that really does not relate to anything else but is still worth storing in the database.
|
|
|
|
|
|
At least better than using strings which are created by adding several different infos as primary keys.
Especially when a certain combination of these infos can appear multiple times and you need to add new rows in tables, not because they contain new data, but only because the primary key of a different table would be the same as it uses the same combination of infos.
Or using columns with non-atomic values as lists separated by commas (I can tell you, getting all entries from that list is a pain)...
|
|
|
|
|
Sometimes using compound string keys is the only way to maintain data between multiple systems / instances where there is no shared system key (and no reason to force such a situation). That said, you would hope the individual elements which were used to generate the keys are available as individual columns also to give good performance for queries...
AJ
|
|
|
|
|
Hi all,
I had found this master piece in one of my junior's coding.Check this duffer act.
We are having a table tblBookings with primary key nBookingID to save bookings with other fields like nProductID, nCategoryID etc.
Now see what he wrote to access perticular booking:
"Select ...... from tblBookings where nBookingID='...' and nProductID='..' and nCategoryID='..'"
I just could not stop laughing after listing of his answer of my question.. "why the hell you want to write two more conditions while u will get unique booking after checking with primary key itself????"
Check his answer.. "For extra perfection sir!!!!"
Anand Desai
Developer
Atharva Infotech
|
|
|
|
|
Anand Desai wrote: I just could not stop laughing after listing of his answer of my question.. "why the hell you want to write two more conditions while u will get unique booking after checking with primary key itself????"
Check his answer.. "For extra perfection sir!!!!"
Neither of you have heard of optimistic concurrency, dont worry, you will get there 
|
|
|
|
|
I used to see quite a lot of that in ABAP/4 report programs.
|
|
|
|
|
While it didn't involve a primary key, I learned something similar about databases many years ago (mid-90s).
Using Oracle 6, I had a query that I wanted to speed up.
I went to one of the DBAs and asked for help.
The where clause involved two fields (I'll call then X and Y).
The DBA said that there was no index for X and Y, but there was an index for X, Y, and Z.
If I added Z to the where clause the query would use the index and therefore be faster.
So I added a test for Z that would always be true, like "AND Z NOT NULL" when I know Z is never null.
And by golly it worked!
|
|
|
|
|
Certainly not relevant to SQL Server - SQL Server can always use the leftmost columns of an index, if that's all that's required.
It does depend on the ordering of columns in the CREATE INDEX or PRIMARY KEY declaration, though; if the ordering is X, Y, Z it can generate a seek on X and Y when you're looking for X and Y, but if it's X, Z, Y it can only generate a seek on X, because the identical Y values are not clumped together (they're separated by different values of Z). Adding AND Z NOT NULL doesn't help much here because Z likely isn't selective, unless the Zs in the table are mostly NULL.
I'm pretty sure modern Oracle can use prefixes as well.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
The priest told you to 'honor the god of index'. ... Thats why the called their product "Oracle"
Asking a specialist is always one of the best ideas. 
Greetings from Germany
|
|
|
|
|
It's my personal opinion that posting the mistakes of ones juniors is mean spirited - if you're a supervisor, your job is to teach and encourage, not to ridicule.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Right, so one should always write it up as if the culprit is "the senior design guru". 
|
|
|
|
|
|
I would tend to agree with this
|
|
|
|
|
Oh, it doesn't matter. With such efforts toward 'extra perfection', a brilliant career in management is waiting for the guy.
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke
|
|
|
|
|
That was the intention of his remarks.
Greetings from Germany
|
|
|
|
|
Well said - my rule for juniors is always to praise in public and critique in private (note - critique and not criticize).
|
|
|
|
|
Pete O'Hanlon wrote: my rule for juniors is always to praise in public and critique in private (note - critique and not criticize).
Sounds to me like your a down to earth guy.
There is nothing worse than some prick who constantly humiliates their employee's in public (no matter what field their working in).
|
|
|
|
|
MarkBrock wrote:
There is nothing worse than some prick who constantly humiliates their employee's in public (no matter what field their working in).
It's only good business sense. I'd rather I had a happy member of staff who's had a chance to improve than somebody who I've p!ssed off and who's just going to be resentful. I don't want to be a Pointy Haired Boss.
|
|
|
|
|
But this costs execution time. And not to ferget what consequneces statement "may be". First selecting "nCategoryID" and create a nice temporary table and than run "nProductID" and now last but not least select the ID.
I would compare the complete againt the short mode and look. In greater databases strang things can happens... 
Greetings from Germany
|
|
|
|
|
I keep note of this next time your junior developer is up for a performance review 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
