环境WINDOWS 2003+ SQL 2005
SQL 语句
SELECT distinct ISNull(e.FNumber,'') AS FShopNumber,ISNull(e.FName,'') AS FShopName,S.FBillNo,S.FDate,sum(SE.famount) famount,e.FSendDay,
DateDiff(day,DateAdd(day,FSendDay,S.FCheckDate),GetDate()) AS FDiffDay
From
SEOutStock S Inner Join SEOutStockEntry SE On S.FInterID=SE.FInterID
Left Join t_Emp e On S.FEMPID=e.FItemID
WHERE
S.FDate>='2008-01-01' and s.FTrantype=83
AND S.FBillNo Not In (Select FBillNO_Src From t_ItemCheckentry)
AND (''='' Or e.FNumber<='') AND (''='' Or e.FNumber>='')
AND (''='' OR S.FBillNo>='') AND (''='' OR S.FBillNo<='')
AND ('2010-08-31'='' OR S.FDate<='2010-08-31')
group by s.fbillno,s.fdate,e.FNumber,e.FName,e.fsendday,S.FCheckDate
ORDER BY FDiffDay错误类型
服务器: 消息 8649,级别 17,状态 1,行 1
查询已取消,因为此查询的估计开销(319)超出了配置的阈值 300。请与系统管理员联系。
急求解决办法,在线等
SQL 语句
SELECT distinct ISNull(e.FNumber,'') AS FShopNumber,ISNull(e.FName,'') AS FShopName,S.FBillNo,S.FDate,sum(SE.famount) famount,e.FSendDay,
DateDiff(day,DateAdd(day,FSendDay,S.FCheckDate),GetDate()) AS FDiffDay
From
SEOutStock S Inner Join SEOutStockEntry SE On S.FInterID=SE.FInterID
Left Join t_Emp e On S.FEMPID=e.FItemID
WHERE
S.FDate>='2008-01-01' and s.FTrantype=83
AND S.FBillNo Not In (Select FBillNO_Src From t_ItemCheckentry)
AND (''='' Or e.FNumber<='') AND (''='' Or e.FNumber>='')
AND (''='' OR S.FBillNo>='') AND (''='' OR S.FBillNo<='')
AND ('2010-08-31'='' OR S.FDate<='2010-08-31')
group by s.fbillno,s.fdate,e.FNumber,e.FName,e.fsendday,S.FCheckDate
ORDER BY FDiffDay错误类型
服务器: 消息 8649,级别 17,状态 1,行 1
查询已取消,因为此查询的估计开销(319)超出了配置的阈值 300。请与系统管理员联系。
急求解决办法,在线等
用户操作将 QUERY_GOVERNOR_COST_LIMIT 选项设置为更大的值。
请参阅
其他资源
SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL)
LZ先优化SQL试试看,能不能解决你的问题。
AND (''='' OR S.FBillNo>='') AND (''='' OR S.FBillNo<='')
AND ('2010-08-31'='' OR S.FDate<='2010-08-31')
SELECT distinct ISNull(e.FNumber,'') AS FShopNumber,
ISNull(e.FName,'') AS FShopName,S.FBillNo,S.FDate,
sum(SE.famount) famount,e.FSendDay,
DateDiff(day,DateAdd(day,FSendDay,S.FCheckDate),GetDate()) AS FDiffDay
From SEOutStock S -----JOIN部分,还是检查下index问题
Inner Join SEOutStockEntry SE
On S.FInterID=SE.FInterID
Left Join t_Emp e On S.FEMPID=e.FItemID
WHERE
----where条件部分逻辑看上去不是很清晰,如S.FDate>='2008-01-01',你下面又有AND ('2010-08-31'='' OR S.FDate<='2010-08-31')出现,还有AND (''='' Or e.FNumber<='') AND (''='' Or e.FNumber>='')这两个可以整合到一起去。
S.FDate>='2008-01-01' and s.FTrantype=83
AND S.FBillNo Not In (Select FBillNO_Src From t_ItemCheckentry)
AND (''='' Or e.FNumber<='') AND (''='' Or e.FNumber>='')
AND (''='' OR S.FBillNo>='') AND (''='' OR S.FBillNo<='')
AND ('2010-08-31'='' OR S.FDate<='2010-08-31')
group by s.fbillno,s.fdate,e.FNumber,e.FName,e.fsendday,S.FCheckDate
ORDER BY FDiffDay