为何 这段SQL查询一个AND 条件就会卡死?

PeterYing 2017-09-15 03:43:13
SELECT DISTINCT t_BOSqlgxhb.FID,t_BOSqlgxhb.FClassTypeID ,t_BOSqlgxhbEntry.FEntryID,t_BOSqlgxhbEntry.FIndex,t_User.FName FROM t_BOSqlgxhb
INNER JOIN t_BOSqlgxhbEntry ON t_BOSqlgxhb.FID=t_BOSqlgxhbEntry.FID
LEFT JOIN t_Item_3013 ON t_BOSqlgxhb.FGX=t_Item_3013.FItemID AND t_Item_3013.FItemID<>0
LEFT JOIN t_User ON t_BOSqlgxhb.FBiller=t_User.FUserID AND t_User.FUserID<>0
LEFT JOIN t_User t_User2 ON t_BOSqlgxhb.FModifier=t_User2.FUserID AND t_User2.FUserID<>0
LEFT JOIN t_ICItem t_ICItem1 ON t_BOSqlgxhbEntry.FBase9=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0
LEFT JOIN t_ICItem ON t_BOSqlgxhbEntry.FitemID=t_ICItem.FItemID AND t_ICItem.FItemID<>0
LEFT JOIN t_Item t_ICItem_FBaseProperty1 ON t_ICItem.F_121=t_ICItem_FBaseProperty1.FItemID AND t_ICItem_FBaseProperty1.FItemID<>0
LEFT JOIN t_Item t_ICItem_FBaseProperty2 ON t_ICItem.F_150=t_ICItem_FBaseProperty2.FItemID AND t_ICItem_FBaseProperty2.FItemID<>0
LEFT JOIN t_Item t_ICItem_FBaseProperty10 ON t_ICItem.F_141=t_ICItem_FBaseProperty10.FItemID AND t_ICItem_FBaseProperty10.FItemID<>0
LEFT JOIN t_Item t_ICItem_FBaseProperty11 ON t_ICItem.F_131=t_ICItem_FBaseProperty11.FItemID AND t_ICItem_FBaseProperty11.FItemID<>0
LEFT JOIN t_Measureunit ON t_BOSqlgxhbEntry.Funit=t_Measureunit.FItemID AND t_Measureunit.FItemID<>0
LEFT JOIN t_Emp ON t_BOSqlgxhbEntry.FEmp=t_Emp.FItemID AND t_Emp.FItemID<>0
LEFT JOIN t_Emp t_Emp2 ON t_BOSqlgxhbEntry.FEmp2=t_Emp2.FItemID AND t_Emp2.FItemID<>0
LEFT JOIN t_Emp t_Emp1 ON t_BOSqlgxhbEntry.Fbanz=t_Emp1.FItemID AND t_Emp1.FItemID<>0
LEFT JOIN t_Emp t_Emp4 ON t_BOSqlgxhbEntry.FBase5=t_Emp4.FItemID AND t_Emp4.FItemID<>0
LEFT JOIN t_Emp t_Emp3 ON t_BOSqlgxhbEntry.Fzhuren=t_Emp3.FItemID AND t_Emp3.FItemID<>0
WHERE (
t_Item_3013.FName Like '%铂大铸造-重力浇铸%' AND
t_ICItem.FName Like '%A6091%' AND
t_BOSqlgxhb.FHBDate BETWEEN '2017-08-01' AND '2017-08-31 23:59:59'
--AND t_User.FName = '卢若卡'
) AND t_BOSqlgxhb.FClassTypeID=200000013 Order By t_BOSqlgxhb.FID,t_BOSqlgxhbEntry.FIndex



以上SQL 如果--AND t_User.FName = '卢若卡' 注释 执行正常
如果注释去掉 则卡死,很久不能返回结果。。请教各位原因。谢谢各位帮助!
...全文
495 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
PeterYing 2017-09-27
  • 打赏
  • 举报
回复
谢谢大家,问题解决了,加了N多索引竟然就可以了
深度格式 2017-09-25
  • 打赏
  • 举报
