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

见鬼了,加了两个字段爆慢!
数据库为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.ckqtyFROM (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.ckqtyFROM (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

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

这样该试下，要先看结果是否一样，再看性能有无提高

SQL code

SELECT buhuo = CASE WHEN b.slqty > a.qty THEN '*' ELSE '' END, m.PLUno, m.pluname,       m.pkunit, m.spec, mll = (n.slprc - n.avgcsprc) / m.slprc * 100, n.avgcsprc as csprc, n.slprc, a.QTY,       kcje = a.qty * n.avgcsprc, b.slqty, slamt = b.slqty * n.slprc, zck = d.ckqty        FROM basplumain m        inner join baspluprc n        on m.pluid = n.pluid         inner 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 = m.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 m.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 m.pluid = d .pluid
[解决办法]
探讨

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 
这里也有<=:这个啊

[解决办法]
探讨


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.plu……
 

 

查看更多 下一篇