About 6 months ago, we upgraded a large .NET project from SQL server 2000 to 2012. (i know it's 2 versions behind already, don't get me started!)
One of the stored procedures ran a query in the overnight batch process that took 55 mins since the upgrade, but jumped up to 3 hours last week. Prior to the upgrade however, the query took 1 minute. I added an index on the table as per the execution plan and it brought the run time down to 10 mins. I'm not a DBA, so I'm wondering if someone could explain why this is, as it doesn't make sense to me.
This was the query that caused the problems:
DELETE @ExcludedPersons FROM @ExcludedPersons WHERE personID NOT IN (SELECT personID FROM PersonStudy ps WHERE (ps.StudyTypeID = 1) AND (ps.StudyClosedDate IS NULL))
There are 2 indexes on the PersonStudy table:
- A clustered index on StudyID which is the primary key.
- A non-clustered index on PersonID which is also unique.
These indexes were just carried forward from SQL 2000. There are about 60,000 rows in the table. There are about 500,000 rows in the @ExcludedPerson table variable (but only 1 column).
For the last few months, the sub-query in the where clause returned 1 row and the query was taking ablout 55 mins. Last week, the final person had a date added to StudyClosedDate so the subquery returned 0 rows and that was when the time jumped up to 3 hours.
The Index that execution plan suggested was to add in an index with the Keys of StudyTypeID and StudyClosedDate and include PersonID and it was this that brought processing time to 10 mins.
So, my question is this:
Why is there this much processing time on a subquery that returns 0 rows and why does the index make such a big difference?
And why the jump in processing time between the sub-query returning 1 row and 0 rows? Is it do do with the different way indexes are handled in sql 2000 and 2012?