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

原创 2006年11月02日 02:06:00

应用场景

 

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

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

 

相关文章推荐

多条件组合查询Sql语句巧用StringBuffer

StringBuffer的使用: 线程安全的可变字符序列。一个类似于 String的字符串缓冲区,但不能修改。虽然在任意时间点上它都包含某种特定的字符序列,但通过某些方法调用可以改变该序列的长度和...

linq to sql 多条件组合查询

//一個條件一個條件串接 using System.Linq.Expressions; Expression exps = A => true; if (GroupId != "0") { ...

WEB界面编辑任意SQL条件组合

  • 2017年04月19日 15:36
  • 938KB
  • 下载

sql大数据多条件查询索引优化

此优化的前提可以称之为最近流行的头条人物“许三多”,总数据多,查询条件多,返回列多 优化前分页查询内部select列为需要的全部列,优化后内部select只返回ID主键,外部查询关联原数据表,然后查出...

jqGrid分页技术学习(九)spring jdbc多单条件组合查询

TablePagingController对多条件组合查询的判断:...... if (search) {// _search:true // filters:{"groupOp":...

在用java 对hbase多条件组合查询过程中遇到的坎坷

最近公司的数据部门提出了要做大数据分析的需求,大数据方确定数据储存在hbase中,java工程师进行调用展示。我作为一个java工程师,觉得这很easy,不就是展示一个非关系型数据库的数据吗?当时我还...

Linq 多条件查询组合问题

借助老外写的一个扩展表达式的类,可以把上篇中的代码写得更优雅 这是PredicateBuilder的源文件 public static class PredicateBuilder { ...

利用hibernate实现多条件组合查询的两种方式(2017年5月18日22:45:09)

利用hibernate实现多条件组合查询的两种方式:find和findByCriteria

Hibernate模糊查询之多条件任意组合查询

不多说,直接上dao层该查询方法代码,一共是三种(HQL,QBC,QBE): public List getUser(User user){ List list = null; Str...

如何进行ibatis动态多条件组合查询以及模糊查询

这几天在学习使用IBATIS突然要使用模糊查询,以及动态多个条件查询,按照自己的想法试了很久,都没解决这个问题. 首先是模糊查询的问题,开始时我使用如下条件:select * from user w...
  • kim
  • kim
  • 2013年02月08日 10:16
  • 2781
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:多条件组合查询SQL优化一则
举报原因:
原因补充:

(最多只允许输入30个字)