IF NOT EXISTS(SELECT 0 FROM TABLE WHERE UUU=@UUU AND VVV=ISNULL(@VVV,'') AND WWW=ISNULL(@WWW,'') AND XXX=ISNULL(@XXX,'') AND YYY=ISNULL(@YYY,0) AND ZZZ=ISNULL(@ZZZ,0)) INSERT INTO TABLE (UUU,VVV,WWW,XXX,YYY,ZZZ) VALUES(@UUU,@VVV,@WWW,@XXX,@YYY,@ZZZ) ELSE UPDATE TABLE SET AAA=@AAA WHERE UUU=@UUU AND VVV=ISNULL(@VVV,'') AND WWW=ISNULL(@WWW,'') AND XXX=ISNULL(@XXX,'') AND YYY=ISNULL(@YYY,0) AND ZZZ=ISNULL(@ZZZ,0)
AND ISNULL(YYY,0)=ISNULL(@YYY,0)==> AND ( (YYY IS NULL and ISNULL(NULLIF(@YYY,0),0)=0) or (YYY = ISNULL(NULLIF(@YYY,0),0)) )因为有or逻辑,恐怕还是用不上Index
AND (VVV=ISNULL(@VVV,'') OR (VVV IS NULL AND ISNULL(@VVV,'') = ''))是不是这种?
经过测试, ISNULL(YYY,0)=ISNULL(@YYY,0) 和 AND (VVV=ISNULL(@VVV,'') OR (VVV IS NULL AND ISNULL(@VVV,'') = '')) 效果一样,应该都没有用到索引。
错了,AND (VVV=ISNULL(@VVV,'') OR (VVV IS NULL AND ISNULL(@VVV,'') = '')) 有问题,数据少了,应该插入的,但没有插入,被Update了。
看了执行计划,AND ISNULL(XXX,'')=ISNULL(@XXX,'') 这种的,也可以用到索引,成本为65%,如果把字符串的默认值改为''(也就是字符串字段没有NULL的情况了,数字的还有NULL),用 AND XXX=ISNULL(@XXX,'') AND ISNULL(YYY,0)=ISNULL(@YYY,0) ,索引的成本是99%各位看看还有什么方法?是不是直接Update,再看@@ROWCOUNT 再Insert 这种快呢?
然后判断
IF @@ROWCOUNT = 0
--再INSERT
INSERT INTO TABLE (UUU,VVV,WWW,XXX,YYY,ZZZ) VALUES(@UUU,@VVV,@WWW,@XXX,@YYY,@ZZZ)
ELSE
UPDATE TABLE SET AAA=@AAA WHERE UUU=@UUU AND VVV=ISNULL(@VVV,'') AND WWW=ISNULL(@WWW,'') AND XXX=ISNULL(@XXX,'') AND YYY=ISNULL(@YYY,0) AND ZZZ=ISNULL(@ZZZ,0)
建议 1. 设置Default: ''
2. 将库中现有的NULL数据更新为''
3. 建一个Trigger, 将被赋值为NULL的数据以''替代调整后>
1.可简化查询代码; 2.可用上Index
改成
VVV=ISNULL(@VVV,'')
OR VVV IS NULL AND ISNULL(@VVV,'') = ''
(YYY = ISNULL(NULLIF(@YYY,0),0)) )因为有or逻辑,恐怕还是用不上Index
AND (VVV=ISNULL(@VVV,'') OR (VVV IS NULL AND ISNULL(@VVV,'') = ''))是不是这种?
ISNULL(YYY,0)=ISNULL(@YYY,0)
和
AND (VVV=ISNULL(@VVV,'') OR (VVV IS NULL AND ISNULL(@VVV,'') = ''))
效果一样,应该都没有用到索引。