sql 多个表连接 查询速度慢
我最近在优化sql语句，这里碰到了棘手的问题，多个表连接，我把连接的字段都建立了索引，每个表的数据在10w左右。运行我的代码后，sql执行是9s；这个时间太慢了，能否有更好的提升空间？
下面把代码贴出：
select row_number() over (order by b.applicationmainCode desc) as rowid,
a.applicationdetailid,a.ApplicationMainCode,a.price,a.goodsnum,a.reciveNum,a.sendNum,a.PublicNum,
a.Currency,a.equipment,a.equipmentnum,a.requiredate,a.des,
c.suppliername,d.goodsname,d.standard, d.unit,d.productid,
d.productArea,d.buyarea,e.paymenter,f.workunit,g.classname,
b.Ausername,b.AworkUnit,b.applicationMaindate,b.buycode,UseProuduct,
ProductCode,TSort,TsortChild,TSortNum,
case when tnewflag=1 then '新' else '旧' end as tnewStatus,Tcontent,Tdes,d.price as GPrice ,
case when b.comfigflag=0 then '是' else '否' end as comfigstatus,
(select top 1 getgoodsdate from instock where applicationdetailid=a.applicationdetailid order by getgoodsdate) as getgoodsdate
from Gr_dt_applicationDetail a left join Gr_db_supplier c on a.supplierid=c.supplierid
left join applicationMain b on a.applicationmainCode=b.ApplicationMaincode
left join goods d on a.goodsid=d.goodsid
left join Payment e on b.paymentid=e.paymentid
left join workunit f on b.UworkUnitId=f.workUnit_Id
left join goodstreeclass g on b.classid=g.classid
where a.applicationmainCode=b.ApplicationMaincode and a.goodsid=d.goodsid
and 1=1 and b.passflag>=1
order by b.applicationmainCode desc
SELECT row_number() OVER ( ORDER BY b.applicationmainCode DESC ) AS rowid ,
a.applicationdetailid ,
a.ApplicationMainCode ,
a.price ,
a.goodsnum ,
a.reciveNum ,
a.sendNum ,
a.PublicNum ,
a.Currency ,
a.equipment ,
a.equipmentnum ,
a.requiredate ,
a.des ,
c.suppliername ,
d.goodsname ,
d.standard ,
d.unit ,
d.productid ,
d.productArea ,
d.buyarea ,
e.paymenter ,
f.workunit ,
g.classname ,
b.Ausername ,
b.AworkUnit ,
b.applicationMaindate ,
b.buycode ,
UseProuduct ,
ProductCode ,
TSort ,
TsortChild ,
TSortNum ,
CASE WHEN tnewflag = 1 THEN '新'
ELSE '旧'
END AS tnewStatus ,
Tcontent ,
Tdes ,
d.price AS GPrice ,
CASE WHEN b.comfigflag = 0 THEN '是'
ELSE '否'
END AS comfigstatus ,
( SELECT TOP 1
getgoodsdate
FROM instock
WHERE applicationdetailid = a.applicationdetailid
ORDER BY getgoodsdate
) AS getgoodsdate
FROM Gr_dt_applicationDetail a
LEFT JOIN Gr_db_supplier c ON a.supplierid = c.supplierid
LEFT JOIN applicationMain b ON a.applicationmainCode = b.ApplicationMaincode
LEFT JOIN goods d ON a.goodsid = d.goodsid --#
LEFT JOIN Payment e ON b.paymentid = e.paymentid
LEFT JOIN workunit f ON b.UworkUnitId = f.workUnit_Id
LEFT JOIN goodstreeclass g ON b.classid = g.classid
WHERE a.applicationmainCode = b.ApplicationMaincode
AND a.goodsid = d.goodsid --这个地方跟#处有点儿重复了,#处改成 inner join
AND 1 = 1
AND b.passflag >= 1
ORDER BY b.applicationmainCode DESC
