只要我把if isnull(@InOutType,'')='19040 ' and isnull(@OutBillNo,'')<>''这句注释掉,直接写 if exists(....) 就很快 0秒,要是没注释掉这2个变量的判断,查询分析器里要3秒, 这是什么原因?
declare @OutBillNo varchar(20), @InOutType varchar(20), @msg varchar(500), @BarCode varchar(20)
set @InOutType = '19040'
set @OutBillNo = '1109' --发货通知单主表ID
set @BarCode='200708010004'
if isnull(@InOutType,'')='19040 ' and isnull(@OutBillNo,'')<>'' --这里 and
exists (select 1 from da_ProBarcode where Barcode=isnull(@BarCode,'') or
BoxNO=isnull(@BarCode,'')
and isnull(Szzd,'') not in ( select productNO from yx_OutStockDetail where BillID=isnull(@OutBillNo,'') ) )
begin
declare @OutBillNo varchar(20), @InOutType varchar(20), @msg varchar(500), @BarCode varchar(20)
set @InOutType = '19040'
set @OutBillNo = '1109' --发货通知单主表ID
set @BarCode='200708010004'
if isnull(@InOutType,'')='19040 ' and isnull(@OutBillNo,'')<>'' --这里 and
exists (select 1 from da_ProBarcode where Barcode=isnull(@BarCode,'') or
BoxNO=isnull(@BarCode,'')
and isnull(Szzd,'') not in ( select productNO from yx_OutStockDetail where BillID=isnull(@OutBillNo,'') ) )
begin
@InOutType VARCHAR(20),
@msg VARCHAR(500),
@BarCode VARCHAR(20) SET @InOutType = '19040'
SET @OutBillNo = '1109' --发货通知单主表ID
SET @BarCode='200708010004'
SET @InOutType=isnull(@InOutType,'')
SET @OutBillNo = isnull(@OutBillNo,'')
SET @BarCode=isnull(@BarCode,'')
IF @InOutType='19040 ' AND @OutBillNo<>'' --这里 and
EXISTS (SELECT 1 FROM DBO.da_ProBarcode WITH(NOLOCK)
WHERE Barcode=@BarCode OR BoxNO=@BarCode
AND ISNULL(Szzd,'') not in
( SELECT productNO FROM DBO.yx_OutStockDetail WITH(NOLOCK)
WHERE BillID=@OutBillNo ) )
BEGIN你再看看 条件中字段的类型和长度是否一致,如果还是慢的话,你把里面的子查询放到外面去,带个临时表进来