1

I had some blocks in a table that contains IMAGE and TEXT columns(similar to this SO question) after some researches in-row and off-row feature in sybase ase 15.7 can improve the performance (if the size less then 4k in the logical storage, the LOB data will be place with the same page of table values thats called in-row more info here).

Can anyone explain:

-How to enable this feature on the database ? is it enabled with create table command ? or alter table ?

-How to check if its enables ?

-Why it might reduce or remove the blocks ?

-Why Text/image datatype might cause locks/blocks and enabling in-rows would remove it ?

Moudiz
  • 7,211
  • 22
  • 78
  • 156

1 Answers1

2

You have to enable the option for each column via an alter table against the column for an existing table or you can set the option if it's a new table against the column:

alter table tablename modify mycol in row (500)

Here I've said anything less than 500 bytes in length for the column will be stored in-row, anything over that in size will be stored off-row (old default behaviour). This can massively shrink a table size where you have lots of very small text/image columns and a large page size as it avoids wasting a full page per row in the text chain.

One enabled it will show against the column in sp_help output. To check whether it's a benefit you need to consider:

  1. Your Sybase dataserver page size (bigger page sizes waste more space for each text chain page)
  2. The average size of data in your text/image column, as the smaller the data the more you will benefit from using in-row LOBs. If all your data is larger than the page size of your dataserver, there will be no benefit as the data will still be stored off-row as it was before the change.

You have to re-load the data into the table for the change to take effect so you can do this via a select into (to create a new copy of the table and data) or via BCP out/in.

There's some more info in the Sybase docs here:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/CHECDADI.htm

In terms of blocking, where the data meets the criteria and is held in-row it would be read from the table itself rather than from the text chain which is effectively a big heap at the end of the table. You also get the benefit (size of data depending) of significant space savings on your table size which reduces IO and thus can help performance.

Rich Campbell
  • 566
  • 2
  • 9
  • In terms of checking for suitability in the first instance you can run something like `select max(datalength()),avg(datalength()) from table_name` to get the max and average size of your text column. If you have a low average compared to your page size then this is a good candidate for in-row lob. If the maximum is much lower than your page size then you might want to check further to see the percentage of rows higher than the average and size the in-row lob setting to get as much data in-row as you can. – Rich Campbell Nov 06 '18 at 12:33
  • 1
    thanks from your help, another question what is the difference between applying in row 500 and in row 1000 ? I understand it takes less then 500 but if even I increased , would be the same affect ? – Moudiz Nov 06 '18 at 12:35
  • 1
    The only difference is rows with > 500 bytes of text/image data would be stored off-row as before with the first setting and > 1000 bytes with the second. This is why checking each table and validating whether it's a good candidate is key. For example I've seen this take a 20GB trade table to 500MB when in-row lob was utilised. This was because 99% of the data in the text column was a single space character because they made the text column not nullable so I set the in-row setting to 100 bytes and this covered 99% of the data easily and saved a ton of 8k pages on the instance. – Rich Campbell Nov 06 '18 at 12:41