0

Data gets read in every quarter into this table. Then after it is read in, certain fields get updated through an update command. The updates run really fast on the previous quarter's data, but when it gets to the new data it takes a long time.

We solved the problem by rebuilding the primary key after data is read in, and then running the update commands. This makes it just as fast as previous quarters but the rebuild can take a long time.

When I rebuild the indexes, we started using a fill factor but that did not help the cause.

Is there anything else I should consider?

2
  • Thank you. can you also add the CREATE TABLE script (and all the indexes) of the table and the UPDATE statement? It might help others to answer better the question. Aug 3, 2016 at 17:23
  • It might be useful to compare the statement plans for the previous quarter and the current one.
    – mustaccio
    Aug 3, 2016 at 20:37

1 Answer 1

1

It is taking a long time because of out-of-date statistics. You've added a lot of rows to the table, so the statistics are not good now. Rebuilding the primary key seems to resolve the performance issue because you get updated statistics when you rebuild an index. But rebuilding an index is an expensive way to just get updated statistics.

Run UPDATE STATISTICS WITH FULLSCAN on the primary key, or consider a sampling. If the table has millions of rows in it, perhaps a 30 percent sampling would do the trick.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.