Paradox Community

Items in pnews.paradox-client_server

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

Copyright © 2004