ado-712642 (8/14/2014)
Is there a different way I could do a query like this:SELECT ID FROM PropertiesFreeholdAndCondos WHERE lattlongchecked = 1 AND Latitude IS NOT NULL AND Longitude IS NOT NULL AND ( Geometry::STGeomFromText('MULTIPOLYGON(((-79.63285446166992 43.5662751531822,-79.6457290649414 43.57498126377117,-79.63620185852051 43.58300220875825,-79.6347427368164 43.581634371234145,-79.63259696960449 43.58119914368649,-79.63062286376953 43.58101261663148,-79.62882041931152 43.580826088998585,-79.62727546691895 43.58032867915232,-79.62564468383789 43.57983126519669,-79.62495803833008 43.57902295875415,-79.62367057800293 43.578276820098864,-79.62221145629883 43.578028105159085,-79.62152481079102 43.57796592626361,-79.6208381652832 43.57790374730393,-79.62015151977539 43.577530672197476,-79.63285446166992 43.5662751531822)))', 4326).STIntersects(Geometry::STGeomFromText('POINT(' + CAST(Longitude AS VARCHAR(20)) + ' ' + CAST(Latitude AS VARCHAR(20)) + ')', 4326))=1) AND Sale_Lease IN ('Sale')
The speed of a similar query that doesn't have to use the Geometry runs in split seconds while this one takes 5 seconds every time even if it's pulling back much less records. Does the Geometry query take this long usually, is there anything different I can do?
There is in fact quite some room for improvements here but before one can properly suggest any changes you must respond to anthony.green's request on supplying additional information such as DDL, Sample Data etc.
😎
BTW: First thoughts when looking at your code; Filtered index, Calculated column, GEOGRAPHY instead of GEOMETRY, Point instead of STGeomFromText.