Please help. I have a SELECT INTO that runs in 25 minutes - way too long for our biz requirements. But a very similar select for another
situation runs in 73 seconds. See below.


PLATFORM
--------------------------------------------------------
Sybase on Windows NT 2000: 4 CPUs, 4 GIGS memory, very fast
controller and disk.

FIRST SCENARIO - 73 seconds
--------------------------------------------------------
I have the following:

SELECT *
INTO tempdb..newtable
FROM table1 a(index table1_index), /* unique idx. 2.2 million rows */
table2 b(index table2_index) /* unique idx. 3.3 million rows */

WHERE a.joincol=b.joincol

SECOND SCENARIO - 25 minutes
---------------------------------------------------------
Select is exactly the same, but the index on table 3's join col is not unique.

Row counts are nearly the same.

SELECT *
INTO tempdb..newtable
FROM table3 c(index table3_index), /* non uniq idx. 2.5 million rows */
table4 d(index table4_index) /* unique idx. 3.7 million rows */

WHERE c.keycol=d.keycol


RELEVANT FACTS
---------------------------------------
table 3 has 2.5 million rows in total - 2.2 distinct values of c.keycol.

IO BYTES READ/WRITTEN seen under WINDOWS TASK MANAGER:

READ: 16,474,866,127 WRITTEN: 784,321,442


In the FIRST SCENARIO the TASK MANAGER BYTES READ is far
closer to the BYTES WRITTEN (750 megs).

Why is my process reading so much data - the table itself is only
about 700 megs big - I don't have any table in my SELECT that's
anywhere close to 16 gigs.

To me, this looks like a table scan is happening.

Any insights/suggestions would be greatly appreciated.

Many Thanks,

Isaac