回复
试下条件放后面
RICHEER COCA 2017-09-17
  • 打赏
  • 举报
回复
引用 楼主 yj2173591 的回复:
如果--AND t_User.FName = '卢若卡' 注释 执行正常 如果注释去掉 则卡死,很久不能返回结果。。请教各位原因。谢谢各位帮助! 试试下面的
SELECT DISTINCT 
 t_BOSqlgxhb.FID
,t_BOSqlgxhb.FClassTypeID  
,t_BOSqlgxhbEntry.FEntryID
,t_BOSqlgxhbEntry.FIndex
,t_User.FName   
FROM  t_BOSqlgxhb 
 
INNER JOIN t_BOSqlgxhbEntry  ON t_BOSqlgxhb.FID=t_BOSqlgxhbEntry.FID
 LEFT  JOIN t_Item_3013  ON t_BOSqlgxhb.FGX=t_Item_3013.FItemID AND t_Item_3013.FItemID<>0
 LEFT  JOIN t_User  ON t_BOSqlgxhb.FBiller=t_User.FUserID AND t_User.FUserID<>0
 LEFT  JOIN t_User t_User2 ON t_BOSqlgxhb.FModifier=t_User2.FUserID AND t_User2.FUserID<>0
 LEFT  JOIN t_ICItem t_ICItem1 ON t_BOSqlgxhbEntry.FBase9=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0
 LEFT  JOIN t_ICItem  ON t_BOSqlgxhbEntry.FitemID=t_ICItem.FItemID AND t_ICItem.FItemID<>0
 LEFT  JOIN t_Item t_ICItem_FBaseProperty1 ON t_ICItem.F_121=t_ICItem_FBaseProperty1.FItemID AND t_ICItem_FBaseProperty1.FItemID<>0
 LEFT  JOIN t_Item t_ICItem_FBaseProperty2 ON t_ICItem.F_150=t_ICItem_FBaseProperty2.FItemID AND t_ICItem_FBaseProperty2.FItemID<>0
 LEFT  JOIN t_Item t_ICItem_FBaseProperty10 ON t_ICItem.F_141=t_ICItem_FBaseProperty10.FItemID AND t_ICItem_FBaseProperty10.FItemID<>0
 LEFT  JOIN t_Item t_ICItem_FBaseProperty11 ON t_ICItem.F_131=t_ICItem_FBaseProperty11.FItemID AND t_ICItem_FBaseProperty11.FItemID<>0
 LEFT  JOIN t_Measureunit  ON t_BOSqlgxhbEntry.Funit=t_Measureunit.FItemID AND t_Measureunit.FItemID<>0
 LEFT  JOIN t_Emp  ON t_BOSqlgxhbEntry.FEmp=t_Emp.FItemID AND t_Emp.FItemID<>0
 LEFT  JOIN t_Emp t_Emp2 ON t_BOSqlgxhbEntry.FEmp2=t_Emp2.FItemID AND t_Emp2.FItemID<>0
 LEFT  JOIN t_Emp t_Emp1 ON t_BOSqlgxhbEntry.Fbanz=t_Emp1.FItemID AND t_Emp1.FItemID<>0
 LEFT  JOIN t_Emp t_Emp4 ON t_BOSqlgxhbEntry.FBase5=t_Emp4.FItemID AND t_Emp4.FItemID<>0
 LEFT  JOIN t_Emp t_Emp3 ON t_BOSqlgxhbEntry.Fzhuren=t_Emp3.FItemID AND t_Emp3.FItemID<>0
 WHERE 
 (
 charindex('卢若卡',t_User.FName)>0
 and charindex('铂大铸造-重力浇铸',t_Item_3013.FName)>0  
	 and charindex('A6091',t_ICItem.FName)>0
		and t_BOSqlgxhb.FHBDate  BETWEEN '2017-08-01' AND '2017-08-31 23:59:59'   
) 
AND t_BOSqlgxhb.FClassTypeID=200000013
 Order By t_BOSqlgxhb.FID,t_BOSqlgxhbEntry.FIndex
