Paradox Community

Items in pnews.paradox-client_server

Subject:Not quie sure how to do this
Date:Sun, 19 Jun 2016 18:10:45 +1000
From:Tom Krieg <"tom krieg AT">
I have a query

SELECT adb_street.street_uid FROM adb_street
WHERE upper(adb_street.street_name) like 'THEOD%'

Table adb_street has about 1 million records, index on street name. 
Street_uid is the PK. This query takes about 200ms and returns 45 rows.

I have another query

SELECT * FROM adb_address_detail
WHERE adb_address_detail.street_uid IN
   (SELECT adb_street.street_uid FROM adb_street
    WHERE upper(adb_street.street_name) like 'THEOD%')

Table adb_address_detail has 14 million records and has an index on 
street_uid. This query takes a long time (10 - 30 seconds, depending on 
which way a butterfly in Amazonia flaps its wings).

Is there any way I can make this query faster?

The database has been normalised to within an inch of its life by an Aus 
government department. Theoretically according to all the rules (Denn 
Santoro would have approved) but in real life not so practical. The 
second query is the pseudocode for the following ...

FROM adb_address_detail a
   JOIN adb_street s ON a.street_uid = s.street_uid
   JOIN adb_locality lo ON a.locality_uid = lo.locality_uid
   JOIN adb_address_geocode ag ON a.address_detail_uid = 
   JOIN adb_state t ON lo.state_uid = t.state_uid
   LEFT JOIN __flat_type ft ON a.flat_type_code = ft.code
   LEFT JOIN __level_type lt ON a.level_type_code = lt.code
   LEFT JOIN __street_suffix ss ON s.street_suffix_code = ss.code
   LEFT JOIN __street_class sc ON s.street_class_code = sc.code
   LEFT JOIN __street_type st ON s.street_type_code = st.code
   LEFT JOIN __geocode_type gt ON ag.geocode_type_code = gt.code
WHERE a.street_uid IN
   (SELECT adb_street.street_uid FROM adb_street
    WHERE upper(adb_street.street_name) like 'HEREF%');

(Those tables starting with __ are reference tables, i.e. providing a 
full description for a code, for e.g. St = Street or URHSE = Urban 
House, RUHSE = Rural House. These tables consist of 2 fields.)

If it takes this long to do a query, what's going to happen when I have 
to do geometry calculations on the geocodes, like select all the 
addresses within a certain distance of a selected address? Would it be 
better to create some denormalised tables for queries? (The database is 
updated every 6 months in bulk so I wouldn't need to worry about user 

To send me an email, remove the spaces.

Copyright © 2004