0

I am inserting some records(~10k) into a temporary table using the insert into cluase. I prepared a select which will pick the indexes and perform better. But when i use the same select with "Insert into cluase" then it results in a table scan .

my query looks like this

Insert into tmpio..table
select top 10000 Column_names
from Table
where <criteria>

if i check the query plan only for the SELECT query i can see that it picks the index,but for the entire query which inclused "INSERT INTO" it doesn't pick any index.

Is this behaviour normal? Are indexes of no use when you are selecting data from one table and inserting it directly into the other table?

These are my assumptions prior to writing the query.

  1. Target table should not have any indexes to improve performance.
  2. The source table can have indexes and we can use them.
Prashant
  • 11
  • 3
  • Could you use the planviewer of InteractiveSQL (Menu Tools-> Plan viewer) to see if the graphical query plan show the indexes being used? – Vince Jan 02 '14 at 19:57
  • Hi Vince, I tried using Plan Viewer but i thnk it works only for plain "Select" queries. It can't parse and gets XML error if we have Insert cluase somewhere in query. – Prashant Jan 06 '14 at 07:39

1 Answers1

0

Looks like Sybase doesen´t match the index in some way. It could be the "top 10000" that mess things up.

If you know what index to use you could force sybase to use it by entering:

select top 10000 Column_names from Table (index index_name)

Aside from your solution, you maybe should consider to use the "select * into" approach instead. It´s faster and more effective since it doesen´t use the transaction log.

select top 10000 Column_names into tmpio..table
from Table
where <criteria>

Ps.

Attention: when using select top N the query is still executed fully, just the data page reads stop after the specified number of rows is affected.

carleson
  • 728
  • 1
  • 5
  • 14
  • Thanks Carleson "select * into" approach did speed up things compared to "Insert into" approach. But i was just wondering why "insert into" is pretty slow. Also did you think my assumptions mentioned above are correct? – Prashant Jan 07 '14 at 12:52