The Temporary Index

« Return to Article
  • wernercd 2011-11-07 09:01
    CREATE CLUSTERED INDEX "Hello" on #First_Comment
  • Rnd( 2011-11-07 09:02
    Databeses and what can be done... To mess those...
  • blank 2011-11-07 09:04
    to trwtf is Matthew not charging his friend for his consulting services?
  • wtf 2011-11-07 09:08
    He travel around globe!
  • K 2011-11-07 09:09
    blank:
    to trwtf is Matthew not charging his friend for his consulting services?
    Wouldn't it be more reasonable to charge the vendor?
  • The Hated 2011-11-07 09:16
    Given the super-detailed requirements, you can hardly blame the consultants. They just did their best to respond to what their customer requested.
  • Conspirator 2011-11-07 09:27
    If Matthew is highly-paid, and as we know cost is inversely proportional to quality, does that mean that Matthew is a poor quality consultant?
  • Your Name 2011-11-07 09:29
    Conspirator:
    If Matthew is highly-paid, and as we know cost is inversely proportional to quality, does that mean that Matthew is a poor quality consultant?


    Only when he's not working for free.
  • Zolcos 2011-11-07 09:30
    Conspirator:
    If Matthew is highly-paid, and as we know cost is inversely proportional to quality, does that mean that Matthew is a poor quality consultant?

    By day, yes. But the consulting services he provides to his friend are of infinite quality.
  • anonymous 2011-11-07 09:38
    I don't see a problem - it's only a temporary cluster-fcuk.
  • recovering consultant 2011-11-07 09:50
    do I dare give them credit there might be trade-offs on report speed vs. transaction speed for adding that index?
  • The poop of DOOM 2011-11-07 10:11
    recovering consultant:
    do I dare give them credit there might be trade-offs on report speed vs. transaction speed for adding that index?

    That's what pagination's for, or, god forbid, block loading data from the database. That crap's got a name, but it's such a pain in the behind for users to work with it, that I never use it and thus don't even know its name. Saw it while testing hospital software once, though. Loading patients... Tumdeedumdum... Ah, 50 loaded. Scrollscrollscroll... Oh, we're not at the V yet. Loadloadload... Scrollscrollscroll... Loadloadload, and that for several minutes until you find that one patient you're looking for, whose name is somewhere near the end of the alphabet. Worst thing is that that wasn't the worst part of that software. The amount of icky diseases patients could get because that program overwrote some "special care" parameters it set itself... Urgh!
  • Nagesh 2011-11-07 10:11
    Our DBA saying that sometime temp table also need index.
  • boog 2011-11-07 10:11
    It's disturbing that so many DBAs don't know how indexes work, but hilarious that so many big enterprise vendors don't.
  • Geoff 2011-11-07 10:19
    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.
  • PedanticCurmudgeon 2011-11-07 10:19
    It would add a whole new level of fail if it turned out that the temp table wasn't really necessary.
  • Dan 2011-11-07 10:23
    Alright, I'll bite. Can someone more SQL-adept than me explain the problem?
  • np 2011-11-07 10:27
    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.
  • boog 2011-11-07 10:28
    Dan:
    Alright, I'll bite. Can someone more SQL-adept than me explain the problem?
    Explanation can be found here. Second-to-last paragraph.
  • C-Octothorpe 2011-11-07 10:35
    boog:
    Dan:
    Alright, I'll bite. Can someone more SQL-adept than me explain the problem?
    Explanation can be found here. Second-to-last paragraph.
    To expand on boog's explanation, the temp table would be created and destroyed every time the report was run, thus making this "fix" essentially a noop.

    I'm curious though, how the vendor asked the client to "apply" this fix. If it's just run in a query analyzer, I'm sure you'd get an error stating that the (temp) table doesn't exist, no?
  • Yazeran 2011-11-07 10:38
    Yep, the report generated a temporary table from some big data table. and apparently that temporary table was also quite big.

    Creation of indexes take time, but usually you only do it once and then any inserts on that table will update the index (which also increases the time of an insert/update a little).

    In this case, they first extracted data from the big table, an operation which WOULD have benefited from an index and used these data to create the temp table and THEN created an index on that table!...

    What they SHOULD have done was create an index on the original (permanent) table and then they would had seen an increase in performance as expected.

    Yours Yazeran

    Plan: To go to Mars one day with a hammer
  • Martin 2011-11-07 10:39
    Dan:
    Alright, I'll bite. Can someone more SQL-adept than me explain the problem?


    The temporary table presumably holds the subset of records from the inventory that are required by the report. Where the performance problem appears to be is in actually populating that table in the first place from the master inventory table. This is where the complicated query is likely to be. If they'd applied the index to that table instead, it might have sped up the report by making it faster to populate the temporary table.

    What they've actually done is apply an index to the temporary table, AFTER all of the hard work has been done populating it. This index will probably make no noticeable difference to the extracting of the report data from the temporary table (if they're extracting all of it in one go), or only a very slight one as this is not where the performance bottleneck is - it's probably a very simple query. So they've completely missed the point of the original problem - hence the WTF.
  • boog 2011-11-07 10:41
    C-Octothorpe:
    boog:
    Dan:
    Alright, I'll bite. Can someone more SQL-adept than me explain the problem?
    Explanation can be found here. Second-to-last paragraph.
    To expand on boog's explanation, the temp table would be created and destroyed every time the report was run, thus making this "fix" essentially a noop.

    I'm curious though, how the vendor asked the client to "apply" this fix. If it's just run in a query analyzer, I'm sure you'd get an error stating that the (temp) table doesn't exist, no?
    The patch probably inserted the line after an already existing CREATE TABLE #tbl_inv_items command.
  • recovering consultant 2011-11-07 10:48
    I "hope" it's inside the report.

    It's not pretty but I've done this before. It really points to the need for a data warehouse or at least replicated reporting environment.
  • Nagesh 2011-11-07 10:48
    Nagesh:
    Our DBA saying that sometime temp table also need index.

    Seme DBA also indice al colums for preformance.
  • TheJonB 2011-11-07 10:50
    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?
  • AverageJon 2011-11-07 10:54
    Maybe the issue is that the index is on the item_id, instead of whatever data fields the report is sorted on?
  • C-Octothorpe 2011-11-07 10:55
    recovering consultant:
    I "hope" it's inside the report.

    It's not pretty but I've done this before. It really points to the need for a data warehouse or at least replicated reporting environment.
    I've been in the situation of proposing a reporting env. a couple of times, however the business usually makes an argument about the need for "real time reporting"... To be honest, I don't care, just be prepared for reports which take forever and an application which performs so badly it makes glaciers look like F1 racers.
  • Anketam 2011-11-07 10:55
    Martin:
    Dan:
    Alright, I'll bite. Can someone more SQL-adept than me explain the problem?


    The temporary table presumably holds the subset of records from the inventory that are required by the report. Where the performance problem appears to be is in actually populating that table in the first place from the master inventory table. This is where the complicated query is likely to be. If they'd applied the index to that table instead, it might have sped up the report by making it faster to populate the temporary table.

    What they've actually done is apply an index to the temporary table, AFTER all of the hard work has been done populating it. This index will probably make no noticeable difference to the extracting of the report data from the temporary table (if they're extracting all of it in one go), or only a very slight one as this is not where the performance bottleneck is - it's probably a very simple query. So they've completely missed the point of the original problem - hence the WTF.
    On top of that it takes time to build an index. So not only is it missing the whole point of an index, it is adding the time to build that index to every query, and depending on the size of that temporary table it could be measurable.
  • mjk340 2011-11-07 10:55
    It is very difficult to speculate who is to blame given the details in the story. Table size, level of normalization, structure of queries/subqueries, report logic, access statistics, and cluster architecture/physical layout are all unknown variables into an equation that returns 'true' if an index will help and 'false' if it is unnecessary or otherwise counterproductive.

    That being said, it is not a WTF in and of itself to add an index to a temp table if most of the processing is through insert/delete statements, the table has a large number of rows, and a flurry of select queries are only needed at report generation time. In that case it is better to leave the column unindexed for responsiveness in the common case (insert/delete heavy), and build an index when needed for the report (select heavy).

    The fact that it is taking longer now leads me to believe that the WTF is not with building the index itself. Building an index, even on a large table, takes a faction of the time of, for example, an outer join against an unindexed column in that table. The report logic is most likely creating a temp table and building an index on each iteration of some loop (e.g. once per item in inventory or once per customer). The report logic may need to just be restructured to create a temp table outside of the loop, and release it after all processing is completed.

  • Rawr 2011-11-07 10:57
    Dan:
    Alright, I'll bite. Can someone more SQL-adept than me explain the problem?


    It really depends. If they ran the script once and expected the clustered index to stay on the temp table, obviously it'll be destroyed when the table falls out of the batch.

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

    If this is a large temp table and you're joining or have a WHERE clause on item_id, this could very possibly speed up your processing time.

    Unfortunately pretty much everything in the DBA's world is 'it depends', and would need to be tested.
  • Sizik 2011-11-07 11:04
    TRWTF is "a un-paid".
  • mjk340 2011-11-07 11:11
    Nagesh:
    Nagesh:
    Our DBA saying that sometime temp table also need index.

    Seme DBA also indexes al colums for preformance.


    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.
  • Nagesh 2011-11-07 11:20
    mjk340:
    Nagesh:
    Nagesh:
    Our DBA saying that sometime temp table also need index.

    Seme DBA also indexes al colums for preformance.


    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.

    With NSQL (Nagesh SQL), indeks always get create automaticaly on colum creation.
  • iToad 2011-11-07 11:28
    The really depressing thing is that these big enterprisey companies pay an enormous amount of money for their big enterprisey palatial headquarters, but won't pay chump change to hire a couple of real database programming experts.
  • boog 2011-11-07 11:33
    iToad:
    The really depressing thing is that these big enterprisey companies pay an enormous amount of money for their big enterprisey palatial headquarters, but won't pay chump change to hire a couple of real database programming experts.
    Perhaps they figure if they turn the workplace into a place of luxury, it will attract the very best database programming experts.

    You know, because scumbag salesmen and misrepresenting managerial types never try to bullshit their way into luxurious jobs for which they're not qualified.
  • Nagesh 2011-11-07 11:36
    Dan:
    Alright, I'll bite. Can someone more SQL-adept than me explain the problem?


    There's no trwtf in this place. Temp table also needing indexes before acess is permited.
  • Altourus 2011-11-07 11:36
    In my company, that would eat up way to much space and out database servers would need new hard drive space every other day.
  • Nagesh 2011-11-07 11:37
    mjk340:
    Nagesh (feke):
    Nagesh:
    Our DBA saying that sometime temp table also need index.

    Seme DBA also indexes al colums for preformance.


    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.


    yes it is time-saving mecanism only.
  • Altourus 2011-11-07 11:38
    Altourus:
    In my company, that would eat up way to much space and out database servers would need new hard drive space every other day.


    Damn no edit for unregistered, was responding to

    MikeOrSomething:
    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:
  • Nagesh 2011-11-07 11:47
    plz talk here


    %77%68%65%72%65%20%69%73%20%74%68%65%20%73%70%61%6d%2c%20%79%6f%75%20%73%69%6c%6c%79%20%6d%6f%6e%6b%65%79%3f%20%69%20%77%69%6c%6c%20%68%61%76%65%20%79%75%6f%20%72%6f%61%73%74%65%64%20%6f%76%65%72%20%73%70%69%74%20%66%69%72%65%20%66%6f%72%20%74%68%69%73%2e
  • PedanticCurmudgeon 2011-11-07 12:32
    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.
  • anonymouser 2011-11-07 12:58
    mjk340:
    Nagesh:
    Nagesh:
    Our DBA saying that sometime temp table also need index.

    Seme DBA also indexes al colums for preformance.


    In my organization, standard practice is to make an index on all possible columns, in all possible orders, so that select operations are always as fast as can be, but insert operations take far, far longer than necessary. 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

    Stupid DBAs just don't know how to create the indexes necessary for maximum performance.

    FTFY
  • An alternate explanation 2011-11-07 13:09
    I don't think anyone have mentioned the true wtf here.

    They are creating a <strong>clustered</strong> 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.
  • PedanticCurmudgeon 2011-11-07 13:27
    An alternate explanation:
    I don't think anyone have mentioned the true wtf here.

    They are creating a <strong>clustered</strong> 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.
    And now, folks, we have a perfect 10! Let's have a round of applause for the new troll master!
  • Sadly, I've Seen This 2011-11-07 13:28
    wtf:
    He travel around globe!

    Maybe, he works around Globe, Arizona.
  • Rawr 2011-11-07 13:29
    An alternate explanation:
    I don't think anyone have mentioned the true wtf here.

    They are creating a <strong>clustered</strong> 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.
  • Hortical 2011-11-07 13:29
    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>
  • Anders 2011-11-07 13:31
    An alternate explanation:
    I don't think anyone have mentioned the true wtf here.

    They are creating a <strong>clustered</strong> 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.
  • Leenus 2011-11-07 13:32
    True WTF is saying indexes instead of indices
  • C-Octothorpe 2011-11-07 13:34
    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. 2011-11-07 13:47
    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 2011-11-07 13:56
    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 2011-11-07 14:01
    Leenus:
    True WTF is saying indexes instead of indices

    you probably call multiple pointer input devices "mice" too, don't you?
  • Nagesh 2011-11-07 14:03
    Rawr:
    An alternate explanation:
    I don't think anyone have mentioned the true wtf here.

    They are creating a <strong>clustered</strong> 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
  • C-Octothorpe 2011-11-07 14:15
    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)...
  • boog 2011-11-07 14:16
    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.
  • C-Octothorpe 2011-11-07 14:22
    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...
  • boog 2011-11-07 14:25
    C-Octothorpe:
    But... But... Indexes on temp tables are OK! Right? So this ISN'T a WTF...
    Skipping your meds again, I see.
  • C-Octothorpe 2011-11-07 14:29
    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!
  • no laughing matter 2011-11-07 14:31
    My old friend Bobby Tables had a temporary index once, and of course it was no living matter!
  • An alternate explanation 2011-11-07 14:41
    Anders:
    An alternate explanation:
    I don't think anyone have mentioned the true wtf here.

    They are creating a <strong>clustered</strong> 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 2011-11-07 14:55
    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 2011-11-07 15:01
    Wow. The obvious-and-stupid trolls have run everyone off. The only ones left are the usual suspects and Serguey123's multiple sockpuppets. Nice!
  • boog 2011-11-07 15:12
    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.)
  • C-Octothorpe 2011-11-07 15:13
    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 2011-11-07 15:18
    So we've been arguing about two different understandings of the article. That works for me.
  • Brian White 2011-11-07 15:43
    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 2011-11-07 15:43
    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 2011-11-07 15:52
    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 2011-11-07 16:00
    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 2011-11-07 16:06
    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 2011-11-07 16:20
    Sorry guys I'm too busy today fucking bitches on my yacht.

    Come tomorrow.
  • C-Octothorpe 2011-11-07 16:23
    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 2011-11-07 16:32
    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.
  • Jaime 2011-11-07 16:59
    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.
  • m 2011-11-07 18:03
    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 2011-11-07 18:27
    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 2011-11-07 18:58
    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 2011-11-07 19:02
    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 2011-11-07 19:51
    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 2011-11-07 20:11
    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 2011-11-07 20:16
    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.
  • SCSimmons 2011-11-07 20:22
    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?
  • Scarlet Manuka 2011-11-07 22:16
    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 2011-11-07 22:43
    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 2011-11-08 01:47
    The vendor wants more $$$ perhaps. Come on, lets upgrade the system to 7 figures.
  • Watson 2011-11-08 01:50
    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 2011-11-08 04:44
    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"
  • dkf 2011-11-08 04:45
    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 2011-11-08 04:47
    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 2011-11-08 05:13
    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'
  • no laughing matter 2011-11-08 05:32
    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!
  • no laughing matter 2011-11-08 05:43
    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 2011-11-08 05:53
    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 2011-11-08 06:02
    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 2011-11-08 06:46
    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.
  • The poop of DOOM 2011-11-08 07:17
    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 2011-11-08 07:44
    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
  • boog 2011-11-08 09:40
    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
  • boog 2011-11-08 09:48
    bob:
    That is a really stupid thing to do........
    I think he was implying that.

    bob:
    Specifically because not all table data is suitable for 'indexing'
    ........
    Hay lets just index these BLOBS & CLOBS Columns.
    I was thinking the same thing. What need would you ever have to index a description?
  • The poop of DOOM 2011-11-08 09:50
    boog:
    I was thinking the same thing. What need would you ever have to index a description?

    Easy! You index a description if you want to find it quickly
  • C-Octothorpe 2011-11-08 09:58
    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"
    Godammit! You sound like my wife... :)
  • Nagesh 2011-11-08 10:14
    I am puting index card on detabase servers, but no preformance increase.
  • Obviously Obvious 2011-11-08 10:43
    C-Octothorpe:
    The poop of DOOM:
    C-Octothorpe:
    Rawr:
    Barf Barf
    Blablabla
    That reads so nicely: "RAWR RAWR blablablabla RAWR RAWR blablablabla RAWR RAWR"
    Godammit! You sound like my wife... :)
    I have news for you: it is YOUR WIFE!
  • The poop of DOOM 2011-11-08 10:49
    Obviously Obvious:
    C-Octothorpe:
    The poop of DOOM:
    C-Octothorpe:
    Rawr:
    Barf Barf
    Blablabla
    That reads so nicely: "RAWR RAWR blablablabla RAWR RAWR blablablabla RAWR RAWR"
    Godammit! You sound like my wife... :)
    I have news for you: it is YOUR WIFE!

    Yes honey, please come home! Our kids and I haven't seen you for three weeks already. Where did you go? You said you were just going out for a smoke...
  • Obviously Obvious 2011-11-08 10:54
    The poop of DOOM:
    Obviously Obvious:
    C-Octothorpe:
    The poop of DOOM:
    C-Octothorpe:
    Rawr:
    Barf Barf
    Blablabla
    That reads so nicely: "RAWR RAWR blablablabla RAWR RAWR blablablabla RAWR RAWR"
    Godammit! You sound like my wife... :)
    I have news for you: it is YOUR WIFE!

    Yes honey, please come home! Our kids and I haven't seen you for three weeks already. Where did you go? You said you were just going out for a smoke...
    Last time we saw him, he was going all the way IN with the Zune
  • C-Octothorpe 2011-11-08 10:55
    The poop of DOOM:
    Obviously Obvious:
    C-Octothorpe:
    The poop of DOOM:
    C-Octothorpe:
    Rawr:
    Barf Barf
    Blablabla
    That reads so nicely: "RAWR RAWR blablablabla RAWR RAWR blablablabla RAWR RAWR"
    Godammit! You sound like my wife... :)
    I have news for you: it is YOUR WIFE!

    Yes honey, please come home! Our kids and I haven't seen you for three weeks already. Where did you go? You said you were just going out for a smoke...
    I did; it was just a very long smoke... On an unrelated topic, I'll just be going out for a coffee now. BRB!

    Filed Under: deadbeat dad
  • frits 2011-11-08 10:59
    C-Octothorpe:
    a very long smoke
    A pole smoke? (sorry too easy)
  • C-Octothorpe 2011-11-08 11:02
    frits:
    C-Octothorpe:
    a very long smoke
    A pole smoke? (sorry too easy)
    What has zunesis done to you?
  • Steve The Cynic 2011-11-08 11:04
    anonymouser:
    Leenus:
    True WTF is saying indexes instead of indices

    you probably call multiple pointer input devices "mice" too, don't you?

    I call them "trackballs"...
  • frits 2011-11-08 11:18
    C-Octothorpe:
    frits:
    C-Octothorpe:
    a very long smoke
    A pole smoke? (sorry too easy)
    What has zunesis done to you?
    Nothing *shivers*. The difference is that Zune would have added the "rape element" somewhere in there.
  • The poop of DOOM 2011-11-08 11:29
    frits:
    C-Octothorpe:
    frits:
    C-Octothorpe:
    a very long smoke
    A pole smoke? (sorry too easy)
    What has zunesis done to you?
    Nothing *shivers*. The difference is that Zune would have added the "rape element" somewhere in there.

    Wind!
    Water!
    Fire!
    Earth!
    Heart!
    Rape!

    When these six elements combine, here comes... CAPTAIN PLANET!
  • C-Octothorpe 2011-11-08 11:52
    The poop of DOOM:
    frits:
    C-Octothorpe:
    frits:
    C-Octothorpe:
    a very long smoke
    A pole smoke? (sorry too easy)
    What has zunesis done to you?
    Nothing *shivers*. The difference is that Zune would have added the "rape element" somewhere in there.

    Wind!
    Water!
    Fire!
    Earth!
    Heart!
    Rape!

    When these six elements combine, here comes... CAPTAIN PLANET!
    HA! Yeah, zune would be "Rape" in a gimp outfit.
  • Joe Blow 2011-11-08 12:26
    I am a software developer and I have a pickup truck....it's a double whammy.
  • Coyne 2011-11-08 13:34
    I just decided I don't like the term "enterprisey" for cases like this. I think we ought to switch "enterpriseismic" (EN-ter-pry-SIGHS-mic)...

    ...as in earthquake...

    ...as in disaster.
  • Rat 2011-11-08 15:33
    Steve The Cynic:
    anonymouser:
    Leenus:
    True WTF is saying indexes instead of indices

    you probably call multiple pointer input devices "mice" too, don't you?

    I call them "trackballs"...

    Is that just the males?
  • Grefg 2011-11-08 15:34
    Coyne:
    I just decided I don't like the term "enterprisey" for cases like this. I think we ought to switch "enterpriseismic" (EN-ter-pry-SIGHS-mic)...

    ...as in earthquake...

    ...as in disaster.

    Why not just enterpreismic?
  • Geoff 2011-11-08 15:40
    "Unless the reporting is doing some sort of joining/ selects against other tables with the temp data, which would be another WTF. "

    I am not sure that is always a WTF. Frequently data gets organized into Parent-child relationships which may simply inserts of new records (where the parent ID) will be know by the software doing the insert. Lowing the cost of inserts transaction may be very important, but make reporting a pain. That *can* be a good trade.

    Often in those cases you will produce a "flattened" temp table to report against, that might indeed join with other things.
  • The Holey Order of the Zune 2011-11-08 21:03
    C-Octothorpe:
    The poop of DOOM:
    frits:
    C-Octothorpe:
    frits:
    C-Octothorpe:
    a very long smoke
    A pole smoke? (sorry too easy)
    What has zunesis done to you?
    Nothing *shivers*. The difference is that Zune would have added the "rape element" somewhere in there.

    Wind!
    Water!
    Fire!
    Earth!
    Heart!
    Rape!

    When these six elements combine, here comes... CAPTAIN PLANET!
    HA! Yeah, zune would be "Rape" in a gimp outfit.
  • barf4eva 2011-11-10 15:22
    It's disturbing that you think this is true of DBAs.. You must have had some bad experiences. I don't think, however, that your experiences are indicitive of general knowledge of a DBA. At least where I work, they know quite a bit about indexing.
  • boog 2011-11-10 15:42
    barf4eva:
    It's disturbing that you think this is true of DBAs.. You must have had some bad experiences. I don't think, however, that your experiences are indicitive of general knowledge of a DBA. At least where I work, they know quite a bit about indexing.
    I certainly don't think this is true of all DBAs, or even most DBAs, and I certainly wasn't implying that my experiences are indicative of any DBA's general knowledge. I've actually worked with a lot of very smart DBAs.

    But yes, I have had some bad experiences. I have worked with data warehouses (large tables, updated nightly) that had poor indexing (bad column order, for example) on some tables and no indexing on others. I've worked with databases that had no primary keys, foreign keys, etc. I have even worked with DBAs that have literally told me "I don't know much about indexes," in those exact words. I'm surprised and disturbed at how many I've encountered.
  • SQLMaster 2011-11-11 04:53
    anonymouser:
    Leenus:
    True WTF is saying indexes instead of indices

    you probably call multiple pointer input devices "mice" too, don't you?


    Nah, that sounds too dicey...
  • Bob 2011-11-11 17:52
    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.


    Ummmmmmmmmm, that's the point. Otherwise it's just a regular index. That's why you get exactly one clustered index per table, and why if you're selecting by the clustered index's field(s), it's as fast as it can possibly be.
  • Bob 2011-11-11 17:58
    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.


    Joo need to go back to DBA school, mang. A clustered index on an incremental field is just about the worst choice for a clustered index. You WANT to vary the insertion point so that you don't hot spot the leaf nodes. That's where the fill-factor comes in, with regard to re-ordering the physical pages. Actual usage and testing are required to figure out the optimal set up, but decent guessing based on expected transaction throughput and size can work.

    Those articles you mention are no doubt written by people with absolutely no understanding of a b-tree.


    The advice quoted is so unbelievably bad, it HAS to be sarcastic.