Re: SQLServer beats 8i finding top-10 duplicates

To me, this is comparing apples to oranges. In your Oracle query, you are doing two select statments whereas the SQLServer query only has one. The SQLServer query utilitizes a function that is not available in Oracle. If I create a system that has functionality not found in other systems and then take advantage of that functionality, is it surprising that this system will perform better than the systems that lack that functionality? I think not. I could have asked the question, "which is more stable, Oracle on Unix or SQLServer on NT"?

HTH,
Brian

Scott Narveson wrote:
>
> 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 Fri Jan 07 2000 - 10:32:09 CST