我用以下语句查询，返回结果需要40秒，返回约3000条数据
select a.TLI,a.serialnumber,a.fun,b.kit
from (
select distinct serialnumber,TLI,b.entrydatetime as fun,'' as kit
from products a join outcomes b
on a.recno=b.product
where b.entrydatetime>='2014/05/16 07:00:00' and operationtypename='Functional Test' and passfail='1') a left join (
select distinct serialnumber,TLI, '' as fun,b.entrydatetime as kit
from products a join outcomes b
on a.recno=b.product
where b.entrydatetime>='2014/05/16 07:00:00' and operationtypename='Manufacturing Kit Check' and passfail='1') b
on a.serialnumber=b.serialnumber
但是单独查一条语句只需5ms
select distinct serialnumber,TLI, '' as fun,b.entrydatetime as kit
from products a join outcomes b
on a.recno=b.product
where b.entrydatetime>='2014/05/16 07:00:00' and operationtypename='Manufacturing Kit Check' and passfail='1
针对以上left join的语名是否有好的优化，请大神帮忙瞧瞧！
------解决方案--------------------
try this,
select distinct serialnumber,TLI,b.entrydatetime as fun,'' as kit
into #t1
from products a
join outcomes b on a.recno=b.product
where b.entrydatetime>='2014/05/16 07:00:00'
and operationtypename='Functional Test' and passfail='1'
select distinct serialnumber,TLI, '' as fun,b.entrydatetime as kit
into #t2
from products a
join outcomes b on a.recno=b.product
where b.entrydatetime>='2014/05/16 07:00:00'
and operationtypename='Manufacturing Kit Check' and passfail='1'
select a.TLI,a.serialnumber,a.fun,b.kit
from #t1 a
left join #t2 b on a.serialnumber=b.serialnumber
------解决方案--------------------
用with先削减下outcomes表的数据量抽出共同条件+operationtypename='Functional Test' or operationtypename='Manufacturing Kit Check'的数据，然后用with后的数据再去和products表联结
------解决方案--------------------
建议:
1.将原语句拆分为3个语句实现相同的功能. (如6楼代码)
2.注意大表的索引碎片情况,及时整理.
3.最好在查询语句的表名之后加"(nolock)".
------解决方案--------------------
嗯，这句话我主观了，虽然临时表有不能预编译，不断开链接不会自动清除的问题，但他能建索引，可以解决表变量处理大数量的性能问题，不能简单的认为临时表就比表变量差