sql语句优化问题

[qinjipan]

select a.*,b.a_key,b.a_name,c.p_name,d.g_name
,
f.t_province,f.t_mark,f.t_ip,f.t_brand,
f.t_network,f.t_nettype,f.t_system,f.t_screen
from jmp_order_20160502 a
left join jmp_app b on a.o_app_id=b.a_id
left join jmp_paymode c on c.p_id=a.o_paymode_id
left join jmp_goods d on d.g_id=a.o_goods_id and d.g_app_id=b.a_id and d.g_app_id=a.o_app_id
left join jumipay_device_2016.dbo.jmp_terminal
--(select * from jumipay_device_2016.dbo.jmp_terminal
--where t_id in( select max(t_id) as tt from jumipay_device_2016.dbo.jmp_terminal group by t_key))
f on f.t_key=a.o_term_key
where 1=1 and b.a_user_id='1'
and convert(varchar(10),a.o_ctime,120)>='2016-05-02' and convert(varchar(10),a.o_ctime,120)<='2016-05-25'
and a.o_state='1'
我left join中套的子查询执行效率很慢，数据库中才4w条数据，但是直接关联又不行因为有重复数据需要排除重复数据，直接用去重复也不行达不到效果，求大神帮忙解决一下谢谢。在线等。

[中国风]

时间 列上建上索引位置 以上改两个位置，日期列条件，left join

SELECT  a.* ,
        b.a_key ,
        b.a_name ,
        c.p_name ,
        d.g_name ,
        f.t_province ,
        f.t_mark ,
        f.t_ip ,
        f.t_brand ,
        f.t_network ,
        f.t_nettype ,
        f.t_system ,
        f.t_screen
FROM    jmp_order_20160502 a
        LEFT JOIN jmp_app b ON a.o_app_id = b.a_id
        LEFT JOIN jmp_paymode c ON c.p_id = a.o_paymode_id
        LEFT JOIN jmp_goods d ON d.g_id = a.o_goods_id
                                 AND d.g_app_id = b.a_id
                                 AND d.g_app_id = a.o_app_id
        LEFT JOIN (SELECT *,RN=ROW_NUMBER()OVER(PARTITION BY t_key ORDER BY t_id DESC) FROM jumipay_device_2016.dbo.jmp_terminal) AS f ON f.t_key = a.o_term_key AND f.RN=1
WHERE   1 = 1
        AND b.a_user_id = '1'
        AND a.o_ctime >= '2016-05-02'
        AND a.o_ctime < '2016-05-26'--+1
        AND a.o_state = '1';  
		

[tcmakebest]

日期转字符串会影响性能, 改成字符串转日期