SQLServer beats 8i finding top-10 duplicates

From: Scott Narveson <psnarv_at_visi.com>
Date: Wed, 05 Jan 2000 19:15:12 GMT
Message-ID: <4LMc4.1100$v31.120505@ptah.visi.com>

Background:

    Given the following table:

        create table testtable (num number(9));

    Note: no index present.

Problem:

    Insert 1 million random numbers with value from 1 to 100,000.     Write a query that finds the top 10 most duplicated numbers.

Oracle 8i:

    select * from (

        select count(num) from testtable
        group by num order by count(num) desc)

    where rownum < 10;

    Time: ~ 3 minutes

SQLServer 7:

    [After creating a similar table in its syntax...]

    select top 10 count(num) from testtable

        group by num order by count(num)

    Time: 9 seconds

Is this sort of operation just a sweet spot for SQLServer? Can I get Oracle to match SQLServer performance (adding indexes is ok, raw performance is the hope)? Any stupid mistakes in my Oracle attempt...?

Thanks for any insight anyone can offer.

• Scott Narveson

Received on Wed Jan 05 2000 - 13:15:12 CST