I am testing different securing schemes for any large table, a lot more than 4 million rows and likely to develop to ten million.
My knowledge about SyBase:
ALLPAGES, very slow BCP (in) increase having a area that goes towards the Primary Key.
DATAROWS, fast BCP, fast update for fields within the PK, quickest choose.
DATAPAGES, quickest BCP increase, and faster choose than ALLPAGES but a lot slower as DATAROWS.
I am no expert, however it appears in my experience that DATAROWS blocks a lot more than DATAPAGES, and that i haven't seen any downside with DATAPAGES.
The only real reason for favor for DATAROWS which i may find was a little faster performance in chooses which use primary key, but DATAPAGES is substantially faster at updates.
Any encounters used?
So how exactly does this securing schemes compare?
Pros/Cons of every one?
I attempted to discover the concept of it, but it is not so obvious in my experience.
Securing is really a concurrency control mechanism. It guarantees the consistency of information within and across transactions. Securing is required inside a multiuser atmosphere, since many customers might be working with similar data simultaneously, so you shouldn't choose securing schemes based on e.g. BCP speed.
For tables which use allpages securing or datapages securing, either page locks or table locks
For tables which use datarows securing, either row locks or table locks
But allpages-locked tables safeguard against phantoms (rows that may appear or disappear from the results set) by holding locks around the index pages for that
serializable read transaction
Generally you need to use datarows or datapages securing for tables with lock contention because of updates and chooses.
Find out more about securing and gratifaction here