复杂语句的优化

Question

各位大侠，请问如下SQL语句如何优化呢？
表结构是：
表 tb1_batchesLog
logId (nvarchar(20),not null)
batchid (nvarchar(20), null)
batchName (nvarchar(100),null)
jobid ((nvarchar(20),null))
serverid (int ,null)
status ((nvarchar(100),null))
workflow (nvarchar(100),null),
ImageCount (int,null)
createdatetime (datetime,null)
lastdatetime(datetime,null)
comments (nvarchar(400),null)
表 tb1_jobs
jobid (pk,nvarchar(20),not null)
jobname (nvarchar(100),null)
jobpath (nvarchar(100),null)
Temp;ate (nvarchar(100),null)
BatchAuditPercent (nvarchar(10),null)
PassingRate (decimal(18,2),null)
lastdatetime (datetime,null)
createdatetime (datetime,null)
表 tb1_serversinfo
serverid (pk,int,not null)
servername (nvarchar(100),null)
ipaddress (nvarchar(100),null)
comments (nvarchar(100),null)
现在我查询的是这个Batch的完成的时间差即开始工作到结束这个Batch所花费的时间。
select c.LogId,d.JobName,c.BatchId,c.BatchName,e.ServerName,c.Status ,c.Workflow,c.ImageCount,c.LoadDateTime,c.LastDateTime,
                        LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )/86400))+'D '+
                        LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )%86400/3600))+'H '+
                        LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )%86400%3600/60))+'M ' 
                       as UseTime,
                       c.Comments 
                        from  
                            (select a.LogId,a.BatchId,a.BatchName,a.JobId,a.ServerId,a.Status, 
                            a.Workflow,a.ImageCount,b.LoadDateTime,a.LastDateTime,a.Comments from dbo.tbl_BatchesLog a  
                            inner join  
(select LogId,min(LastDateTime) LoadDateTime, max(LastDateTime) lastDatetime 
from tbl_BatchesLog 
group by LogId) b 
                            on a.LogId=b.LogId and a.LastDateTime=b.lastDatetime) c
                        Inner join tbl_Jobs d On c.JobId=d.JobId
                        Inner join  tbl_ServersInfo e On c.ServerId=e.ServerId and d.JobName = '32672_Baptisms'and e.ServerName ='YY17_4'and convert(nvarchar(10),c.LastDateTime,120) ='2010-10-22'
请问这个语句应该如何优化呢？谢谢。在线等。

Answer 1

各位大侠，请问如下SQL语句如何优化呢？
表结构是：
表 tb1_batchesLog
logId (nvarchar(20),not null)  --log  id 号
batchid (nvarchar(20), null)   --Batch id号
batchName (nvarchar(100),null)  --Batch Name信息
jobid ((nvarchar(20),null))    
serverid (int ,null)
status ((nvarchar(100),null))   --每个Batch的各种状态 即Key AuditPosted Finished
workflow (nvarchar(100),null),   --Batch的工作流程 即 Key|Audit010 或 Check|Audit010
ImageCount (int,null)  --图片的数量
createdatetime (datetime,null) --Batch的创建时间
lastdatetime(datetime,null)  --Batch最后在那个状态的时间
comments (nvarchar(400),null)--备注
表 tb1_jobs
jobid (pk,nvarchar(20),not null) 
jobname (nvarchar(100),null)
jobpath (nvarchar(100),null) --Job的路径
Template (nvarchar(100),null) --模板的路径
BatchAuditPercent (nvarchar(10),null) --Batch审核率
PassingRate (decimal(18,2),null) --Batch通过率
lastdatetime (datetime,null) --Job最后状态的时间
createdatetime (datetime,null)--创建Job的时间
表 tb1_serversinfo
serverid (pk,int,not null)
servername (nvarchar(100),null)
ipaddress (nvarchar(100),null)
comments (nvarchar(100),null)
现在我查询的是这个Batch的完成的时间差即开始工作到结束这个Batch所花费的时间。
select c.LogId,d.JobName,c.BatchId,c.BatchName,e.ServerName,c.Status ,c.Workflow,c.ImageCount,c.LoadDateTime,c.LastDateTime,
  LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )/86400))+'D '+
  LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )%86400/3600))+'H '+
  LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )%86400%3600/60))+'M '  
  as UseTime,
  c.Comments  
  from   
  (select a.LogId,a.BatchId,a.BatchName,a.JobId,a.ServerId,a.Status,  
  a.Workflow,a.ImageCount,b.LoadDateTime,a.LastDateTime,a.Comments from dbo.tbl_BatchesLog a   
  inner join   
(select LogId,min(LastDateTime) LoadDateTime, max(LastDateTime) lastDatetime  
from tbl_BatchesLog  
group by LogId) b  
  on a.LogId=b.LogId and a.LastDateTime=b.lastDatetime) c
  Inner join tbl_Jobs d On c.JobId=d.JobId
  Inner join tbl_ServersInfo e On c.ServerId=e.ServerId and d.JobName = '32672_Baptisms'and e.ServerName ='YY17_4'and convert(nvarchar(10),c.LastDateTime,120) ='2010-10-22'得到结果:
LG201010200948359821 32672_Baptisms BS201010200948359827 646987_2_000_L56170_283-PET-2-65_002 YY17_4 Finished Key|Audit010 5 2010-10-20 09:48:35.987 2010-10-22 08:37:20.000 1D 22H 48M 
LG201010201020337085 32672_Baptisms BS201010201020337083 646994_2_000_L56170_283-PET-2-72_001_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-20 10:20:40.557 2010-10-22 08:53:08.000 1D 22H 32M 
LG201010210915335448 32672_Baptisms BS201010210915335283 646970_2_000_L56170_283-PET-2-48_001_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-21 09:16:01.233 2010-10-22 08:10:56.000 0D 22H 54M 
LG201010200950338244 32672_Baptisms BS201010200950338093 646990_2_000_L56170_283-PET-2-68_002_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-20 09:50:33.820 2010-10-22 08:16:08.000 1D 22H 25M 
LG201010200925172618 32672_Baptisms BS201010200925172456 646980_2_000_L56170_283-PET-2-58_001_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-20 09:25:17.260 2010-10-22 08:29:38.000 1D 23H 4M 
LG201010200950337779 32672_Baptisms BS201010200950337621 646989_2_000_L56170_283-PET-2-67_004_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-20 09:50:33.770 2010-10-22 08:54:38.000 1D 23H 4M 
LG201010191732154104 32672_Baptisms BS201010191732154101 646701_2_000_GLB5617_283-SPE-2-3_002_1st YY17_4 Finished Check|Audit010 4 2010-10-19 17:32:15.413 2010-10-22 08:39:05.000 2D 15H 6M 
LG201010210916013412 32672_Baptisms BS201010210916012783 646970_2_000_L56170_283-PET-2-48_004_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-21 09:16:01.363 2010-10-22 08:19:26.000 0D 23H 3M 
LG201010200948359660 32672_Baptisms BS201010200948359669 646986_2_000_L56170_283-PET-2-64_004_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-20 09:48:35.970 2010-10-22 08:23:08.000 1D 22H 34M 
LG201010191720058063 32672_Baptisms BS201010191720057751 646644_2_000_GLB5617_283-PAU-2-5_1st YY17_4 Finished Check|Audit010 2 2010-10-19 17:20:05.790 2010-10-22 08:53:05.000 2D 15H 33M 
LG201010201028250603 32672_Baptisms BS201010201028250443 649737_2_000_GL56170_283-CLP-2-4_009 YY17_4 Finished Key|Audit010 5 2010-10-20 10:28:25.053 2010-10-22 09:02:50.000 1D 22H 34M 
LG201010200925173234 32672_Baptisms BS201010200925173072 646980_2_000_L56170_283-PET-2-58_004_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-20 09:25:17.320 2010-10-22 08:40:08.000 1D 23H 14M 
LG201010200925045110 32672_Baptisms BS201010200925044957 646979_2_000_L56170_283-PET-2-57_001_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-20 09:25:17.190 2010-10-22 08:53:07.000 1D 23H 27M 
LG201010200950336844 32672_Baptisms BS201010200950336685 646988_2_000_L56170_283-PET-2-66_004_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-20 09:50:33.680 2010-10-22 09:07:25.000 1D 23H 16M 
LG201010210931520162 32672_Baptisms BS201010210931519698 646971_2_000_L56170_283-PET-2-49_001_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-21 09:32:02.190 2010-10-22 09:08:55.000 0D 23H 36M 
LG201010200925171989 32672_Baptisms BS201010200925171988 646979_2_000_L56170_283-PET-2-57_003_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-20 09:25:17.203 2010-10-22 09:09:10.000 1D 23H 43M 
LG201010200930103494 32672_Baptisms BS201010200930103493 646982_2_000_L56170_283-PET-2-60_002_QC YY17_4 AuditPosted Key|Audit010 5 2010-10-20 09:30:10.353 2010-10-22 09:09:38.000 1D 23H 39M 
LG201010202012070689 32672_Baptisms BS201010202012070686 641375_2_000_GLB5617_283-SVR-2-2_001_1st YY17_4 Finished Check|Audit010 5 2010-10-20 20:12:07.083 2010-10-22 09:17:49.000 1D 13H 5M 
LG201010202012076465 32672_Baptisms BS201010202012076317 646436_2_000_GLB5617_283-CAT-2-1_002_1st YY17_4 Finished Check|Audit010 5 2010-10-20 20:12:07.640 2010-10-22 09:22:05.000 1D 13H 9M 
请问这个语句应该如何优化呢？谢谢。在线等。

Answer 2

1、检查有效索引，并添加。
2、增加硬件。（比如内存）
3、语句四表连接没有特殊需要处理的。join取max虽然性能差点，但结果集需要min(LastDateTime) 也没有办法。
4、考虑归档历史数据。（减少数据量）

Answer 3

LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )/86400))+'D '+
  LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )%86400/3600))+'H '+
  LTRIM(str(DateDiff(Second,c.LoadDateTime,c.LastDateTime )%86400%3600/60))+'M '  
上述部分用str函数有点多于，ltrim已经隐式转为字符。

Answer 4

将表连接改成 cross join