sql优化解决办法
sql优化
优化sql了
耗时 11秒
czjl 没建索引，有100W 数据
------解决思路----------------------
select d.*,max(date) 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 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 on c.company_id=a.parent_id
where a.name <>isnull(b.name,'') and a.parent_id!=0 and a.parent_id is not null and a.name!='上海' and a.parent_id<15 and a.parent_id='2'
group by a.name,a.company_id,a.parent_id,a.man1,a.addr1,c.name
)as d left join view_czjl v1 on v1.cid=d.company_id
group by time,name,d.company_id,parent_id,man1,addr1,d.pname
优化sql了
czjl 没建索引，有100W 数据
------解决思路----------------------
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 */