帮忙看一上这条语句为什么有的时候会造成死锁

帮忙看一下这条语句为什么有的时候会造成死锁

SQL code



select top 10 Pid,(select top 1 packageName from [File] where [File].pid=Product.pid) as packagename,dappitemname,pName,potherName,img,(select top 1 version from [File] where [File].pid=Product.pid) as version,Itemid,product.OrderNum,pv,downcount,(select count(commid) as commcount from comm where pid=product.pid) as commcoun,
(select top 1 xingji from xingji  where pid=product.pid  group by xingji order by count(xjid) desc) as xingji
 from product left join dapp_item on dapp_item.ditemid=product.ditemid where  ((cid=37 or pid in (851,402,826,968,849,830,702,964,31423,841,843,841,3107,1434,2781,833,1222,3087,3656,845,847,1203,1210,3107,838)) and cid=37) and cstate=0 and (select top 1 COUNT(fid) from [File] where  Pid=Product.Pid  and (Fenbianlv='0' or Fenbianlv=',0,' or Fenbianlv like '%,0,%')  and [online]=0  and ((ms=0 or ms=2) or (ms=1 and tid='1001') or (ms=1 and tid='0')))>0 order by CASE when(charindex(','+ltrim(pid)+',',',851,402,826,968,849,830,702,964,31423,841,843,841,3107,1434,2781,833,1222,3087,3656,845,847,1203,1210,3107,838,'))=0 then  pid+100 else charindex(','+ltrim(pid)+',',',851,402,826,968,849,830,702,964,31423,841,843,841,3107,1434,2781,833,1222,3087,3656,845,847,1203,1210,3107,838,') end

这条语句为什么有的时候会造成死锁？

------解决方案--------------------
不要用太多的子查询，会引起性能问题，把该语句用“数据顾问”运行一下，会有建议的。
------解决方案--------------------
先把子查询改掉再说。。
------解决方案--------------------
SQL 写的太复杂了
((cid=37 or pid in (851,402,826,968,849,830,702,964,31423,841,843,841,3107,1434,2781,833,1222,3087,3656,845,847,1203,1210,3107,838)) and cid=37)
像这个不是37 的永远是返回false
先优化下SQL看看自己要什么吧
------解决方案--------------------

SQL code

只是格式化一下：

select   top 10  Pid
                 ,(select top 1  packageName
                   from   [File]
                   where  [File].pid = Product.pid) as packagename
                 ,dappitemname
                 ,pName
                 ,potherName
                 ,img
                 ,(select top 1  version
                   from   [File]
                   where  [File].pid = Product.pid) as version
                 ,Itemid
                 ,product.OrderNum
                 ,pv
                 ,downcount
                 ,(select count(commid) as commcount
                   from   comm
                   where  pid = product.pid) as commcoun
                 ,(select   top 1  xingji
                   from     xingji
                   where    pid = product.pid
                   group by xingji
                   order by count(xjid) desc) as xingji
from     product
         left join dapp_item
           on dapp_item.ditemid = product.ditemid
where    ((cid = 37
   or pid in (851,402,826,968,849,830,702,964,31423,841,
              843,841,3107,1434,2781,833,1222,3087,3656,
              845,847,1203,1210,3107,838))
 and cid = 37)
and cstate = 0
and (select top 1  COUNT(fid)
     from   [File]
     where  Pid = Product.Pid
     and (Fenbianlv = '0'
           or Fenbianlv = ',0,'
           or Fenbianlv like '%,0,%')
     and [online] = 0
     and ((ms = 0
            or ms = 2)
           or (ms = 1
               and tid = '1001')
           or (ms = 1
               and tid = '0'))) > 0
order by CASE 
           when (charindex(',' + ltrim(pid) + ',',',851,402,826,968,849,830,702,964,31423,841,843,841,3107,1434,2781,833,1222,3087,3656,845,847,1203,1210,3107,838,')) = 0 then pid + 100
           else charindex(',' + ltrim(pid) + ',',',851,402,826,968,849,830,702,964,31423,841,843,841,3107,1434,2781,833,1222,3087,3656,845,847,1203,1210,3107,838,')
         end

------解决方案--------------------

帮忙看一上这条语句为什么有的时候会造成死锁

相关推荐