sql语句优化

Question

数据库是sqlserver2005,从某张表tbData中查询数据并排重  select ID,flight_no,passenger_cn_name,passenger_en_name,credent_no,Flight_Time, flight_takeoff_place_code,flight_arrive_place_code from dbo.tbData  where ID in (select max(id) from dbo.tbData where Flight_Date='2012/1/31 0:00:00' AND (Flight_Code='HAK' or Flight_Code='SYX') group by Passenger_EN_Name,Credent_No)上面语句执行用了2-3分钟，tbData表数据量420万，如何优化这个语句

Answer 1

--try
select a.ID,a.flight_no,a.passenger_cn_name,
       a.passenger_en_name,a.credent_no,a.Flight_Time, 
       a.flight_takeoff_place_code,a.flight_arrive_place_code
from dbo.tbData a,
            (select max(id) ID from dbo.tbData 
             where Flight_Date='2012/1/31 0:00:00' 
             AND (Flight_Code='HAK' or Flight_Code='SYX') 
             group by Passenger_EN_Name,Credent_No)b
where a.id=b.id

Answer 2

flight_no和Flight_Date建了索引，语句里面两个聚集索引各占用45%，我怀疑有几个地方造成效率低：
1、or是全盘扫描
2、select max(id) ID from dbo.tbData 
             where Flight_Date='2012/1/31 0:00:00' 
             AND (Flight_Code='HAK' or Flight_Code='SYX')
也造成效率低，能否从临时表来替换表变量

Answer 3

2、select max(id) ID from dbo.tbData  
  where Flight_Date='2012/1/31 0:00:00'  
  AND (Flight_Code='HAK' or Flight_Code='SYX')
也造成效率低，能否从临时表来替换表变量
可以考虑
select max(id) ID into #T from dbo.tbData  
  where Flight_Date='2012/1/31 0:00:00'  
  AND (Flight_Code='HAK' or Flight_Code='SYX')
create index index_name on #T(ID)go
然后再关联试下既然你想到了
不妨可以试下

Answer 4

在Table后加上 with(no lock).

Answer 5

是不是先创建一个临时表#T，然后从源表和#T中关联ID
能否给个完整的例子，这条语句估计都要执行很久啊？

Answer 6

select max(id) ID into #T from dbo.tbData   
  where Flight_Date='2012/1/31 0:00:00'   
  AND (Flight_Code='HAK' or Flight_Code='SYX')
create index index_name on #T(ID)

Answer 7

我把数据导出到另外一台测试机器上面，执行语句很快。用sqlserver profiler工具查看了原来数据库一下，数据库的写操作非常频繁，这样对数据库的IO开销很大，可不可以在同一台机器上做该数据库的读写分离？

Answer 8

如果你允许脏读
读的时候用未提交读隔离级别
或者查询时候用nolock表提示

Answer 9

看到你sql语句里的三字码，我自己都想跳

Answer 10

增加了索引，
CREATE NONCLUSTERED INDEX IX_Include_data  ON tbFlight_data_chengxing (Flight_Date,Flight_TakeOff_Place_Code)  <-- 组合索引用于做 Flight_Date='2012/1/31 0:00:00' AND Flight_TakeOff_Place_Code='HAK' 这个查询的优化
    INCLUDE (Passenger_EN_Name,Credent_No,ID) <-- 覆盖 Passenger_EN_Name,Credent_No,ID 列，用于 group 和 ID查询和Max函数操作的优化
查询语句如下：SET STATISTICS IO ON
SET STATISTICS TIME ONselect a.ID,a.flight_no,a.passenger_cn_name,
       a.passenger_en_name,a.credent_no,a.Flight_Time, 
       a.flight_takeoff_place_code,a.flight_arrive_place_code
from dbo.tbFlight_data_chengxing a,
            (select max(id) ID from dbo.tbFlight_data_chengxing 
             where Flight_Date='2012/1/31 0:00:00' 
             AND (flight_arrive_place_code='HAK' or  flight_arrive_place_code='SYX')
             group by Passenger_EN_Name,Credent_No)b   
where a.id=b.idSET STATISTICS IO OFF
SET STATISTICS TIME OFF执行时间在8-10秒之间