看看我们网站首页的存储过程,不知道为什么首页访问速度这么慢.
0
悬赏园豆：20
[已关闭问题]
CREATE PROCEDURE Getdefaultinfo<br><br> AS <br><br>/*顶部交换技能*/<br>select top 30 FTID,FuserID,dbo.TurnUserName(FuserID) as userName,FShi,FQu,substring(FTtitle,40,50) as title,FTechKey,FPClassID,dbo.TurnClassName(FPClassID) as PClassName,FBClassID,dbo.TurnClassName(FBClassID) as BClassName,dbo.FormatFtime(FcreateTime) as createTime from T_Techtory where FTtype=1 and FmoneyArea in (1,3) order by FcreateTime desc <br><br>/*顶部交易技能*/<br>select top 30 d.FUserLoginName,a.FTID,substring(a.FTtitle,40,50) title,a.FPClassID,a.FBClassID,b.FclassName fpc,a.FuserID,c.FclassName fbc,FShi,FQu,dbo.FormatFtime(a.FcreateTime) createTime from T_Techtory a left join T_ClassType b on a.FPClassID=b.FClassID left join T_ClassType c on a.FBClassID=c.FClassID left join T_UserInfo d on a.FuserID=d.FuserID where a.FTtype=1 and a.FmoneyArea in(2,3) order by a.FcreateTime desc <br><br>/*最新需求*/<br>select top 24 ftid, fttitle,Fmoney from t_techtory where FTtype='2' and Fispublic='0'and FStatus='0' and fdatetime >getdate() order by fcreatetime desc<br><br>/*热门需求*/<br>select top 24 ftid, fttitle,Fmoney from t_techtory where FTtype='2' and Fispublic='0'and FStatus='0' and fdatetime >getdate() order by FTechCredit desc<br><br>/*推荐交换*/<br>select top 16 ftid, fttitle,FtechKey,FuserID,dbo.TurnUserName(FuserID) as userName from t_techtory where FTtype=1 and Frecommend>0 and FmoneyArea in(1,3) and fdatetime >getdate() and Fispublic='0'and FStatus='0' and FmoneyArea in(1,3) order by fcreatetime desc<br><br>/*推荐交易*/<br>select top 24 ftid,fttitle,fuserid from t_techtory where fttype=1 and Frecommend>0 and FmoneyArea in(2,3) and fdatetime >getdate() order by fcreatetime desc<br><br>/*热门交换*/<br>select top 16 ftid, fttitle,FtechKey,FuserID,dbo.TurnUserName(FuserID) as userName from t_techtory where FTtype=1 and fdatetime >getdate() and Fispublic='0'and FStatus='0' and FmoneyArea in(1,3) order by fskillhit desc<br><br>/*读取社区图片*/<br>select top 2 title,imgurl,link from usernews order by [time] desc<br><br>/*资讯频道图片*/<br>select top 2 FnewsID, Ftitle,FhtmlUrl from T_News where FType='N00002' and FisTopNews=1 order by fregtime desc<br><br>/*资讯频道内容*/<br>select top 12 FnewsID, Ftitle from T_News where FType='N00002' and FisTopNews<>1 order by fregtime desc<br><br>/*综合频道图片*/<br>select top 2 FnewsID, Ftitle,FhtmlUrl from T_News where FType='N00003' and FisTopNews=1 order by fregtime desc<br><br>/*综合频道内容*/<br>select top 12 FnewsID, Ftitle from T_News where FType='N00003' and FisTopNews<>1 order by fregtime desc<br><br>/*申请友情链接*/<br>select * from t_link order by displayorder<br>GO
其他回答(6)
0
都是select 和交叉链，首页缓存了没有？用缓存就不用每次都读数据库了，语句应该能再精简一些，一些地方可以用ajax来处理。另外可以用框架来处理一些地方
0
定时生成首页的静态页，
如果实时性和交互性不是那么强的说。
0
慢不一定是程序执行效率的问题，不过看你这个首页，用缓存比较好一些，甚至可以把HTML直接缓存起来定时更新
如果有缓存，SQL的效率显得不是“特别”重要了，不然，要好好优化SQL语句，优化索引等数据库元素，这是一项系统工程啊
/*顶部交易技能*/这个怕是会有问题，left join一不小心就会成为性能杀手
另外，看你where后面一堆的条件，不大可能快起来，一般首页推荐之类的东西其实不太要求实时更新，所以可以考虑在数据库中建立冗余表，然后跑作业进行定时更新，首页显示时直接从冗余表里取信息，会快很多，程序也好写
0
还是用数据缓存一下比较好
但也要看到底是多慢，打开页面慢 不一定都是还数据库操作的问题。
0
总的来说你还是好好在索引下功夫，如条件字段还有排序字段，按不同的要求建立聚集/非聚集，单一或者复合索引~~
0
1.先把查询字段建立一下索引.
2.如果还是不行,你只有生成静态页面了.
0
在一个存储过程中取出这么多数据？
这些数据的缓存时间一样吗？建议把这个存储过程拆开，缓存时间一样的放在一个存储过程中，对数据做缓存。