比较两个sql语句的性能

Helen_px

create table fruits (
    type varchar(10) not null,
    variety varchar(20) not null,
    primary key(type, variety));

insert into fruits values
('apple', 'gala'),
('apple', 'fuji'),
('apple', 'limbertwig'),
('orange', 'valencia'),
('orange', 'navel'),
('pear', 'bradford'),
('pear', 'bartlett'),
('cherry', 'bing'),
('cherry', 'chelan');

第一种:
select l.type, l.variety, count(*) as num
from fruits as l
left outer join fruits as r
    on l.type = r.type
    and l.variety >= r.variety
group by l.type, l.variety;

第二种:
select l.type,l.variety,(select count(*) from fruits r where  
                        r.type = l.type and r.variety <= l.variety) num
from fruits l

第三种:
oracle的查询语句
select l.type,l.variety,ROW_NUMBER() OVER(PARTITION BY l.type ORDER BY  l.variety) NUM
from fruits l
由于oracle8i标准版不支持分析函数,改写成前面两种语句后,哪个的性能会快点!第二种解法属于查询语句的那种查询?

[ 本帖最后由 Helen_px 于 2007-11-14 17:22 编辑 ]

derekzhang

第一种方法比第二种方法更加快速，节省查询时间，而且性能比较优越。

[ 本帖最后由 derekzhang 于 2007-11-20 16:28 编辑 ]

puchonghui

第一个效率高
子查询会大幅降低效率。。。

supergirlly1984

第一种方法运行比第二种方法快，子查询要慢，下面是我的测试sql语句：
第一种sql语句：
select getdate()
declare @i int
set @i = 1;
while(@i < 71)
        begin
                select l.[type], l.variety, count(*) as num
                from fruits as l
                left outer join fruits as r
                        on l.[type] = r.[type]
                        and l.variety >= r.variety
                group by l.[type], l.variety;
                set @i = @i + 1;
        end
select getdate()
执行70次的起始时间和结束时间分别是：2007-11-18 23:36:48.623 和2007-11-18 23:36:49.700，差值为1077ms

第二种sql语句：
select getdate()
declare @j int
set @j = 1;
while(@j < 71)
        begin
                select l.type,l.variety,(select count(*) from fruits r where  
                r.type = l.type and r.variety <= l.variety) num
                from fruits l
                set @j = @j + 1;
        end
select getdate()
执行70次的起始时间和结束时间分别是：2007-11-18 23:36:07.140和2007-11-18 23:36:08.263，差值为1123ms

有时我们发现，会有不同的运行结果，但通过大量的实验，证明子查询会降低效率，总体来说，第一种方法比第二种方法要快

有不对的地方请大家指教

rxlnancy

楼上的真厉害

derekzhang

看来你是个数据库高手，你怎么测试出来差值的呀！！

supergirlly1984

在语句执行器前后分别用select getdate()获取当前时间，用执行后的时间与执行前的时间相减即可

mrxangel1986

用select getdate()这个语句，，，，高。。。

hupose

其实在SQL查询器里面是有官方的测试工具的,可以测试到所有语句的用时,用的资源等等
建议去试试
这样拿出来的数据会更准确

grubbysong2

太强了。特别是4楼的。。。

bob.wu

还是四楼彪悍，佩服，

Helen_px

谢谢你们的回复，早已解决了