X-Received: by 10.224.160.65 with SMTP id m1mr8719606qax.2.1365532054814; Tue, 09 Apr 2013 11:27:34 -0700 (PDT)
X-Received: by 10.50.130.9 with SMTP id oa9mr2007480igb.3.1365532054768; Tue, 09 Apr 2013 11:27:34 -0700 (PDT)
Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!ca1no38470755qab.0!news-out.google.com!ef9ni18755qab.0!nntp.google.com!ca1no38470751qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-sqlserver
Date: Tue, 9 Apr 2013 11:27:34 -0700 (PDT)
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=173.55.225.235; posting-account=PG2dbQkAAADVILsQ0GhgAM6hZK18SIjs
NNTP-Posting-Host: 173.55.225.235
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <9cc8b542-800d-4221-9367-4ed17a42ea46@googlegroups.com>
Subject: How to use hint in the update statement
From: migurus <migurus@yahoo.com>
Injection-Date: Tue, 09 Apr 2013 18:27:34 +0000
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Xref: csiph.com comp.databases.ms-sqlserver:1439

I have a table that keeps spatial data of Los Angeles freeways, the data is=
 combined into one record and column type is geography. There is another ta=
ble that keeps some 8+ million of points. I need to calculate and set the d=
istance from each point to the freeway and keep it in the points table. The=
 distance attribute is actually 1, 2, 3, 4, 5, -1. That mark a point within=
 1 mile, or 2 mile etc. from the freeway. All points further than 5 miles a=
re marked with -1.
--
So, I have following:
create table XPOINTS (=20
  ID int not null primary key
, DIST int null
, GP geography null
)
create table HWY (
  ID int not null primary key
, GEOG geography null
)
-- i can't show data for HWY as it is huge one record
update XPOINTS set DIST =3D 1=20
where ID IN (
  select ID=20
  from XPOINTS p
  ,    HWY
  where HWY.GEOG.STDistance( p.GP ) < 1609
)
go
update XPOINTS set DIST =3D 2
where ID IN (
  select ID=20
  from XPOINTS p
  ,    HWY
  where HWY.GEOG.STDistance( p.GP ) < 1609*2
  and   p.DIST is not null
)
go
etc... for up to 5 miles, then I just update remaining nulls with -1.

I do have a spatial index on the HWY.GEOG and it is used when I just try to=
 run select of a point against GEOG.

The update statement plan does not show use of the index. How can I hint sq=
l to use it?

Also, any other ideas would be much appreciated, as I hate five updates run=
 sequentially.=20

Thanks in advance.