RE: Slow performance of SP – SQLServerCentral

SSC Guru

Points: 1003864

June 23, 2017 at 9:47 am

#1947983

Thom A - Friday, June 23, 2017 4:29 AM
Do you have the query plans. Those are the really important part here, if you're estimates are off.
How often do you run these delete statements? Do you also update your statistics on a regular basis (especially you're deleting a large amount of data)?
Considering, however, how simple the queries are, do you have an index the article columns on each of your tables?

Also need to be aware of FKs. If there are any tables that reference the ones being deleted from, you can get great benefit by indexing those column on those tables that point to these. I'm talking about a night and day change. On one of our larger tables, a single row delete was taking minutes. Once we added indexes to all of the columns in the tables that were referencing the table we were deleting from, the time went so nearly 0.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)