baomf 2017-09-16
  • 打赏
  • 举报
回复
我在想 是不是可能字段重复了导致的 给重新命名确定表字段说不定可以试试
吉普赛的歌 2017-09-15
  • 打赏
  • 举报
回复
--先筛选了再去连接, 还慢你来找我
IF OBJECT_ID('tempdb..#t_Item_3013') IS NOT NULL 
	DROP TABLE #t_Item_3013
IF OBJECT_ID('tempdb..#t_User') IS NOT NULL 
	DROP TABLE #t_User
IF OBJECT_ID('tempdb..#t_BOSqlgxhb') IS NOT NULL 
	DROP TABLE #t_BOSqlgxhb

SELECT FItemID FROM t_Item_3013
INTO #t_Item_3013
WHERE t_Item_3013.FItemID <> 0
AND t_Item_3013.FName LIKE '%铂大铸造-重力浇铸%'

SELECT * FROM t_User
INTO #t_User
WHERE t_User.FName = '卢若卡'

SELECT * FROM t_BOSqlgxhb 
INTO #t_BOSqlgxhb
where t_BOSqlgxhb.FHBDate BETWEEN '2017-08-01' AND '2017-08-31 23:59:59' 
  AND t_BOSqlgxhb.FClassTypeID = 200000013

--还有的条件你也可以这么改,太花时间略过吧

SELECT DISTINCT t_BOSqlgxhb.FID,
       t_BOSqlgxhb.FClassTypeID,
       t_BOSqlgxhbEntry.FEntryID,
       t_BOSqlgxhbEntry.FIndex,
       t_User.FName
FROM   t_BOSqlgxhb
       INNER JOIN #t_BOSqlgxhb AS t_BOSqlgxhbEntry
            ON  t_BOSqlgxhb.FID = t_BOSqlgxhbEntry.FID
       LEFT  JOIN #t_Item_3013 AS t_Item_3013
            ON  t_BOSqlgxhb.FGX = t_Item_3013.FItemID
            AND t_Item_3013.FItemID <> 0
       LEFT  JOIN #t_User AS t_User
            ON  t_BOSqlgxhb.FBiller = t_User.FUserID
            AND t_User.FUserID <> 0
       LEFT  JOIN t_User t_User2
            ON  t_BOSqlgxhb.FModifier = t_User2.FUserID
            AND t_User2.FUserID <> 0
       LEFT  JOIN t_ICItem t_ICItem1
            ON  t_BOSqlgxhbEntry.FBase9 = t_ICItem1.FItemID
            AND t_ICItem1.FItemID <> 0
       LEFT  JOIN t_ICItem
            ON  t_BOSqlgxhbEntry.FitemID = t_ICItem.FItemID
            AND t_ICItem.FItemID <> 0
       LEFT  JOIN t_Item t_ICItem_FBaseProperty1
            ON  t_ICItem.F_121 = t_ICItem_FBaseProperty1.FItemID
            AND t_ICItem_FBaseProperty1.FItemID <> 0
       LEFT  JOIN t_Item t_ICItem_FBaseProperty2
            ON  t_ICItem.F_150 = t_ICItem_FBaseProperty2.FItemID
            AND t_ICItem_FBaseProperty2.FItemID <> 0
       LEFT  JOIN t_Item t_ICItem_FBaseProperty10
            ON  t_ICItem.F_141 = t_ICItem_FBaseProperty10.FItemID
            AND t_ICItem_FBaseProperty10.FItemID <> 0
       LEFT  JOIN t_Item t_ICItem_FBaseProperty11
            ON  t_ICItem.F_131 = t_ICItem_FBaseProperty11.FItemID
            AND t_ICItem_FBaseProperty11.FItemID <> 0
       LEFT  JOIN t_Measureunit
            ON  t_BOSqlgxhbEntry.Funit = t_Measureunit.FItemID
            AND t_Measureunit.FItemID <> 0
       LEFT  JOIN t_Emp
            ON  t_BOSqlgxhbEntry.FEmp = t_Emp.FItemID
            AND t_Emp.FItemID <> 0
       LEFT  JOIN t_Emp t_Emp2
            ON  t_BOSqlgxhbEntry.FEmp2 = t_Emp2.FItemID
            AND t_Emp2.FItemID <> 0
       LEFT  JOIN t_Emp t_Emp1
            ON  t_BOSqlgxhbEntry.Fbanz = t_Emp1.FItemID
            AND t_Emp1.FItemID <> 0
       LEFT  JOIN t_Emp t_Emp4
            ON  t_BOSqlgxhbEntry.FBase5 = t_Emp4.FItemID
            AND t_Emp4.FItemID <> 0
       LEFT  JOIN t_Emp t_Emp3
            ON  t_BOSqlgxhbEntry.Fzhuren = t_Emp3.FItemID
            AND t_Emp3.FItemID <> 0
