Re: How do I speed up this query ? (1.7M table entries)
From: Seeker (seeker_at_mailinator.com)
Date: 06/19/04
- Next message: Seeker: "Re: SQL help needed"
- Previous message: bb_43_at_hotmail.com: "Re: slow sp from web, fast from QA"
- In reply to: Aaron W. West: "Re: How do I speed up this query ? (1.7M table entries)"
- Messages sorted by: [ date ] [ thread ]
Date: 19 Jun 2004 16:13:27 -0700
Alan,
A way to improve queries of this type is to consider the fact that if
an x and y are not in the bounding square (x-r, y-r)-(x+r, y+r), then
neither will they be in the circle bounded by radius r (the distance).
Filtering on the bounding square first allows us to use sargable
arguments, and limits the heavy calculation later. See amended proc
below.
I hope this helps you,
Keith, EBSCO
------------------------------------
create procedure NearPostcodes
@postcode varchar(8),
@radius int -- distance
as
begin
-- This method assumes a sufficiently small radius
-- is chosen, so that cost of populating the local
-- table var, is offset by the performance gain
-- of using sargable arguments.
declare @tblWithinSquare TABLE
(
PostCode varchar(8),
Northing int,
Easting int
)
declare @n int
declare @e int
declare @left int -- bounded square extents
declare @right int
declare @top int
declare @bottom int
-- center of square/circle
select
@n=Northing,
@e=Easting
from
Postcodes with (nolock)
where
Postcode = @postcode
-- consider adding a indexed CHECKSUM on the postcode column
-- it improves exact match string lookups
-- since the CBO can filter on a smaller integer index
-- prior to the expensive string compare
-- AND PostcodeCheckSum = CHECKSUM(@postcode)
-- bounding square extents
select
@left = @e - @radius
@right = @e + @radius
@bottom = @n - radius
@top = @n + radius
-- pre-filter post codes within bounding square
insert into @tblWithinSquare
(
Postcode,
Northing,
Easting
)
select
Postcode,
Northing,
Easting
from
Postcodes with (nolock)
where
-- recommend unique index on (Northing, Easting)
-- since two postcodes should always have different coordinates
Northing between @top and @bottom
and Easting between @left and @right
-- Now it is safe to do the heavy calculation on the
-- hopefully much smaller set of postcodes. (keep radius small)
-- Or even better stop now, :) and consider anything in the
-- bounding square as worthy of reporting.
select
Postcode,
(Northing - @n) * (Northing - @n)
+ (Easting - @e) * (Easting - @e)
as Distance
from
@tblWithinSquare
where
(Northing - @n) * (Northing - @n)
+ (Easting - @e) * (Easting - @e)
< (@radius * @radius)
order by
Distance,
Postcode
end
go
First apply the following efficient filter.
"Aaron W. West" <tallpeak@hotmail.NO.SPAM> wrote in message news:<ccucnVokeYzVfk7d38DK-g@speakeasy.net>...
> I was surprised no one mentioned rtree/spatial indexes (in a post).
> PostgreSQL, Oracle, and to some extent recent versions of MySQL (4.x) each
> have spatial index support using r-tree indexes.
>
> I found this library, but fear it may be hard to integrate with MS SQL:
>
> Spatial Index Library 0.4b
> http://freshmeat.net/projects/spatialindexlib/?branch_id=35217&release_id=105467
>
> I thought MS FoxPro was supposed to have some support for distance queries
> too, but not sure.
>
> My only other recommendation was to reduce the "resolution" of your search,
> if you don't need such accuracy. For example, create a table of 10,000 or
> 100,000 zip code areas (like US 5-digit zips), and a cross-referencing table
> to your 1.7 million row one, and run your query against the zip code area
> table instead. I would probably use rectangular areas, and assign all zips
> in that area the same area_id. Find the areas in the desired distance, then
> find all zips in those areas with a join.
>
> An article I found:
> Microsoft SQL Server: Future Plans for Supporting Spatial Data
> http://www.directionsmag.com/article.php?article_id=430
>
> I suppose you could install Cygwin and PostgreSQL and write an extended
> stored procedure in C to query a Postgresql table.... Ugh! It'd be easier to
> migrate to PostgreSQL, probably.
- Next message: Seeker: "Re: SQL help needed"
- Previous message: bb_43_at_hotmail.com: "Re: slow sp from web, fast from QA"
- In reply to: Aaron W. West: "Re: How do I speed up this query ? (1.7M table entries)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
