Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

Tags:
Former Member
Not what you were looking for? View more on this topic or Ask a question