见鬼了,加了两个字段爆慢!该怎么解决-MSSQL教程-爱易网页

见鬼了,加了两个字段爆慢!该怎么解决

日期：2014-05-18　浏览次数：20712 次

见鬼了,加了两个字段爆慢!
数据库为SQLServer 2000

先看下原来的查询

SQL code


SELECT buhuo = CASE WHEN b.slqty > a.qty THEN '*' ELSE '' END, c.PLUno, c.pluname,
mll = (c.slprc - c.csprc) / c.slprc * 100, c.csprc, c.slprc, a.QTY, 
      kcje = a.qty * c.csprc, b.slqty, slamt = b.slqty * c.slprc, zck = d.ckqty
FROM 

(SELECT pluid = a.pluid, pluno = a.pluno, pluname = a.pluname, 
               csprc = b.avgcsprc, slprc = b.slprc
        FROM basplumain a, baspluprc b
        WHERE a.pluid = b.pluid AND a.pluno LIKE :PLUNO + '%'
        group by a.pluid) c JOIN 
          
      
          (SELECT PLUID = a.pluid, QTY = SUM(a.BEGQTY + a.INQTY - a.OUTQTY)
         FROM FINSTOCKSHP a
         WHERE shpid = 2
         GROUP BY pluid) a ON a.pluid = c.pluid LEFT JOIN
          
          
          (SELECT slQTY = SUM(QTY), pluid
         FROM FINEBK
         WHERE DOCTYP / 1000000 = 21 AND level3 = 0 AND docdat >= :BDAT AND 
               docdat <= :EDAT
         GROUP BY pluid) b ON c.pluid = b.pluid LEFT JOIN
          
          
          (SELECT pluid, ckQTY = a.BEGQTY + a.INQTY - a.OUTQTY
         FROM (SELECT PLUID, BEGCS = SUM(BEGCS), BEGQTY = SUM(BEGQTY), 
                       INCS = SUM(INCS), INQTY = SUM(INQTY), OUTQTY = SUM(OUTQTY)
                 FROM FINSTOCKSHP A
                 WHERE a.shpid = 7
                 GROUP BY PLUID) A) d ON c.pluid = d .pluid

其中 :BDAT即为当月开始日期,即 '2012-02-01'
:EDAT即为当月结束日期,即 '2012-02-29'
:PLUNO 是字符型常量,客户端允许为空,为空即查询通配符 '%'

现增加两个字段,
代码如下

SQL code


SELECT buhuo = CASE WHEN b.slqty > a.qty THEN '*' ELSE '' END, c.PLUno, c.pluname, 
      c.pkunit, c.spec, mll = (c.slprc - c.csprc) / c.slprc * 100, c.csprc, c.slprc, a.QTY, 
      kcje = a.qty * c.csprc, b.slqty, slamt = b.slqty * c.slprc, zck = d.ckqty
FROM 

(SELECT pluid = a.pluid, pluno = a.pluno, pluname = a.pluname, spec = a.spec, 
              pkunit = a.pkunit, csprc = b.avgcsprc, slprc = b.slprc
        FROM basplumain a, baspluprc b
        WHERE a.pluid = b.pluid AND a.pluno LIKE :PLUNO + '%'
        group by a.pluid) c JOIN 
          
      
          (SELECT PLUID = a.pluid, QTY = SUM(a.BEGQTY + a.INQTY - a.OUTQTY)
         FROM FINSTOCKSHP a
         WHERE shpid = 2
         GROUP BY pluid) a ON a.pluid = c.pluid LEFT JOIN
          
          
          (SELECT slQTY = SUM(QTY), pluid
         FROM FINEBK
         WHERE DOCTYP / 1000000 = 21 AND level3 = 0 AND docdat >= :BDAT AND 
               docdat <= :EDAT
         GROUP BY pluid) b ON c.pluid = b.pluid LEFT JOIN
          
          
          (SELECT pluid, ckQTY = a.BEGQTY + a.INQTY - a.OUTQTY
         FROM (SELECT PLUID, BEGCS = SUM(BEGCS), BEGQTY = SUM(BEGQTY), 
                       INCS = SUM(INCS), INQTY = SUM(INQTY), OUTQTY = SUM(OUTQTY)
                 FROM FINSTOCKSHP A
                 WHERE a.shpid = 7
                 GROUP BY PLUID) A) d ON c.pluid = d .pluid

增加了 spec,pkunit,两个字段均来自于basplumain表中
但,增加了两个字段后,客户端查询通配符时超慢, 去掉两个字段就好了....汗...

另外,在查询分析器中却没有这么诡异...哎...

实在不懂到底哪里错了,求各位大大帮忙!

或者,这段SQL应该如何优化? 代码中我是否有不规范的地方?

------解决方案--------------------
SELECT pluid = a.pluid, pluno = a.pluno, pluname = a.pluname, spec = a.spec,
pkunit = a.pkunit, csprc = b.avgcsprc, slprc = b.slprc
FROM basplumain a, baspluprc b
WHERE a.pluid = b.pluid AND a.pluno LIKE :PLUNO + '%'
group by a.pluid
这一段应该有错误，或者你根本贴的不是原代码
------解决方案--------------------

探讨

高手们怎么看出不是SQL SERVER

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

见鬼了,加了两个字段爆慢!该怎么解决

相关资料更多>

推荐阅读更多>