Sql Query
-
I have problem with this query.. it is taking too much time to fetch the records .. can anybody tell me general solution on this. is their any problem in any join ? i have main problem in the following condition ( (b.cenid = c.cenidr and b.polyid = c.polyidr)or(b.cenid = c.cenidl and b.polyid = c.polyidl)) if only one condition is used then it gives result very fast so help me .. SELECT a.laname, a.cfcc, b.cenid, b.polyid, c.tlid, d.fename, d.fetype, d.frlong as d_frlong, d.frlat as d_frlat, d.tolong as d_tolong, d.tolat as d_tolat, p.polylong, p.polylat, p.water ,e.rtsq, e.long1, e.lat1, e.long2, e.lat2, e.long3, e.lat3, e.long4, e.lat4, e.long5, e.lat5, e.long6, e.lat6, e.long7, e.lat7, e.long8, e.lat8, e.long9, e.lat9, e.long10, e.lat10 FROM (((( ( rt7 as a inner join rt8 as b on a.land = b.land ) inner join rti c on ( (b.cenid = c.cenidr and b.polyid = c.polyidr)or(b.cenid = c.cenidl and b.polyid = c.polyidl)) ) inner join rt11 as d on c.tlid = d.tlid) inner join rtp as p on p.cenid = b.cenid and p.polyid = b.polyid )) left join rt2 as e on d.tlid = e.tlid WHERE a.laname like 'Mill Pond%' and (( 3958.682 ) * ( acos( sin( (d.frlat) * 0.01745 ) * sin( ( $latc ) * 0.01745 ) + cos( (d.frlat) * 0.01745 ) * cos( ( $latc) * 0.01745 ) * cos( (d.frlong - ($longc) ) * 0.01745 ) ) )) <=$mile ORDER BY a.laname
-
I have problem with this query.. it is taking too much time to fetch the records .. can anybody tell me general solution on this. is their any problem in any join ? i have main problem in the following condition ( (b.cenid = c.cenidr and b.polyid = c.polyidr)or(b.cenid = c.cenidl and b.polyid = c.polyidl)) if only one condition is used then it gives result very fast so help me .. SELECT a.laname, a.cfcc, b.cenid, b.polyid, c.tlid, d.fename, d.fetype, d.frlong as d_frlong, d.frlat as d_frlat, d.tolong as d_tolong, d.tolat as d_tolat, p.polylong, p.polylat, p.water ,e.rtsq, e.long1, e.lat1, e.long2, e.lat2, e.long3, e.lat3, e.long4, e.lat4, e.long5, e.lat5, e.long6, e.lat6, e.long7, e.lat7, e.long8, e.lat8, e.long9, e.lat9, e.long10, e.lat10 FROM (((( ( rt7 as a inner join rt8 as b on a.land = b.land ) inner join rti c on ( (b.cenid = c.cenidr and b.polyid = c.polyidr)or(b.cenid = c.cenidl and b.polyid = c.polyidl)) ) inner join rt11 as d on c.tlid = d.tlid) inner join rtp as p on p.cenid = b.cenid and p.polyid = b.polyid )) left join rt2 as e on d.tlid = e.tlid WHERE a.laname like 'Mill Pond%' and (( 3958.682 ) * ( acos( sin( (d.frlat) * 0.01745 ) * sin( ( $latc ) * 0.01745 ) + cos( (d.frlat) * 0.01745 ) * cos( ( $latc) * 0.01745 ) * cos( (d.frlong - ($longc) ) * 0.01745 ) ) )) <=$mile ORDER BY a.laname
Writing a GIS System? From my experience trig functions are expensive so you should cut down the amount of data that has to be calculated. To do this, recreate your existing query (without the trig functions) as a subquery, replacing the trig functions with a very basic bounding box - this will cut out a vast quantity of data quickly. You are now left with just a subset of the original data, your outer query can now perform the trig operations on a much smaller set of data. As for the joins, I'm assuming that these id fields are all primary keys and indexed by default, you may want to index the foreign key side of them to see if it speeds up the query also. However, from experience, I'd say the trig functions are where your bottle neck is.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
-
I have problem with this query.. it is taking too much time to fetch the records .. can anybody tell me general solution on this. is their any problem in any join ? i have main problem in the following condition ( (b.cenid = c.cenidr and b.polyid = c.polyidr)or(b.cenid = c.cenidl and b.polyid = c.polyidl)) if only one condition is used then it gives result very fast so help me .. SELECT a.laname, a.cfcc, b.cenid, b.polyid, c.tlid, d.fename, d.fetype, d.frlong as d_frlong, d.frlat as d_frlat, d.tolong as d_tolong, d.tolat as d_tolat, p.polylong, p.polylat, p.water ,e.rtsq, e.long1, e.lat1, e.long2, e.lat2, e.long3, e.lat3, e.long4, e.lat4, e.long5, e.lat5, e.long6, e.lat6, e.long7, e.lat7, e.long8, e.lat8, e.long9, e.lat9, e.long10, e.lat10 FROM (((( ( rt7 as a inner join rt8 as b on a.land = b.land ) inner join rti c on ( (b.cenid = c.cenidr and b.polyid = c.polyidr)or(b.cenid = c.cenidl and b.polyid = c.polyidl)) ) inner join rt11 as d on c.tlid = d.tlid) inner join rtp as p on p.cenid = b.cenid and p.polyid = b.polyid )) left join rt2 as e on d.tlid = e.tlid WHERE a.laname like 'Mill Pond%' and (( 3958.682 ) * ( acos( sin( (d.frlat) * 0.01745 ) * sin( ( $latc ) * 0.01745 ) + cos( (d.frlat) * 0.01745 ) * cos( ( $latc) * 0.01745 ) * cos( (d.frlong - ($longc) ) * 0.01745 ) ) )) <=$mile ORDER BY a.laname
Try using UNION to solve the INNER JOIN condition. Here's how it should be like: SELECT ... FROM ...
INNER JOIN rtiC ON b.cenid = c.cenidr AND b.polyid = c.polyidr...UNIONSELECT ... FROM ...INNER JOIN rtiC ON b.cenid = c.cenidl AND b.polyid = c.polyidl...