光谱机数据查询SQL优化

需要写一个查询，是光谱机上的数据，有两个表组成，第一个表叫Measurements，存储主要的信息，第二个表叫Results，存储每次测量，每种元素的含量，就是一条Measurements对应多条Results，现在需要做一个同步程序，做一个差异数据，以下是定义的数据。

select top 50 CONVERT(varchar(12) ,t1.DateTime_, 23) AS FDate,t1.ID1+’-’+convert(nvarchar(45),t1.ID) as KeyID,t1.ID,t1.ID1,t1.ID2,t2.Reported as FCVal,t3.Reported as FNiVal,t4.Reported as FCrVal,t5.Reported as FMnVal,t6.Reported as FCuVal,t7.Reported as FSiVal,t8.Reported as FSVal,t9.Reported as FPVal,t10.Reported as FNVal from
(select top 50 * from Measurements t1 where #t1.{ordercol}>’{keyinterid}’# and (t1.ID1 like ‘%-CP’ or t1.ID1 like ‘%-C’)) t1
inner join (select top 500 Measurement,Reported from Results where Component=‘C’ and Measurement>’{keyinterid}’# order by Measurement asc) t2 on t1.ID=t2.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Ni’ and Measurement>’{keyinterid}’ order by Measurement asc) t3 on t1.ID=t3.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Cr’ and Measurement>’{keyinterid}’ order by Measurement asc) t4 on t1.ID=t4.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Mn’ and Measurement>’{keyinterid}’ order by Measurement asc) t5 on t1.ID=t5.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Cu’ and Measurement>’{keyinterid}’ order by Measurement asc) t6 on t1.ID=t6.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Si’ and Measurement>’{keyinterid}’ order by Measurement asc) t7 on t1.ID=t7.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘S’ and Measurement>’{keyinterid}’ order by Measurement asc) t8 on t1.ID=t8.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘P’ and Measurement>’{keyinterid}’ order by Measurement asc) t9 on t1.ID=t9.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘N’ and Measurement>’{keyinterid}’ order by Measurement asc) t10 on t1.ID=t10.Measurement
order by t1.ID asc
程序化转变后，变为如下：
select top 50 * from (select top 50 CONVERT(varchar(12) ,t1.DateTime_, 23) AS FDate,t1.ID1+’-’+convert(nvarchar(45),t1.ID) as KeyID,t1.ID,t1.ID1,t1.ID2,t2.Reported as FCVal,t3.Reported as FNiVal,t4.Reported as FCrVal,t5.Reported as FMnVal,t6.Reported as FCuVal,
t7.Reported as FSiVal,t8.Reported as FSVal,t9.Reported as FPVal,t10.Reported as FNVal from
(select top 50 * from Measurements t1 where t1.ID1 like ‘%-CP’ and t1.ID>‘89024’) t1
inner join (select top 500 Measurement,Reported from Results where Component=‘C’ and Measurement>‘89024’ order by Measurement asc) t2 on t1.ID=t2.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Ni’ and Measurement>‘89024’ order by Measurement asc) t3 on t1.ID=t3.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Cr’ and Measurement>‘89024’ order by Measurement asc) t4 on t1.ID=t4.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Mn’ and Measurement>‘89024’ order by Measurement asc) t5 on t1.ID=t5.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Cu’ and Measurement>‘89024’ order by Measurement asc) t6 on t1.ID=t6.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Si’ and Measurement>‘89024’ order by Measurement asc) t7 on t1.ID=t7.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘S’ and Measurement>‘89024’ order by Measurement asc) t8 on t1.ID=t8.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘P’ and Measurement>‘89024’ order by Measurement asc) t9 on t1.ID=t9.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘N’ and Measurement>‘89024’ order by Measurement asc) t10 on t1.ID=t10.Measurement
order by t1.ID asc) rainsync1 order by rainsync1.ID
之前做过一个版本，是这样的：
select top 10000 * from (select top 50 CONVERT(varchar(12) ,t1.DateTime_, 23) AS FDate,t1.ID1+’-’+convert(nvarchar(45),t1.ID) as KeyID,t1.ID,t1.ID1,t1.ID2,t2.Reported as FCVal,t3.Reported as FNiVal,t4.Reported as FCrVal,t5.Reported as FMnVal,t6.Reported as FCuVal,
t7.Reported as FSiVal,t8.Reported as FSVal,t9.Reported as FPVal,t10.Reported as FNVal from Measurements t1
inner join (select top 500 Measurement,Reported from Results where Component=‘C’ and Measurement>‘89024’ order by Measurement asc) t2 on t1.ID=t2.Measurement and t1.ID1 like ‘%-CP’ and t1.ID>‘89024’
inner join (select top 500 Measurement,Reported from Results where Component=‘Ni’ and Measurement>‘89024’ order by Measurement asc) t3 on t1.ID=t3.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Cr’ and Measurement>‘89024’ order by Measurement asc) t4 on t1.ID=t4.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Mn’ and Measurement>‘89024’ order by Measurement asc) t5 on t1.ID=t5.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Cu’ and Measurement>‘89024’ order by Measurement asc) t6 on t1.ID=t6.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘Si’ and Measurement>‘89024’ order by Measurement asc) t7 on t1.ID=t7.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘S’ and Measurement>‘89024’ order by Measurement asc) t8 on t1.ID=t8.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘P’ and Measurement>‘89024’ order by Measurement asc) t9 on t1.ID=t9.Measurement
inner join (select top 500 Measurement,Reported from Results where Component=‘N’ and Measurement>‘89024’ order by Measurement asc) t10 on t1.ID=t10.Measurement
order by t1.ID asc) rainsync1 order by rainsync1.ID
执行时间1分钟还未结束，经过优化后，执行时间不到1秒。
做下记录，以防忘了。