现在的位置: 首页 > 综合 > 正文

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

2013年09月25日 ⁄ 综合 ⁄ 共 4049字 ⁄ 字号 评论关闭

应用场景

 

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

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秒。

 

抱歉!评论已关闭.