科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道基础软件多条件组合查询SQL优化一则

多条件组合查询SQL优化一则

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

将特定的查询条件从WHERE子句移至FROM子句的ON条件里

作者:土豆爸爸 来源:CSDN 2007年12月28日

关键字: 优化 SQL 组合查询 多条件

  • 评论
  • 分享微博
  • 分享邮件

应用场景

 

上图中要求按以下条件查询学生作业:

1、按学生作业的状态:全部 / 待批改 / 已批改

2、按学生学号 /  学生姓名 /  作业题目 /  全部

其中1和2是“并”的关系。

原始查询

ALTER PROCEDURE [dbo].[QueryStudentHomework]
    
@teacherId int,
    
@state int--0:全部,1:已提交,2:已批改,3:已退回
    @queryType int,
    
@queryArg varchar(500)

AS
BEGIN
SET NOCOUNT ON;
DECLARE @queryIntArg int@queryStringArg varchar(502)
IF(@queryType = 1)
    
SET @queryIntArg = Convert(int@queryArg);
ELSE IF(@queryType = 2 OR @queryType = 3)
    SET @queryStringArg = '%' + @queryArg + '%'

SELECT    教学.学生作业.学生作业Id as '@id'
        教学.课程.课程简称 
+ ' : ' + 教学.课程班.课程班名称 AS '@classname'
        教学.作业.作业题目 
as '@title',
        教学.作业.发布时间 
as '@publishTime'
        教学.课程作业.上交时间 
as '@commitTime',
        教学.作业.作业类型 
as '@type'
        教学.学生作业.状态 
as '@state',
        教学.作业.作业内容 
as 'content',
        org.学生.学号 
as 'commit/@studentCode'
        org.学生.姓名 
as 'commit/@studentName'
        教学.学生作业.提交时间 
as 'commit/@commitTime',
        教学.学生作业.答案附件Id 
as 'commit/@fileId',
        pub.Upload.FileName 
as 'commit/@fileName',
        教学.学生作业.作业答案 
as 'commit',
        教学.学生作业.批改人 
as 'check/@checker',
        教学.学生作业.批改时间 
as 'check/@checkTime',
        教学.学生作业.分数 
as 'check/@score',
        教学.学生作业.评语 
as 'check'
FROM    教学.课程 INNER JOIN
        教学.课程班 
ON  教学.课程班.课程Id = 教学.课程.课程Id INNER JOIN
        教学.课程作业 
ON 教学.课程作业.课程班Id = 教学.课程班.课程班Id INNER JOIN
        教学.作业 
ON 教学.作业.作业Id = 教学.课程作业.作业Id INNER JOIN
        教学.学生作业 
ON  教学.学生作业.作业Id = 教学.课程作业.作业Id AND 教学.学生作业.课程班Id = 教学.课程班.课程班Id INNER JOIN
        org.学生 
ON org.学生.学生Id = 教学.学生作业.学生Id INNER JOIN
        教学.课程班权限 
ON 教学.课程班权限.课程班Id = 教学.课程班.课程班Id AND 教学.课程班权限.作业批改 = 1 LEFT OUTER JOIN
        pub.Upload 
ON pub.Upload.FileId = 教学.学生作业.答案附件Id
WHERE    教学.课程班权限.教师Id = @teacherId
        
AND (教学.课程班.状态 = 1)
        
AND (@state = 0 OR 教学.学生作业.状态 = @state)
        
AND (@queryType = 0 
            
OR (@queryType = 1 AND 教学.学生作业.学生Id = @queryIntArg)
            
OR (@queryType = 2 AND org.学生.姓名 like @queryStringArg)
            
OR (@queryType = 3 AND 教学.课程班.课程班Id = @queryIntArg)
            )
ORDER BY 提交时间
FOR XML PATH('homework'), ROOT('homeworks')
END

执行时间20几秒。

优化查询

ALTER PROCEDURE [dbo].[QueryStudentHomework]
    
@teacherId int,
    
@state int--0:全部,1:已提交,2:已批改,3:已退回
    @queryType int,
    
@queryArg varchar(500)

