Discussion is incomplete without mentioning effect of concurrency & transaction isolation. Yes, splitting into smaller queries may reduce lock hold time and reduce contention, but what does that gain if the end result is *different* than 1 large statement, because other processes changed data between the steps of a broken out process ?

Pretty good article, with some nice tips. Regarding PW's note, it's a valid option, but no real performance increase over using variables.

Situation 3 looks dicey, though. I understand what you're trying to do, and if your "Where" clause is expensive it could perform better. What you really need to do in this case is populate a seed table with the PK values, then use this to move rows around. It will perform better than moving the whole row one extra time, especially if it's a big row.

Situation 4 is nice...I've been harping on the uselessness of fast forward cursors for a "while" (oops.... I usually use a different form, though; The performance is significantly better.

For number 3, rather than moving the data into the archive table, rename the history table to archive then move the relatively few records into a new history table. This should work OK since you said the tables have no defined relationships. Or does renaming a table do things internally that I don't know about that make this a bad idea?

Second comment. I am writing this article mainly for the application developers and/or report writers. Most of them have no idea what mean effect of concurrency & transaction isolation. But they need a general guide line to make better performance.

This article I placed as a general guideline for the developers because I am tired with obvious performance glitches. Probably some parts can be done better. But my main point was to show that small thoughts can make a difference.

You right if it is the simple table in air. In our case each table has the corresponding names for the PK and indexes. For example, table t_customer will have PK named as I_PK_T_Customer_10. Index may have a name I_IN_T_Customer_20. By renaming the table I will ruin the database standartization.

But the main point of the article is to show that a simple change may have a big effect. And another point, that developer who is writing an archiving process have no permissions to rename objects. Plus this is only a small part of the process. Process consists with tonns of pages of code and this is the only one small part of the logic.