Update closest 3 stores based on stored procedure result

Question

Post reply

Update closest 3 stores based on stored procedure result

swilloughby

Grasshopper

Points: 14
More actions
April 3, 2008 at 7:18 am

#71537

Hello! I'm currently trying to update tblMaster which contains customers for a company (about 300k records). For each customer/record I want to update it with the closest 3 stores from tblStores (about 25 records). All records in tblMaster and tblStores have full address, city, state, zip, zip4 to include latitude/longitude.
I have a function in-place that will return a distance in miles if given 2 sets of latitude/longitude. What I'm stuck on is this...
What is the best/easiest/fastest way to (basically) take each record from tblMaster, run it against the 25 records from tblStores with my dbo.getDistance function (returns distance in miles), return the closest 3 stores within 50 miles, and update the record from tblMaster with store1, dist1, store2, dist2, store3, dist3? At a glance I'm thinking nested cursors but I really want to stay away from that if possible.
Thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Here is a query to get you the 3 closest stores for each customer:

Select TOP 3

S.store_id,

M.customer_id,

DistanceFunction(S.Lat, S.Long, M.Lat, M.Long) as distance

From

tblStores S Cross Join

tblMaster M

Where

DistanceFunction(S.Lat, S.Long, M.Lat, M.Long) <= 50

Order By

distance

You could store this in a temp table or table variable and then use it to do your update.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

swilloughby Grasshopper Points: 14 More actions · Answer 2

Here is what I have (after a long break). However I'm getting records coded with STORE_2 being closer than STORE_1. All records in both tables have LAT/LON. Any help in solving this or speeding it up would be greatly appreciated! Right now it runs roughly 5 minutes and has a massive result set.

USE [CUST]

GO

/****** Object: StoredProcedure [dbo].[spStoreAssign] Script Date: 06/13/2008 14:47:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET NOCOUNT ON

GO

-- =============================================

-- Author:

-- Create date:

-- Modified date:

-- Description:

-- =============================================

ALTER PROCEDURE [dbo].[spStoreAssign]

AS

CREATE TABLE #tblDistance

(

[STORENO] [char](4) NULL,

[ID] [char](10) NOT NULL,

[DIST] [numeric](16, 9) NULL

) ;

--ON [PRIMARY] ;

UPDATE CUST.tblMaster

SET STORE_1 = NULL,

STORE_2 = NULL,

DISTANCE_1 = NULL,

DISTANCE_2 = NULL ;

INSERT INTO #tblDistance ( STORENO, ID, DIST )

SELECT S.STORENO,

M.ID,

dbo.fnGetDistance(S.LATITUDE, S.LONGITUDE, M.LATITUDE,

M.LONGITUDE) AS DIST

FROM CUST.tblStores S ( NOLOCK ),

CUST.tblMaster M ( NOLOCK )

WHERE dbo.fnGetDistance(S.LATITUDE, S.LONGITUDE, M.LATITUDE,

M.LONGITUDE) <= 50

GROUP BY M.ID,

S.STORENO,

S.LATITUDE,

S.LONGITUDE,

M.LATITUDE,

M.LONGITUDE

ORDER BY M.ID,

DIST ;

UPDATE CUST.tblMaster

SET store_1 = match.STORENO,

distance_1 = match.DIST

FROM CUST.tblMaster tm,

( SELECT TOP 100 PERCENT

td.ID,

td.STORENO,

td.DIST

FROM #tblDistance td ( NOLOCK ),

CUST.tblMaster tm ( NOLOCK )

WHERE tm.ID = td.ID

ORDER BY DIST DESC

) match

WHERE tm.ID = match.ID ;

UPDATE CUST.tblMaster

SET store_2 = match.STORENO,

distance_2 = match.DIST

FROM CUST.tblMaster tm,

( SELECT TOP 100 PERCENT

td.ID,

td.STORENO,

td.DIST

FROM #tblDistance td ( NOLOCK ),

CUST.tblMaster tm ( NOLOCK )

WHERE tm.ID = td.ID

ORDER BY DIST DESC

) match

WHERE tm.ID = match.ID

AND match.STORENO <> tm.STORE_1 ;

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 3

I'd sure be willing to try if you'd post your distance function, the CREATE TABLE statements for the two tables, and attach the data for all 25 stores and a 100 customers using the format found in the article in the URL in my signature line below. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Wow, there is so much wrong here, it's hard to know where to begin. Still, if you are looking for a quick fix (which I suspect) then change this:

to this:

INSERT INTO #tblDistance ( STORENO, ID, DIST )

SELECT S.STORENO,

M.ID,

dbo.fnGetDistance(S.LATITUDE, S.LONGITUDE, M.LATITUDE,

M.LONGITUDE) AS DIST

FROM CUST.tblStores S ( NOLOCK )

Inner Join CUST.tblMaster M ( NOLOCK )

ON (M.Latitude between S.LATITUDE-'01:00:00' and S.LATITUDE+'01:00:00'

And M.Longitude between S.LONGITUDE-'01:00:00' and S.LONGITUDE+'01:00:00')

WHERE dbo.fnGetDistance(S.LATITUDE, S.LONGITUDE, M.LATITUDE,

M.LONGITUDE) <= 50

GROUP BY M.ID,

S.STORENO,

S.LATITUDE,

S.LONGITUDE,

M.LATITUDE,

M.LONGITUDE

ORDER BY M.ID,

DIST ;

Also, make sure that you have covering indexes on STORE(Lat,Lon,StoreNo) and CUSTOMER(Lat,Lon,ID).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]