AS
BEGIN
SET NOCOUNT ON;
DECLARE @queryIntArg int@queryStringArg varchar(502)
IF(@queryType = 1)
    
SET @queryIntArg = Convert(int@queryArg);
ELSE IF(@queryType = 2 OR @queryType = 3)
    
SET @queryStringArg = '%' + @queryArg + '%'


SELECT    教学.学生作业.学生作业Id as '@id'
        教学.课程.课程简称 
+ ' : ' + 教学.课程班.课程班名称 AS '@classname'
        教学.作业.作业题目 
as '@title',
        教学.作业.发布时间 
as '@publishTime'
        教学.课程作业.上交时间 
as '@commitTime',
        教学.作业.作业类型 
as '@type'
        教学.学生作业.状态 
as '@state',
        教学.作业.作业内容 
as 'content',
        org.学生.学号 
as 'commit/@studentCode'
        org.学生.姓名 
as 'commit/@studentName'
        教学.学生作业.提交时间 
as 'commit/@commitTime',
        教学.学生作业.答案附件Id 
as 'commit/@fileId',
        pub.Upload.FileName 
as 'commit/@fileName',
        教学.学生作业.作业答案 
as 'commit',
        教学.学生作业.批改人 
as 'check/@checker',
        教学.学生作业.批改时间 
as 'check/@checkTime',
        教学.学生作业.分数 
as 'check/@score',
        教学.学生作业.评语 
as 'check'
FROM    教学.课程 INNER JOIN
        教学.课程班 
ON  教学.课程班.课程Id = 教学.课程.课程Id AND 教学.课程班.状态 = 1 INNER JOIN
        教学.课程作业 
ON 教学.课程作业.课程班Id = 教学.课程班.课程班Id INNER JOIN
        教学.作业 
ON 教学.作业.作业Id = 教学.课程作业.作业Id INNER JOIN
        教学.学生作业 
ON  教学.学生作业.作业Id = 教学.课程作业.作业Id 
            
AND 教学.学生作业.课程班Id = 教学.课程班.课程班Id 
            
AND (@state = 0 OR 教学.学生作业.状态 = @stateINNER JOIN
        org.学生 
ON org.学生.学生Id = 教学.学生作业.学生Id INNER JOIN
        教学.课程班权限 
ON 教学.课程班权限.课程班Id = 教学.课程班.课程班Id 
            
AND 教学.课程班权限.作业批改 = 1 
            
AND 教学.课程班权限.教师Id = @teacherId LEFT OUTER JOIN
        pub.Upload 
ON pub.Upload.FileId = 教学.学生作业.答案附件Id
WHERE    @queryType = 0 
    
OR (@queryType = 1 AND 教学.学生作业.学生Id = @queryIntArg)
    
OR (@queryType = 2 AND org.学生.姓名 like @queryStringArg)
    
OR (@queryType = 3 AND 教学.作业.作业题目 like @queryStringArg)
ORDER BY 提交时间
FOR XML PATH('homework'), ROOT('homeworks')
END

将特定的查询条件从WHERE子句移至FROM子句的ON条件里,执行时间为0.04秒。

 

查看本文来源
    • 评论
    • 分享微博
    • 分享邮件
    闂侇収鍠曞▎銏㈡媼閵忋倖顫�

    濠碘€冲€归悘澶愬箖閵娾晜濮滈悽顖涚摃閹烩晠宕氶崶鈺傜暠闁诡垰鍘栫花锛勬喆椤ゅ弧濡澘妫楅悡娆撳嫉閳ь剟寮0渚€鐛撻柛婵呮缁楀矂骞庨埀顒勫嫉椤栨瑤绻嗛柟顓у灲缁辨繈鏌囬敐鍕杽閻犱降鍨藉Σ鍕嚊閹跺鈧﹦绱旈幋鐐参楅柡鍫灦閸嬫牗绂掔捄铏规闁哄嫷鍨遍崑宥夋儍閸曨剚浠樺ù锝嗗▕閳ь剚鏌ㄧ欢鐐寸▕鐎b晝顏遍柕鍡嫹

    重磅专题
    往期文章
    最新文章