SQL优化

misswangjinfeng 2011-02-18 09:19:35
如果有数万条记录,那这个SQL语句要如何进行优化呢 ?

select * from
(select OBJID,FMTTITLE,TITLE as TITLE,TITLEPRE from IMDS_PAOTITLE
where(TITLE like 'A%' or dbo.fun_getPY(TITLE) like 'A%')) a
left join
(select oimg.OBJID,'<a href=''javascript:entreeDownloadAttachment("'+att.id+'")''><img border=0 src="'+att.path+'thn_'+att.id+'"/><div>'+att.name+'('+convert(varchar(8),att.size)+'k)</div></a>' as attobj
from IMDS_PAOIMG oimg left join _en_attachobject att on oimg.attachment=att.pid
where oimg.defaultimg='Y')img on a.OBJID=img.OBJID where a.titlepre='PREFER'
order by a.FMTTITLE

急啊!!
...全文
69 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
kevin_long 2011-02-18
select * *转换成你需要查询出的具体字段 这样效率会高一些
回复
renwenli07461 2011-02-18
select * from
(
select OBJID,FMTTITLE,TITLE,TITLEPRE,oimg.OBJID,'<a href=''javascript:entreeDownloadAttachment("'+att.id+'")''><img border=0 src="'+att.path+'thn_'+att.id+'"/><div>'+att.name+'('+convert(varchar(8),att.size)+'k)</div></a>' as attobj
from IMDS_PAOTITLE a
left join IMDS_PAOIMG oimg on a.OBJID=oimg.OBJID and a.titlepre='PREFER' and oimg.defaultimg='Y'
left join _en_attachobject att on oimg.attachment=att.pid

where (a.TITLE like 'A%' or dbo.fun_getPY(TITLE) like 'A%')
) b
回复

select * from --*写成具体字段
(select OBJID,FMTTITLE,TITLE as TITLE,TITLEPRE from IMDS_PAOTITLE --TITLE as TITLE 多此一举
where(TITLE like 'A%' or dbo.fun_getPY(TITLE) like 'A%')) a
--左边一个字符是A的话用 left(TITLE,1) = 'A' left(dbo.fun_getPY(TITLE),1) = 'A' 好点
left join
(select oimg.OBJID,'<a href=''javascript:entreeDownloadAttachment("'+att.id+'")''><img border=0 src="'
+att.path+'thn_'+att.id+'"/><div>'+att.name+'('+convert(varchar(8),att.size)+'k)</div></a>' as attobj
from IMDS_PAOIMG oimg left join _en_attachobject att on oimg.attachment=att.pid
where oimg.defaultimg='Y')img on a.OBJID=img.OBJID where a.titlepre='PREFER'
order by a.FMTTITLE
回复
misswangjinfeng 2011-02-18
我晕咧,你咋不帮忙想办法哦! 尽港废话!是不是应该创建一个索引呢 ?
回复
guoyanhong1111 2011-02-18
哈哈!猴子知道急 啊!
回复
Godsaidlwq 2011-02-18
select a.objid,a.fmttitle,a.title,a.imds_PAOTITLE,IMG.OBJID,IMG.attobj
from
(select OBJID,FMTTITLE,TITLE as TITLE,TITLEPRE from IMDS_PAOTITLE
where titlepre='PREFER' and (TITLE like 'A%' or dbo.fun_getPY(TITLE) like 'A%')) a
left join
(select oimg.OBJID
,'<a href=''javascript:entreeDownloadAttachment("'+att.id+'")''><img border=0 src="'+att.path+'thn_'+att.id+'"/><div>'+att.name+'('+convert(varchar(8),att.size)+'k)</div></a>' as attobj
from (select objid,attachment from IMDS_PAOIMG where defaultimg='Y') oimg left join _en_attachobject att on oimg.attachment=att.pid
)img on a.OBJID=img.OBJID
order by a.FMTTITLE

以索引级别高的条件为最先判断条件,如果可以的话,在返回结果中字段排列以所占有字节数小的为最先,试试看
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2011-02-18 09:19
社区公告
暂无公告