用的MSSQL2000 数据表数据是700000(会员发布的供求数据),需要分页并做复杂查询分页方案分二种情况当浏览第一页,分组并读取15条SELECT top 15 a.tradeid From buyorsell a inner join (select max(tradeid) as tradeid from buyorsell where group by publicname ) b on a.tradeid=b.tradeid order by a.vip_typeA , a.PublicDate desc当浏览第二页的时候SELECT top 15 a.TradeID from BuyOrSell a
Where a.TradeID not in( SELECT top 15 lxla.TradeID From BuyOrSell lxla
Where tradeid in(select max(tradeid)as tradeid from buyorsell group by publicname ) order by lxla.vip_typeA , lxla.PublicDate desc) and a.tradeid in(select max(tradeid)as tradeid from buyorsell group by publicname ) order by a.vip_typeA , a.PublicDate desc
最开始在publicdate上建立了聚集索引,order by a.PublicDate desc能走到索引上, 但由改成了order by a.vip_typeA , a.PublicDate desc 就走不到索引上去了,性能下降了很多.大家进来看看,有什么方案解决这种分页问题.条件是按order by a.vip_typeA , a.PublicDate desc排序,并分组读取(一个用户读取一条),一个表里有700000(会员发布的供求数据),(就像阿里巴巴的商机信息一样,每个会员只读取一条,并按时间和VIP会员排序,不知道他的数据分页方案是怎么样的?)
Where a.TradeID not in( SELECT top 15 lxla.TradeID From BuyOrSell lxla
Where tradeid in(select max(tradeid)as tradeid from buyorsell group by publicname ) order by lxla.vip_typeA , lxla.PublicDate desc) and a.tradeid in(select max(tradeid)as tradeid from buyorsell group by publicname ) order by a.vip_typeA , a.PublicDate desc
最开始在publicdate上建立了聚集索引,order by a.PublicDate desc能走到索引上, 但由改成了order by a.vip_typeA , a.PublicDate desc 就走不到索引上去了,性能下降了很多.大家进来看看,有什么方案解决这种分页问题.条件是按order by a.vip_typeA , a.PublicDate desc排序,并分组读取(一个用户读取一条),一个表里有700000(会员发布的供求数据),(就像阿里巴巴的商机信息一样,每个会员只读取一条,并按时间和VIP会员排序,不知道他的数据分页方案是怎么样的?)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货