多条件组合查询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秒。

 

mysql千万级大数据SQL查询优化

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎...
  • u014421556
  • u014421556
  • 2016年07月29日 13:27
  • 31054

MySQL搜索: WHERE 多条件

WHERE可以按多条件进行搜索。 products表如下: a WHERE后以and 接两个条件以及三个条件进行搜索: b 使用or来搜索多个条件 ...
  • liuying_1001
  • liuying_1001
  • 2014年07月11日 21:54
  • 62226

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

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

SQL 多条件查询

网上有不少人提出过类似的问题:“看到有人写了WHERE 1=1这样的SQL,到底是什么意思?”。其实使用这种用法的开发人员一般都是在使用动态组装的SQL。让我们想像如下的场景:用户要求提供一个灵活的查...
  • SJF0115
  • SJF0115
  • 2013年03月26日 10:29
  • 15370

C# SQL多条件查询拼接技巧

本文转载http://www.cnblogs.com/zfanlong1314/p/3874910.html #region 多条件搜索时,使用List集合来拼接条件(拼接Sql) ...
  • u010338733
  • u010338733
  • 2015年06月30日 16:28
  • 2853

动态SQL查询,组合查询

本文通过在SQL语句select 全部查询尾部添加where 1=1 ,从而实现后期条件拼接的便利,可以无限次添加 and =* and = and… … 这样,就能通过传入参数不同实现不同的查询/...
  • Amen_Wu
  • Amen_Wu
  • 2016年12月01日 18:35
  • 1814

SQL-组合查询

如何利用UNION操作符将多条SELECT语句组合成一个结果集?SQL允许执行多个查询(多条SELECT语句),并将结果作为一个查询结果集返回。这些组合查询通常称为并(union)或复合查询(comp...
  • beauty_1991
  • beauty_1991
  • 2016年04月27日 11:27
  • 2181

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

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

Linq 多条件查询组合问题

借助老外写的一个扩展表达式的类,可以把上篇中的代码写得更优雅 这是PredicateBuilder的源文件 public static class PredicateBuilder { ...
  • kongwei521
  • kongwei521
  • 2014年05月27日 13:57
  • 1958

多重条件查询

--建立一个"学生"表 --学生的学号:Sno,类型是char(9)-->因为学生的学号是9位的定长的字符串; --学生的姓名:Sname,类型是varchar2(8)-->因此可以是2,3,4汉字,...
  • ArithmeticException
  • ArithmeticException
  • 2016年11月20日 12:43
  • 1100
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:多条件组合查询SQL优化一则
举报原因:
原因补充:

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