SELECT distinct
H1.FNumber + '-' + ISNULL(T2.FNumber,'') AS FID,
H1.FNumber AS FMedicalServeDeptIDFNumber,
H1.FName AS FMedicalServeDeptIDFName,
T2.FName AS FMedicoIDFName,
T1.FNumber AS FIatricIDFNumber,
T1.FName AS FIatricIDFName,
D.FMoney AS FMoney,
D.FComeinMoney AS FCostMoney,
(D.FMoney - D.FComeinMoney) AS FProfitMoney
FROM MZ_ChargeBillEntry D
LEFT JOIN MZ_ChargeBill M ON M.FID = D.FID
LEFT JOIN SMO_System_2013.dbo.Com_MedicalServeDept H1 ON H1.FID = M.FMedicalServeDeptID
LEFT JOIN SMO_System_2013.dbo.Com_Regionalism H2 ON H2.FID = H1.FMRegionalismID
RIGHT JOIN SMO_System_2013.dbo.Com_MerchArchive T1 ON T1.FID = D.FIatricID AND T1.FItemFlag = 105002
LEFT JOIN SMO_System_2013.dbo.Com_Medico T2 ON T2.FID = M.FMedicoID
WHERE H2.FNumber Like '37130401%' And H1.FID=5
and (M.FDate>='2010-03-01 00:00:00' and M.FDate<='2013-03-31 23:59:59')
这个SQL语句,只要加上那个 RIGHT JOIN 速度就会很慢,换成LEFT JOIN速度就很快。
如果换成 LEFT JOIN ,在Where 条件里加上对表 Com_MerchArchive T1 限制条件 AND T1.FItemFlag = 105002,速度也会很慢,实在找不出原因了,望各位高手指点下。谢谢SQL右连接
H1.FNumber + '-' + ISNULL(T2.FNumber,'') AS FID,
H1.FNumber AS FMedicalServeDeptIDFNumber,
H1.FName AS FMedicalServeDeptIDFName,
T2.FName AS FMedicoIDFName,
T1.FNumber AS FIatricIDFNumber,
T1.FName AS FIatricIDFName,
D.FMoney AS FMoney,
D.FComeinMoney AS FCostMoney,
(D.FMoney - D.FComeinMoney) AS FProfitMoney
FROM MZ_ChargeBillEntry D
LEFT JOIN MZ_ChargeBill M ON M.FID = D.FID
LEFT JOIN SMO_System_2013.dbo.Com_MedicalServeDept H1 ON H1.FID = M.FMedicalServeDeptID
LEFT JOIN SMO_System_2013.dbo.Com_Regionalism H2 ON H2.FID = H1.FMRegionalismID
RIGHT JOIN SMO_System_2013.dbo.Com_MerchArchive T1 ON T1.FID = D.FIatricID AND T1.FItemFlag = 105002
LEFT JOIN SMO_System_2013.dbo.Com_Medico T2 ON T2.FID = M.FMedicoID
WHERE H2.FNumber Like '37130401%' And H1.FID=5
and (M.FDate>='2010-03-01 00:00:00' and M.FDate<='2013-03-31 23:59:59')
这个SQL语句,只要加上那个 RIGHT JOIN 速度就会很慢,换成LEFT JOIN速度就很快。
如果换成 LEFT JOIN ,在Where 条件里加上对表 Com_MerchArchive T1 限制条件 AND T1.FItemFlag = 105002,速度也会很慢,实在找不出原因了,望各位高手指点下。谢谢SQL右连接
速度慢可以检查一下索引是否建得合理了
然后再左连接其他表,一般以实际数据最多的那个作为主表查询.还有为什么你要用到distinct语句,这个语句也会导致查询变慢.
你就试换个写法吧,如果还不行,建议你对H2.FNumber 和 M.FDate两个字段建索引
--#2.把结果先放到表变量中,再JOIN: select FID,FNumber,FName from SMO_System_2013.dbo.Com_MerchArchive T1 where T1.FItemFlag = 105002
--#3.其实,你可以查看一下你的执行计划,就明白原因啦!
这个方法对,我速度就是这么改善的,我看了执行执行计划,慢就慢在Com_MerchArchive这个表的连接上,如果用结果集去连就很快了,我也不知道啥原因,