大家帮忙优化一下。

Question

temp2表有10万条，kqda01表内有3万条，下面语句竟然要运行1分20秒，大家有没有好的办法优化一下？update kqda01  set b_time1=(  select min( time)  from temp2
 where   CardNo=cacc_cqk.CardNo and date=cacc_cqk.date 
and   cast(substring(temp2.time,1,2) as int)*60+cast(substring(temp2.time,4,2) as int)
<= cast(substring(temp2.sb_time1,1,2) as int)*60+cast(substring(temp2.sb_time1,4,2) as int)+last_time1 +90 and cast(substring(temp2.time,1,2) as int)*60+cast(substring(temp2.time,4,2) as int)
>=cast(substring(temp2.sb_time1,1,2) as int)*60+cast(substring(temp2.sb_time1,4,2) as int)-before_time1  

Answer 1

CAST(SUBSTRING(temp2.time, 1, 2) AS INT) * 60
                            + CAST(SUBSTRING(temp2.time, 4, 2) AS INT) <= CAST(SUBSTRING(temp2.sb_time1,
                                                              1, 2) AS INT)
                            * 60
                            + CAST(SUBSTRING(temp2.sb_time1, 4, 2) AS INT)
                            + last_time1 + 90
                            AND CAST(SUBSTRING(temp2.time, 1, 2) AS INT) * 60
                            + CAST(SUBSTRING(temp2.time, 4, 2) AS INT) >= CAST(SUBSTRING(temp2.sb_time1,
                                                              1, 2) AS INT)
                            * 60
                            + CAST(SUBSTRING(temp2.sb_time1, 4, 2) AS INT)
                            - before_time1
你这边时间怎么处理的？尝试用时间函数

Answer 2

update kqda01 set b_time1=( select min( time) from temp2
 where CardNo=cacc_cqk.CardNo and date=cacc_cqk.date  
and cast(substring(temp2.time,1,2) as int)*60+cast(substring(temp2.time,4,2) as int)
<= cast(substring(temp2.sb_time1,1,2) as int)*60+cast(substring(temp2.sb_time1,4,2) as int)+last_time1 +90 and cast(substring(temp2.time,1,2) as int)*60+cast(substring(temp2.time,4,2) as int)
>=cast(substring(temp2.sb_time1,1,2) as int)*60+cast(substring(temp2.sb_time1,4,2) as int)-before_time1   
where date>='2011-03-01' and date<='2011-03-31'
上面还有加时间段的，一个月的数据。可是为何才10万条要这么久

Answer 3

UPDATE  kqda01
SET     b_time1 = ( SELECT  MIN(time)
                    FROM    temp2
                    WHERE   CardNo = cacc_cqk.CardNo
                            AND date = cacc_cqk.date
                            AND CAST(SUBSTRING(temp2.time, 1, 2) AS INT) * 60
                            + CAST(SUBSTRING(temp2.time, 4, 2) AS INT) <= CAST(SUBSTRING(temp2.sb_time1,
                                                              1, 2) AS INT)
                            * 60
                            + CAST(SUBSTRING(temp2.sb_time1, 4, 2) AS INT)
                            + last_time1 + 90
                            AND CAST(SUBSTRING(temp2.time, 1, 2) AS INT) * 60
                            + CAST(SUBSTRING(temp2.time, 4, 2) AS INT) >= CAST(SUBSTRING(temp2.sb_time1,
                                                              1, 2) AS INT)
                            * 60
                            + CAST(SUBSTRING(temp2.sb_time1, 4, 2) AS INT)
                            - before_time1
                  )据我观察，year()和 month()两函数你看看能不能用上，你老截取很影响效率。