We have two stored procedures taking five minutes to run in our development database, but the same stored procedures run quickly on our QA database. Why?

Question

We have two stored procedures taking about five minutes to run in our development database. The same two stored procedures run quickly on our QA database. Both databases are on the same physical server. What can account for this difference?

Answer

There are several possibilities:

• The data is different, which can lead the query optimizer to make different decisions when the stored procedures are run.
  
• But what if the data is identical, or very close to identical? In this case, the most likely is that the statistics have not been updated on the development server. When statistics are invalid, for whatever reason, the query optimizer may not make correct optimization decisions, resulting in poor performance. I would suggest in your case that the statistics be updated, in the development database, and then test. I imagine you will find that this resolves the problem.

So the moral of this story is, if you are doing performance testing on a non-production database, you must ensure that it exactly duplicates your production data, or at least, come very close. This includes using the same data, and ensuring that the databases are similarly up-to-date, such as with statistics. If you don't, you may find yourself faced with what appear to be strange problems, as described in this question.