Performance Penatly using Partitions

Hi,

deleting 6500 rows from a partitioned table took 3-4 times longer if the table has no partitions.

ASE 15.5. ESD 5.3 on Solaris 64bit. Sample MonProcessActivity output for unpartitioned Table:

CPUTime WaitTime PhysicalReads LogicalReads PagesRead PhysicalWrites PagesWritten MemUsageKB LocksHeld TableAccesses IndexAccesses TempDbObjects WorkTables ULCBytesWritten ULCFlushes ULCFlushFull ULCMaxUsage ULCCurrentUsage Transactions Commits Rollbacks

----------- ----------- ------------- ------------ ----------- -------------- ------------ ----------- ----------- ------------- ------------- ------------- ----------- --------------- ----------- ------------ ----------- --------------- ------------ ----------- -----------

18100 17400 0 446147 0 6808 6808 162 0 275093 19742 0 7 4464192 6550 0 2024 0 6546 6546 0

Partitioned Table

CPUTime WaitTime PhysicalReads LogicalReads PagesRead PhysicalWrites PagesWritten MemUsageKB LocksHeld TableAccesses IndexAccesses TempDbObjects WorkTables ULCBytesWritten ULCFlushes ULCFlushFull ULCMaxUsage ULCCurrentUsage Transactions Commits Rollbacks

----------- ----------- ------------- ------------ ----------- -------------- ------------ ----------- ----------- ------------- ------------- ------------- ----------- --------------- ----------- ------------ ----------- --------------- ------------ ----------- -----------

87600 23400 0 603242 0 6814 6814 162 0 432195 19742 0 7 4463632 6561 0 2024 0 6546 6546 0

Showplan for the partitioned table:

QUERY PLAN FOR STATEMENT 1 (at line 1).

Optimized using Parallel Mode

STEP 1

The type of query is DELETE.

2 operator(s) under root

|ROOT:EMIT Operator (VA = 2)

|

| |DELETE Operator (VA = 1)

| | The update mode is direct.

| |

| | |SCAN Operator (VA = 0)

| | | FROM TABLE

| | | prognosen3_part

| | | [ Eliminated Partitions : 1 2 3 4 6 ]

| | | Index : prognosen3_aglsec

| | | Forward Scan.

| | | Positioning by key.

| | | Keys are:

| | | aglsec ASC

| | | statnr ASC

| | | modprog ASC

| | | datumsec ASC

| | | dauersec ASC

| | | Using I/O Size 16 Kbytes for index leaf pages.

| | | With LRU Buffer Replacement Strategy for index leaf pages.

| | | Using I/O Size 16 Kbytes for data pages.

| | | With LRU Buffer Replacement Strategy for data pages.

| |

| | TO TABLE

| | prognosen3_part

| | Using I/O Size 16 Kbytes for data pages.

Showplan for the non-partitioned table is equal with exception of the partion elimination line [ Eliminated Partitions : 1 2 3 4 6 ]. I did a similar repro on 15.5 ESD#5.3 on Linux x64 which shows no performance penalty. What is a possible reason for this behavior?

Many thanks

Robert