sql 多个表联接 查询速度慢

sql 多个表连接 查询速度慢
我最近在优化sql语句，这里碰到了棘手的问题，多个表连接，我把连接的字段都建立了索引，每个表的数据在10w左右。运行我的代码后，sql执行是9s；这个时间太慢了，能否有更好的提升空间？
下面把代码贴出：

SQL code

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.ApplicationMaincodeleft join goods d on a.goodsid=d.goodsidleft join Payment e on b.paymentid=e.paymentid left join workunit f on b.UworkUnitId=f.workUnit_Idleft join goodstreeclass g on  b.classid=g.classid   where a.applicationmainCode=b.ApplicationMaincode and a.goodsid=d.goodsid and 1=1 and b.passflag>=1order by b.applicationmainCode desc

查看了执行计划，就是order by b.applicationmaincode 消耗25%的；其他的都很少了；有没有更好的方法解决，争取速度的提升到1s之内。

[解决办法]
LZ where里的"a.applicationmainCode=b.ApplicationMaincode and a.goodsid=d.goodsid"与left join的on条件有冲突。如果你这样写的话，相当于用inner join了，也没必要在where语句写"a.applicationmainCode=b.ApplicationMaincode and a.goodsid=d.goodsid 
"了
[解决办法]

SQL code

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 getgoodsdateFROM    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.classidWHERE   a.applicationmainCode = b.ApplicationMaincode        AND a.goodsid = d.goodsid    --这个地方跟#处有点儿重复了,#处改成 inner join        AND 1 = 1        AND b.passflag >= 1ORDER BY b.applicationmainCode DESC
[解决办法]
这样的语句，在数据量大，索引没维护好，想不慢都难呀。 
 

通过执行计划检查性能低的位置，再考虑建索引，关联表太多考虑使用临时表。 

 

查看更多 下一篇