Need help speeding up a Spatial query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help speeding up a Spatial query

Author

Topic

ED_KING
Starting Member

7 Posts

Posted - 2010-04-07 : 18:30:38

Hey guys this query on a very quick SQL server takes about 19 days to complete on the table IP2LocationAllData (6 Million rows)!!

I'm kind of stuck.. any ideas? I'm trying to find the nearest lat long to another table with about 2 Million rows of lat and longs for time zone's.

--------------------------

DECLARE @ipID int
DECLARE @Lat float
DECLARE @Long float
DECLARE @cc varchar(2)
DECLARE @Location GEOGRAPHY

DECLARE IPCursor CURSOR FAST_FORWARD FOR
Select ipID, ipLatitude, ipLongitude, countryshort
FROM IP2LocationAllData with(nolock) where ipLatitude <> 0 AND ipLongitude <> 0 AND ipTimeZoneID IS NULL

OPEN IPCursor

FETCH NEXT FROM IPCursor
INTO @ipID, @Lat, @Long, @cc

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Location=GEOGRAPHY::Point(@Lat,@Long,4326)

UPDATE IP2LocationAllData SET ipTimeZoneID = (
SELECT TOP 1 TimeZone FROM tblTimeZoneLatLong WITH(INDEX(geoindex1))
WHERE CountryCode = @cc AND Location.STDistance(@Location) < 15000
ORDER BY Location.STDistance(@Location))
WHERE ipID=@ipID

FETCH NEXT FROM IPCursor
INTO @ipID, @Lat, @Long, @cc

END
CLOSE IPCursor
DEALLOCATE IPCursor

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 19:44:35

Try doing it without using a cursor. That's going to cause quite a bottleneck.

There are 10 types of people in the world, those that understand binary, and those that don't.

ED_KING
Starting Member

7 Posts

Posted - 2010-04-07 : 23:22:54

quote:
Originally posted by DBA in the making

Try doing it without using a cursor. That's going to cause quite a bottleneck.

There are 10 types of people in the world, those that understand binary, and those that don't.

Thanks for the reply. I did it without a cursor and its actually slower.. it seems its not the cursor but the query that tries to figure out the closest lat long to the @location lat and long. Here is my solution without a cursor.

DECLARE @i int = 1
DECLARE @iRwCnt int

DECLARE @ID int
DECLARE @ipID int
DECLARE @Lat float
DECLARE @Long float
DECLARE @cc varchar(2)
DECLARE @ipTimeZoneID
DECLARE @Location GEOGRAPHY

CREATE TABLE #TEMP11 (ID int identity(1,1),ipID int,ipLatitude float, ipLongitude float, countryshort varchar(2))

INSERT INTO #TEMP11 (ipID, ipLatitude, ipLongitude, countryshort)
Select ipID, ipLatitude, ipLongitude, countryshort
FROM IP2LocationAllData with(nolock,index(temp1)) where ipLatitude <> 0 AND ipLongitude <> 0 AND ipTimeZoneID IS NULL

SET @iRwCnt = @@ROWCOUNT

create clustered index idx_tmp on #TEMP11(ID) WITH FILLFACTOR = 100

WHILE @i <= @iRwCnt
BEGIN
SELECT @ID = ID, @ipID = ipID, @Lat = ipLatitude, @Long = ipLongitude, @cc = countryshort FROM #TEMP11 WHERE ID = @i

--Do work here

SET @Location=GEOGRAPHY::Point(@Lat,@Long,4326)

UPDATE IP2LocationAllData SET ipTimeZoneID = (
SELECT TOP 1 TimeZone FROM tblTimeZoneLatLong WITH(INDEX(geoindex1))
WHERE Location.STDistance(@Location) < 25000
ORDER BY Location.STDistance(@Location))
WHERE ipID=@ipID

SET @i = @i + 1
END
DROP TABLE #TEMP11

Asken
Starting Member

38 Posts

Posted - 2010-04-08 : 01:26:19

Well, you didn't actually do it "without a cursor". You just using cursor-like code doing pretty much the same thing. You need'll to use the select from the "select into #TEMP11" as the source for updating IP2LocationAllData to do a batch update without using a cursor. Start off by doing that and then start looking at the query plan. Also make sure that the stats are updated.

The clause you should look at is the UPDATE FROM. There are some examples of that at http://msdn.microsoft.com/en-us/library/aa260662(SQL.80).aspx.

Reporting & Analysis Specialist

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-08 : 02:55:24

Try this

UPDATE		ad
SET		ad.ipTimeZoneID = x.TimeZone
FROM		(
			SELECT	ipTimeZoneID,
				GEOGRAPHY::Point(ipLatitude, ipLongitude, 4326) AS Location,
				CountryShort
			FROM	IP2LocationAllData
		) AS ad
CROSS APPLY	(
			SELECT TOP(1)	tz.TimeZone
			FROM		tblTimeZoneLatLong AS tz WITH(INDEX(geoindex1))
			WHERE		tz.CountryCode = ad.CountryShort
					AND tz.Location.STDistance(ad.Location) < 15000
			ORDER BY	tz.Location.STDistance(ad.Location)
		) AS x(TimeZone)
WHERE		ad.ipLatitude <> 0
		AND ad.ipLongitude <> 0
		AND ad.ipTimeZoneID IS NULL

N 56°04'39.26"
E 12°55'05.63"

ED_KING
Starting Member

7 Posts

Posted - 2010-04-08 : 16:35:06

quote:

Originally posted by Peso

Try this

UPDATE		ad
SET		ad.ipTimeZoneID = x.TimeZone
FROM		(
			SELECT	ipTimeZoneID,
				GEOGRAPHY::Point(ipLatitude, ipLongitude, 4326) AS Location,
				CountryShort
			FROM	IP2LocationAllData
		) AS ad
CROSS APPLY	(
			SELECT TOP(1)	tz.TimeZone
			FROM		tblTimeZoneLatLong AS tz WITH(INDEX(geoindex1))
			WHERE		tz.CountryCode = ad.CountryShort
					AND tz.Location.STDistance(ad.Location) < 15000
			ORDER BY	tz.Location.STDistance(ad.Location)
		) AS x(TimeZone)
WHERE		ad.ipLatitude <> 0
		AND ad.ipLongitude <> 0
		AND ad.ipTimeZoneID IS NULL

N 56°04'39.26"
E 12°55'05.63"

Okay this looks interesting and i'm going to read up on CROSS APPLY thank you so much you guys.

Also i took out the ad.ipLatitude and ad.ipLongitude because their not part of the "AS ad".. i just included the the ipLatitude and ipLongitude <> NULL in the SELECT as ad portion... i'm going to understand it before giving it a shot since this looks promising.

cheers! i'll let you know

Asken
Starting Member

38 Posts

Posted - 2010-04-09 : 03:51:36

Caution on the cross and outer apply though. Please note that for each row it will apply the cross apply statement. If joins can be used it'll be much faster... not saying it applies to this specific case but as a general note.

Reporting & Analysis Specialist

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-09 : 09:43:20

In this case it's justified, since the logic is to get the nearest location [in another table] based on every single entry in source table.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources