February 25, 2015 at 9:58 am
PHYData DBA (2/25/2015)
Eric M Russell (2/25/2015)
If I offended anyone here with my analogy, of a table having no primary key, to that of a feral child having no name, then I'm sorry. I think that all feral children and keyless tables deserve attention and help. My point was just that in a relational database, there is little practical use for a collection of records having no uniquely identifying key. They can't be properly managed or counted.I'm moving on.
It is cool Eric. I am sorry that my post was so insult.
How could we know what powder kegs our words could be construed as.
I love how we can both disagree on so much, but still agree that both of these things deserve our help and love.
Lets concentrate on the positive things we can do for the future, not the negative stuff that exists in the past.
PHYData DBA, it is cool. The table presented in the original post has a handful of potential design issues, and the request seemed more reaching out for general advice and open ended discussion. The conversation was switching between clustered indexes and/or primary keys, identity keys versus composite natural keys, etc. In the end, I'm not sure the two of us would even disagree about how this table should be designed, given a blank slate.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 25, 2015 at 10:32 am
Eric M Russell (2/25/2015)
PHYData DBA (2/25/2015)
Eric M Russell (2/25/2015)
If I offended anyone here with my analogy, of a table having no primary key, to that of a feral child having no name, then I'm sorry. I think that all feral children and keyless tables deserve attention and help. My point was just that in a relational database, there is little practical use for a collection of records having no uniquely identifying key. They can't be properly managed or counted.I'm moving on.
It is cool Eric. I am sorry that my post was so insult.
How could we know what powder kegs our words could be construed as.
I love how we can both disagree on so much, but still agree that both of these things deserve our help and love.
Lets concentrate on the positive things we can do for the future, not the negative stuff that exists in the past.
PHYData DBA, it is cool. The table presented in the original post has a handful of potential design issues, and the request seemed more reaching out for general advice and open ended discussion. The conversation was switching between clustered indexes and/or primary keys, identity keys versus composite natural keys, etc. In the end, I'm not sure the two of us would even disagree about how this table should be designed, given a blank slate.
Eric - I am sure we would not disagree on that either.
My very fist professional Job doing dev/reporting/dba work was having to resolve issues with code that accessed heaps created by importing data from EXCEL files into a database using an application called Spinnaker.
These were Heaps of course since the tables were created on import.
The code that accessed these heaps could not be changed and neither could the structure for business reasons and no access to the source code or the person that created that source.
I mention this because usually HEAPS are found in something that was NEVER designed only created.
The only one I have to deal with currently exists in an HL7 staging table used by a .NET web application.
Please don't laugh or Rant but I gave up discussing this with the application vender when they stated:
"We use a Heap table with no indexes at all because that is the best way to make sure we get serialized returns and First In First Out in the record set."
Yes I know these are contradictions for a database HEAP table.
Not certain if they are for the HEAP in C++ or other things.
It does give me something to point to at when they ask me why that part of their application does not work as expected.
Of course I get to listen to them tell me how wrong I am and that I don't understand what I am reading.
These things usually make me snort into the phone.
My favorite argument for Keeping a Heap that had no indexes at all was:
"This is the only way we can guarantee the performance and record set order for this table. If we added indexes or a primary key to this 10 million row 20 column table it would reduce performance by having to update these things on every insert."
Some concepts are very frustrating to have to work with in the real world.
HEAP tables and using (NOLOCK) seem to be the ones that cause DBA's and Developers the most friction.
February 26, 2015 at 5:43 pm
I've always created "temporary" (not really temporary) tables, or used dev/test databases to test performance. It's not perfect but you can easily see whether a clustered index will increase performance by running the procedure in the separate environment.
Also, how often is this going to run? If it's a lot, you may also consider creating a non-clustered index to assist with performance.
March 2, 2015 at 5:59 am
JoshDBGuy (2/26/2015)
I've always created "temporary" (not really temporary) tables, or used dev/test databases to test performance. It's not perfect but you can easily see whether a clustered index will increase performance by running the procedure in the separate environment.Also, how often is this going to run? If it's a lot, you may also consider creating a non-clustered index to assist with performance.
Absolutely this, this, this!
NEVER change anything performance-related without testing your change on a like-for-like test or dev system first.
Extra brownie points are available for having some nice colourful "before and after" charts to wave at Management, and also for having a friendly political attitude to both the developer in question and the organisational structure at large.
Do your testing alone - wouldn't want anyone else observing that your proposed improvements actually make things worse, but once you have your stats, find a decent way in which to present the facts.
Heaps are not necessarily the Worst Thing in the World (TM) if you have an INSERT-heavy database table that is rarely SELECTed from or maintained, but ninety nine times out of ten a clustered index is a pretty sound idea.
Viewing 4 posts - 61 through 63 (of 63 total)
You must be logged in to reply to this topic. Login to reply