|Subject:||Re: OT - SQL Help needed...
|Date:||Sun, 4 Dec 2016 19:56:30 +1100
|From:||Tom Krieg <"tom krieg AT outlook.com">
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
> ,ALS.locality_name AS locality_alias
> 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.