SQL优化

[misswangjinfeng]

如果有数万条记录，那这个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

急啊!!

[Godsaidlwq]

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

以索引级别高的条件为最先判断条件，如果可以的话，在返回结果中字段排列以所占有字节数小的为最先，试试看

[guoyanhong1111]

哈哈！猴子知道急 啊！

[misswangjinfeng]

我晕咧，你咋不帮忙想办法哦！ 尽港废话！是不是应该创建一个索引呢 ？

[AcHerat]

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

[renwenli07461]

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

[kevin_long]

select * *转换成你需要查询出的具体字段 这样效率会高一些