Subject: | Not quie sure how to do this
| Date: | Sun, 19 Jun 2016 18:10:45 +1000
| From: | Tom Krieg <"tom krieg AT outlook.com">
| Newsgroups: | pnews.paradox-client_server
|
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 ...
SELECT <LIST OF FIELDS ... >
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 =
ag.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
changes).
TIA
--
To send me an email, remove the spaces.
|