0

In our sybase ASE 15.7 db, we have a table with 4 columns

uid int
id varchar 32
version varchar 32
xml text

Two indices on uid and id respectively; datarow locking; ~130 row of data

We also have 2k pagesize; xml length min 1012/max 5176/avr 1837. At the moment it's off row LOB

The problem is that sometimes simple insert takes ~10-15 seconds and I'm struggling to understand why.

  1. Can anyone give any theories?
  2. Would in-row LOB help? probably with a size of 2000?
  3. In general how would in-row / off-row affect locking?
CSBob
  • 39
  • 2

1 Answers1

0

I would say you need to test it which sounds obvious but its key for a change such as this. The thing about text columns is that in effect you have a heap of data at the end of the table which can be a focal point for contention and blocking.

That said, in your particular example your average rowsize is pretty close to the maximum size (1962 bytes) for the page size of your Sybase server. You can't set it to 2000 on a 2k page server because that's bigger than your page size is in bytes.

Realistically you would have to set it to 1894 which is your max row size less your other columns, but that's probably a bit close to the maximum, so it depends how full your ID and version columns are.

You also do not specify what type of indexes are in use i.e. clustered or non-clustered because if they're non-clustered it's still a heap table and can be subject to last page chain contention. You also don't quantify your rowcount of the table or any other information as to why your inserts are too slow i.e. transaction volume etc. so consider adding this information to your post such as query plans etc. or whether the ID is sequential as a hotspot for inserts and so on.

In-row LOB can work well for the right data.

Rich Campbell
  • 566
  • 2
  • 9