Skip to Content

poor performance with APL tables - sybase ASE

Dears, kindly help in this case:

we faced a poor performance in our system which connect to Sybase ASE 15.7 database,

the system consists of some applications,

one of those application uses 5 tables, four of them have allpages locking scheme and the last one uses data rows locking schem, for this one we do indexes maintenance weekly where we issue: reorg rebuild tbl_name index_name when sput < 0.75 and ipcr < 0.95

but queries in the appliocation still slow, what can I do with the first four tables, is it efficient to alter table to data-rows locking then rebuild its indexes then return the locking scheme to allpages??

or I've to drop and recreate the indexes? here what are the downsides related to this operation?

in both scenarios I will implement in off-work hours at midnight

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Oct 26, 2017 at 12:23 PM

    On APL tables, rebuilding a clustered index is roughly equivalent to performing a REORG REBUILD - all the data is moved and indexes are rebuilt. Converting to DOL and then back to APL is not efficient, it causes the data to be moved twice and manually rebuilding the indexes in the between the two operations is wasted time as the indexes are all rebuilt by the change in lock scheme anyway where going between APL and DOL in either direction (but not when going between data rows and data pages).

    Add comment
    10|10000 characters needed characters exceeded

    • What happens when you try to run reorg rebuild on an APL table? I do expect REORG REBUILD to work (other flavors of REORG do not work on APL).

      Example from a 16.0 SP03 server:

      1> create table u (x int) lock allpages
      2> go
      1> reorg rebuild u
      2> go
      Beginning REORG REBUILD of table 'u'.
      (0 rows affected)
      REORG REBUILD of table 'u' completed.