Efficient SQL Geography wilcard name search for 20mill+ records

Question

We have a SQL 2008 database with 20million+ geoWe locations (and growing) , each location includes the standard name/address/Geography/ID/Etc columns.

We need a way to efficiently search through the records based on a distance but also a "contains" keyword via the full text index. The basic idea is we search for locations near us based on a max distance.

Right now when we search for full strings such as StarBucks within 1 mile the search returns in a few seconds. However if we search for "star" within 1 mile the search can sometimes take several min to return.

We have been playing around logic such as this :

DECLARE @geoSearchLocation GEOGRAPHY, @geoSearchPolygon GEOGRAPHY, @returncount smallint = 50
SET @geoSearchLocation = geography::Point(40.729047, -74.010086, 4326); --NYC
SET @geoSearchPolygon = geography::STGeomFromText('POLYGON((-74.015086 40.734047, 
                                         -74.015086 40.718047, 
                                         -74.005086 40.718047, 
                                         -74.005086 40.734047, 
                                         -74.015086 40.734047))', 4326);
SET @geoSearchLocation = geography::Point(40.729047, -73.010086, 4326);
SELECT TOP (100) --WITH TIES
*, gt.LocationGeog.STDistance(@geoSearchLocation) AS dist
FROM dbo.GeoLocation_Locations gt WITH (NOLOCK, INDEX(geolocation_HHHH128_sidx))
WHERE gt.LocationGeog.STIntersects(@geoSearchPolygon) = 1
ORDER BY gt.LocationGeog.STDistance(@geoSearchLocation)

This introduces dupes and other issues in the search however. We have also been trying to use the POWER forumlas we have found online.

We have other queries working great which are only based on distance or a certain category ID, those return in a under a second. The big problem is the wildcard string matches.

Does anyone have a awesome SQL or CLR proc which accepts name (wildcard support) and distance when dealing with 20million+ records ?

Right now we are very stuck :(

thanks in advance, Jeff

score 0 · Answer 1 · answered Aug 14 '12 at 13:09

Simple LIKE predicates will only work almost well if you are searching for 'star%' and that column has an index. Performance however will continue to degrade as the dataset gets larger. If you can set up Sql Server's Full-Text mechanism you will be better off, it's not hard and it is way faster than indexed LIKE's

Efficient SQL Geography wilcard name search for 20mill+ records

1 Answers1