Skip to Content

Stored Procedure Performance slowed down down after deletion of 1 million data

Nov 28, 2017 at 06:04 PM


avatar image

Hi team,

We have developed an application which uses a stored procedure ..say SP1 and multiple views to transform data and populate in table T1. It generally used to take around 1 hour to complete the whole process.And then we have another stored procedure say SP2 which uses another set of views and populates table T1 and another table T2. It generally takes time 2 hour.There was some issue with procedure SP2 and we deleted around a million data from both T1 and T2 and fixed SP2. But after fix, suddenly SP1 processing became really slow.. like 3 hours, there was no changes made to the code of SP1 or any of the related views, and SP2 works just fine. There is no relation between SP1 and related views with SP2 and its views. Just wanted to know, did this issue happened due to mass deletion of data? What should be starting point of the investigation?

10 |10000 characters needed characters left characters exceeded

There is too little information in your question to answer it.

Based on the description the data deletion may

  • have influenced the runtime
  • have had no effect on the runtime

We don't know what "issue" was "fixed" with table T1 data and we don't know the effect on SP1 since we don't know when SP1 actually does. The same is true to T2 and SP2.

As usual, it is best to provide a minimal reproducible example that shows the effect (maybe on a lesser scale, but comparable) and then it is possible to analyse the different runtime scenarios.

* Please Login or Register to Answer, Follow or Comment.

0 Answers