Hints to speed-up select into statement

sybase version: Adaptive Server Enterprise/15.0.3/EBF 17686 ESD#1.1 RELSE/P/Sun_svr4/OS 5.8/ase1503/2681/64-bit/FBO/Thu Aug 20 14:20:57 2009

Server page size is 4K

--

Hi all,

I'm looking at any hints that could speed up some 'select into' statements.

While upgrading our software, the datamodel is changed through 'select into' statements. Basically what we're doing is:

• select ... into NEW_TABLE_A lock datarows from TABLE_A

for some big tables the operation is long because of the size of the table. For example, I have a table with about 17 millions rows and the select into lasted 40 minutes. Looking at some metrics gathered through ASEMON I had the following:

• Phys reads: 339 973
• Logic reads: 22 466 937
• Rows affected: 17 244 487

The showplan looks not too bad for me (no defered updates, using large IO)

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is CREATE TABLE.

STEP 2
The type of query is INSERT.

2 operator(s) under root

|ROOT:EMIT Operator (VA = 2)
|
| |INSERT Operator (VA = 1)
| | The update mode is direct.
| |
| | |SCAN Operator (VA = 0)
| | | FROM TABLE
| | | DB..TABLE_A
| | | Table Scan.
| | | Forward Scan.
| | | Positioning at start of table.
| | | Using I/O Size 32 Kbytes for data pages.
| | | With MRU Buffer Replacement Strategy for data pages.
| |
| | TO TABLE
| | NEW_TABLE_A
| | Using I/O Size 32 Kbytes for data pages.

My assumption is that there's nothing I could do at Sybase level to speed-up the execution time.

Do you agree or anybody has any hints?

Thanks

Simon