poor performance with APL tables - sybase ASE

[suznCB]

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

[former_member188958 · Accepted solution]

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).

[suznCB · Accepted solution]

Dear Bret, thank you very much

you meant that I can change lock scheme between data rows and data pages without any impact on performance, but it is not good between APL and DOL, right ?

so what can I do for the performance issue, drop the APL table's indexes then recreate them, and is there any downside related to this operation, Or would you recommend me something else that can enhances the performance?

[former_member188958 · Accepted solution]

Changing lock schemes between datarows and datapages involves just a few simple changes in the system catalogs, while changing between APL and either of the DOL schemes involves copying the entire table to new pages using a new row format. (datarows and datapages use the same row format). So datarows to datapages is a very quick operation. It can have a considerable effect on the performance of other queries, though.

The downsides of rebuilding indexes is it takes time and free disk space. Note that if the APL table has a clustered index, it is enough to rebuild the clustered index as this automatically rebuilds all the nonclustered indexes as well (it has to because the location of all the rows has changed).

Rebuilding the indexes for APL tables makes sense if you have determined that fragmentation is a problem in the table. Sometimes all that is really needed is updating statistics.

[suznCB · Accepted solution]

Thank you very much for explanation

kindly note that we have installed sybase ASE 15.7, but I cannot run reorg on a table which uses allpages locking although I read that it is enabled for 15.0 and later versions.

is this right?

or I have to drop the clustered index then recreate it after knowing its DDL.

thanks in advance

[former_member188958 · Accepted solution]

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.