优化个SQL,求赐教....

yanhurenjian 2014-02-25 02:35:47
有个SQL:
select top 20 a.ServiceNo, a.Name,b.Seq_Points,c.Points from
TR_Driver a left join T21_DriverOptInfoDaily_Points b
on a.ServiceNo=b.ServiceNo and b.CompanyID=a.CompanyID
left join TR_DriverPointInfo c
on a.ServiceNo=c.ServiceNo and c.CompanyID=b.CompanyID
where a.IsDelete=0 and DATEDIFF(SECOND,'2014/2/25 10:00:00',b.Data_Dt)=0
and DATEDIFF(SECOND,'2014/2/25 10:00:00',c.Data_Dt)=0
and b.CompanyID=(select CompanyID from TR_Driver where ServiceNo=843391 and IsDelete=0) order by Seq_Points

T21_DriverOptInfoDaily_Points这个表有两百多万条数据

查询的性能非常差,结果要20多秒才能出来,大家看看有什么好的办法优化下??
...全文
188 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
發糞塗牆 2014-02-25
  • 打赏
  • 举报
回复
如果觉得性能还不行,再贴执行计划
yanhurenjian 2014-02-25
  • 打赏
  • 举报
回复
引用 16 楼 DBA_Huangzj 的回复:
这个不是top 20了吗?
是的是的,我弄错了,不好意思,按照你说的建了两个索引,现在速度快多了!3Q very much!
  • 打赏
  • 举报
回复
引用 15 楼 yanhurenjian 的回复:
[quote=引用 12 楼 DBA_Huangzj 的回复:] 加索引: CREATE INDEX IX_Tr ON TR_Driver(ServiceNo,CompanyID,IsDelete) CREATE INDEX IX_T12 ON T21_DriverOptInfoDaily_Points(ServiceNo,CompanyID)
SELECT TOP 20
        a.ServiceNo ,
        a.Name ,
        b.Seq_Points ,
        c.Points
FROM    TR_Driver a
        LEFT JOIN T21_DriverOptInfoDaily_Points b ON a.ServiceNo = b.ServiceNo
                                                     AND b.CompanyID = a.CompanyID
        LEFT JOIN TR_DriverPointInfo c ON a.ServiceNo = c.ServiceNo
                                          AND c.CompanyID = b.CompanyID
WHERE   a.IsDelete = 0
        AND b.Data_Dt = '2014/2/25 10:00:00'
        AND c.Data_Dt = '2014/2/25 10:00:00'
        AND b.CompanyID = ( SELECT  CompanyID
                            FROM    TR_Driver
                            WHERE   ServiceNo = 843391
                                    AND IsDelete = 0
                          )
ORDER BY Seq_Points


还有我要查询前20条的数据,这个怎么改?[/quote] 你是要简化语句吗
發糞塗牆 2014-02-25
  • 打赏
  • 举报
回复
你不会连正确的结果都没得到吧?
發糞塗牆 2014-02-25
  • 打赏
  • 举报
回复
这个不是top 20了吗?
yanhurenjian 2014-02-25
  • 打赏
  • 举报
回复
引用 12 楼 DBA_Huangzj 的回复:
加索引: CREATE INDEX IX_Tr ON TR_Driver(ServiceNo,CompanyID,IsDelete) CREATE INDEX IX_T12 ON T21_DriverOptInfoDaily_Points(ServiceNo,CompanyID)
SELECT TOP 20
        a.ServiceNo ,
        a.Name ,
        b.Seq_Points ,
        c.Points
FROM    TR_Driver a
        LEFT JOIN T21_DriverOptInfoDaily_Points b ON a.ServiceNo = b.ServiceNo
                                                     AND b.CompanyID = a.CompanyID
        LEFT JOIN TR_DriverPointInfo c ON a.ServiceNo = c.ServiceNo
                                          AND c.CompanyID = b.CompanyID
WHERE   a.IsDelete = 0
        AND b.Data_Dt = '2014/2/25 10:00:00'
        AND c.Data_Dt = '2014/2/25 10:00:00'
        AND b.CompanyID = ( SELECT  CompanyID
                            FROM    TR_Driver
                            WHERE   ServiceNo = 843391
                                    AND IsDelete = 0
                          )
ORDER BY Seq_Points


还有我要查询前20条的数据,这个怎么改?
發糞塗牆 2014-02-25
  • 打赏
  • 举报
回复
引用 13 楼 yanhurenjian 的回复:
[quote=引用 8 楼 u013774485 的回复:] [quote=引用 1 楼 dotnetstudio 的回复:] 试试这个

