Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1439

How to use hint in the update statement

Show all headers | View raw

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 table that keeps some 8+ million of points. I need to calculate and set the distance 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 are marked with -1.
--
So, I have following:
create table XPOINTS ( 
  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 = 1 
where ID IN (
  select ID 
  from XPOINTS p
  ,    HWY
  where HWY.GEOG.STDistance( p.GP ) < 1609
)
go
update XPOINTS set DIST = 2
where ID IN (
  select ID 
  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 sql to use it?

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

Thanks in advance.

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

How to use hint in the update statement migurus <migurus@yahoo.com> - 2013-04-09 11:27 -0700
  Re: How to use hint in the update statement Erland Sommarskog <esquel@sommarskog.se> - 2013-04-09 22:38 +0200

csiph-web