WHERE  t_ICItem.FName LIKE '%A6091%'
ORDER BY
       t_BOSqlgxhb.FID,
       t_BOSqlgxhbEntry.FIndex
二月十六 2017-09-15
  • 打赏
  • 举报
回复
SELECT DISTINCT
        t_BOSqlgxhb.FID ,
        t_BOSqlgxhb.FClassTypeID ,
        t_BOSqlgxhbEntry.FEntryID ,
        t_BOSqlgxhbEntry.FIndex ,
        t_User.FName
INTO    #temp
FROM    t_BOSqlgxhb
        INNER JOIN t_BOSqlgxhbEntry ON t_BOSqlgxhb.FID = t_BOSqlgxhbEntry.FID
        LEFT  JOIN t_Item_3013 ON t_BOSqlgxhb.FGX = t_Item_3013.FItemID
                                  AND t_Item_3013.FItemID <> 0
        LEFT  JOIN t_User ON t_BOSqlgxhb.FBiller = t_User.FUserID
                             AND t_User.FUserID <> 0
        LEFT  JOIN t_User t_User2 ON t_BOSqlgxhb.FModifier = t_User2.FUserID
                                     AND t_User2.FUserID <> 0
        LEFT  JOIN t_ICItem t_ICItem1 ON t_BOSqlgxhbEntry.FBase9 = t_ICItem1.FItemID
                                         AND t_ICItem1.FItemID <> 0
        LEFT  JOIN t_ICItem ON t_BOSqlgxhbEntry.FitemID = t_ICItem.FItemID
                               AND t_ICItem.FItemID <> 0
        LEFT  JOIN t_Item t_ICItem_FBaseProperty1 ON t_ICItem.F_121 = t_ICItem_FBaseProperty1.FItemID
                                                     AND t_ICItem_FBaseProperty1.FItemID <> 0
        LEFT  JOIN t_Item t_ICItem_FBaseProperty2 ON t_ICItem.F_150 = t_ICItem_FBaseProperty2.FItemID
                                                     AND t_ICItem_FBaseProperty2.FItemID <> 0
        LEFT  JOIN t_Item t_ICItem_FBaseProperty10 ON t_ICItem.F_141 = t_ICItem_FBaseProperty10.FItemID
                                                      AND t_ICItem_FBaseProperty10.FItemID <> 0
        LEFT  JOIN t_Item t_ICItem_FBaseProperty11 ON t_ICItem.F_131 = t_ICItem_FBaseProperty11.FItemID
                                                      AND t_ICItem_FBaseProperty11.FItemID <> 0
        LEFT  JOIN t_Measureunit ON t_BOSqlgxhbEntry.Funit = t_Measureunit.FItemID
                                    AND t_Measureunit.FItemID <> 0
        LEFT  JOIN t_Emp ON t_BOSqlgxhbEntry.FEmp = t_Emp.FItemID
                            AND t_Emp.FItemID <> 0
        LEFT  JOIN t_Emp t_Emp2 ON t_BOSqlgxhbEntry.FEmp2 = t_Emp2.FItemID
                                   AND t_Emp2.FItemID <> 0
        LEFT  JOIN t_Emp t_Emp1 ON t_BOSqlgxhbEntry.Fbanz = t_Emp1.FItemID
                                   AND t_Emp1.FItemID <> 0
        LEFT  JOIN t_Emp t_Emp4 ON t_BOSqlgxhbEntry.FBase5 = t_Emp4.FItemID
                                   AND t_Emp4.FItemID <> 0
        LEFT  JOIN t_Emp t_Emp3 ON t_BOSqlgxhbEntry.Fzhuren = t_Emp3.FItemID
                                   AND t_Emp3.FItemID <> 0
