Skip to Content

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

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?

Add a comment
10|10000 characters needed 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.

Assigned Tags

Related questions

0 Answers

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.