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 扫描计数和逻辑读取翻倍