Subject: | Re: OT - SQL Help needed...
| Date: | Sun, 4 Dec 2016 19:56:30 +1100
| From: | Tom Krieg <"tom krieg AT outlook.com">
| Newsgroups: | pnews.paradox-client_server
|
You sure were right about indexes, Liz. I had one query that returned 62
addresses (all the apartments in a very big apartment building) and it
took 28 seconds! Went into the database to try and tweak it a bit and
discovered that the ALS table did not have an index on locality name.
Created one and the query went from 28 seconds to 46ms.
I sometimes do wonder about the forward-planning ability of some of
these government database designers, though. I de-normalized a couple of
tables by including the NAME (and an index) in some records to avoid
joins on millions of records and queries went from minutes to milliseconds.
On 3/12/2016 2:17 PM, Tom Krieg wrote:
> SELECT ADD.address_detail_uid
> ,ADD.lot_number
> ,ADD.level_number
> ,ADD.level_number_suffix
> ,ADD.flat_number,
> ,ADD.number_first
> ,ADD.number_first_suffix
> ,ADD.street_name
> ,ADD.street_type
> ,LOC.locality_uid
> ,LOC.locality_name
> ,LOC.state_abbrev
> ,ADD.address_postcode
> ,ALS.locality_name AS locality_alias
> ,ALS.locality_alias_uid
> FROM gnaf.adb_address_detail ADD
> LEFT JOIN gnaf.adb_locality LOC
> ON ADD.locality_uid = LOC.locality_uid
> LEFT JOIN gnaf.adb_locality_alias ALS
> ON ALS.locality_uid = LOC.locality_uid
> AND ALS.locality_name = 'BALACLAVA'
> WHERE ADD.street_name = 'INKERMAN'
> AND ADD.number_first = 33
> AND ADD.street_type LIKE 'ST%'
> AND (ALS.locality_name = 'BALACLAVA' OR
> LOC.locality_name = 'BALACLAVA');
--
To send me an email, remove the spaces.
|