G
George Potemkin
Guest
In the ancient days it was usually faster to do them in batches of 100. In current versions with proper -spin settings it is usually just as fast or faster to delete one per transaction. I forced to disagree. Increasing the number of records processed per transaction (RPT) decreases the number of recovery notes (ai/bi notes) generated by the whole transaction per record. Just by two times (in best case) between 1 and 100 RPT. And the changes will be negligible for RPT higher than 100. The simplest transaction consists of the following notes: RL_TBGN + RL_RMCR + RL_CXINS + RL_TEND Transaction creates one record (RL_RMCR) and one index key (RL_CXINS). RL_TBGN/RL_TEND are bracketing the transaction. Minimal size of any recover note is 32 bytes. The size of RL_TBGN/RL_TEND is 60 bytes + length(USERID) for RL_TBGN note but let's assume we run the transaction as a blank user. The size of RL_RMCR includes the size of the record. The minimal record size is 16 bytes. (but in typical cases the record size is 100-300 bytes). So let's estimate the minimal size of RL_RMCR as 48 bytes (it's, of course, an underestimation). I did not check the sizes of RL_CXINS notes but let's estimate it by the minimum: 32 bytes So the total size of recovery notes generated by our simplest transaction is 200 bytes (=60+48+32+60) If we will create n records per transaction then the size of recovery notes per record will be 80 + 120/n = (60+n*(48+32)+60)/n Recs RL_Size 1 200 10 92 100 81 1000 80 The larger the records the less benefits from high RPT. During a transaction Progress will create a lot of latch locks. While using the large transactions we commit less transactions per sec and it does slightly decrease the latch usage. But as everybody knows the latches are very short term resource locks. The lock duration is much much shorter than any operation with a record. So the total number of latch locks will only slightly depends from the number of records per transaction. Also the -spin parameter helps to resolve the latch /conflicts/. It can't care how large are the transactions. And if other sessions mainly use/lock the different latches then the spin will not help either because we don't have a competition for shared memory resources.
Continue reading...
Continue reading...