• (cs) in reply to Hortical
    Hortical:
    PedanticCurmudgeon:
    mjk340:
    In my organization, standard practice is to make an index on all possible columns, in all possible orders, so that operations are always as fast as can be. For example, if I have a table with columns A1, B1, and C1 my create table scripts will create these indexes:

    A1 B1 C1 A1,B1 A1,C1 B1,A1 B1,C1 C1,A1 C1,B1 A1,B1,C1 A1,C1,B1 B1,A1,C1 B1,C1,A1 C1,A1,B1 C1,B1,A1

    Lazy DBAs just don't spend the time to create all the indexes necessary for maximum performance.

    This troll is pretty close to perfect.
    Hmmmmm... I interpreted it more like:

    <Description of company policy designed to draw sympathy> <Sarcastic statement expressing contempt for said policy>

    You must have though it conformed to the more typical troll pattern:

    <[pseudo-]Naive boasting of one's incompetence> <Flames/Flamebait>

    Thanks for pointing that out. To be honest I intially thought it was flamebait as well. I mean, this is TDWTF. Can you blame me?

  • I. G. E. (unregistered) in reply to K
    K:
    blank:
    to trwtf is Matthew not charging his friend for his consulting services?
    Wouldn't it be more reasonable to change the vendor?
    FTFY
  • Hortical (unregistered) in reply to C-Octothorpe
    C-Octothorpe:
    Thanks for pointing that out. To be honest I intially thought it was flamebait as well. I mean, this is TDWTF. Can you blame me?
    Uh... yeah, I can. This is the internet, I can and will blame anyone for anything. This week, I'm blaming fat people for expensive gasoline. If they didn't lug around so much weight, they wouldn't use as much gas, lowering demand and lowering prices.

    Fat bastards! Why you get places by rolling around, like you do in my dreams?!

  • anonymouser (unregistered) in reply to Leenus
    Leenus:
    True WTF is saying indexes instead of indices
    you probably call multiple pointer input devices "mice" too, don't you?
  • (cs) in reply to Rawr
    Rawr:
    An alternate explanation:
    I don't think anyone have mentioned the true wtf here.

    They are creating a clustered index, on a temp table.

    When you create a clustered index, the entire table is rewritten, and new rows are not included in the index.

    That's why you create the index after you have the data in your table. I still fail to see the WTF.

    i with you, bro

  • (cs) in reply to Hortical
    Hortical:
    C-Octothorpe:
    Thanks for pointing that out. To be honest I intially thought it was flamebait as well. I mean, this is TDWTF. Can you blame me?
    Uh... yeah, I can. This is the internet, I can and will blame anyone for anything. This week, I'm blaming fat people for expensive gasoline. If they didn't lug around so much weight, they wouldn't use as much gas, lowering demand and lowering prices.

    Fat bastards! Why you get places by rolling around, like you do in my dreams?!

    Expensive gas? Man, that's stupid! Everybody knows they cause global warming (methane gas and whatnot)...

  • (cs) in reply to Rawr
    Rawr:
    However, if they actually added the clustered index addition to the script that creates the temp table, then there is really no WTF. In that case I'd say the WTF is everyone blindly saying this is a WTF...
    Neat. But what about the folks who read the whole article and say this is a WTF?
    FTFA:
    "When we did the query trace, it was clear that the problem was a lack of indexes on the inventory table."
    Optimize the temp table all you fucking want, it won't change the fact that the query on the real inventory table is still shit.

    Addendum (2011-11-07 14:23): EDIT: I mispoke. The query on the real inventory table isn't necessarily shit - it just runs like shit because there's no index. Sorry.

  • (cs) in reply to boog
    boog:
    Rawr:
    However, if they actually added the clustered index addition to the script that creates the temp table, then there is really no WTF. In that case I'd say the WTF is everyone blindly saying this is a WTF...
    Neat. But what about the folks who read the whole article and say this is a WTF?
    FTFA:
    "When we did the query trace, it was clear that the problem was a lack of indexes on the inventory table."
    Optimize the temp table all you fucking want, it won't change the fact that the query on the real inventory table is still shit.
    But... But... Indexes on temp tables are OK! Right? So this ISN'T a WTF...
  • (cs) in reply to C-Octothorpe
    C-Octothorpe:
    But... But... Indexes on temp tables are OK! Right? So this ISN'T a WTF...
    Skipping your meds again, I see.
  • (cs) in reply to boog
    boog:
    C-Octothorpe:
    But... But... Indexes on temp tables are OK! Right? So this ISN'T a WTF...
    Skipping your meds again, I see.
    Oh please; don't act as though I never *not* took them... Sheesh!
  • (cs)

    My old friend Bobby Tables had a temporary index once, and of course it was no living matter!

  • An alternate explanation (unregistered) in reply to Anders
    Anders:
    An alternate explanation:
    I don't think anyone have mentioned the true wtf here.

    They are creating a clustered index, on a temp table.

    When you create a clustered index, the entire table is rewritten, and new rows are not included in the index.

    Not sure what database engine you use..... but I hope that is not your understanding of MS SQL Server.... Either that or I am confused and hardly any of my transactions have been saving for the last 17 years (YIKES!). But I think I would have heard something about that by now.

    I am using PostgreSQL where a clustered index seems to be something different from a clustered index in MS Sql server.

    I should by now be used to different sql servers using the same word for different features.

    Clustered index in Postgresql is described at http://www.postgresql.org/docs/8.3/interactive/sql-cluster.html

  • Rawr (unregistered) in reply to boog
    boog:
    Rawr:
    However, if they actually added the clustered index addition to the script that creates the temp table, then there is really no WTF. In that case I'd say the WTF is everyone blindly saying this is a WTF...
    Neat. But what about the folks who read the whole article and say this is a WTF?
    FTFA:
    "When we did the query trace, it was clear that the problem was a lack of indexes on the inventory table."
    Optimize the temp table all you fucking want, it won't change the fact that the query on the real inventory table is still shit.

    Addendum (2011-11-07 14:23): EDIT: I mispoke. The query on the real inventory table isn't necessarily shit - it just runs like shit because there's no index. Sorry.

    I think that's a leap, but if that's the point that is trying to be made, it's very unclear.

    It's specifically pointed out that temp table is re-generated every query (of course it is) as if it's going to FUBAR the clustered index, which it won't. Do you see what I mean? No one can say it's a WTF with the information that was given.

    You assume they aren't using the temp table to query, I give them the benefit of the doubt and assume they are.

  • frits (unregistered)

    Wow. The obvious-and-stupid trolls have run everyone off. The only ones left are the usual suspects and Serguey123's multiple sockpuppets. Nice!

  • (cs) in reply to Rawr
    Rawr:
    I think that's a leap, but if that's the point that is trying to be made, it's very unclear.
    I'll agree there is often a disconnect in these articles between the background of the WTF and the punchline. Benefits go to those of us with a good attention to detail.
    Rawr:
    It's specifically pointed out that temp table is re-generated every query (of course it is) as if it's going to FUBAR the clustered index, which it won't. Do you see what I mean? No one can say it's a WTF with the information that was given.
    I can't say having an index on a temp table is a WTF, no, but I can say that A) the index failed to solve the problem, and B) the report was noticeably slower after the representative line was added. That information is provided in the article.
    Rawr:
    You assume they aren't using the temp table to query, I give them the benefit of the doubt and assume they are.
    I'm not assuming anything. Summary of the article:
    1. Matthew and his friend run a query trace from which they identify the problem as a lack of indexes on the inventory table.
    2. The vendor sends a patch that adds an index on a temporary table; it does not fix the problem.
    3. ???
    4. Profit!

    (Southpark reference aside, 3 represents Matthew's reaction to 2, and 4 goes to the vendor.)

  • (cs) in reply to Rawr
    Rawr:
    It's specifically pointed out that temp table is re-generated every query (of course it is) as if it's going to FUBAR the clustered index, which it won't.
    Who the fuck said it would FUBAR the index? There is no index to FUBAR, get it?
    Rawr:
    No one can say it's a WTF with the information that was given.
    Uh, yeah you can... Here: they put an index on a temp table rather than the source table. There ya go!
    Rawr:
    You assume they aren't using the temp table to query
    Not sure what you're trying to say here or where anybody would disagree with you...
    Rawr:
    I give them the benefit of the doubt
    Ah, there's your problem...
  • Rawr (unregistered)

    So we've been arguing about two different understandings of the article. That works for me.

  • Brian White (unregistered) in reply to TheJonB
    TheJonB:
    The temporary table thing aside, does a clustered index make sense on an Id column?

    Surely it's only worth doing if you're likely to select a group of related Id's - a range basically?

    Sure it can make sense. Are you going to join to that table a lot on the id? It also allows the fastest writes, since the new row always goes at the end, instead of needing to reshuffle the (potentially huge) table to fit it in somewhere in the middle. You can also be sure that that particular column will never ever change, whereas pretty much any other column in the table could change, triggering a re-shuffling of the row in addition to updating the column. There are some high level DBA articles I've seen that argue that clustering on anything other than an identity column will eventually cause problems.

  • Heavy (unregistered) in reply to np
    np:
    Geoff:
    The WTF here is the consulting company not being able to correctly interpret the information given them, sending the customer a "fix" without testing it, and generally making no attempt to really understand the problem beyond, "you want an index?...fine here ya go..."

    Your DBA is correct there certainly are times when you might want to index a temp table. This apparently however was not one of them.

    Not consulting company, the software vendor.

    Although I agree it a WTF, this seems quite the norm for many of the Software Providers around the place. One three letter acronym that we work with (who continually buys out smaller companies and rebrands their products to be their own) seems more like a sales outfit than an IT company. I fell of my chair when they asked us for advice on how to fix a problem with one of their products - apparently, the people who know how to support it are in America, and cannot be engaged for our account.

    Frighteningly (though I disagree) there are people within who think this three letter acronym is better than the 3 letter acronym it replaced (which was swallowed up by a two letter acronym).

    Point is, they're quick to promote their own OS, Message Broker or (God forbid) Database when they want to make some coinage, but they are incapable of supporting anything (be it their own product or someone else's). I'm often amazed that they are capable of creating a veritable shitstorm over some irrelevant alert from their monitoring of a Test environment (usually caused by testers under a specific Change Request that outlines that such an alert will trigger), yet are incapable to act when we tell them (they never notice themselves) that the production version of same system is unavailable with significant impact.

    sigh Maybe three letter acronyms are the WTF....

  • Yuri (unregistered) in reply to Hortical
    Hortical:
    C-Octothorpe:
    Thanks for pointing that out. To be honest I intially thought it was flamebait as well. I mean, this is TDWTF. Can you blame me?
    Uh... yeah, I can. This is the internet, I can and will blame anyone for anything. This week, I'm blaming fat people for expensive gasoline. If they didn't lug around so much weight, they wouldn't use as much gas, lowering demand and lowering prices.

    Fat bastards! Why you get places by rolling around, like you do in my dreams?!

    Finally! someone actually understands trwtf here...Fat People!

  • Fred (unregistered) in reply to anonymouser
    anonymouser:
    Leenus:
    True WTF is saying indexes instead of indices
    you probably call multiple pointer input devices "mice" too, don't you?
    Not sure what your point is. Indices would surely be correct English (although not necessarily American).

    As for mice vs mouses, it would seem that mice is more common according to Wikipedia (the font of all Wisdom:

    wikipedia:
    The online Oxford Dictionaries entry for mouse states the plural for the small rodent is mice, while the plural for the small computer connected device is either mice or mouses[b]. However, in the usage section of the entry it states that the [b]more common plural is mice, and the first recorded use of the term in the plural (1984) is mice as well.[2] The fourth edition of The American Heritage Dictionary of the English Language endorses both computer mice and computer mouses as correct plural forms for computer mouse. Some authors of technical documents may prefer either mouse devices or the more generic pointing devices. The plural mouses treats mouse as a "headless noun".
    Of course, the same wikipedia also refers to WikiProject Indexes....

    MELTDOWN: Don't know what to believe anymore.....

  • An Old Hacker (unregistered) in reply to PedanticCurmudgeon
    PedanticCurmudgeon:
    mjk340:
    In my organization, standard practice is to make an index on all possible columns, in all possible orders, so that operations are always as fast as can be. For example, if I have a table with columns A1, B1, and C1 my create table scripts will create these indexes:

    A1 B1 C1 A1,B1 A1,C1 B1,A1 B1,C1 C1,A1 C1,B1 A1,B1,C1 A1,C1,B1 B1,A1,C1 B1,C1,A1 C1,A1,B1 C1,B1,A1

    Lazy DBAs just don't spend the time to create all the indexes necessary for maximum performance.

    This troll is pretty close to perfect.

    Nah, a better troll would have eliminated all of the one- and two-column indices. That way smart people could explain to the uninformed why the one- and two-column indices aren't needed.

    Pretty good, though. Did I mention that we have a table with 135 columns? I need to talk to my DBA about this!

  • H.a.t.e.r (unregistered)

    Sorry guys I'm too busy today fucking bitches on my yacht.

    Come tomorrow.

  • (cs) in reply to An Old Hacker
    An Old Hacker:
    PedanticCurmudgeon:
    mjk340:
    In my organization, standard practice is to make an index on all possible columns, in all possible orders, so that operations are always as fast as can be. For example, if I have a table with columns A1, B1, and C1 my create table scripts will create these indexes:

    A1 B1 C1 A1,B1 A1,C1 B1,A1 B1,C1 C1,A1 C1,B1 A1,B1,C1 A1,C1,B1 B1,A1,C1 B1,C1,A1 C1,A1,B1 C1,B1,A1

    Lazy DBAs just don't spend the time to create all the indexes necessary for maximum performance.

    This troll is pretty close to perfect.

    Nah, a better troll would have eliminated all of the one- and two-column indices. That way smart people could explain to the uninformed why the one- and two-column indices aren't needed.

    Pretty good, though. Did I mention that we have a table with 135 columns? I need to talk to my DBA about this!

    I really wonder how long an insert would take if you were to index the table this way...

    Sounds like an excellent time-killer for a rainy day. Oh, and please post the results here! :)

  • Holy Order of the Zune (unregistered) in reply to H.a.t.e.r
    H.a.t.e.r:
    Sorry guys I'm too busy today fucking bitches on my yacht.

    Come tomorrow.

    Wow, you can really hold out.

    K9 usually taps me in the first couple minutes.

  • (cs) in reply to An alternate explanation
    An alternate explanation:
    I am using PostgreSQL where a clustered index seems to be something different from a clustered index in MS Sql server.

    I should by now be used to different sql servers using the same word for different features.

    Clustered index in Postgresql is described at http://www.postgresql.org/docs/8.3/interactive/sql-cluster.html

    That documentation is for a cluster, not a clustered index. The noun phrases used to describe them and the SQL syntaxes are different, so this is a simple misunderstanding by you that these both are the same thing.
  • (cs) in reply to Fred
    Fred:
    As for mice vs mouses, it would seem that mice is more common according to Wikipedia (the font of all Wisdom:

    The Truth is, that nobody writes mouses. Really. I even searched the Chinese, French, German, Hebrew, Russian, and Spanish corpora—nada!

  • Peter (unregistered) in reply to mjk340
    mjk340:
    In my organization, standard practice is to make an index on all possible columns, in all possible orders, so that operations are always as fast as can be. For example, if I have a table with columns A1, B1, and C1 my create table scripts will create these indexes:

    A1 B1 C1 ...snip... C1,B1,A1

    Topcod3r? Is that you? Welcome back!
  • PG4 (unregistered)

    This kind of sounds like a pile of crap from a company who's name sounds close to an accident.

    Write big app, hire tons of people off the street including the DBA that is clueless. No indexes at first very bad performance, blame our DBAs. Well put on indexes now and on just about every column in all the tables. Cluless DBA hears that bit map indexes take much less space so this is what he uses even on columns that are unique.

    Oracle did the right thing for once and figures out that reading a bit map index is crap in most caes, and ignores it. Application still runs badly, blames our DbAs, our sysadmins, our SAN, etc.

    Clueless DBA reads that full table scans are bad and can't understand why Oracle hates his inexdes. So now he plays around with tuning parameters that are used the calculate the cost of operations. He makes it so not matter what the size of the table, type of index, stats on the table, an index will always be used.

    Yea, no more full table scans, his job is done. Except the application is still slow as hell. He gets let go from the project about this time, leaving it to our DBAs to figure out what he did.

    Ooops too far into production now to make major changes like putting the right indexes on tables. And no to putting the parameters back to normal, it's just too much risk at this point, says the customer and project managers. But, hey let's buy more memory and CPUs for the servers, that's sure to make it faster.

    6 to 7 years later the DB is still messed up.

  • Dan Neely (unregistered) in reply to blank
    blank:
    to trwtf is Matthew not charging his friend for his consulting services?

    No, TRWTF if anyone being so mercenary that they think helping a friend and not charging for it is a WTF.

  • Herby (unregistered)

    On the subject of mice vs mouses, I defer to the foremost authority on the subject, Jinks the cat how said: "I hate miceses to pieces!".

  • Ken Row (unregistered) in reply to Rawr

    The WTF is that, at least on some DB products, building a clustered index means the entire table gets physically re-arranged so that its data matches the order of the index.

    The line about 'new rows are not included' seems troll-ish, but that may also vary from one DB product to another.

  • Ken Row (unregistered) in reply to Nagesh

    The WTF comes up if the table's original order is different than the order specified by the index. If you write the data to the table in order of "LastName, FirstName" and build a clustered index on "SSN", the entire table will get physically re-written in order of SSN.

    If the new order already matches the index order, it's not a big deal.

  • (cs) in reply to Ken Row
    Ken Row:
    The WTF is that, at least on some DB products, building a clustered index means the entire table gets physically re-arranged so that its data matches the order of the index.
    Um, that's what 'clustered index' means, isn't it? What database product uses clustered indexes (or the same concept under another name, eg. Oracle's 'index organized table') and doesn't physically order the records to match their index order?
  • (cs) in reply to Heavy
    Heavy:
    One three letter acronym that we work with (who continually buys out smaller companies and rebrands their products to be their own) seems more like a sales outfit than an IT company. I fell of my chair when they asked us for advice on how to fix a problem with one of their products - apparently, the people who know how to support it are in America, and cannot be engaged for our account.
    I'm sorry; I've been thinking about this for several minutes, and I still don't see how "Oracle" can be a three letter acronym. Can you please explain?
  • An alternate explanation (unregistered) in reply to Ken Row
    Ken Row:
    The WTF is that, at least on some DB products, building a clustered index means the entire table gets physically re-arranged so that its data matches the order of the index.

    The line about 'new rows are not included' seems troll-ish, but that may also vary from one DB product to another.

    More a small mistake from me. It seems that a "cluster created on an index" is something completely different from a "clustered index".

  • +9 (unregistered) in reply to Leenus

    The vendor wants more $$$ perhaps. Come on, lets upgrade the system to 7 figures.

  • (cs) in reply to C-Octothorpe
    C-Octothorpe:
    Hortical:
    This week, I'm blaming fat people for expensive gasoline. If they didn't lug around so much weight, they wouldn't use as much gas, lowering demand and lowering prices.

    Fat bastards! Why you get places by rolling around, like you do in my dreams?!

    Expensive gas? Man, that's stupid! Everybody knows they cause global warming (methane gas and whatnot)...

    All that just means they're not fat enough. If they were totally housebound then they'd be net carbon sinks.

  • craig (unregistered) in reply to Herby
    Herby:
    On the subject of mice vs mouses, I defer to the foremost authority on the subject, Jinks the cat how said: "I hate miceses to pieces!".

    I think it's actually "mieces" so it rhymes with "pieces"

  • (cs) in reply to Scarlet Manuka
    Scarlet Manuka:
    I'm sorry; I've been thinking about this for several minutes, and I still don't see how "Oracle" can be a three letter acronym. Can you please explain?
    It's not. It's a six-letter “four-letter Anglo-Saxon word” that is based on a Latin word.

    Hopefully that's clear. Rhymes with IBM, SAP, HP, Atos, Accenture, GE and Infosys.

  • Philosopher (unregistered) in reply to blank
    blank:
    to trwtf is Matthew not charging his friend for his consulting services?

    The gender of the friend is not mentioned in the article so perhaps Matthew had other rewards in mind.

    I guess altruism can't be ruled out either. considers the world Actually it probably can be.

  • craig (unregistered) in reply to Philosopher
    Philosopher:
    The gender of the friend is not mentioned in the article so perhaps Matthew had other rewards in mind.

    The gender preference of Matthew isn't specified either... just sayin'

  • (cs) in reply to Watson
    Watson:
    C-Octothorpe:
    Expensive gas? Man, that's stupid! Everybody knows they cause global warming (methane gas and whatnot)...

    All that just means they're not fat enough. If they were totally housebound then they'd be net carbon sinks.

    As C-Octothorpe already explained: They do cause climate change when they originate methane - i.o.w. when they are farting!

  • (cs) in reply to An alternate explanation
    An alternate explanation:
    Ken Row:
    The WTF is that, at least on some DB products, building a clustered index means the entire table gets physically re-arranged so that its data matches the order of the index.

    The line about 'new rows are not included' seems troll-ish, but that may also vary from one DB product to another.

    More a small mistake from me. It seems that a "cluster created on an index" is something completely different from a "clustered index".

    To be 100% precise: The PostgreSQL-docu you linked to speaks of "cluster a table based on an index".

    MS SQL Server inherited clustered indieces from Sybase.

    This is what the Sybase Adaptive Server docu says about clustered indieces:

    Using clustered or nonclustered indieces:
    With a clustered index, Adaptive Server sorts rows on an ongoing basis so that their physical order is the same as their logical (indexed) order. The bottom or leaf level of a clustered index contains the actual data pages of the table. Create the clustered index before creating any nonclustered indieces, since nonclustered indieces are automatically rebuilt when a clustered index is created.

    There can be only one clustered index per table. It is often created on the primary key--the column or columns that uniquely identify the row.

  • bob (unregistered) in reply to Dan

    To create an index from scratch on a 'big' table, can take a significant amount of time. Normally an index is updated when the table data changes, thereby amortizing the processing requirements against future selects or updates on the table.

    Since reporting 'is/should' just be a 'dump' of the final temp table it is truly a WTF........ Unless the reporting is doing some sort of joining/ selects against other tables with the temp data, which would be another WTF.

  • bob (unregistered) in reply to mjk340

    That is a really stupid thing to do........ Specifically because not all table data is suitable for 'indexing' ........ Hay lets just index these BLOBS & CLOBS Columns.

  • blank (unregistered) in reply to Dan Neely

    thanks, i'll take that as a compliment :)

    Just for the record, I've often helped out friends and ex-colleagues with various IT issues and I've never charged for it. Admittedly, I have accepted the odd bottle of booze by way of a thankyou, but that's neither expected nor demanded.

  • (cs) in reply to C-Octothorpe
    C-Octothorpe:
    Rawr:
    It's specifically pointed out that temp table is re-generated every query (of course it is) as if it's going to FUBAR the clustered index, which it won't.
    Who the fuck said it would FUBAR the index? There is no index to FUBAR, get it?
    Rawr:
    No one can say it's a WTF with the information that was given.
    Uh, yeah you can... Here: they put an index on a temp table rather than the source table. There ya go!
    Rawr:
    You assume they aren't using the temp table to query
    Not sure what you're trying to say here or where anybody would disagree with you...
    Rawr:
    I give them the benefit of the doubt
    Ah, there's your problem...
    That reads so nicely: "RAWR RAWR blablablabla RAWR RAWR blablablabla RAWR RAWR"
  • itsmo (unregistered) in reply to bob
    bob:
    That is a really stupid thing to do........ Specifically because not all table data is suitable for 'indexing' ........ Hay lets just index these BLOBS & CLOBS Columns.

    F*ck off Blob

  • (cs) in reply to The poop of DOOM
    The poop of DOOM:
    C-Octothorpe:
    Rawr:
    It's specifically pointed out that temp table is re-generated every query (of course it is) as if it's going to FUBAR the clustered index, which it won't.
    Who the fuck said it would FUBAR the index? There is no index to FUBAR, get it?
    Rawr:
    No one can say it's a WTF with the information that was given.
    Uh, yeah you can... Here: they put an index on a temp table rather than the source table. There ya go!
    Rawr:
    You assume they aren't using the temp table to query
    Not sure what you're trying to say here or where anybody would disagree with you...
    Rawr:
    I give them the benefit of the doubt
    Ah, there's your problem...
    That reads so nicely: "RAWR RAWR blablablabla RAWR RAWR blablablabla RAWR RAWR"
    Hahaha nice! +1

Leave a comment on “The Temporary Index”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article