Re: How do I speed up this query ? (1.7M table entries)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Aaron W. West (tallpeak_at_hotmail.NO.SPAM)
Date: 06/19/04

  • Next message: Aaron W. West: "Re: memory utilization" 
    Date: Fri, 18 Jun 2004 23:55:00 -0700

    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: Aaron W. West: "Re: memory utilization"

    Relevant Pages