sql执行效率

Question

select
        top 20  b.housesysid,
        house.houseID,
        loupanName,
        (house.buildingNum+'栋'+house.unit+'单元'+house.doorNum+'室') as houseAddress,
        house.houseArea,
        house.feeStandard,
        house.ownerName,
        b.payablemonth,
        b.ArrearMonth,
        b.PayFeeMonth,
        house.houseState,
        house.startPayMonth,
        b.otherMonth,
        house.beforeArrearMoney,
        sumpayableMoney,
        sumalreadyMoney,
        (select
            fdataContent 
        from
            Tc_datadic 
        where
            fclass='1104' 
            and fdataid=house.checkInState) as checInState,
        (select
            houseTypeName 
        from
            houseType 
        where
            houseType.houseTypeSysID=house.houseType) as houseType   
    from
        ( select
            housesysid,
            sum(payableMoney) as sumpayableMoney,
            sum(alreadyMoney) as sumalreadyMoney,
            count(*) as payablemonth,
            sum(case 
                when mouthState='缴讫' then 1  
                else 0 
            end) as PayFeeMonth,
            sum(case 
                when mouthState='欠缴' then 1 
                else 0 
            end) as ArrearMonth,
            sum(case 
                when mouthState='其它' then 1 
                else 0 
            end) as otherMonth 
        from
            (select
                TYearMonth.YearMonth,
                house.startPayMonth,
                isnull( houseMouthState.mouthState,
                '欠缴') as mouthState,
                isnull(houseMouthState.alreadyMoney,
                0.00) as alreadyMoney,
                case 
                    when houseMouthState.payableMoney is not null then houseMouthState.payableMoney 
                    else isnull(house.feeStandard,
                    0.00) 
                end as payableMoney,
                house.housesysid  
            from
                house 
            join
                TYearMonth  
                    on   TYearMonth.YearMonth  >= case 
                        when house.startPayMonth='210001' then '201001' 
                        else house.startPayMonth 
                    end  
                    and TYearMonth.YearMonth <='201012' 
            left join
                houseMouthState 
                    on houseMouthState.housesysid=house.housesysid  
                    and houseMouthState.YearMouth=TYearMonth.YearMonth 
            where
                1=1  )a 
        group by
            housesysid) b ,
            house,
            loupan 
        where
            house.housesysid=b.housesysid  
            and loupan.loupansysid=house.loupansysid 
        order by
            space(10-len(buildingNum))+buildingNum asc,
            space(10-len(house.unit))+house.unit asc,
            space(10-len(house.doorNum))+house.doorNum asc上面的语句执行了1分多钟，但是如果去掉 order by 语句的话 就只用大概2秒 这到底为什么？怎么优化啊？

Answer 1

order by
            space(10-len(buildingNum))+buildingNum asc,
            space(10-len(house.unit))+house.unit asc,
            space(10-len(house.doorNum))+house.doorNum asc这句耗时太长,
可能原因为:
1.记录过多.
2.order by 子句需要进行复杂的计算.

Answer 2

order by 内容需要进行运算  

Answer 3

表中有索引的吧~，SQL SERVER自带的

Answer 4

order by
  space(10-len(buildingNum))+buildingNum asc,
  space(10-len(house.unit))+house.unit asc,
  space(10-len(house.doorNum))+house.doorNum asc的排序如果都是数字，建议改为bigint排序
order by 
10000000000+convert(bigint,buildingNum) asc,
10000000000+convert(bigint,house.unit) asc,
10000000000+convert(hhouse.doornum) asc
这样的形式，看看会不会快一些

Answer 5

你的order by需要计算
所以很慢

Answer 6

order by
            space(10-len(buildingNum))+buildingNum asc,
            space(10-len(house.unit))+house.unit asc,
            space(10-len(house.doorNum))+house.doorNum asc
...排序进行了这么复杂的计算 肯定快不了...试试把这个逻辑放入临时表 链接 排序..加上索引

Answer 7

要想速度快，就把子查询insert 到临时表中，再与临时表left join.速度会快几倍。

Answer 8

把结果集用视图来表示 order by的值也作为字段