哪个sql相对来说会效率高一点呢？排除索引的影响
-1
悬赏园豆：10
[已解决问题]
解决于 2014-02-15 16:14
1，
DECLARE @sender VARCHAR(50),@customerid BIGINT,@departmentid INT
SET @sender=''
SET @customerid=74494141947604
SET @departmentid=0
SELECT m.customername,( select bd.[Address] from Customer bd(nolock) where bd.id=m.CustomerID) as[Address] , m.ID,m.CustomerID,m.Mobiles,m.SmsContent,m.SendDate,m.sendnumber,
f.UserName as Sender,f.DepartmentId,md.DepartName,
a.CommentContent,m.IsComment,CASE WHEN m.IsComment IS NULL THEN '点评' ELSE '已点评' END AS CommentRemark
FROM SMS m(NOLOCK)
--LEFT JOIN Customer bc(NOLOCK) ON bc.ID=m.CustomerID
JOIN frmuser f(NOLOCK) on f.account=m.sender
JOIN dbo.mdDepartment md(NOLOCK) ON md.ID=f.DepartmentId
OUTER APPLY(
SELECT TOP 1 sc.CommentContent FROM SmsComment sc(NOLOCK)
WHERE m.ID=sc.SmsId AND sc.SmsType='Sms'
)a
WHERE m.customerid =case(@customerid) when 0 then m.customerid else @customerid end
AND f.DepartmentId=CASE ISNULL(@departmentid,0) WHEN 0 THEN f.DepartmentId ELSE @departmentid END
AND m.sender = CASE(@sender) WHEN '' THEN m.sender ELSE @sender END
AND m.senddate >='2013-06-23'--@begintime
AND m.senddate <='2013-07-03'--@endtime+1
ORDER BY m.senddate DESC
2.
DECLARE @sender VARCHAR(50),@customerid BIGINT,@departmentid INT SET @sender='' SET @customerid=74494141947604 SET @departmentid=0
SELECT m.customername,bc.[Address],bc.CustomerName,m.ID,m.CustomerID,m.Mobiles,m.SmsContent,m.SendDate,m.sendnumber, f.UserName as Sender,f.DepartmentId,md.DepartName, a.CommentContent,m.IsComment,CASE WHEN m.IsComment IS NULL THEN '点评' ELSE '已点评' END AS CommentRemark FROM SMS m(NOLOCK)
LEFT JOIN Customer bc(NOLOCK) ON bc.ID=m.CustomerID
JOIN frmuser f(NOLOCK) on f.account=m.sender
JOIN dbo.mdDepartment md(NOLOCK) ON md.ID=f.DepartmentId
OUTER APPLY( SELECT TOP 1 sc.CommentContent FROM SmsComment sc(NOLOCK) WHERE m.ID=sc.SmsId AND sc.SmsType='Sms' )a
WHERE m.customerid =case(@customerid) when 0 then m.customerid else @customerid end
AND f.DepartmentId=CASE ISNULL(@departmentid,0) WHEN 0 THEN f.DepartmentId ELSE @departmentid END
AND m.sender = CASE(@sender) WHEN '' THEN m.sender ELSE @sender END
AND m.senddate >='2013-06-23'--@begintime
AND m.senddate <='2013-07-03'--@endtime+1
ORDER BY m.senddate DESC
Customer 是一个百万级数据的表，1是采用子查询来获得customername,2是通过join
customer表来获得customername
有优化的地方吗？
最佳答案
-1
jion会好一点
收获园豆：5
其他回答(1)
0
我觉得非主表的数据到了百万级，join会高效些。建好索引，速度应该很快的。
我这边主表快上亿了，连接表千万级，一直用的join，效率8错。
收获园豆：5
