• wernercd (unregistered)

    CREATE CLUSTERED INDEX "Hello" on #First_Comment

  • Rnd( (unregistered)

    Databeses and what can be done... To mess those...

  • blank (unregistered)

    to trwtf is Matthew not charging his friend for his consulting services?

  • wtf (unregistered)

    He travel around globe!

  • K (unregistered) in reply to blank
    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 (unregistered)

    Given the super-detailed requirements, you can hardly blame the consultants. They just did their best to respond to what their customer requested.

  • Conspirator (unregistered)

    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 (unregistered) in reply to Conspirator
    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.

  • (cs) in reply to Conspirator
    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 (unregistered)

    I don't see a problem - it's only a temporary cluster-fcuk.

  • recovering consultant (unregistered)

    do I dare give them credit there might be trade-offs on report speed vs. transaction speed for adding that index?

  • (cs) in reply to recovering consultant
    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!
  • (cs)

    Our DBA saying that sometime temp table also need index.

  • (cs)

    It's disturbing that so many DBAs don't know how indexes work, but hilarious that so many big enterprise vendors don't.

  • Geoff (unregistered) in reply to Nagesh

    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.

  • (cs)

    It would add a whole new level of fail if it turned out that the temp table wasn't really necessary.

  • Dan (unregistered)

    Alright, I'll bite. Can someone more SQL-adept than me explain the problem?

  • np (unregistered) in reply to Geoff
    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.

  • (cs) in reply to Dan
    Dan:
    Alright, I'll bite. Can someone more SQL-adept than me explain the problem?
    Explanation can be found here. Second-to-last paragraph.
  • (cs) in reply to boog
    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?

  • (cs) in reply to Dan

    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 (unregistered) in reply to Dan
    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.

  • (cs) in reply to C-Octothorpe
    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 (unregistered) in reply to C-Octothorpe

    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 (unregistered) in reply to Nagesh
    Nagesh:
    Our DBA saying that sometime temp table also need index.
    Seme DBA also indice al colums for preformance.
  • TheJonB (unregistered)

    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 (unregistered)

    Maybe the issue is that the index is on the item_id, instead of whatever data fields the report is sorted on?

  • (cs) in reply to recovering consultant
    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.

  • (cs) in reply to Martin
    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 (unregistered)

    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 (unregistered) in reply to Dan
    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 (unregistered)

    TRWTF is "a un-paid".

  • mjk340 (unregistered) in reply to Nagesh
    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 (unregistered) in reply to mjk340
    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 (unregistered)

    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.

  • (cs) in reply to iToad
    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.

  • (cs) in reply to Dan
    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 (unregistered) in reply to mjk340

    In my company, that would eat up way to much space and out database servers would need new hard drive space every other day.

  • (cs) in reply to mjk340
    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 (unregistered) in reply to Altourus
    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:
  • (cs)

    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
  • (cs) 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 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 (unregistered) in reply to mjk340
    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 (unregistered)

    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.

  • (cs) in reply to An alternate explanation
    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.

    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 (unregistered) in reply to wtf
    wtf:
    He travel around globe!
    Maybe, he works around Globe, Arizona.
  • Rawr (unregistered) in reply to An alternate explanation
    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.

  • Hortical (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.
    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 (unregistered) in reply to An alternate explanation
    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.

  • Leenus (unregistered)

    True WTF is saying indexes instead of indices

Leave a comment on “The Temporary Index”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article