ＳＱＬ ｉｍｐｒｏｖｅ

 

-----低效率

select a.id,a.agentName,a.environment, b.monitorTime, b.FreeMemory, b.TotalMemory, b.PendingMsgCnt, b.AvgMsgRate, b.Status

 from ddi_client_inventory a, ddi_monitor_clients b
 where a.id = b.id and b.monitorTime <='2012-05-22 14:27:11.0' and  b.monitorTime >='2012-05-10 14:27:11.0'  
 and a.agentName in ( 'QPS-SMC3-BSKT-SIT-REALTIME', 'ALI-QPS-DELTA_ISCITIRESTRICTED_SIT', 'RDS3-SIT', 'SECORE_EMC_EMEA_SIT', 'DMS_DEV_EQUITY',   'SMC_DQ_PROD-CMO', 'OPS-UAT-TML', 'QPS-SMC-OCEAN-PROD-REALTIME-CORP', 'QPS-SMC-EMEA-LNProdCustom11-CUSTOM', 'GEM-SMC-PROD')
 order by b.monitortime ,a.agentName   
 

 

--------高效率

 select monitor.*, inventory.*  from
 (
  select b.id, b.monitorTime, b.FreeMemory, b.TotalMemory, b.PendingMsgCnt, b.AvgMsgRate, b.Status
     from ddi_monitor_clients b
     where b.monitorTime <='2012-05-22 14:27:11.0' and  b.monitorTime >='2012-05-10 14:27:11.0'  
 ) monitor left join
 (select a.id,a.agentName,a.environment
     from ddi_client_inventory a
     where a.agentName in ( 'QPS-SMC3-BSKT-SIT-REALTIME', 'ALI-QPS-DELTA_ISCITIRESTRICTED_SIT', 'RDS3-SIT', 'SECORE_EMC_EMEA_SIT', 'DMS_DEV_EQUITY',  'OPS-UAT-TML', 'QPS-SMC-OCEAN-PROD-REALTIME-CORP', 'QPS-SMC-EMEA-LNProdCustom11-CUSTOM', 'GEM-SMC-PROD')
 ) inventory on monitor.id = inventory.id
 
 order by monitor.monitortime ,inventory.agentName   

描述:根据表t1.t2的ID关联，将表2的字段2更新到表1的字段1上

语法:   update t1 set 字段1=t2.字段2 from t2 where t1.ID=t2.ID

 update ddi_client_inventory set    expectedmsgRate =b.expectedmsgRate from ddi_client_inventory_backup b    where ddi_client_inventory.agentname = b.agentname