Optimizing a very slow select max group by query on Sybase ASE 15.5

Question

I have a very simple query on a table with 60 million rows :

select id, max(version) from mytable group by id

It returns 6 million records and takes more than one hour to run. I just need to run it once because I am transferring the records to another new table that I keep updated.

I tried a few things that didn't work for me but that are often suggested here on stackoverflow:

1. inner query with select top 1 / order by desc: it is not supported in Sybase ASE
2. left outer join where a.version < b.version and b.version is null: I interrupted the query after more than one hour and barely a hundred thousand records were found

I understand that Sybase has to do a full scan.

Why could the full scan be so slow?

Is the slowness due to the Sybase ASE instance itself or specific to the query?

What are my options to reduce the running time of the query?

Answer 1

So finally the nonclustered index on (id, version desc) did the trick without having to change anything to the query. Index creation also takes one hour and the query responds in few seconds. But I guess it's still better than having another table that could cause data integrity issues.