August 14, 2014 at 12:21 am
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?
August 14, 2014 at 1:03 am
What's even more weird is when I put in the ORDER BY Field it takes even longer to pull the results back. There has got to be a better way 🙂
August 14, 2014 at 1:08 am
Please post the table definitions, index definitions and the actual execution plan for the query. There is a link in my signature on posting code and data for the best help if you get stuck. With that information we can see what is happening on your server for that particular query.
As for the order by, it will take longer. There is no default order in SQL, it will bring back results in any order it wishes. If you need the results in a specific order, you have to order it via order by which is an additional operation for SQL to do and hence increase the time it takes to run the query.
August 17, 2014 at 1:47 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply