sql语句查询条件改变速度变慢?
查询语句如下
SELECT a.S_BarCode, V_QAFilmdata.k3code, a.S_GoodsName, V_QAFilmdata.sdate, a.GoodsLotNO AS LotNO, CASE WHEN LEFT(S_GoodsCode, 3)
= '20.' THEN S_goodsMFGdate END AS S_goodsindate, V_QAFilmdata.class, V_QAFilmdata.LV, V_QAFilmdata.Expdayqty, V_QAFilmdata.zhCheck AS ck,
V_QAFilmdata.mark, a.S_GoodsUnitName AS UnitName, V_QAFilmdata.FaceCheck, V_QAFilmdata.txCheck, V_QAFilmdata.QaDataIntName, a.GoodsTypeID, a.S_NV,
V_QAFilmdata.QADataZbit
FROM (SELECT dbo.V_QAFilmdata_Auto.k3code, dbo.V_QAFilmdata_Auto.sdate AS sdate, dbo.V_QAFilmdata_Auto.goodsname,
CAST(dbo.V_QAFilmdata_Auto.GoodsSpec AS varchar) AS goodsspec, ISNULL(dbo.V_QAFilmdata_Auto.LotNo, dbo.V_QAFilmdata_Auto.lot) AS LotNo,
dbo.V_QAFilmdata_Auto.class, dbo.V_QAFilmdata_Auto.LV, QAFilmBasic.QAFilmExpQty AS Expdayqty, dbo.V_QAFilmdata_Auto.FaceCheck,
dbo.V_QAFilmdata_Auto.txCheck, dbo.V_QAFilmdata_Auto.zhCheck, dbo.V_QAFilmdata_Auto.mark, dbo.V_QAFilmdata_Auto.QaDataIntName,
dbo.V_QAFilmdata_Auto.QADataZbit
FROM dbo.V_QAFilmdata_Auto INNER JOIN
(SELECT QAFilmName, QAFilmExpQty
FROM dbo.QAFilmBasic_New
WHERE (QAFilmint = 1)
UNION
SELECT QAFilmName, QAFilmExpQty
FROM dbo.QAFilmBasic_New
WHERE (QAFilmint = 2) AND (NOT EXISTS
(SELECT QAFilmName, QAFilmint, QAFilm000, QAFilm011, QAFilm012, QAFilm013, QAFilm020, QAFilm030, QAFilm040, QAFilm051,
QAFilm052, QAFilm053, QAFilm061, QAFilm062, QAFilm063, QAFilm070, QAFilm081, QAFilm082, QAFilm083, QAFilm090,
QAFilm101, QAFilm102, QAFilm103, QAFilm111, QAFilm112, QAFilm113, QAFilm120, QAFilm130, QAFilm141, QAFilm142,
QAFilm143, QAFilm151, QAFilm152, QAFilm153, QAFilm161, QAFilm162, QAFilm163, QAFilm171, QAFilm172, QAFilm173,
QAFilm181, QAFilm182, QAFilm183, QAFilm191, QAFilm192, QAFilm193, QAFilm201, QAFilm202, QAFilm203, QAFilmBit,
QAFilmSysDate, QAFilmUse, QAFilmBitUse, QAFilmBitDate, QAFilmExpQty, QAFilmRemark
FROM dbo.QAFilmBasic_New AS a
WHERE (QAFilmint = 1) AND (QAFilmName = dbo.QAFilmBasic_New.QAFilmName)))) AS QAFilmBasic ON
dbo.V_QAFilmdata_Auto.goodsname = QAFilmBasic.QAFilmName) AS V_QAFilmdata
INNER JOIN
(SELECT dbo.SA_MaterielBar.S_BarCode, dbo.SA_MaterielBar.S_GoodsCode, dbo.SA_MaterielBar.S_GoodsMFGDate, dbo.SA_MaterielBar.S_GoodsinDate,
dbo.SA_MaterielBar.S_GoodsLotNO AS goodsLot, dbo.SA_MaterielBar.S_GoodsLot AS GoodsLotNO, dbo.SA_MaterielBar.S_GoodsQty AS S_Qty,
dbo.SA_MaterielBar.S_GoodsUnitName, dbo.SA_MaterielBar.S_GoodsName, dbo.SA_MaterielBar.S_GoodsTypeID AS GoodsTypeID,
dbo.SA_MaterielBar.S_NV
FROM dbo.SA_MaterielBar INNER JOIN
dbo.SA_goodslist ON dbo.SA_MaterielBar.S_BarCode = dbo.SA_goodslist.S_GListBarCode
WHERE (dbo.SA_MaterielBar.S_GoodsTypeID = 117) AND (dbo.SA_MaterielBar.S_GoodsDel = 0) AND (dbo.SA_goodslist.S_GlistFlag = 2) AND
(dbo.SA_goodslist.S_GListQty > 0) AND (dbo.SA_MaterielBar.S_GoodsMFGDate >= GETDATE() - 1000)) AS a ON
V_QAFilmdata.LotNo = a.GoodsLotNO and V_QAFilmdata.k3code = a.S_GoodsCode AND V_QAFilmdata.sdate= a.S_GoodsMFGDate
SELECT a.S_BarCode, V_QAFilmdata.k3code, a.S_GoodsName, V_QAFilmdata.sdate, a.GoodsLotNO AS LotNO, CASE WHEN LEFT(S_GoodsCode, 3)
= '20.' THEN S_goodsMFGdate END AS S_goodsindate, V_QAFilmdata.class, V_QAFilmdata.LV, V_QAFilmdata.Expdayqty, V_QAFilmdata.zhCheck AS ck,
V_QAFilmdata.mark, a.S_GoodsUnitName AS UnitName, V_QAFilmdata.FaceCheck, V_QAFilmdata.txCheck, V_QAFilmdata.QaDataIntName, a.GoodsTypeID, a.S_NV,
V_QAFilmdata.QADataZbit
FROM (SELECT dbo.V_QAFilmdata_Auto.k3code, dbo.V_QAFilmdata_Auto.sdate AS sdate, dbo.V_QAFilmdata_Auto.goodsname,
CAST(dbo.V_QAFilmdata_Auto.GoodsSpec AS varchar) AS goodsspec, ISNULL(dbo.V_QAFilmdata_Auto.LotNo, dbo.V_QAFilmdata_Auto.lot) AS LotNo,
dbo.V_QAFilmdata_Auto.class, dbo.V_QAFilmdata_Auto.LV, QAFilmBasic.QAFilmExpQty AS Expdayqty, dbo.V_QAFilmdata_Auto.FaceCheck,
dbo.V_QAFilmdata_Auto.txCheck, dbo.V_QAFilmdata_Auto.zhCheck, dbo.V_QAFilmdata_Auto.mark, dbo.V_QAFilmdata_Auto.QaDataIntName,
dbo.V_QAFilmdata_Auto.QADataZbit
FROM dbo.V_QAFilmdata_Auto INNER JOIN
(SELECT QAFilmName, QAFilmExpQty
FROM dbo.QAFilmBasic_New
WHERE (QAFilmint = 1)
UNION
SELECT QAFilmName, QAFilmExpQty
FROM dbo.QAFilmBasic_New
WHERE (QAFilmint = 2) AND (NOT EXISTS
(SELECT QAFilmName, QAFilmint, QAFilm000, QAFilm011, QAFilm012, QAFilm013, QAFilm020, QAFilm030, QAFilm040, QAFilm051,
QAFilm052, QAFilm053, QAFilm061, QAFilm062, QAFilm063, QAFilm070, QAFilm081, QAFilm082, QAFilm083, QAFilm090,
QAFilm101, QAFilm102, QAFilm103, QAFilm111, QAFilm112, QAFilm113, QAFilm120, QAFilm130, QAFilm141, QAFilm142,
QAFilm143, QAFilm151, QAFilm152, QAFilm153, QAFilm161, QAFilm162, QAFilm163, QAFilm171, QAFilm172, QAFilm173,
QAFilm181, QAFilm182, QAFilm183, QAFilm191, QAFilm192, QAFilm193, QAFilm201, QAFilm202, QAFilm203, QAFilmBit,
QAFilmSysDate, QAFilmUse, QAFilmBitUse, QAFilmBitDate, QAFilmExpQty, QAFilmRemark
FROM dbo.QAFilmBasic_New AS a
WHERE (QAFilmint = 1) AND (QAFilmName = dbo.QAFilmBasic_New.QAFilmName)))) AS QAFilmBasic ON
dbo.V_QAFilmdata_Auto.goodsname = QAFilmBasic.QAFilmName) AS V_QAFilmdata
INNER JOIN
(SELECT dbo.SA_MaterielBar.S_BarCode, dbo.SA_MaterielBar.S_GoodsCode, dbo.SA_MaterielBar.S_GoodsMFGDate, dbo.SA_MaterielBar.S_GoodsinDate,
dbo.SA_MaterielBar.S_GoodsLotNO AS goodsLot, dbo.SA_MaterielBar.S_GoodsLot AS GoodsLotNO, dbo.SA_MaterielBar.S_GoodsQty AS S_Qty,
dbo.SA_MaterielBar.S_GoodsUnitName, dbo.SA_MaterielBar.S_GoodsName, dbo.SA_MaterielBar.S_GoodsTypeID AS GoodsTypeID,
dbo.SA_MaterielBar.S_NV
FROM dbo.SA_MaterielBar INNER JOIN
dbo.SA_goodslist ON dbo.SA_MaterielBar.S_BarCode = dbo.SA_goodslist.S_GListBarCode
WHERE (dbo.SA_MaterielBar.S_GoodsTypeID = 117) AND (dbo.SA_MaterielBar.S_GoodsDel = 0) AND (dbo.SA_goodslist.S_GlistFlag = 2) AND
(dbo.SA_goodslist.S_GListQty > 0) AND (dbo.SA_MaterielBar.S_GoodsMFGDate >= GETDATE() - 1000)) AS a ON
V_QAFilmdata.LotNo = a.GoodsLotNO and V_QAFilmdata.k3code = a.S_GoodsCode AND V_QAFilmdata.sdate= a.S_GoodsMFGDate
...全文
相关推荐
MySQL中exists关键字和in的区别有那些? SQL语句优化有那些方法? 查询速度很慢应该怎么办?
一、exists和in有何区别如下? exists使用循环的方式,由outer表的记录数决定循环的次数,对于exists的影响最大,所以,外表的记录越小,子查询结果集较大时适用于exists; in 先执行子查询,子查询的结果返回去重之后,再执行主查询,所以,子查询的返回结果越少,越适合使用in关键字。 二、SQL语句的优化方式有那些? 1. 尽量避免非操作符的使用。在索引列上用NOT,等非操作符会导致索引失效,数据库管理系统是不会使用索引的,可以将查询语句转换为可以使用索引的查询。 2. 避免对查询列的操作。任何对查询列的操作都有可能导致全表
SQL语句查询速度慢的48个原因
1、没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 2、I/O吞吐量小,形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足
页面传入多个条件——sql语句的拼接
页面传进来传个条件判断的时候,要用到sql语句的拼接才能解决sql的问题,这是一个小例子。
Oracle表删除大量数据(千万)后查询变慢问题(原因分析)
Oracle表删除大量数据(千万)后查询变慢问题(原因分析)
易语言-程序结合SQL语句结构,动态实现SQL多条件查询语句代码
程序结合SQL语句结构,动态实现SQL多条件查询语句代码。


疑难问题
