请教SQL Server 2012 SQL语句优化

黑夜的白羊 发布于 2018/06/08 10:49
阅读 396
收藏 1
set STATISTICS io ON;

SELECT
	t1.*,
( SELECT COUNT (1) FROM t_article WHERE sitesectionid = 23 AND status = 2 AND adddate >= '2018-04-01 00:00:00' AND adddate <= '2018-06-25 18:00:00' AND patindex( ',' + CAST (t1.depid AS VARCHAR) + ',', ',' + undertakepid + ',' ) > 0 ) AS num1,
( SELECT COUNT (1) FROM t_article WHERE sitesectionid = 23 AND status = 2 AND adddate >= '2018-04-01 00:00:00' AND adddate <= '2018-06-25 18:00:00' AND patindex( ',' + CAST (t1.depid AS VARCHAR) + ',', ',' + assistpid + ',' ) > 0 ) AS num2,
( SELECT COUNT (1) FROM t_article WHERE sitesectionid = 19 AND publishedtype = 16 AND status = 2 AND adddate >= '2018-04-01 00:00:00' AND adddate <= '2018-06-25 18:00:00' AND patindex( ',' + CAST (t1.depid AS VARCHAR) + ',', ',' + publishedtypepid + ',' ) > 0 ) AS num3,
( SELECT COUNT (1) FROM t_article WHERE sitesectionid = 19 and status = 2 AND adddate >= '2018-04-01 00:00:00' AND adddate <= '2018-06-25 18:00:00' AND publishedtype = 17 AND patindex( ',' + CAST (t1.depid AS VARCHAR) + ',', ',' + publishedtypepid + ',' ) > 0 ) AS num4
FROM
	(
		SELECT
			dg1.id AS groupid, dg.sort, dg1.title AS groupname, dg.depid, d.depname
		FROM
			t_dep_group AS dg
		LEFT JOIN t_department AS d ON dg.depid = d.id
		LEFT JOIN t_dep_group AS dg1 ON dg.pid = dg1.id
		WHERE dg.pid IN ( SELECT id FROM t_dep_group WHERE pid = 1 )
		GROUP BY dg1.title, dg.depid, d.depname, dg.sort, dg.pid, dg1.id
	) AS t1;

SET STATISTICS io off;
表 'Worktable'。扫描计数 488,逻辑读取 1940 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 't_article'。扫描计数 244,逻辑读取 223992 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 't_dep_group'。扫描计数 1,逻辑读取 277 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 't_department'。扫描计数 0,逻辑读取 122 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

受影响的行: 0
时间: 1.065s

请教SQL语句优化

数据库SQL Server 2012;

表t_article 数据共24500,其他表都是几百条左右;

目前已知num1,num2,num3,num4 这4条子查询影响大;

每个num1子查询都会使 表worktable 和表t_article 扫描计数和逻辑读取翻倍

加载中
OSCHINA
登录后可查看更多优质内容
返回顶部
顶部