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
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).
The type of query is CREATE TABLE.
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?