title: Sybase ASE - Performance Select Into Vs Insert Select
body: <p>I have seen that the performance of a select into is better than a insert/select combination as it is un-logged. But today I encountered a different scenario. I am doing a select into from a physical table into a temp table and it inserts 2.4M records in 410 seconds, but when I split this into a select into statement to create the table first and then insert the data using a insert/select the same query clicks 2.4M records in 10.6 seconds.</p>
  
  <p>I am confused, what can be the reason?</p>
  
  <pre><code>Query 1
  =======
  select a, b, c
  into #A
  from table1
  where a = 'XYZ' 
  and c = 'ABC'
  </code></pre>
  
  <p>2.4M records inserted in 410 seconds</p>
  
  <pre><code>Query 2
  =======
  select a, b, c
  into #A
  from table1
  where 1=2
  
  insert #A
  select a, b, c
  from table1
  where a = 'XYZ' 
  and c = 'ABC'
  </code></pre>
  
  <p>2.4M records inserted in 10.6 seconds</p>
  
  <h2>After comment from Jean</h2>
  
  <p>Now its acting strange. If I execute query 1 first and then query 2, then the results are as above. But if I switch the order, query 1 runs in ~9 secs and query 2 in ~7 secs. </p>
  
  <pre><code>Plans
  </code></pre>
  
  <p><strong>Query 1</strong></p>
  
  <pre><code>-------
  ""
  QUERY PLAN FOR STATEMENT 1 (at line 1).
  ""
  ""
  STEP 1
  The type of query is DECLARE.
  ""
  ""
  QUERY PLAN FOR STATEMENT 2 (at line 2).
  ""
  ""
  STEP 1
  The type of query is SELECT.
  ""
  1 operator(s) under root
  ""
  |ROOT:EMIT Operator (VA = 1)
  |
  |   |SCALAR Operator (VA = 0)
  ""
  ""
  ""
  QUERY PLAN FOR STATEMENT 3 (at line 3).
  ""
  ""
  STEP 1
  The type of query is CREATE TABLE.
  ""
  STEP 2
  The type of query is INSERT.
  ""
  5 operator(s) under root
  ""
  |ROOT:EMIT Operator (VA = 5)
  |
  |   |INSERT Operator (VA = 4)
  |   |  The update mode is direct.
  |   |
  |   |   |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
  |   |   |
  |   |   |   |SCAN Operator (VA = 0)
  |   |   |   |  FROM OR List
  |   |   |   |  OR List has up to 3 rows of OR/IN values.
  |   |   |
  |   |   |   |RESTRICT Operator (VA = 2)(0)(0)(0)(10)(0)
  |   |   |   |
  |   |   |   |   |SCAN Operator (VA = 1)
  |   |   |   |   |  FROM TABLE
  |   |   |   |   |  ABCD.dbo.ITR
  |   |   |   |   |  a
  |   |   |   |   |  Using Clustered Index.
  |   |   |   |   |  Index : ITR_typ_imnt_cl_optn
  |   |   |   |   |  Forward Scan.
  |   |   |   |   |  Positioning by key.
  |   |   |   |   |  Keys are:
  |   |   |   |   |    id_typ_imnt ASC
  |   |   |   |   |  Using I/O Size 16 Kbytes for data pages.
  |   |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
  |   |
  |   |  TO TABLE
  |   |  #A
  |   |  Using I/O Size 16 Kbytes for data pages.
  ""
  ""
  ""
  QUERY PLAN FOR STATEMENT 4 (at line 9).
  ""
  ""
  STEP 1
  The type of query is SELECT.
  ""
  1 operator(s) under root
  ""
  |ROOT:EMIT Operator (VA = 1)
  |
  |   |SCALAR Operator (VA = 0)
  ""
  ""
  Parse and Compile Time 0.
  Adaptive Server cpu time: 0 ms.
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.
  Table: #A scan count 0, logical reads: (regular=2452707 apf=0 total=2452707), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
  Table: ABCD.dbo.ITR (a) scan count 3, logical reads: (regular=581980 apf=0 total=581980), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
  Execution Time 75.
  Adaptive Server cpu time: 7500 ms.  Adaptive Server elapsed time: 7546 ms.
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.
  </code></pre>
  
  <p><strong>Query 2</strong></p>
  
  <pre><code>-------
  ""
  QUERY PLAN FOR STATEMENT 1 (at line 1).
  ""
  ""
  STEP 1
  The type of query is DECLARE.
  ""
  ""
  QUERY PLAN FOR STATEMENT 2 (at line 2).
  ""
  ""
  STEP 1
  The type of query is SELECT.
  ""
  1 operator(s) under root
  ""
  |ROOT:EMIT Operator (VA = 1)
  |
  |   |SCALAR Operator (VA = 0)
  ""
  ""
  ""
  QUERY PLAN FOR STATEMENT 3 (at line 3).
  ""
  ""
  STEP 1
  The type of query is CREATE TABLE.
  ""
  STEP 2
  The type of query is INSERT.
  ""
  3 operator(s) under root
  ""
  |ROOT:EMIT Operator (VA = 3)
  |
  |   |INSERT Operator (VA = 2)
  |   |  The update mode is direct.
  |   |
  |   |   |RESTRICT Operator (VA = 1)(4)(0)(0)(0)(0)
  |   |   |
  |   |   |   |SCAN Operator (VA = 0)
  |   |   |   |  FROM TABLE
  |   |   |   |  CERD_CORPORATE..INSTRUMENT
  |   |   |   |  Table Scan.
  |   |   |   |  Forward Scan.
  |   |   |   |  Positioning at start of table.
  |   |   |   |  Using I/O Size 2 Kbytes for data pages.
  |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
  |   |
  |   |  TO TABLE
  |   |  #A
  |   |  Using I/O Size 16 Kbytes for data pages.
  ""
  ""
  ""
  QUERY PLAN FOR STATEMENT 4 (at line 5).
  ""
  ""
  STEP 1
  The type of query is INSERT.
  ""
  5 operator(s) under root
  ""
  |ROOT:EMIT Operator (VA = 5)
  |
  |   |INSERT Operator (VA = 4)
  |   |  The update mode is direct.
  |   |
  |   |   |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
  |   |   |
  |   |   |   |SCAN Operator (VA = 0)
  |   |   |   |  FROM OR List
  |   |   |   |  OR List has up to 3 rows of OR/IN values.
  |   |   |
  |   |   |   |RESTRICT Operator (VA = 2)(0)(0)(0)(10)(0)
  |   |   |   |
  |   |   |   |   |SCAN Operator (VA = 1)
  |   |   |   |   |  FROM TABLE
  |   |   |   |   |  CERD_CORPORATE.dbo.INSTRUMENT
  |   |   |   |   |  a
  |   |   |   |   |  Using Clustered Index.
  |   |   |   |   |  Index : INSTRUMENT_typ_imnt_cl_optn
  |   |   |   |   |  Forward Scan.
  |   |   |   |   |  Positioning by key.
  |   |   |   |   |  Keys are:
  |   |   |   |   |    id_typ_imnt ASC
  |   |   |   |   |  Using I/O Size 16 Kbytes for data pages.
  |   |   |   |   |  With LRU Buffer Replacement Strategy for data pages.
  |   |
  |   |  TO TABLE
  |   |  #A
  |   |  Using I/O Size 2 Kbytes for data pages.
  ""
  ""
  ""
  QUERY PLAN FOR STATEMENT 5 (at line 11).
  ""
  ""
  STEP 1
  The type of query is SELECT.
  ""
  1 operator(s) under root
  ""
  |ROOT:EMIT Operator (VA = 1)
  |
  |   |SCALAR Operator (VA = 0)
  ""
  ""
  Parse and Compile Time 0.
  Adaptive Server cpu time: 0 ms.
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.
  Table: #A scan count 0, logical reads: (regular=1 apf=0 total=1), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
  Table: CERD_CORPORATE..INSTRUMENT scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.
  Table: #A scan count 0, logical reads: (regular=2493221 apf=0 total=2493221), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
  Table: CERD_CORPORATE.dbo.INSTRUMENT (a) scan count 3, logical reads: (regular=581980 apf=0 total=581980), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
  Execution Time 96.
  Adaptive Server cpu time: 9600 ms.  Adaptive Server elapsed time: 9580 ms.
  Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 0 ms.
  </code></pre>