select top 20 a.ServiceNo, a.Name,b.Seq_Points,c.Points from 
(select * from TR_Driver where ServiceNo=843391 and IsDelete=0) a  left join  T21_DriverOptInfoDaily_Points b 
on a.ServiceNo=b.ServiceNo and b.CompanyID=a.CompanyID 
left join TR_DriverPointInfo c 
on a.ServiceNo=c.ServiceNo and c.CompanyID=b.CompanyID 
where a.IsDelete=0 and DATEDIFF(SECOND,'2014/2/25 10:00:00',b.Data_Dt)=0 
 and DATEDIFF(SECOND,'2014/2/25 10:00:00',c.Data_Dt)=0 
 order by Seq_Points
这个查出来的是ServiceNo=843391个人的数据。不是top20[/quote] 我只要前20条的数据。[/quote]12楼的试了没?
yanhurenjian 2014-02-25
  • 打赏
  • 举报
回复
引用 8 楼 u013774485 的回复:
[quote=引用 1 楼 dotnetstudio 的回复:] 试试这个

select top 20 a.ServiceNo, a.Name,b.Seq_Points,c.Points from 
(select * from TR_Driver where ServiceNo=843391 and IsDelete=0) a  left join  T21_DriverOptInfoDaily_Points b 
on a.ServiceNo=b.ServiceNo and b.CompanyID=a.CompanyID 
left join TR_DriverPointInfo c 
on a.ServiceNo=c.ServiceNo and c.CompanyID=b.CompanyID 
where a.IsDelete=0 and DATEDIFF(SECOND,'2014/2/25 10:00:00',b.Data_Dt)=0 
 and DATEDIFF(SECOND,'2014/2/25 10:00:00',c.Data_Dt)=0 
 order by Seq_Points
这个查出来的是ServiceNo=843391个人的数据。不是top20[/quote] 我只要前20条的数据。
發糞塗牆 2014-02-25
  • 打赏
  • 举报
回复
加索引: CREATE INDEX IX_Tr ON TR_Driver(ServiceNo,CompanyID,IsDelete) CREATE INDEX IX_T12 ON T21_DriverOptInfoDaily_Points(ServiceNo,CompanyID)
SELECT TOP 20
        a.ServiceNo ,
        a.Name ,
        b.Seq_Points ,
        c.Points
FROM    TR_Driver a
        LEFT JOIN T21_DriverOptInfoDaily_Points b ON a.ServiceNo = b.ServiceNo
                                                     AND b.CompanyID = a.CompanyID
        LEFT JOIN TR_DriverPointInfo c ON a.ServiceNo = c.ServiceNo
                                          AND c.CompanyID = b.CompanyID
WHERE   a.IsDelete = 0
        AND b.Data_Dt = '2014/2/25 10:00:00'
        AND c.Data_Dt = '2014/2/25 10:00:00'
        AND b.CompanyID = ( SELECT  CompanyID
                            FROM    TR_Driver
                            WHERE   ServiceNo = 843391
                                    AND IsDelete = 0
                          )
ORDER BY Seq_Points


發糞塗牆 2014-02-25
  • 打赏
  • 举报
回复
别@,引用我回复即可
發糞塗牆 2014-02-25
  • 打赏
  • 举报
回复
把鼠标移到开销74、62那两个上面,看看它用到哪些列,分别建“一个索引”,包住这些列
yanhurenjian 2014-02-25
  • 打赏
  • 举报
回复
@DBA_Huangzj
执行计划:

PsyFlowers 2014-02-25
  • 打赏
  • 举报
回复
引用 1 楼 dotnetstudio 的回复:
试试这个

select top 20 a.ServiceNo, a.Name,b.Seq_Points,c.Points from 
(select * from TR_Driver where ServiceNo=843391 and IsDelete=0) a  left join  T21_DriverOptInfoDaily_Points b 
on a.ServiceNo=b.ServiceNo and b.CompanyID=a.CompanyID 
left join TR_DriverPointInfo c 
on a.ServiceNo=c.ServiceNo and c.CompanyID=b.CompanyID 
where a.IsDelete=0 and DATEDIFF(SECOND,'2014/2/25 10:00:00',b.Data_Dt)=0 
 and DATEDIFF(SECOND,'2014/2/25 10:00:00',c.Data_Dt)=0 
 order by Seq_Points
这个查出来的是ServiceNo=843391个人的数据。不是top20
灰色 2014-02-25
  • 打赏
  • 举报
回复
这个脚本的写法和你数据库中设计的关联字段有关系,1:m或是m:n
灰色 2014-02-25
  • 打赏
  • 举报
回复

