以下代码，总的就是在指定范围内，那些字段加起来在t_Xm_SalePrice 表没有记录的数据。请大神帮忙优化下，查不出数据 ，显示执行计划又是正常。谢谢
SELECT DISTINCT c.FNumber 物料代码,c.FName 物料名称,c.FModel 物料规格,b.FMapNumber 客户对应代码,d.FNumber 客户代码,'' 价格类型,1 [销货量(从)],10000000 [销货量(到)], b1.FNumber 计量单位代码,a1.FName 币别,0 [报价],e1.FName 业务员,CONVERT(DATETIME,'1900-01-01') 生效日期,CONVERT(DATETIME,'2100-01-01') 失效日期,'' 备注,d.fname 客户名称,a.FDate 订单日期 ,a.FInterID,a.FBillNo 订单编号,ss1.fname 销售模式
FROM dbo.SEOrder a INNER JOIN dbo.SEOrderEntry b ON a.FInterID = b.FInterID
INNER JOIN dbo.t_MeasureUnit b1 ON b.FUnitID=b1.FMeasureUnitID
INNER JOIN dbo.t_ICItem c ON b.FItemID = c.FItemID INNER JOIN dbo.t_Organization d ON a.FCustID=d.FItemID
INNER JOIN dbo.t_Currency a1 ON a.FCurrencyID=a1.FCurrencyID LEFT JOIN t_Item t7933 ON b.FEntrySelfS0150 = t7933.FItemID
LEFT JOIN dbo.t_Emp e1 ON a.FEmpID=e1.FItemID
LEFT JOIN dbo.t_SubMessage ss1 ON ss1.FInterID=a.FHeadSelfS0138 WHERE ISNULL(d.F_120,0)=1 and t7933.fname not like '%取消%'
AND a.FDate >='2014-09-23' and a.FDate<='2014-10-23' AND
CONVERT(VARCHAR(15),a.FCustID)+CONVERT(VARCHAR(15),b.FItemID)+CONVERT(VARCHAR(15),a.FCurrencyID)+b.FMapNumber
NOT IN (
SELECT CONVERT(VARCHAR(15),FCustID)+CONVERT(VARCHAR(15),FItemID)+CONVERT(VARCHAR(15),FCurrency)+FMapNumber
FROM dbo.t_Xm_SalePrice WHERE CONVERT(VARCHAR(10),GETDATE(),120)>=FQuoteTime
AND CONVERT(VARCHAR(10),GETDATE(),120)<=FDisTime ) AND a.FInterID=(SELECT min(a1.FInterID)
FROM seorder a1 INNER JOIN dbo.SEOrderEntry b1 ON a1.FInterID = b1.FInterID
WHERE a1.FCustID=a.FCustID AND b1.FItemID=b.FItemID AND b1.FMapNumber=b.FMapNumber and a1.fdate>='2014-09-23'
)
------解决思路----------------------
ISNULL(d.F_120,0)=1
可以直接改为 d.F_120=1，因为d.F_120为NULL的时候，这个等式同样不成立
然后，尽量不要DISTINCT 和NOT IN
其他的，不熟悉你的业务，不好优化
------解决思路----------------------
SELECT DISTINCT
c.FNumber 物料代码 ,
c.FName 物料名称 ,
c.FModel 物料规格 ,
b.FMapNumber 客户对应代码 ,
d.FNumber 客户代码 ,
'' 价格类型 ,
1 [销货量(从)] ,
10000000 [销货量(到)] ,
b1.FNumber 计量单位代码 ,
a1.FName 币别 ,
0 [报价] ,
e1.FName 业务员 ,
CONVERT(DATETIME, '1900-01-01') 生效日期 ,
CONVERT(DATETIME, '2100-01-01') 失效日期 ,
'' 备注 ,
d.fname 客户名称 ,
a.FDate 订单日期 ,
a.FInterID ,
a.FBillNo 订单编号 ,
ss1.fname 销售模式
FROM dbo.SEOrder a
INNER JOIN dbo.SEOrderEntry b ON a.FInterID = b.FInterID
INNER JOIN dbo.t_MeasureUnit b1 ON b.FUnitID = b1.FMeasureUnitID
INNER JOIN dbo.t_ICItem c ON b.FItemID = c.FItemID
INNER JOIN dbo.t_Organization d ON a.FCustID = d.FItemID
INNER JOIN dbo.t_Currency a1 ON a.FCurrencyID = a1.FCurrencyID
LEFT JOIN t_Item t7933 ON b.FEntrySelfS0150 = t7933.FItemID
LEFT JOIN dbo.t_Emp e1 ON a.FEmpID = e1.FItemID
LEFT JOIN dbo.t_SubMessage ss1 ON ss1.FInterID = a.FHeadSelfS0138
WHERE ISNULL(d.F_120, 0) = 1
AND t7933.fname NOT LIKE '%取消%'
AND a.FDate >= '2014-09-23'
AND a.FDate <= '2014-10-23'
AND CONVERT(VARCHAR(15), a.FCustID) + CONVERT(VARCHAR(15), b.FItemID)
+ CONVERT(VARCHAR(15), a.FCurrencyID) + b.FMapNumber NOT IN ( --这个not in的地方可以使用not exists来处理
SELECT CONVERT(VARCHAR(15), FCustID) + CONVERT(VARCHAR(15), FItemID)
+ CONVERT(VARCHAR(15), FCurrency) + FMapNumber
FROM dbo.t_Xm_SalePrice
WHERE CONVERT(VARCHAR(10), GETDATE(), 120) >= FQuoteTime
AND CONVERT(VARCHAR(10), GETDATE(), 120) <= FDisTime )
AND a.FInterID = ( SELECT MIN(a1.FInterID) --这个地方也可以考虑使用exists来处理
FROM seorder a1
INNER JOIN dbo.SEOrderEntry b1 ON a1.FInterID = b1.FInterID
WHERE a1.FCustID = a.FCustID
AND b1.FItemID = b.FItemID
AND b1.FMapNumber = b.FMapNumber
AND a1.fdate >= '2014-09-23'
)
配合索引进行处理 。参考 http://blog.csdn.net/orchidcat/article/details/6267552
另外，最好贴出来执行计划看看是哪儿的问题。
------解决思路----------------------
这个拼凑比较
CONVERT(VARCHAR(15),a.FCustID)+CONVERT(VARCHAR(15),b.FItemID)+CONVERT(VARCHAR(15),a.FCurrencyID)+b.FMapNumber
能换成几个条件吗
------解决思路----------------------
4个字段的比较当然是应该逐个直接比较，先转换、再拼字符串、再比较太作死了。
NOT IN 不是改成 LEFT JOIN，是改成 NOT EXISTS。
------解决思路----------------------
这个建议索引，仅仅是一个参考方案。还得结合你的使用来看看索引的创建是否合理。