Question

SQL语句执行太慢，怎么优化？

select storage.prodcode,(select sum(billsale.prod_number) from billsale where billsale.prodcode=storage.prodcode) as '已销售数量'
from storage，查询出来有三百行数据 ，差不多要46秒钟，怎么优化呢

Answer 1

不用子查询,用临时表,子查询的效率下降时必然的,你建立临时表试试

Answer 2

select billsale.prodcode,
sum(billsale.prod_number) as '已销售数量'
from storage, billsale
where billsale.prodcode = storage.prodcode
group by billsale.prodcode

追问

谢谢您，不过这样查出来只有128行数量，少了很多，storage这个表是库存表，billsale这个表是销售表，storage里有的产品可能billsale里可能没有，但这些产品全部得显示出来

追答

是什么数据库？
select storage.prodcode,
sum(
case when billsale.prod_number is null then 0.0
else billsale.prod_number
end
) as '已销售数量'
from storage left join billsale
on storage.prodcode = billsale.prodcode
group by storage.prodcode

追问

是 SQL 2000，结果出来了，速度有所提高，但还是有点慢，我用了一个临时表，去掉了子查询，搞定了，谢谢您！

Answer 3

Try this:

select storage.prodcode, sum(case when billsale.prod_number is null then 0 else billsale.prod_number end) as '已销售数量'
from storage left join billsale on billsale.prodcode=storage.prodcode
group by storage.prodcode

Answer 4

在数据建索引撒，或通过引擎优化顾问