Select ii.fjdbm,iii.fjxbm,iii.fjxmc,COUNT(Distinct i.ddbm) AS OrderCount, SUM(i.zsl) AS Quantity
From HYFJJK..MS.VORD_DDZ i ,HYFJJK..MS.VORD_PCDZ ii,HYFJJK..MS.VORD_FJFPZ iii
Where i.pcdbm=ii.pcdbm and ii.fjdbm=iii.fjdbm and ii.fjdbm IS NOT NULL
and i.sfky='1' and ii.fjdbm NOT IN (Select fjpcbm From ms_fjpc)
Group by ii.fjdbm,iii.fjxbm,iii.fjxmc
请问如何改才能提高SQL的查询速度
From HYFJJK..MS.VORD_DDZ i ,HYFJJK..MS.VORD_PCDZ ii,HYFJJK..MS.VORD_FJFPZ iii
Where i.pcdbm=ii.pcdbm and ii.fjdbm=iii.fjdbm and ii.fjdbm IS NOT NULL
and i.sfky='1' and ii.fjdbm NOT IN (Select fjpcbm From ms_fjpc)
Group by ii.fjdbm,iii.fjxbm,iii.fjxmc
请问如何改才能提高SQL的查询速度
Select ii.fjdbm,iii.fjxbm,iii.fjxmc,COUNT(Distinct i.ddbm) AS OrderCount, SUM(i.zsl) AS Quantity
From HYFJJK..MS.VORD_DDZ i ,HYFJJK..MS.VORD_PCDZ ii,HYFJJK..MS.VORD_FJFPZ iii
Where i.pcdbm=ii.pcdbm and ii.fjdbm=iii.fjdbm and ii.fjdbm IS NOT NULL
and i.sfky='1' and NOT EXISTS (Select * From ms_fjpc WHERE fjpcbm=ii.fjdbm)
Group by ii.fjdbm,iii.fjxbm,iii.fjxmc
VORD_FJFPZ和VORD_PCDZ的fjdbm字段根据实际情况建索引
并且VORD_FJFPZ的fjdbm字段设置默认值(假设是-1) not null(null是不索引的)
那么修改成
Select ii.fjdbm,iii.fjxbm,iii.fjxmc,COUNT(Distinct i.ddbm) AS OrderCount, SUM(i.zsl) AS Quantity
From HYFJJK..MS.VORD_DDZ i ,HYFJJK..MS.VORD_PCDZ ii
,HYFJJK..MS.VORD_FJFPZ iii
left join ms_fjpc on ii.fjdbm=ms_fjpc.fjpcbm
Where i.pcdbm=ii.pcdbm and ii.fjdbm=iii.fjdbm and ii.fjdbm>-1-- ii.fjdbm IS NOT NULL
and i.sfky='1' and ms_fjpc.fjpcbm is null
Group by ii.fjdbm,iii.fjxbm,iii.fjxmc