>我试图从这个查询中挤出一些性能,任何帮助将不胜感激,我唯一能做的就是在第二个语句上删除顺序,因为它是按主键排序的
--SET STATISTICS TIME ON
DECLARE
@MasterAdGroupId Int = 3469476,
@IncludeDeleted Bit = 1,
@Deleted Bit = 0,
@IncludeDirty Bit= 0,
@Dirty Bit =1,
@IncludeDupes Bit=0,
@OrderByText Bit= 1,
@UseInDisplayNetworkOnly Bit=0
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @OrderByText=1
BEGIN
SELECT MasterKeywordId, Text, URL, MasterAdGroupId, mk.KeywordTemplateId, Dirty, mk.Deleted, Negative, Duplicate
FROM admanMasterKeyword_tbl mk
INNER JOIN admanKeywordTemplate_tbl kt on kt.KeywordTemplateId = mk.KeywordTemplateId
WHERE MasterAdGroupId = @MasterAdGroupId
AND mk.Deleted = CASE WHEN @IncludeDeleted=0 THEN @Deleted ELSE mk.Deleted END
AND Dirty = CASE WHEN @IncludeDirty=0 THEN @Dirty ELSE Dirty END
AND Duplicate = CASE WHEN @IncludeDupes=1 THEN Duplicate ELSE 0 END
AND UseInDisplayNetwork = CASE WHEN @UseInDisplayNetworkOnly=1 THEN 1 ELSE UseInDisplayNetwork END
ORDER BY Text
END
ELSE
BEGIN
SELECT MasterKeywordId, Text, URL, MasterAdGroupId, mk.KeywordTemplateId, Dirty, mk.Deleted, Negative, Duplicate
FROM admanMasterKeyword_tbl mk
INNER JOIN admanKeywordTemplate_tbl kt on kt.KeywordTemplateId = mk.KeywordTemplateId
WHERE MasterAdGroupId = @MasterAdGroupId
AND mk.Deleted = CASE WHEN @IncludeDeleted=0 THEN @Deleted ELSE mk.Deleted END
AND Dirty = CASE WHEN @IncludeDirty=0 THEN @Dirty ELSE Dirty END
AND Duplicate = CASE WHEN @IncludeDupes=1 THEN Duplicate ELSE 0 END
AND UseInDisplayNetwork = CASE WHEN @UseInDisplayNetworkOnly=1 THEN 1 ELSE UseInDisplayNetwork END
ORDER BY MasterKeywordId
END
END
这不会改变任何性能,但是如果您在两个查询中更改的只是它们的排序方式(我看不到任何差异),则可以将CASE语句放在ORDER BY中,而不必重复自己或根本不执行IF语句:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT MasterKeywordId, Text, URL, MasterAdGroupId, mk.KeywordTemplateId, Dirty, mk.Deleted, Negative, Duplicate
FROM admanMasterKeyword_tbl mk
INNER JOIN admanKeywordTemplate_tbl kt on kt.KeywordTemplateId = mk.KeywordTemplateId
WHERE MasterAdGroupId = @MasterAdGroupId
AND mk.Deleted = CASE WHEN @IncludeDeleted=0 THEN @Deleted ELSE mk.Deleted END
AND Dirty = CASE WHEN @IncludeDirty=0 THEN @Dirty ELSE Dirty END
AND Duplicate = CASE WHEN @IncludeDupes=1 THEN Duplicate ELSE 0 END
AND UseInDisplayNetwork = CASE WHEN @UseInDisplayNetworkOnly=1 THEN 1 ELSE UseInDisplayNetwork END
ORDER BY (CASE WHEN @OrderByText = 1 THEN Text ELSE MasterKeywordId END)
此查询的查询计划是什么样的?您可以在任何地方创建索引?