- Feature Articles
- CodeSOD
- Error'd
- Forums
-
Other Articles
- Random Article
- Other Series
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
CREATE CLUSTERED INDEX "Hello" on #First_Comment
Admin
Databeses and what can be done... To mess those...
Admin
to trwtf is Matthew not charging his friend for his consulting services?
Admin
He travel around globe!
Admin
Admin
Given the super-detailed requirements, you can hardly blame the consultants. They just did their best to respond to what their customer requested.
Admin
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?
Admin
Only when he's not working for free.
Admin
Admin
I don't see a problem - it's only a temporary cluster-fcuk.
Admin
do I dare give them credit there might be trade-offs on report speed vs. transaction speed for adding that index?
Admin
Admin
Our DBA saying that sometime temp table also need index.
Admin
It's disturbing that so many DBAs don't know how indexes work, but hilarious that so many big enterprise vendors don't.
Admin
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.
Admin
It would add a whole new level of fail if it turned out that the temp table wasn't really necessary.
Admin
Alright, I'll bite. Can someone more SQL-adept than me explain the problem?
Admin
Not consulting company, the software vendor.
Admin
Admin
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?
Admin
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
Admin
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.
Admin
Admin
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.
Admin
Admin
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?
Admin
Maybe the issue is that the index is on the item_id, instead of whatever data fields the report is sorted on?
Admin
Admin
Admin
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.
Admin
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.
Admin
TRWTF is "a un-paid".
Admin
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.
Admin
Admin
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.
Admin
You know, because scumbag salesmen and misrepresenting managerial types never try to bullshit their way into luxurious jobs for which they're not qualified.
Admin
There's no trwtf in this place. Temp table also needing indexes before acess is permited.
Admin
In my company, that would eat up way to much space and out database servers would need new hard drive space every other day.
Admin
yes it is time-saving mecanism only.
Admin
Damn no edit for unregistered, was responding to
Admin
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%2eAdmin
Admin
Admin
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.
Admin
Admin
Admin
That's why you create the index after you have the data in your table. I still fail to see the WTF.
Admin
You must have though it conformed to the more typical troll pattern:
<[pseudo-]Naive boasting of one's incompetence> <Flames/Flamebait>
Admin
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.
Admin
True WTF is saying indexes instead of indices