the following query causes pretty heavy load on server, , run every 60 seconds. finds rows in table not have lat/long data, , looks lat/long based on city , state, source , destination locations represented each row. right off bat, assume ltrim/rtrim functions slowing things down, , if so, easy remedy making sure data cleansed on way in. zip codes/geo database huge, , indexes, things pretty slow , cpu intensive (entirely possible i'm not creating indexes properly). advice welcome - if best thing somehow limit number of rows per execution of query, spread out load on time bit.
update t1 set t1.coordinateschecked = 1 , t1.fromlatitude = t2.latitude , t1.fromlongitude = t2.longitude , t1.tolatitude = t3.latitude , t1.tolongitude = t3.longitude loadsavail t1 left join zipcodes t2 on ltrim(rtrim(t1.fromcity)) = t2.cityname , ltrim(rtrim(t1.fromstate)) = t2.provinceabbr left join zipcodes t3 on ltrim(rtrim(t1.tocity)) = t3.cityname , ltrim(rtrim(t1.tostate)) = t3.provinceabbr t1.coordinateschecked = 0
Comments
Post a Comment