首页 新闻 搜索 专区 学院

哪个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

有优化的地方吗?

heqianjin的主页 heqianjin | 初学一级 | 园豆:188
提问于:2013-07-02 15:44
< >
分享
最佳答案
-1

jion会好一点

收获园豆:5
若雪封尘 | 菜鸟二级 |园豆:463 | 2013-07-03 16:29
其他回答(1)
0

我觉得非主表的数据到了百万级,join会高效些。建好索引,速度应该很快的。

我这边主表快上亿了,连接表千万级,一直用的join,效率8错。

收获园豆:5
+小马哥++ | 园豆:906 (小虾三级) | 2013-07-02 17:00
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册