Vision Binary

I received a query from my supervisor today, she need me to create a report, and what I need to do is just create parameters in SSRS and map them with the variables in the query,  it would be a snap report.

I start to check the query. In the beginning of the query, a temp table was created, then it insert into the temp table from the result dataset that several tables were joined and union many times by different where clauses.

then update the temp table, last step is query the temp table.

I have been always told that: you better index your temp table to make a better performance.

So when I had that query, first thing I did is –add indexes. I was happy with my “smart” work,
however when I run the query, it turned out the 18s query now run about 57s and keep going on.
How come the query is slower? Isn’t it supposed to be faster if I added the indexes?
Well, answer is: NOPE.

Just to clarify: the slow performance is not caused by some redundant indexes.

It’s just because, load will happen much slower with the index. And Index, however just faster for querying.

So does it mean we better create indexes after we load the data into temp table?
what I think is, it depends.

But if you trying to create a clustered indexes, you better create it before you populated the table.,if you create them afterwards, then engine will have to recreate then entire table.
Any other index can be added after populating the table. This can save time.
Especially if you populate the table in batches. But the Index temp table afterwards, still take some time.  I had 1.5 million rows load into the temp table, it takes me 7s to index after I populated the table. Without indexes, i query against the temp table just within 2s. and it didn’t really enhance the performance, looks it just slowed down the whole process

So my suggestion is, if you want to add indexes to your temp table, first think about it: Do I really query a lot against the temp table? and go run with and without indexes to compare it.