WHERE   ( t_Item_3013.FName LIKE '%铂大铸造-重力浇铸%'
          AND t_ICItem.FName LIKE '%A6091%'
          AND t_BOSqlgxhb.FHBDate BETWEEN '2017-08-01'
                                  AND     '2017-08-31 23:59:59'
        )
        AND t_BOSqlgxhb.FClassTypeID = 200000013
ORDER BY t_BOSqlgxhb.FID ,
        t_BOSqlgxhbEntry.FIndex;

SELECT  *
FROM    #temp
WHERE   FName = '卢若卡';
OwenZeng_DBA 2017-09-15
  • 打赏
  • 举报
回复
引用 4 楼 PeterYing的回复:
[quote=引用 2 楼 z10843087 的回复:] 这个查询查一个月的数据,而且还有模糊查询,肯定速度慢
注释 去掉 不能执行,执行很久没有返回,如果 注释了,没有这个条件,反而能执行[/quote] 语句执行计划没选对,重建相关表的统计信息,或者你可以强制hash join 试试
leo_lesley 2017-09-15
  • 打赏
  • 举报
回复

 LEFT  JOIN t_User         ON t_BOSqlgxhb.FBiller=t_User.FUserID    AND t_User.FUserID<>0
 LEFT  JOIN t_User t_User2 ON t_BOSqlgxhb.FModifier=t_User2.FUserID AND t_User2.FUserID<>0
 
 -- 换成
 
 LEFT  JOIN t_User ON (t_BOSqlgxhb.FBiller=t_User.FUserID or t_BOSqlgxhb.FModifier=t_User.FUserID) AND t_User.FUserID<>0
leo_lesley 2017-09-15
  • 打赏
  • 举报
回复
应该是没有使用到相关的索引, 先建个索引然后再执行你的SQL语句试试(索引创建语法如下)。

create index Idx_t_user on t_User(FName)
顺势而为1 2017-09-15
  • 打赏
  • 举报
回复
引用 5 楼 yj2173591 的回复:
[quote=引用 3 楼 appetizing_fish1 的回复:] 那把这语句放在外一层看看. AND t_User.FName = '卢若卡'
放在外面一层 也不行[/quote] 这样看看 AND t_User.FName =N '卢若卡' 如果还不行, 可以前面放到临时表中, 再加条件 AND t_User.FName =N '卢若卡' 看看
PeterYing 2017-09-15
  • 打赏
  • 举报
回复
引用 3 楼 appetizing_fish1 的回复:
那把这语句放在外一层看看. AND t_User.FName = '卢若卡'
放在外面一层 也不行
PeterYing 2017-09-15
  • 打赏
  • 举报
回复
引用 2 楼 z10843087 的回复:
这个查询查一个月的数据,而且还有模糊查询,肯定速度慢
注释 去掉 不能执行,执行很久没有返回,如果 注释了,没有这个条件,反而能执行
顺势而为1 2017-09-15
  • 打赏
  • 举报
回复
那把这语句放在外一层看看. AND t_User.FName = '卢若卡'
OwenZeng_DBA 2017-09-15
  • 打赏
  • 举报
回复
这个查询查一个月的数据,而且还有模糊查询,肯定速度慢
OwenZeng_DBA 2017-09-15
  • 打赏
  • 举报
回复
注释加上就对数据加上了筛选条件,就会执行更快。你没加这个条件时,是不是返回太多数据

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