Hi,
For below mentioned scenario can any one please advice if this is a right approach or still there is a cleaner way to tune the query
The scenario is as follows:

I have a query in one of the existing proc which is of kind
insert into #table
select distinct <70 columns>
from <join on 7 tables which have data in millions>

create a nonclustered index on the above mentioned #table


I am planning to convert this query into

set table count 7
select * into #table
<70 columns>
from <join on 7 tables which have data in millions>


create clustered index on #table with ignore_dup_row--to get distinct rows

Thanking in advance for time


Cheers!!!!
Prasad

Changing is insert into to select * into is the rite step. Make sure all the 7 tables have index in them and indexes are being used by the sybase optimizer. You can do this this by set showplan on.

Also, I would suggest you to break down your query in 2 pieces. Create 1 temp table using 3/4 tables and then create final table by joining temp tables previously created and remaining of 7 tables. You can create clustered index on the intermediate temp table too.

If trying to speed things up, why in the world would you insert to a temp table??? Work should be done on the query to alter the problem that is slowing things down so much. As the prior poster already suggested look at the plan and see how the tables are being accessed. If still continuing to have issues, post the SQL and we may be able to offer some suggestions.
Dave