select top 20 a.ServiceNo, a.Name,b.Seq_Points,c.Points 
from TR_Driver a  
left join  
	(select u.Seq_Points,u.ServiceNo,u.CompanyID from T21_DriverOptInfoDaily_Points u 
		inner join 
			(select CompanyID from TR_Driver where ServiceNo=843391 and IsDelete=0) v on u.CompanyID=v.CompanyID
	 )b  on a.ServiceNo=b.ServiceNo and b.CompanyID=a.CompanyID 
left join TR_DriverPointInfo c  on a.ServiceNo=c.ServiceNo and c.CompanyID=b.CompanyID 
where a.IsDelete=0 and DATEDIFF(SECOND,'2014/2/25 10:00:00',b.Data_Dt)=0 and DATEDIFF(SECOND,'2014/2/25 10:00:00',c.Data_Dt)=0  
order by Seq_Points
left join TR_DriverPointInfo c  on a.ServiceNo=c.ServiceNo and c.CompanyID=b.CompanyID 这段中 是 c.CompanyID=b.CompanyID 还是 c.CompanyID=a.CompanyID ?根据你需求而定!
發糞塗牆 2014-02-25
  • 打赏
  • 举报
回复
不加索引的前提下看看这句
SELECT TOP 20
        a.ServiceNo ,
        a.Name ,
        b.Seq_Points ,
        c.Points
FROM    ( SELECT    *
          FROM      TR_Driver
          WHERE     ServiceNo = 843391
                    AND IsDelete = 0
        ) a
        LEFT JOIN T21_DriverOptInfoDaily_Points b ON a.ServiceNo = b.ServiceNo
                                                     AND b.CompanyID = a.CompanyID
        LEFT JOIN TR_DriverPointInfo c ON a.ServiceNo = c.ServiceNo
                                          AND c.CompanyID = b.CompanyID
WHERE   a.IsDelete = 0
        AND b.Data_Dt = DATEADD(SECOND, 0, '2014/2/25 10:00:00')
        AND c.Data_Dt = DATEADD(SECOND, 0, '2014/2/25 10:00:00')
        AND b.CompanyID = a.companyid
ORDER BY Seq_Points
  • 打赏
  • 举报
回复
简化成这样呢:
select top 20 a.ServiceNo, a.Name,b.Seq_Points,c.Points 
from TR_Driver a  
left join  T21_DriverOptInfoDaily_Points b 
       on a.ServiceNo=b.ServiceNo and b.CompanyID=a.CompanyID 
          and a.ServiceNo=843391 and a.IsDelete=0
left join TR_DriverPointInfo c 
       on a.ServiceNo=c.ServiceNo and c.CompanyID=b.CompanyID 

where a.IsDelete=0 and b.Data_Dt='2014/2/25 10:00:00'
 and c.Data_Dt='2014/2/25 10:00:00' 

 order by Seq_Points
专注or全面 2014-02-25
  • 打赏
  • 举报
回复

T21_DriverOptInfoDaily_Points的Data_Dt上创建聚集索引(没有的话)
TR_Driver的ServiceNo上创建索引(没有的话)
TR_Driver的CompanyID上创建索引(没有的话)
TR_DriverPointInfo的TR_DriverPointInfo创建索引(没有的话)
改写sql
select top 20 a.ServiceNo, a.Name, b.Seq_Points, c.Points
  from TR_Driver a
  left join T21_DriverOptInfoDaily_Points b on a.ServiceNo = b.ServiceNo
                                           and b.CompanyID = a.CompanyID
  left join TR_DriverPointInfo c on a.ServiceNo = c.ServiceNo
                                and c.CompanyID = b.CompanyID
 where a.IsDelete = 0
   and b.Data_Dt= '2014/2/25 10:00:00'
   and c.Data_Dt= '2014/2/25 10:00:00'
   and b.CompanyID = a.CompanyID
   and a.ServiceNo = 843391
 order by Seq_Points
          
發糞塗牆 2014-02-25
  • 打赏
  • 举报
回复
执行计划贴上来看看
KeepSayingNo 2014-02-25
  • 打赏
  • 举报
回复
试试这个

select top 20 a.ServiceNo, a.Name,b.Seq_Points,c.Points from 
(select * from TR_Driver where ServiceNo=843391 and IsDelete=0) a  left join  T21_DriverOptInfoDaily_Points b 
on a.ServiceNo=b.ServiceNo and b.CompanyID=a.CompanyID 
left join TR_DriverPointInfo c 
on a.ServiceNo=c.ServiceNo and c.CompanyID=b.CompanyID 
where a.IsDelete=0 and DATEDIFF(SECOND,'2014/2/25 10:00:00',b.Data_Dt)=0 
 and DATEDIFF(SECOND,'2014/2/25 10:00:00',c.Data_Dt)=0 
 order by Seq_Points

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