我这有一段语句:
select a.aaid,amount,b.exit_amount
from (select id,object_id,amount,price,date1 from AA where date1 BETWEEN '20100401' AND '20100430' ) a
LEFT JOIN
(SELECT aa_id, isnull(SUM(exit_amount), 0) AS exit_amount from AA_Exit GROUP BY aa_id) b ON a.id = b.yz_id
现在有个问题:
AA这个表有40万条数据,现在我用左连接,语句如上,这样执行要15秒时间.但如果改成如下语句,就只要1秒钟.
select a.aaid,amount
from (select id,object_id,amount,price,date1 from AA where date1 BETWEEN '20100401' AND '20100430' ) a
LEFT JOIN
(SELECT aa_id, isnull(SUM(exit_amount), 0) AS exit_amount from AA_Exit GROUP BY aa_id) b ON a.id = b.yz_id
但我现在必须要用上面这条语句执行.该如何优化.
一样？》
create index ix_aa_id on AA_Exit(aa_id)建索引 试一下
第二次1秒是因为直接从BUFFER CACHE取的原因吧,没有理由两个相差这么大.
{{
