SELECT d.*, /* 参见 (4) MAX(DATE) lastuploadtime */ v1.lastuploadtime FROM ( SELECT '2014-06' AS TIME, a.name, a.company_id, a.parent_id, a.man1, a.addr1, c.name pname FROM bas_company a /* (1) LEFT JOIN (SELECT company.name, DATE enddate, v.cid FROM dbo.czjl v LEFT OUTER JOIN bas_company company ON (company.company_id = v.cid) WHERE Substring(CONVERT(VARCHAR(10),v.DATE,120),1,7) = '2014-06') b ON a.name = b.name */ LEFT JOIN bas_company c -- (2) 可以是 INNER JOIN 了 ON c.company_id = a.parent_id WHERE a.name <> Isnull(b.name,'') -- (1) 可以改为 NOT EXISTS 关系 /* (2) AND a.parent_id != 0 AND a.parent_id IS NOT NULL */ /* (3) 已经有 = 了 AND a.parent_id < 15 */ AND a.parent_id = '2' AND a.name != '上海' /* 没有了 LEFT JOIN b 就不会有重复了 GROUP BY a.name,a.company_id,a.parent_id,a.man1,a.addr1,c.name */ ) AS d/* (4) 只是求一个最大 DATA,用 APPLY 最合适 LEFT JOIN view_czjl v1 ON v1.cid = d.company_id GROUP BY TIME,name,d.company_id,parent_id,man1,addr1,d.pname */ OUTER APPLY (SELECT TOP 1 DATE lastuploadtime FROM view_czjl WHERE cid = d.company_id ) v1/* (5) 还有 d 这个子查询没必要了,APPLY 关系改为 cid = a.company_id */
SELECT avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS PossibleImprovement , last_user_seek , last_user_scan , [statement] AS [Object] , 'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + '_' + CONVERT(VARCHAR(32), D.index_handle) + '_' + REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '') + ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '') + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + included_columns + ')', '') AS Create_Index_Syntax FROM sys.dm_db_missing_index_groups AS G INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle and [statement] like '%表名%' ORDER BY PossibleImprovement DESC 实在不会就用这个,表名的地方写上您用到过的表。 然后执行Create_Index_Syntax 中的语句。
为了先过滤再APPLY ,d 还是要的。 SELECT d.*, v1.lastuploadtime FROM ( SELECT '2014-06' AS TIME, a.name, a.company_id, a.parent_id, a.man1, a.addr1, c.name pname FROM bas_company a JOIN bas_company c ON c.company_id = a.parent_id WHERE a.parent_id = '2' AND a.name != '上海' AND NOT EXISTS( /* 还有这里比较奇怪,为什么用 name 而不是 company_id 关联? 否则只需要单独对 czjl 一张表进行 NOT EXISTS 判断。 */ SELECT 1 FROM dbo.czjl v JOIN bas_company company ON company.company_id = v.cid WHERE a.name = company.name AND v.DATE >= '2014-06-01' AND v.DATE < '2014-07-01 ) ) AS d OUTER APPLY (SELECT TOP 1 DATE lastuploadtime FROM view_czjl WHERE cid = d.company_id ) v1
Ctrl+L 查看执行计划:sql 2008R2 会提示您有缺失索引,然后根据提示建立索引即可。
Ctrl+L 查看执行计划:sql 2008R2 会提示您有缺失索引,然后根据提示建立索引即可。我的是2005
MAX(DATE) lastuploadtime
*/
v1.lastuploadtime FROM ( SELECT '2014-06' AS TIME,
a.name,
a.company_id,
a.parent_id,
a.man1,
a.addr1,
c.name pname
FROM bas_company a
/* (1)
LEFT JOIN (SELECT company.name,
DATE enddate,
v.cid
FROM dbo.czjl v
LEFT OUTER JOIN bas_company company
ON (company.company_id = v.cid)
WHERE Substring(CONVERT(VARCHAR(10),v.DATE,120),1,7) = '2014-06') b
ON a.name = b.name
*/
LEFT JOIN bas_company c -- (2) 可以是 INNER JOIN 了
ON c.company_id = a.parent_id
WHERE a.name <> Isnull(b.name,'') -- (1) 可以改为 NOT EXISTS 关系
/* (2)
AND a.parent_id != 0
AND a.parent_id IS NOT NULL
*/
/* (3) 已经有 = 了
AND a.parent_id < 15
*/
AND a.parent_id = '2'
AND a.name != '上海'
/* 没有了 LEFT JOIN b 就不会有重复了
GROUP BY a.name,a.company_id,a.parent_id,a.man1,a.addr1,c.name
*/
) AS d/* (4) 只是求一个最大 DATA,用 APPLY 最合适
LEFT JOIN view_czjl v1
ON v1.cid = d.company_id
GROUP BY TIME,name,d.company_id,parent_id,man1,addr1,d.pname
*/
OUTER APPLY (SELECT TOP 1 DATE lastuploadtime
FROM view_czjl
WHERE cid = d.company_id
) v1/* (5) 还有 d 这个子查询没必要了,APPLY 关系改为 cid = a.company_id */
SELECT avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS PossibleImprovement ,
last_user_seek ,
last_user_scan ,
[statement] AS [Object] ,
'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + '_'
+ CONVERT(VARCHAR(32), D.index_handle) + '_'
+ REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '')
+ ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '')
+ CASE WHEN equality_columns IS NOT NULL
AND inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
+ included_columns
+ ')', '') AS Create_Index_Syntax
FROM sys.dm_db_missing_index_groups AS G
INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle
INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle
and [statement] like '%表名%'
ORDER BY PossibleImprovement DESC
实在不会就用这个,表名的地方写上您用到过的表。
然后执行Create_Index_Syntax 中的语句。
Ctrl+L 查看执行计划:sql 2008R2 会提示您有缺失索引,然后根据提示建立索引即可。我的是2005那用手动方法,看执行计划占用比例的部份,进行优化。有表扫描的部份数据量大时,建上适合的索引
1, 7) = '2014-06'这条件数据量?这用不到索引改为取日期范围
SELECT d.*,
v1.lastuploadtime
FROM ( SELECT '2014-06' AS TIME,
a.name,
a.company_id,
a.parent_id,
a.man1,
a.addr1,
c.name pname
FROM bas_company a
JOIN bas_company c
ON c.company_id = a.parent_id
WHERE a.parent_id = '2'
AND a.name != '上海'
AND NOT EXISTS( /* 还有这里比较奇怪,为什么用 name 而不是 company_id 关联?
否则只需要单独对 czjl 一张表进行 NOT EXISTS 判断。 */
SELECT 1
FROM dbo.czjl v
JOIN bas_company company
ON company.company_id = v.cid
WHERE a.name = company.name
AND v.DATE >= '2014-06-01'
AND v.DATE < '2014-07-01
)
) AS d
OUTER APPLY (SELECT TOP 1 DATE lastuploadtime
FROM view_czjl
WHERE cid = d.company_id
) v1